Database and Data Modelling (Znote)

8.1. File Based System

  • Data stored in discrete files, stored on computer, and can be accessed, altered or removed by the user.

Disadvantages of File Based System

  • No enforcing control on organization/structure of files
  • Data repeated in different files; manually change each
  • Sorting must be done manually or must write a program
  • Data may be in different formats; difficult to find and use
  • Impossible for it to be multi-user; chaotic
  • Security not sophisticated; users can access everything

8.2. Database Management Systems (DBMS)

  • Database: Collection of non-redundant interrelated data.
  • DBMS: Software programs that allow databases to be defined, constructed and manipulated.

Features of a DBMS

  • Data management: Data stored in relational databases - tables stored in secondary storage.
  • Data dictionary contains:
    • List of all files in the database.
    • Number of records in each file.
    • Names & types of each field.
  • Data modeling: Analysis of data objects used in database, identifying relationships among them.
  • Logical schema: Overall view of the entire database, includes entities, attributes, and relationships.
  • Data integrity: Entire block copied to user’s area when being changed, saved back when done.
  • Data security: Handles password allocation and verification, backups database automatically, controls what certain users view by access rights of individuals or groups of users.

Data change clash solutions

  • Open entire database in exclusive mode – impractical with several users.
  • Lock all records in the table being modified – one user changing a table, others can only read the table.
  • Lock record currently being edited – as someone changes something, others can only read the record.
  • User specifies no locks – software warns the user of simultaneous change, resolve manually.
  • Deadlock: Two locks at the same time, DBMS must recognize, one user must abort task.

Tools in a DBMS

  • Developer interface: Allows creating and manipulating database in SQL rather than graphically.
  • Query processor: Handles high-level queries. It parses, validates, optimizes, and compiles or interprets a query which results in the query plan.

8.3. Relational Database Modelling

  • Entity: Object/event which can be distinctly identified.
  • Table: Contains a group of related entities in rows and columns called an entity set.
  • Tuple: A row or a record in a relational database.
  • Attribute: A field or column in a relational database.
  • Primary key: Attribute or combination of them that uniquely defines each tuple in relation.
  • Candidate key: Attribute that can potentially be a primary key.
  • Foreign key: Attribute or combination of them that relates 2 different tables.
  • Referential integrity: Prevents users or applications from entering inconsistent data.
  • Secondary key: Candidate keys not chosen as the primary key.
  • Indexing: Creating a secondary key on an attribute to provide fast access when searching on that attribute; indexing data must be updated when table data changes.

8.4. Relational Design of a System

Normalization

  • 1st Normal Form (1NF): No repeating attribute or groups of attributes. Intersection of each tuple and attribute contains only one value.
    Example:
Num CustName City Country ProdID Description
005 Bill Jones London England 1 Table
005 Bill Jones London England 2 Desk
005 Bill Jones London England 3 Chair
008 Mary Hill Paris France 4 Desk
008 Mary Hill Paris France 7 Cupboard
014 Anne Smith New York USA 5 Cabinet
002 Tom Allen London England 7 Cupboard
002 Tom Allen London England 8 Desk
  • 2nd Normal Form (2NF): It is in 1NF and every non-primary key attribute is fully dependent on the primary key; all incomplete dependencies have been removed.
    Example:
Num CustName City Country
005 Bill Jones London England
008 Mary Hill Paris France
014 Anne Smith New York USA
002 Tom Allen London England
Num ProdID Description
005 1 Table
005 2 Desk
005 3 Chair
008 4 Desk
008 7 Cupboard
014 5 Cabinet
002 7 Cupboard
002 8 Desk
  • 3rd Normal Form (3NF): It is in 1NF and 2NF and all non-key elements are fully dependent on the primary key. No inter-dependencies between attributes. MANY-TO-MANY functions cannot be directly normalized to 3NF, must use a 2-step process.

8.5. Data Definition Language (DDL)

Creation/modification of the database structure using this language, written in SQL.

Creating a database

1
CREATE DATABASE <database-name>;

Creating a table

1
CREATE TABLE <table-name> (…);

Changing a table

1
ALTER TABLE <table-name>;

Adding a primary key

1
PRIMARY KEY (field);

Adding a foreign key

1
FOREIGN KEY (field) REFERENCES <table>(field);

Example

1
2
3
4
5
6
7
8
9
10
CREATE DATABASE 'Personnel.gdb';

CREATE TABLE Training
(
    EmpID INT NOT NULL,
    CourseTitle VARCHAR(30) NOT NULL,
    CourseDate DATE NOT NULL,
    PRIMARY KEY (EmpID, CourseDate),
    FOREIGN KEY (EmpID) REFERENCES Employee(EmpID)
);

8.6. Data Manipulation Language (DML)

Query and maintenance of data done using this language – written in SQL.

Queries

  • Creating a query:
1
2
3
SELECT <field-name>
FROM <table-name>
WHERE <search-condition>;

SQL Operators

Operator Meaning
= Equals to
> Greater than
< Less than
>= Greater than or equal to
<= Less than or equal to
<> Not equal to
IS NULL Check for null values

Sorting into ascending order

1
ORDER BY <field-name>;
1
GROUP BY <field-name>;

Joining fields of different tables

1
INNER JOIN;

Data Maintenance

  • Adding data to a table:
1
2
INSERT INTO <table-name>(field1, field2, field3)
VALUES (value1, value2, value3);
  • Deleting a record:
1
2
DELETE FROM <table-name>
WHERE <condition>;
  • Updating a field in a table:
1
2
3
UPDATE <table-name>
SET <field-name> = <value>
WHERE <condition>;

8.7. Database Design and Management

Database Design Process

  1. Requirement analysis: Identify what data needs to be stored and the relationships between that data.
  2. Conceptual design: Create a high-level design, typically using Entity-Relationship (ER) diagrams to model the data.
  3. Logical design: Transform the conceptual design into a database schema. This process involves selecting the relational model and applying normalization rules.
  4. Physical design: Define the actual structure of the database, focusing on file organization and indexing to improve performance.
  5. Implementation: Use Data Definition Language (DDL) commands to create the database and Data Manipulation Language (DML) commands to populate it.
  6. Testing and evaluation: Ensure that the database meets the requirements and functions efficiently.
  7. Maintenance: Regularly back up the database and update it to reflect changes in data requirements.

8.8. ER Diagrams (Entity-Relationship Diagrams)

  • Entity: Represented by a rectangle, it is a person, place, thing, or event for which data is collected.
  • Attribute: Represented by an oval, it is a property or characteristic of an entity.
  • Relationship: Represented by a diamond, it shows how two entities are related.

Example of ER Diagram

  • Entities: Employee, Department
  • Relationships: Employee works in a Department
  • Attributes:
    • Employee: EmpID (Primary Key), Name, DateOfHire
    • Department: DeptID (Primary Key), DepartmentName

Converting ER Diagrams to Tables

  1. Each entity becomes a table.
  2. Each attribute becomes a column in the corresponding table.
  3. Relationships are expressed through foreign keys.

8.9. Transaction Management and Concurrency Control

  • Transaction: A unit of work performed within a database that is treated in a coherent and reliable way. A transaction must satisfy four properties (ACID):
    • Atomicity: The entire transaction is completed, or none of it is.
    • Consistency: Transactions must leave the database in a consistent state.
    • Isolation: Transactions should not affect each other.
    • Durability: Once a transaction is committed, it will remain so even in the event of a system failure.

Concurrency Control

  • Ensures that multiple users can interact with the database without interfering with each other.
  • Techniques include:
    • Locking: Locks records or tables to prevent simultaneous access.
    • Optimistic concurrency: Assumes conflicts are rare and only checks for conflicts when committing.
    • Pessimistic concurrency: Locks data at the beginning of a transaction to prevent conflicts.

Deadlock

  • Occurs when two or more transactions are waiting for each other to release resources, creating a cycle of dependencies.
  • Deadlock prevention: Ensuring that transactions do not hold resources while waiting for other resources.
  • Deadlock detection: The system detects deadlocks and aborts one of the transactions to break the cycle.

8.10. Backup and Recovery

Backup

  • Regularly creating copies of the database to ensure that data can be restored in the event of corruption or loss.

Types of Backups

  1. Full backup: A complete copy of the entire database.
  2. Incremental backup: Only the changes made since the last backup are saved.
  3. Differential backup: Similar to an incremental backup but includes all changes since the last full backup.

Recovery

  • The process of restoring data from a backup after a failure.
  • Point-in-time recovery: Allows restoring the database to a specific point in time before a failure occurred.
  • Transaction log: A record of all changes made to the database, which can be used to recover from crashes.

8.11. SQL Performance Optimization

Common Techniques

  • Indexing: Create indexes on frequently searched fields to improve query performance.
  • Query optimization: Ensure that queries are written efficiently (e.g., using WHERE clauses to filter data).
  • Partitioning: Divide large tables into smaller, more manageable pieces to improve performance.
  • Denormalization: In some cases, denormalizing data (combining tables) can improve performance, though at the cost of data redundancy.

Query Optimization Example

1
2
3
4
SELECT EmployeeName, DepartmentName
FROM Employee
INNER JOIN Department ON Employee.DeptID = Department.DeptID
WHERE Employee.Salary > 50000;
  • The query can be optimized by ensuring the DeptID column is indexed.

8.12. Database Security

Key Aspects

  • Authentication: Ensuring that only authorized users can access the database.
  • Authorization: Defining what actions users can perform on the data (e.g., read, write, delete).
  • Encryption: Protecting sensitive data by encoding it.
  • Auditing: Keeping a log of database access and modifications for security and compliance purposes.

8.13. Big Data and NoSQL Databases

  • Big Data: Refers to large and complex datasets that traditional databases cannot handle efficiently.
  • NoSQL: A class of database systems that do not follow the relational model and are designed to handle unstructured data.
    • Key-value stores: Store data as key-value pairs.
    • Document databases: Store data as documents (e.g., JSON, XML).
    • Graph databases: Store data as nodes and relationships, suitable for applications like social networks.
    • Column-family stores: Organize data into columns, rather than rows, allowing for faster access to certain types of data.

8.14. Conclusion

Databases are essential for managing large amounts of data efficiently and securely. The relational model remains widely used, but emerging technologies like MySQL offer new ways to handle the growing demands of big data. Effective database management involves understanding design principles, maintaining data integrity, ensuring security, and optimizing performance.