🗄️ Unit 3 – Database & SQL · Lesson 20
DBMS, Relational Model & Keys
File system and its problems
Earlier, data was kept in separate files – one for fees, one for library, one for exam. This caused:
📑
Data redundancySame student details stored in many files.⚠️
InconsistencyAddress changed in one file but not in another.🔍
Difficult accessA new program is needed for every new question.🔓
Poor securityHard to control who sees what.🔗
Data dependenceChanging file format breaks programs.What is a DBMS?
A database is an organised collection of related data. A DBMS (Database Management System) is software to create, store, update and query it – e.g. MySQL, Oracle, PostgreSQL, SQLite. It reduces redundancy, keeps data consistent, allows sharing and provides security and backup.
Relational model terms
In the relational model (E. F. Codd, 1970) data is stored in tables called relations.
| EmpID | Name | Aadhaar | Salary | DeptID |
|---|---|---|---|---|
| 1 | Riya | 1111 | 42000 | D01 |
| 2 | Arjun | 2222 | 56000 | D03 |
| 3 | Meena | 3333 | 38000 | D02 |
| 4 | Kunal | 4444 | 61000 | D03 |
| Term | Meaning | In EMP |
|---|---|---|
| Relation | A table | EMP |
| Attribute | A column | Name, Salary… |
| Tuple | A row (record) | (2, Arjun, 2222, 56000, D03) |
| Domain | Set of allowed values for an attribute | DeptID ∈ {D01, D02, D03} |
| Degree | Number of attributes | 5 |
| Cardinality | Number of tuples | 4 |
Properties of a relation
- Each attribute has a unique name; order of columns does not matter.
- No two tuples are identical; order of rows does not matter.
- Each cell holds a single (atomic) value.
- Values of an attribute come from the same domain; a missing value is NULL.
Keys in a relation
🗝️
Candidate keyAttribute(s) that can uniquely identify each tuple. EMP: EmpID, Aadhaar.🔑
Primary keyThe candidate key chosen to identify tuples; unique and NOT NULL. EmpID.🔁
Alternate keyCandidate keys not chosen as primary. Aadhaar.🔗
Foreign keyAttribute that refers to the primary key of another table. EMP.DeptID.🧩
Composite keyA primary key made of two or more attributes, e.g. (RollNo, Subject).SQL
SELECT * FROM DEPT;
Output
+--------+----------+--------+ | DeptID | DName | City | +--------+----------+--------+ | D01 | Sales | Delhi | | D02 | Accounts | Mumbai | | D03 | IT | Pune | +--------+----------+--------+
SQL
SELECT * FROM EMP;
Output
+-------+-------+---------+--------+--------+ | EmpID | Name | Aadhaar | Salary | DeptID | +-------+-------+---------+--------+--------+ | 1 | Riya | 1111 | 42000 | D01 | | 2 | Arjun | 2222 | 56000 | D03 | | 3 | Meena | 3333 | 38000 | D02 | | 4 | Kunal | 4444 | 61000 | D03 | +-------+-------+---------+--------+--------+
Foreign key in action
MySQL enforces the keys. A duplicate primary key or a DeptID that does not exist in DEPT is rejected; this keeps data correct (referential integrity).
SQL
INSERT INTO EMP VALUES (2, 'Tara', '5555', 40000, 'D01'); -- ERROR 1062 (23000): Duplicate entry '2' for key 'PRIMARY'
SQL
INSERT INTO EMP VALUES (5, 'Tara', '5555', 40000, 'D09'); -- ERROR 1452 (23000): Cannot add or update a child row: -- a foreign key constraint fails (DeptID 'D09' does not exist in DEPT)
SQL
INSERT INTO EMP VALUES (5, 'Tara', '5555', 40000, 'D01'); -- Query OK, 1 row affected
SQL
SELECT EmpID, Name, DeptID FROM EMP WHERE DeptID = 'D03';
Output
+-------+-------+--------+ | EmpID | Name | DeptID | +-------+-------+--------+ | 2 | Arjun | D03 | | 4 | Kunal | D03 | +-------+-------+--------+
Practice
- A table has 6 columns and 20 rows. Degree = 6, cardinality = 20. After adding 2 rows and 1 column? Degree 7, cardinality 22.
- STUDENT(AdmNo, RollNo, Class, Name, Email): identify candidate, primary and alternate keys. Candidate: AdmNo, Email (and RollNo+Class); primary: AdmNo; alternate: Email.
Frequently Asked Questions
What is the difference between degree and cardinality?
Degree is the number of attributes (columns); cardinality is the number of tuples (rows) in a relation.
What is an alternate key?
A candidate key that is not chosen as the primary key. In EMP, if EmpID is the primary key, Aadhaar is an alternate key.
Can a foreign key have NULL or repeated values?
Yes. A foreign key may repeat and may be NULL, but any non-NULL value must exist in the referenced primary key.
