Managing and retrieving information

# Managing and retrieving information ## Objectifs et prérequis This chapter aims to equip Upper Sixth students with a comprehensive understanding of how information is managed and retrieved in computer systems, particularly through database technologies. Students will learn to design, implement, and query relational databases using industry-standard models and SQL. By the end of this chapter, they will be able to analyse data requirements, construct entity-relationship diagrams, normalise databases, enforce integrity constraints, and write complex SQL queries involving joins and aggregate functions. Prerequisites: Basic understanding of data types, file handling, and introductory programming concepts (e.g., variables, loops, conditionals). Familiarity with logical reasoning and set theory is advantageous. ## Introduction In modern information systems, data is a critical asset. Efficient management and retrieval of data require structured approaches that ensure accuracy, consistency, and accessibility. Databases provide such structure through organised storage, controlled access, and powerful query mechanisms. This chapter explores the theoretical foundations and practical implementation of database systems, progressing from basic concepts to advanced design and querying techniques. Emphasis is placed on the relational model due to its dominance in real-world applications. Students will engage with both conceptual design (ER modelling) and physical implementation (SQL, normalisation), culminating in integrated activities that simulate real-world database development. ## I. Database Fundamentals and Data Organisation ### Definition and Purpose A database is an organised collection of related data stored and accessed electronically. Its purpose is to enable efficient storage, retrieval, updating, and management of data while minimising redundancy and ensuring integrity. > **To retain**: A database management system (DBMS) is software that interacts with users, applications, and the database itself to capture and analyse data. ### Data Organisation Data can be organised in various ways: - **File-based systems**: Data stored in isolated files (e.g., flat files). Prone to duplication, inconsistency, and difficult sharing. - **Database approach**: Centralised, structured, and shared data managed by a DBMS. Enables data independence, reduced redundancy, and improved integrity. ### Characteristics of Databases - **Persistence**: Data remains stored after the application terminates. - **Shared access**: Multiple users can access data concurrently. - **Data independence**: Logical and physical data can be changed independently. - **Integrity enforcement**: Rules ensure data accuracy and consistency. - **Security**: Access controls protect sensitive information. ### Solved Example A school maintains student records in separate Excel files for each class. This leads to duplicate entries for students who change classes and inconsistent contact information. **Solution**: Migrate to a centralised database where student data is stored once and linked to class enrolments via relationships. ### Application Exercise **Problem**: A small business stores customer orders in text files, one per day. Describe two problems with this approach and how a database would solve them. **Solution**: 1. **Problem**: Difficulty in generating reports across multiple days (e.g., monthly sales). **Solution**: Database allows querying across all records using SQL. 2. **Problem**: Inconsistent customer addresses if updated in one file but not others. **Solution**: Centralised customer table ensures one source of truth; updates propagate automatically. ## II. Database Models ### Overview Database models define how data is structured, stored, and manipulated. The main models are hierarchical, network, relational, and object-oriented. ### Hierarchical Model Data organised in a tree-like structure with parent-child relationships. Each child has only one parent. **Limitation**: Inflexible for many-to-many relationships; requires navigating through parents to access children. ### Network Model Extends hierarchical model by allowing a child to have multiple parents (via sets). Uses pointers for navigation. **Advantage**: Handles complex relationships better than hierarchical. **Limitation**: Complex to design and maintain; navigational access is cumbersome. ### Relational Model Data stored in tables (relations) with rows (tuples) and columns (attributes). Based on set theory and predicate logic. **Advantages**: Simple, flexible, supports ad-hoc queries, data independence, strong theoretical foundation. **Dominates** modern DBMS due to usability and tool support. ### Object-Oriented Model Integrates object-oriented programming concepts (encapsulation, inheritance, polymorphism) with database capabilities. Stores objects directly. **Use case**: Complex data like multimedia, CAD/CAM, scientific applications. ### Solved Example Compare storing an employee’s department in hierarchical vs. relational model. **Hierarchical**: Employee is a child of Department node. To find all employees in Sales, traverse the Sales branch. **Relational**: Employee table has a DeptID attribute referencing Department table. Query: `SELECT * FROM Employee WHERE DeptID = (SELECT DeptID FROM Department WHERE Name = 'Sales')`. ### Application Exercise **Problem**: Why is the relational model preferred over hierarchical for a university course registration system? **Solution**: Students can enroll in multiple courses (many-to-many), and courses can have multiple students. Hierarchical model cannot efficiently represent this without data duplication or complex restructuring. Relational model uses a junction table (Enrollment) to link Student and Course. ## III. Relational Database Models ### Formal Definition A relation is a subset of the Cartesian product of its domains. It consists of: - **Attributes**: Named columns (e.g., StudentID, Name). - **Tuples**: Rows representing individual entities. - **Domain**: Set of allowable values for an attribute. - **Degree**: Number of attributes. - **Cardinality**: Number of tuples. ### Properties of Relations 1. **Atomicity**: Each cell contains a single indivisible value. 2. **No duplicate tuples**: Each row is unique. 3. **Order insignificance**: Tuples and attributes have no inherent order. 4. **Column homogeneity**: All values in a column come from the same domain. ### Keys - **Superkey**: Set of attributes that uniquely identifies a tuple. - **Candidate key**: Minimal superkey (no subset is a superkey). - **Primary key**: Selected candidate key used for unique identification. - **Foreign key**: Attribute(s) referencing the primary key of another relation. ### Integrity Constraints - **Entity integrity**: Primary key cannot be NULL. - **Referential integrity**: Foreign key must match a primary key in the referenced table or be NULL. - **Domain integrity**: Values must conform to defined domain (type, range, format). ### Solved Example Given relation `Student(StudentID, Name, DOB, DeptID)`: - Candidate keys: {StudentID} (assuming unique), {Name, DOB} (if no two students share name and birthdate). - Primary key: {StudentID} (chosen for simplicity). - Foreign key: DeptID references Department(DeptID). ### Application Exercise **Problem**: In relation `Order(OrderID, CustomerID, ProductID, Quantity, OrderDate)`, identify: a) All possible superkeys b) Candidate keys c) Primary key choice **Solution**: a) Superkeys: Any set containing OrderID (e.g., {OrderID}, {OrderID, CustomerID}, {OrderID, ProductID, Quantity}) b) Candidate key: {OrderID} (minimal superkey) c) Primary key: {OrderID} (standard choice) ## IV. Database Architecture ### Three-Schema Architecture Provides data independence by separating user views from physical storage. 1. **External Schema**: User-specific views (what users see). 2. **Conceptual Schema**: Global logical view of entire database (independent of hardware and software). 3. **Internal Schema**: Physical storage details (file organisation, indexing, storage media). ### Mappings - **Conceptual/External**: Maps user views to conceptual schema (logical data independence). - **Internal/Conceptual**: Maps conceptual schema to internal storage (physical data independence). ### Advantages - **Logical data independence**: Changes to conceptual schema do not affect external schemas/applications. - **Physical data independence**: Changes to internal schema do not affect conceptual/external schemas. - **Security**: External schemas can restrict access to authorised data. - **Data abstraction**: Users interact with simplified views. ### Solved Example A university adds a new attribute `Email` to the `Student` table in the conceptual schema. **Effect**: - External schema for faculty (showing Name, DeptID) remains unchanged → logical data independence. - If storage shifts from heap file to B+ tree index, no change to conceptual schema → physical data independence. ### Application Exercise **Problem**: Explain how the three-schema architecture supports data independence when a DBMS upgrades its storage system from magnetic disks to SSDs. **Solution**: The upgrade affects only the internal schema (storage mechanism). The conceptual schema (logical structure) and external schemas (user views) remain unchanged due to the internal/conceptual mapping, ensuring physical data independence. ## V. Entity-Relationship Modelling ### Purpose ER modelling is a conceptual data modelling technique used to represent data requirements and relationships in a database before logical design. ### Components - **Entities**: Real-world objects (e.g., Student, Course). Represented by rectangles. - **Attributes**: Properties of entities (e.g., StudentID, Name). Represented by ovals. - **Relationships**: Associations between entities (e.g., Enrolls). Represented by diamonds. - **Cardinality**: Specifies number of instances of one entity associated with another (1:1, 1:N, M:N). - **Participation**: Whether entity participation is total (double line) or partial (single line). ### Weak Entities An entity that cannot be uniquely identified by its own attributes and depends on a strong entity (owner). Represented by a double rectangle. Has a partial key (discriminator). ### Strong vs. Weak Entity Example - **Strong**: `Course(CourseID, Title, Credits)` — CourseID uniquely identifies. - **Weak**: `Section(SectionNo, Day, Time, CourseID)` — SectionNo alone not unique; needs CourseID to identify a specific section. Partial key: {SectionNo, Day, Time}. ### Solved Example Model a library system: Books, Authors, Borrowers, Loans. - **Entities**: Book (ISBN, Title), Author (AuthorID, Name), Borrower (BorrowerID, Name), Loan (LoanID, DateOut, DateDue). - **Relationships**: - Writes (Author, Book): M:N - Borrows (Borrower, Loan): 1:N (one borrower can have many loans) - Contains (Book, Loan): 1:N (one book can be in many loans over time, but only one loan at a time if copy-specific — assume copyID added) *Refinement*: Add `Copy(CopyID, ISBN, Status)` and link Loan to Copy. ### Application Exercise **Problem**: Draw an ER diagram for a hospital system where Patients are treated by Doctors in Departments. Each Patient may have multiple Visits. Each Visit is assigned to one Doctor and occurs in one Department. **Solution**: - Entities: Patient(PatientID, Name, DOB), Doctor(DoctorID, Name, Specialty), Department(DeptID, Name), Visit(VisitID, Date, Time) - Relationships: - Treats (Patient, Visit): 1:N - Assigns (Doctor, Visit): 1:N - LocatedIn (Visit, Department): 1:N - Participation: Patient (partial in Treats), Visit (total in all three), Doctor (partial in Assigns), Department (partial in LocatedIn) ## VI. Relational Database Design and Relationships ### Mapping ER to Relational Rules for converting ER diagrams to relational schema: 1. **Strong entity**: Create relation with attributes; primary key = entity’s key. 2. **Weak entity**: Create relation with attributes + foreign key to owner’s PK; PK = partial key + owner’s PK. 3. **1:1 relationship**: Add foreign key in either relation (prefer total participation side) or merge. 4. **1:N relationship**: Add foreign key of “one” side in “many” side relation. 5. **M:N relationship**: Create new relation with foreign keys to both entities; PK = combination of both FKs. ### Example Mapping ER: Student(StudentID, Name) — (Enrolls, M:N) — Course(CourseID, Title) Relational: - Student(StudentID, Name) PK: StudentID - Course(CourseID, Title) PK: CourseID - Enrollment(StudentID, CourseID, Grade) PK: (StudentID, CourseID); FKs: StudentID→Student, CourseID→Course ### Application Exercise **Problem**: Map the following ER to relational schema: Entity: Manager(MgrID, Name) Entity: Department(DeptID, Name, Location) Relationship: Manages (1:1) — a Manager manages one Department, a Department has one Manager. **Solution**: - Manager(MgrID, Name) PK: MgrID - Department(DeptID, Name, Location, MgrID) PK: DeptID; FK: MgrID→Manager(MgrID) *(Alternative: add DeptID to Manager; both valid. Choose based on which side has total participation — if every Manager must manage a Dept, put FK in Manager.)* ## VII. Database Normalisation and Functional Dependencies ### Functional Dependency (FD) An FD X → Y means that for each value of X, there is exactly one associated value of Y in a relation. Notation: X functionally determines Y. ### Types of FDs - **Trivial**: Y ⊆ X (always true). - **Non-trivial**: Y ⊈ X. - **Transitive**: If X → Y and Y → Z, then X → Z. ### Normal Forms Goal: Reduce redundancy, avoid update/delete/insertion anomalies. #### 1NF - All attributes contain atomic values. - No repeating groups. #### 2NF - Relation is in 1NF. - No partial dependency: No non-prime attribute is functionally dependent on a proper subset of any candidate key. #### 3NF - Relation is in 2NF. - No transitive dependency: No non-prime attribute is functionally dependent on another non-prime attribute. #### BCNF (Boyce-Codd NF) - For every non-trivial FD X → Y, X is a superkey. - Stronger than 3NF; eliminates all anomalies due to FDs. ### Solved Example Relation: `Order(OrderID, ProductID, ProductName, Quantity, Price)` Assume: - FD1: OrderID, ProductID → Quantity, Price - FD2: ProductID → ProductName **Analysis**: - Candidate key: {OrderID, ProductID} - FD2: ProductID → ProductName → ProductID is not a superkey (only part of key) → violates 2NF (partial dependency of ProductName on ProductID). **Decomposition to 2NF**: - R1(OrderID, ProductID, Quantity, Price) PK: (OrderID, ProductID) - R2(ProductID, ProductName) PK: ProductID Check R1: FD1 holds; no proper subset of PK determines non-prime attributes → 2NF. Check R2: Only FD is ProductID → ProductName; ProductID is key → 2NF. Now check 3NF/BCNF: - R1: Only FD is PK → non-prime; no transitive FDs → 3NF and BCNF. - R2: Same → 3NF and BCNF. ### Application Exercise **Problem**: Normalise the relation `Employee(EmpID, Name, DeptID, DeptName, Location)` with FDs: EmpID → DeptID, DeptID → DeptName, DeptName → Location. **Solution**: - Candidate key: {EmpID} - FD1: EmpID → DeptID → OK (EmpID is key) - FD2: DeptID → DeptName → DeptID not superkey → violates 2NF? Wait: DeptID → DeptName, but DeptID is not a subset of PK (EmpID is single attribute) → no partial dependency possible. However, transitive dependency: EmpID → DeptID → DeptName → Location. - Since EmpID → DeptID and DeptID → DeptName, then EmpID → DeptName (transitive). Similarly, EmpID → Location. - Non-prime attributes: DeptID, DeptName, Location all depend transitively on EmpID via DeptID. → Violates 3NF. **Decomposition**: - R1(EmpID, Name, DeptID) PK: EmpID; FD: EmpID → DeptID - R2(DeptID, DeptName, Location) PK: DeptID; FDs: DeptID → DeptName, DeptName → Location Check R2: DeptID → DeptName → OK (key); DeptName → Location → DeptName not superkey → violates 3NF. Decompose R2: - R2a(DeptID, DeptName) PK: DeptID - R2b(DeptName, Location) PK: DeptName Final: - Employee(EmpID, Name, DeptID) - Department(DeptID, DeptName) - Location(DeptName, Location) *Note: Better to have DeptID → Location directly if Location depends on DeptID. Assuming DeptName → Location is given, this is correct.* ## VIII. Database Design Stages and Data Modelling ### Stages of Database Design 1. **Requirements Collection and Analysis**: Interview stakeholders, study forms/reports, identify data needs. 2. **Conceptual Design**: Develop high-level model (ER diagram) independent of DBMS. 3. **Logical Design**: Convert conceptual model to logical schema (e.g., relational) based on target DBMS. 4. **Physical Design**: Specify storage structures, file organisation, indexing, access paths. 5. **Implementation**: Create database using DDL, load data, apply constraints. 6. **Testing and Evaluation**: Validate against requirements, performance testing. 7. **Operation and Maintenance**: Monitor, tune, backup, recover, evolve. ### Data Modelling Involves creating abstract models that represent data aspects of an organisation. ER modelling is a key technique. Other models include UML class diagrams (for object-oriented approach). ### Solved Example A bookstore wants to track sales. **Requirements**: Store book details (ISBN, title, author, price), customer info (ID, name, email), sales transactions (date, quantity, total). **Conceptual**: ER diagram with Book, Customer, Sale; relationships: Sale contains Book (M:N via line item), Customer makes Sale (1:N). **Logical**: - Book(ISBN, Title, Author, Price) - Customer(CustID, Name, Email) - Sale(SaleID, CustID, Date, Total) - LineItem(SaleID, ISBN, Quantity, Price) — PK: (SaleID, ISBN); FKs to Sale and Book **Physical**: Choose indexing (e.g., B-tree on Sale.Date, hash on ISBN), storage engine (InnoDB), partitioning by year. ### Application Exercise **Problem**: List the stages of database design and explain why physical design comes after logical design. **Solution**: Stages: Requirements → Conceptual → Logical → Physical → Implementation → Testing → Maintenance. Physical design depends on logical schema (tables, attributes, keys) to decide storage structures (e.g., which columns to index, file organisation). Without a logical model, physical decisions lack basis. Logical design is DBMS-independent; physical design tailors it to a specific system (e.g., MySQL vs. Oracle). ## IX. Database Integrity ### Types of Integrity 1. **Entity Integrity**: Primary key cannot be NULL. Ensures each tuple is uniquely identifiable. 2. **Referential Integrity**: Foreign key must reference an existing primary key value or be NULL. Prevents orphaned records. 3. **Domain Integrity**: Attribute values must conform to defined domain (data type, format, range, constraints). 4. **User-defined Integrity**: Custom rules based on organisational policies (e.g., salary > 0, age ≥ 18). ### Enforcement Mechanisms - **Constraints**: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT. - **Triggers**: Stored procedures that automatically execute on INSERT/UPDATE/DELETE. - **Application Logic**: Validation in frontend/middleware (less reliable; should be backed by DB constraints). ### Solved Example Table: `Product(ProductID, Name, Price, Stock)` Constraints: - ProductID PRIMARY KEY → NOT NULL, unique - Price DECIMAL(10,2) CHECK (Price > 0) - Stock INT CHECK (Stock >= 0) - Name VARCHAR(50) NOT NULL **Scenario**: Try to insert `(NULL, 'Pen', -5.0, 10)` → Fails: ProductID NULL (entity integrity), Price negative (domain integrity). Try: `(1, 'Pen', 5.0, -2)` → Fails: Stock negative. Try: `(2, NULL, 10.0, 5)` → Fails: Name NULL. ### Application Exercise **Problem**: What constraint prevents a customer from being deleted if they have existing orders? **Solution**: A foreign key constraint from Order(CustomerID) referencing Customer(CustomerID) with RESTRICT or NO ACTION on DELETE. This enforces referential integrity by blocking deletion of a customer with dependent orders. ## X. Integration Activity ### Activity: Designing a Library Management System **Scenario**: Design a database for a local library to manage books, members, and loans. **Requirements**: - Books: ISBN, title, author, publication year, genre, available copies. - Members: member ID, name, email, phone, join date, membership type. - Loans: loan ID, book ISBN, member ID, loan date, due date, return date. - Rules: - A book can be loaned to one member at a time. - A member can have multiple active loans. - Loan period is 14 days; fine applies if returned late. - ISBN is unique per book edition. **Steps**: 1. **Conceptual Design (ER Diagram)**: - Entities: Book(ISBN, Title, Author, Year, Genre, Copies), Member(MemberID, Name, Email, Phone, JoinDate, Type), Loan(LoanID, DateOut, DueDate, DateReturned) - Relationships: - Borrows (Member, Loan): 1:N - Involves (Book, Loan): 1:N (assuming one copy per loan; if tracking copies, add Copy entity) - *Refinement*: To track individual copies, add Copy(CopyID, ISBN, Status) and link Loan to Copy. - Final ER: - Book(ISBN, Title, Author, Year, Genre) - Copy(CopyID, ISBN, Status) — weak entity? ISBN not unique per copy; CopyID is PK, ISBN FK to Book. - Member(MemberID, Name, Email, Phone, JoinDate, Type) - Loan(LoanID, CopyID, MemberID, DateOut, DueDate, DateReturned) 2. **Logical Design (Relational Schema)**: - Book(ISBN, Title, Author, Year, Genre) PK: ISBN - Copy(CopyID, ISBN, Status) PK: CopyID; FK: ISBN→Book(ISBN) - Member(MemberID, Name, Email, Phone, JoinDate, Type) PK: MemberID - Loan(LoanID, CopyID, MemberID, DateOut, DueDate, DateReturned) PK: LoanID; FKs: CopyID→Copy(CopyID), MemberID→Member(MemberID) 3. **Normalisation Check**: - All tables in BCNF: - Book: Only FD is ISBN → others; ISBN is key. - Copy: CopyID → ISBN, Status; CopyID is key. - Member: MemberID → others; key. - Loan: LoanID → others; CopyID and MemberID are FKs but not determinants of non-FK attributes (DateOut etc. depend on LoanID). No FDs among non-key attributes → BCNF. 4. **Integrity Constraints**: - Copy.Status CHECK (Status IN ('Available', 'On Loan', 'Maintenance')) - Loan.DateOut NOT NULL - Loan.DueDate > Loan.DateOut (via CHECK or trigger) - Loan.DateReturned >= Loan.DateOut (if not NULL) - Unique constraint: (CopyID, DateOut) where DateReturned IS NULL → ensures a copy is not loaned twice simultaneously. (Implemented via trigger or application logic if DB doesn’t support partial indexes.) 5. **SQL Implementation (DDL)**: ```sql CREATE TABLE Book ( ISBN VARCHAR(13) PRIMARY KEY, Title VARCHAR(100) NOT NULL, Author VARCHAR(50) NOT NULL, Year INT, Genre VARCHAR(30) ); CREATE TABLE Copy ( CopyID INT PRIMARY KEY, ISBN VARCHAR(13) NOT NULL, Status VARCHAR(20) NOT NULL CHECK (Status IN ('Available', 'On Loan', 'Maintenance')), FOREIGN KEY (ISBN) REFERENCES Book(ISBN) ); CREATE TABLE Member ( MemberID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, Email VARCHAR(100) UNIQUE, Phone VARCHAR(20), JoinDate DATE NOT NULL, Type VARCHAR(20) ); CREATE TABLE Loan ( LoanID INT PRIMARY KEY, CopyID INT NOT NULL, MemberID INT NOT NULL, DateOut DATE NOT NULL, DueDate DATE NOT NULL, DateReturned DATE, FOREIGN KEY (CopyID) REFERENCES Copy(CopyID), FOREIGN KEY (MemberID) REFERENCES Member(MemberID), CHECK (DueDate > DateOut), CHECK (DateReturned IS NULL OR DateReturned >= DateOut) ); ``` **Note**: To prevent simultaneous loans on same copy, add: `CREATE UNIQUE INDEX idx_loan_copy ON Loan(CopyID) WHERE DateReturned IS NULL;` *(Syntax varies by DBMS; concept: unique index on CopyID for active loans.)* ### Application Exercise **Problem**: In the library system, why is it better to model Copy as a separate entity rather than storing available copies as an attribute in Book? **Solution**: Storing `AvailableCopies` in Book leads to update anomalies: if two loans occur concurrently, the count may become inaccurate without locking. Modelling Copy as individual entities allows tracking each physical item’s status precisely, supports historical data (e.g., which copy was borrowed when), and avoids concurrency issues. It also enables maintenance tracking per copy. ## XI. SQL - Data Definition Language (DDL) ### Purpose DDL defines the database schema: creates, alters, and drops database objects (tables, indexes, views). ### Key Commands - **CREATE**: Defines new objects. - **ALTER**: Modifies existing objects. - **DROP**: Deletes objects. - **TRUNCATE**: Removes all rows from a table (keeps structure). ### Syntax Examples ```sql CREATE TABLE Student ( StudentID INT PRIMARY KEY, Name VARCHAR(50) NOT NULL, DOB DATE, DeptID INT, FOREIGN KEY (DeptID) REFERENCES Department(DeptID) ); ALTER TABLE Student ADD Email VARCHAR(100); DROP TABLE IF EXISTS OldTable; TRUNCATE TABLE Logs; -- faster than DELETE for clearing all rows ``` ### Constraints in DDL - **PRIMARY KEY**: Uniquely identifies rows; implies NOT NULL and unique. - **FOREIGN KEY**: Enforces referential integrity. - **UNIQUE**: Ensures no duplicate values (can be NULL unless combined with NOT NULL). - **CHECK**: Validates data against a condition. - **DEFAULT**: Provides a value if none specified. - **NOT NULL**: Prevents NULL values. ### Solved Example Create a table for exam results with constraints: - Each student (ID) can have one result per subject. - Score must be between 0 and 100. - Subject must be 'Math', 'English', or 'Science'. ```sql CREATE TABLE ExamResult ( StudentID INT, Subject VARCHAR(20), Score INT, PRIMARY KEY (StudentID, Subject), CHECK (Score BETWEEN 0 AND 100), CHECK (Subject IN ('Math', 'English', 'Science')) ); ``` ### Application Exercise **Problem**: Write DDL to create a table `Flight(FlightNo, Origin, Destination, DepartureTime, ArrivalTime, Price)` with: - FlightNo as primary key. - DepartureTime before ArrivalTime. - Price > 0. - Origin and Destination cannot be the same. **Solution**: ```sql CREATE TABLE Flight ( FlightNo VARCHAR(10) PRIMARY KEY, Origin VARCHAR(50) NOT NULL, Destination VARCHAR(50) NOT NULL, DepartureTime DATETIME NOT NULL, ArrivalTime DATETIME NOT NULL, Price DECIMAL(10,2) NOT NULL CHECK (Price > 0), CHECK (DepartureTime < ArrivalTime), CHECK (Origin <> Destination) ); ``` ## XII. SQL – Data Manipulation Language (DML) ### Purpose DML manages data within tables: inserts, updates, and deletes data. ### Key Commands - **INSERT**: Adds new rows. - **UPDATE**: Modifies existing rows. - **DELETE**: Removes rows. - **SELECT**: Retrieves data (also part of DQL, but often grouped here). ### Syntax ```sql INSERT INTO TableName (col1, col2, ...) VALUES (val1, val2, ...); UPDATE TableName SET col1 = val1, col2 = val2 WHERE condition; DELETE FROM TableName WHERE condition; ``` ### Solved Example Table: `Employee(EmpID, Name, Salary, DeptID)` **Insert**: Add new employee ```sql INSERT INTO Employee (EmpID, Name, Salary, DeptID) VALUES (101, 'Alice', 50000, 5); ``` **Update**: Give 10% raise to employees in DeptID 5 ```sql UPDATE Employee SET Salary = Salary * 1.10 WHERE DeptID = 5; ``` **Delete**: Remove employees with salary < 20000 ```sql DELETE FROM Employee WHERE Salary < 20000; ``` ### Application Exercise **Problem**: In the `Loan` table from the library system, write SQL to: a) Record a new loan of copy 5 to member 3 today, due in 14 days. b) Mark loan 10 as returned today. c) Delete all loans returned more than 2 years ago. **Solution**: a) ```sql INSERT INTO Loan (LoanID, CopyID, MemberID, DateOut, DueDate) VALUES (201, 5, 3, CURDATE(), DATE_ADD(CURDATE(), INTERVAL 14 DAY)); ``` *(Assuming MySQL; use GETDATE() etc. for other DBMS)* b) ```sql UPDATE Loan SET DateReturned = CURDATE() WHERE LoanID = 10; ``` c) ```sql DELETE FROM Loan WHERE DateReturned IS NOT NULL AND DateReturned < DATE_SUB(CURDATE(), INTERVAL 2 YEAR); ``` ## XIII. SQL - Data Query Language (DQL) ### Purpose DQL retrieves data from the database using the `SELECT` statement. It is the most frequently used SQL subset. ### SELECT Syntax ```sql SELECT [DISTINCT] column1, column2, ... FROM table1 [, table2 ...] [WHERE condition] [GROUP BY column(s)] [HAVING condition] [ORDER BY column(s) [ASC|DESC]]; ``` ### Key Clauses - **DISTINCT**: Eliminates duplicate rows. - **WHERE**: Filters rows before grouping. - **GROUP BY**: Groups rows for aggregate functions. - **HAVING**: Filters groups after aggregation. - **ORDER BY**: Sorts result set. ### Solved Example Table: `Product(ProductID, Name, Price, Category)` **Query**: Find average price per category, only for categories with average price > 50. ```sql SELECT Category, AVG(Price) AS AvgPrice FROM Product GROUP BY Category HAVING AVG(Price) > 50; ``` ### Application Exercise **Problem**: Using the `Student(StudentID, Name, DeptID, GPA)` table, write a query to: List names of students with GPA > 3.5, ordered by GPA descending, and show only the top 5. **Solution**: ```sql SELECT Name, GPA FROM Student WHERE GPA > 3.5 ORDER BY GPA DESC LIMIT 5; ``` *(Note: LIMIT is MySQL/PostgreSQL; use TOP 5 in SQL Server, FETCH FIRST 5 ROWS ONLY in standard SQL/Oracle 12c+)* ## XIV. SQL – Joins ### Purpose Joins combine rows from two or more tables based on a related column. ### Types of Joins - **INNER JOIN**: Returns rows with matching values in both tables. - **LEFT (OUTER) JOIN**: Returns all rows from left table, and matched rows from right; NULLs for non-matches. - **RIGHT (OUTER) JOIN**: Returns all rows from right table, and matched from left. - **FULL (OUTER) JOIN**: Returns all rows when there is a match in either table. - **CROSS JOIN**: Cartesian product (every row from first with every row from second). - **SELF JOIN**: Table joined to itself. ### Join Condition Specified using `ON` clause (preferred) or `WHERE` (legacy). ### Solved Example Tables: `Student(StudentID, Name, DeptID)` `Department(DeptID, DeptName)` **Query**: List each student’s name and their department name. ```sql SELECT Student.Name, Department.DeptName FROM Student INNER JOIN Department ON Student.DeptID = Department.DeptID; ``` *Result*: Only students with a valid DeptID appearing in Department table. **Left Join Example**: List all students, even those not assigned to a department. ```sql SELECT Student.Name, Department.DeptName FROM Student LEFT JOIN Department ON Student.DeptID = Department.DeptID; ``` *Result*: Students with NULL DeptName if DeptID is NULL or not in Department. ### Application Exercise **Problem**: Given `Order(OrderID, CustomerID, OrderDate)` and `Customer(CustomerID, Name, City)`, write a query to list all orders with customer name and city, including orders where customer info might be missing. **Solution**: ```sql SELECT Order.OrderID, Order.OrderDate, Customer.Name, Customer.City FROM Order LEFT JOIN Customer ON Order.CustomerID = Customer.CustomerID; ``` *Rationale*: Ensures all orders appear; if CustomerID is invalid or NULL, Name and City will be NULL. ## XV. Aggregate Functions ### Purpose Aggregate functions perform a calculation on a set of values and return a single scalar value. Used often with `GROUP BY`. ### Common Functions - **COUNT()**: Number of rows (COUNT(*) counts all rows; COUNT(column) counts non-NULL values). - **SUM()**: Total of numeric values. - **AVG()**: Average of numeric values. - **MIN()**: Minimum value. - **MAX()**: Maximum value. ### Solved Example Table: `Sales(SaleID, ProductID, Quantity, Price)` **Query**: Total revenue per product. ```sql SELECT ProductID, SUM(Quantity * Price) AS Revenue FROM Sales GROUP BY ProductID; ``` ### Application Exercise **Problem**: In the `ExamResult(StudentID, Subject, Score)` table, write a query to find the number of students who scored above 80 in each subject. **Solution**: ```sql SELECT Subject, COUNT(*) AS NumStudents FROM ExamResult WHERE Score > 80 GROUP BY Subject; ``` *Note: COUNT(*) counts rows meeting the WHERE condition per group.* ## XVI. RDBMS Packages and Database Implementation ### Overview Relational Database Management Systems (RDBMS) are software systems that enable users to define, create, maintain, and control access to relational databases. ### Popular RDBMS - **MySQL**: Open-source, widely used in web applications (LAMP stack). Supports SQL, ACID transactions, replication. - **PostgreSQL**: Open-source, advanced features (JSONB, GIS, full-text search), strong standards compliance. - **Microsoft SQL Server**: Enterprise-focused, integrates with .NET, strong BI tools, Windows-centric. - **Oracle Database**: Commercial leader, high scalability, advanced security, partitioning, used in large enterprises. - **SQLite**: Embedded, zero-configuration, file-based, used in mobile/apps. ### Implementation Considerations - **Hardware**: CPU, RAM, storage (SSD vs HDD), network. - **Software**: OS, RDBMS version, drivers. - **Configuration**: Memory allocation (buffer pool), connection limits, logging. - **Security**: User authentication, role-based access control (RBAC), encryption (at rest/in transit). - **Backup & Recovery**: Regular backups, point-in-time recovery, transaction logs. - **Performance Tuning**: Indexing, query optimisation, partitioning, caching. ### Solved Example A school chooses MySQL for its student information system. **Justification**: - Cost-effective (open-source). - Sufficient for moderate scale (thousands of students). - Easy to administer and integrate with PHP/Python web apps. - Adequate security and backup tools available. *Not chosen*: Oracle (overkill, expensive), SQLite (not concurrent-user friendly), SQL Server (Windows license cost). ### Application Exercise **Problem**: A hospital needs a database for patient records requiring high security, audit trails, and support for complex queries on large datasets. Which RDBMS would be most suitable and why? **Solution**: **PostgreSQL** or **Oracle Database**. - PostgreSQL: Open-source, strong security (row-level security, auditing extensions), excellent SQL support, handles large data well, active community. - Oracle: Industry standard for healthcare, advanced auditing (Fine-Grained Auditing), robust security, scalability, but costly. *Choice depends on budget*: If open-source preferred and expertise available → PostgreSQL; if enterprise support critical → Oracle.