Database Concepts

Database: • A database is an organised, structured collection of related data stored electronically so that it can be accessed, managed, and updated efficiently. • A Database Management System (DBMS) is software that manages databases — it provides tools to create, read, update, and delete data. • Examples of DBMS: MySQL, Oracle, PostgreSQL, SQLite, Microsoft SQL Server. File System (Traditional Approach) vs. DBMS: Problems with File System: 1. Data Redundancy: Same data stored in multiple files → wastes storage space. 2. Data Inconsistency: When the same data is updated in one file but not others, different versions exist simultaneously. 3. Difficulty in Accessing Data: Writing a new program is needed for every new query — no flexible query language. 4. Data Isolation: Data scattered in different files in different formats — hard to access and integrate. 5. Integrity Problems: Enforcing constraints (e.g., age must be positive) requires application-level code in every program. 6. Concurrent Access Issues: Multiple users modifying the same file can cause data corruption. 7. Security Problems: File system provides limited access control — difficult to restrict users to specific data. Advantages of DBMS: 1. Reduces data redundancy: Centralised storage eliminates duplicate data. 2. Ensures data consistency: A change in one place automatically reflects everywhere. 3. Data integrity: DBMS enforces constraints (primary key, foreign key, check constraints) automatically. 4. Data sharing: Multiple users can access the same database simultaneously with proper concurrency control. 5. Data security: DBMS provides authentication, authorisation, and access control at the database, table, and column level. 6. Backup and recovery: DBMS provides automatic backup and recovery mechanisms. 7. Flexible querying: SQL allows complex queries without writing new programs. 8. Data abstraction: Users see a logical view of data without worrying about physical storage.

Relational Model: • The relational model (proposed by E.F. Codd, 1970) organises data in the form of tables (relations). • A TABLE (relation) consists of: - Rows (tuples/records): Each row represents one instance of the entity. - Columns (attributes/fields): Each column represents one attribute of the entity. • Every table must have a unique name. Each column must have a unique name within the table. • All values in a column must be of the same data type. • The order of rows and columns is immaterial — data is accessed by content, not position. Example — Student Table: | RollNo | Name | Age | Class | |--------|-------|-----|-------| | 101 | Priya | 16 | 11A | | 102 | Arjun | 17 | 12B | | 103 | Meena | 16 | 11A | Keys in a Relational Database: 1. Candidate Key: • A minimal set of attribute(s) that can uniquely identify each row in a table. • A table can have multiple candidate keys. • Example: In a Student table, both RollNo and (Name + DateOfBirth) could be candidate keys (if name+dob is always unique). 2. Primary Key: • One candidate key selected by the database designer to uniquely identify rows. • Rules: (i) Must be UNIQUE — no two rows can have the same primary key value; (ii) Cannot be NULL; (iii) Only one primary key per table. • Example: RollNo is the primary key of the Student table. • In SQL: RollNo INT PRIMARY KEY 3. Foreign Key: • An attribute (or set of attributes) in one table that refers to the Primary Key of another table. • Establishes a relationship between two tables — enforces referential integrity. • The foreign key column in the child table must contain values that exist in the primary key column of the parent table (or NULL). • Example: In an Enrollment table, StudentRollNo is a foreign key referencing RollNo in the Student table — ensures you cannot enroll a non-existent student. 4. Composite Key: • A primary key made of more than one attribute. • Example: In an Enrollment table, (StudentRollNo, CourseID) together uniquely identify each enrollment. 5. Alternate Key: • Candidate keys that were not chosen as the primary key.

Data Integrity: • Data integrity refers to the accuracy, consistency, and reliability of data stored in a database. • A DBMS enforces data integrity through constraints — rules that data must satisfy. • Without integrity constraints, a database can contain incorrect, inconsistent, or meaningless data. Types of Data Integrity / Integrity Constraints: 1. Entity Integrity: • Every table must have a primary key, and the primary key cannot contain NULL values. • Each row must be uniquely identifiable — no two rows can have the same primary key value. • 'An entity (row) must be identifiable.' • Example: In the Student table, RollNo (primary key) must be unique and NOT NULL. If RollNo is NULL, we cannot identify which student the row refers to. • SQL enforcement: PRIMARY KEY constraint automatically ensures NOT NULL and UNIQUE. 2. Referential Integrity: • A foreign key value must either match a primary key value in the referenced table, or be NULL. • Prevents 'orphan' records — records that reference non-existent parent records. • Example: In an Enrollment table, StudentRollNo (foreign key) must match an existing RollNo in the Student table. You cannot enroll a student with RollNo 999 if no such student exists. • On deletion: If a parent row is deleted, either: (a) CASCADE — delete child rows too; (b) SET NULL — set foreign key to NULL; (c) RESTRICT — prevent deletion if child rows exist. • SQL enforcement: FOREIGN KEY ... REFERENCES constraint. 3. Domain Integrity: • Each attribute's values must come from a defined domain — the set of permissible values for that attribute. • Enforced through: Data type (INT, VARCHAR, DATE), CHECK constraints, NOT NULL, DEFAULT values. • Example: Age must be an integer between 5 and 100; Gender must be 'M', 'F', or 'Other'; Marks must be between 0 and 100. • SQL: Age INT CHECK (Age > 0 AND Age < 150); Marks INT NOT NULL DEFAULT 0. 4. User-Defined Integrity: • Business rules specific to the application that do not fit the other categories. • Enforced through CHECK constraints, triggers, or application logic. • Example: 'A student cannot enroll in more than 6 courses' — enforced by a trigger or application-level check.