Why do we need a database?
In a file system, each office keeps its own files. This causes problems:
- Data redundancy: the same data is stored again and again, wasting space.
- Data inconsistency: copies disagree when one is updated and another is not.
- Hard access: a new question needs a new program.
- Poor security and sharing: hard to control who sees what, and hard for many people to use at once.
- Data dependence: change the file format and every program breaks.
A database is an organised collection of related data. A DBMS (Database Management System) is the software that creates, stores, updates and protects it, such as MySQL, Oracle, PostgreSQL or SQLite. A DBMS reduces redundancy, keeps data consistent, lets many users share it safely and answers queries quickly.
The relational model: relation, attribute, tuple, domain
In the relational data model (given by E. F. Codd), data is stored in two-dimensional tables.
- Relation: a table.
- Attribute: a column (a named property, like Name).
- Tuple: a row (one record).
- Domain: the set of allowed values for an attribute (Marks: 0 to 100; Gender: M/F/O).
Rules of a relation: every attribute has a unique name; the order of rows and columns does not matter; no two tuples are exactly the same; each cell holds one single value (atomic).
| RollNo | Name | Class | AadhaarNo |
|---|---|---|---|
| 1 | Asha | 12A | 4411… |
| 2 | Ravi | 12B | 5522… |
| 3 | Zoya | 12A | 6633… |
Table STUDENT above.
Degree and cardinality
- Degree = number of attributes (columns). STUDENT above has degree 4.
- Cardinality = number of tuples (rows). STUDENT above has cardinality 3.
Adding a row changes cardinality; adding a column changes degree.
When two relations are combined by a Cartesian product, degrees add and cardinalities multiply.
Keys: candidate, primary, alternate
A key is an attribute (or a set of attributes) used to identify rows.
- Candidate key: any attribute or minimal set of attributes whose values are unique for every tuple and never empty. In STUDENT, both RollNo and AadhaarNo are candidate keys. Name is not (two students may share a name).
- Primary key: the one candidate key chosen to identify tuples. It must be unique and NOT NULL. Say we choose RollNo.
- Alternate key: every candidate key not chosen as primary. Here AadhaarNo.
- Composite key: a key made of two or more attributes together, like (Class, SectionRoll).
Relation: candidate keys = primary key + alternate keys.
Foreign key
A foreign key is an attribute in one relation that refers to the primary key of another relation. It links the two tables.
| ExamID | RollNo | Subject | Marks |
|---|---|---|---|
| 101 | 1 | CS | 92 |
| 102 | 3 | CS | 81 |
In table MARKS, RollNo is a foreign key that refers to STUDENT(RollNo). STUDENT is the parent (referenced) table; MARKS is the child (referencing) table.
Referential integrity: a foreign key value must either match an existing primary key value in the parent table or be NULL. So you cannot enter marks for RollNo 9 if no student 9 exists, and you cannot delete student 1 while marks for student 1 still exist (unless rules say what to do). A foreign key may repeat (a student can have many marks rows).
Try it: find the keys around you
Draw a table of 5 friends with columns Name, Phone, Email, City and BirthDate. Write its degree and cardinality. Which columns could identify each friend (candidate keys)? Pick one as primary key; the rest are alternate keys. Now draw a second table CALLS(CallID, Phone, Minutes) and mark which column is the foreign key. Then check your ideas in the 3D free-play step by trying rows that break the rules.
Key formulas and definitions
- Relation = table · Attribute = column · Tuple = row · Domain = allowed values
- Degree = number of columns · Cardinality = number of rows
- Candidate keys = Primary key + Alternate keys
- Foreign key (child) → Primary key (parent); value must exist or be NULL
Worked examples
1. A table BOOK has columns BookID, Title, Author, Price, ISBN and 40 rows. Find degree and cardinality.
Degree = 5 (columns). Cardinality = 40 (rows).
2. 10 more books are added and a column Publisher is added to BOOK. New degree and cardinality?
Degree = 5 + 1 = 6. Cardinality = 40 + 10 = 50.
3. In BOOK, BookID and ISBN are both unique for every book. Title may repeat. Name the candidate, primary and alternate keys.
Candidate keys: BookID, ISBN. If BookID is chosen as primary key, ISBN is the alternate key. Title is not a key.
4. Table ISSUE(IssueNo, BookID, MemberID, Date). Which are foreign keys and to which tables do they refer?
BookID refers to BOOK(BookID); MemberID refers to MEMBER(MemberID). IssueNo is ISSUE's own primary key.
5. Why can't Name be the primary key of STUDENT?
Two students can have the same name, so it is not unique, and a primary key must be unique and not null.
6. R1 has degree 3 and cardinality 4; R2 has degree 2 and cardinality 5. What are the degree and cardinality of their Cartesian product?
Degree = 3 + 2 = 5. Cardinality = 4 × 5 = 20.
Common mistakes
- Swapping degree (columns) and cardinality (rows).
- Thinking a table can have two primary keys; it has one primary key (which may be composite), other candidates become alternate keys.
- Allowing NULL in a primary key column; it must be NOT NULL.
- Thinking a foreign key must be unique in the child table; it may repeat.