8.1 Database Concepts

Limitations of a File-Based Approach

1. Data Redundancy and Inconsistency: Data is duplicated across multiple files, leading to inconsistency and increased storage requirements.

2. Data Isolation: Retrieving related data spread across multiple files can be challenging.

3. Limited Data Integrity: Maintaining rules to ensure data accuracy (like foreign keys) is hard to enforce.

4. Concurrency Issues: File-based systems struggle with multiple users accessing data simultaneously.

5. Security and Access Control: Difficulties arise in implementing robust access controls for sensitive data.

Features of a Relational Database Addressing File-Based Limitations

1. Data Consistency: By centralizing data, relational databases reduce redundancy.

2. Data Integrity: Constraints like primary keys and foreign keys maintain data accuracy.

3. Efficient Data Access: SQL enables complex querying, reducing the difficulty of retrieving related data.

4. Concurrency Control: Databases can handle multiple users with mechanisms to manage simultaneous access.

5. Enhanced Security: Access controls at the table or even column level help secure data.

Relational Database Terminology

• Table (Relation): A collection of rows and columns representing an entity.

• Field (Attribute): A column in a table.

• Record (Tuple): A single row in a table.

• Primary Key: A unique identifier for records in a table.

• Foreign Key: A field linking to a primary key in another table, ensuring referential integrity.

Entity-Relationship (E-R) Diagrams

1. Entities: Represent tables in the database.

2. Attributes: Represent columns in the table.

3. Relationships: Show how entities relate to each other, often with cardinality notations (e.g., one-to-many).

Normalisation Process

1. First Normal Form (1NF): Ensures data is atomic, with each field containing a single value.

2. Second Normal Form (2NF): Removes partial dependencies for tables with composite keys.

3. Third Normal Form (3NF): Eliminates transitive dependencies, ensuring each attribute depends only on the primary key.

Third Normal Form (3NF) Validation

1. No Partial Dependencies: For tables with a composite primary key, each attribute depends on the full primary key.

2. No Transitive Dependencies: Attributes depend directly on the primary key, not other non-key attributes.

Producing a Normalized Database Design

1. Identify and correct redundancies.

2. Apply normalization steps (1NF to 3NF) to create a design that organizes data logically and consistently.

8.2 Database Management Systems (DBMS)

Features of a DBMS Addressing File-Based Issues

1. Data Independence: Separates data storage from application logic.

2. Efficient Query Processing: Optimizes retrieval and manipulation of data.

3. Transaction Management: Ensures that multiple operations complete successfully together.

4. Backup and Recovery: Helps restore data in case of system failures.

5. Access Control: Manages permissions for users at various levels of access.

Practical Use of DBMS Tools

1. Query Tools: Simplify data retrieval with SQL.

2. Form and Report Generators: Provide user-friendly data input and output interfaces.

3. Backup Utilities: Automate data backup and restore processes.

4. Security Management Tools: Help assign roles and permissions to protect data.

8.3 Data Definition Language (DDL) and Data Manipulation Language (DML)

DDL and DML in DBMS

• DDL (Data Definition Language): Manages database structure with statements like CREATE, ALTER, and DROP.

• DML (Data Manipulation Language): Manages data within tables using commands like SELECT, INSERT, UPDATE, and DELETE.

SQL as the Industry Standard

Writing Basic SQL (DDL) Statements

• Create Table: CREATE TABLE Student (StudentID INT, Name VARCHAR(50), Age INT);

• Alter Table: ALTER TABLE Student ADD Address VARCHAR(100);

• Drop Table: DROP TABLE Student;

Writing SQL Scripts to Query or Modify Data (DML)

• Select Query: SELECT Name, Age FROM Student WHERE Age > 18;

• Insert Data: INSERT INTO Student (StudentID, Name, Age) VALUES (1, 'Alice', 20);

• Update Data: UPDATE Student SET Age = 21 WHERE StudentID = 1;