Why do we need a database?
In a file system, each department keeps its own files. This causes problems:
- Data redundancy: the same data is stored many times, wasting space.
- Data inconsistency: one copy is updated, others are not, so they disagree.
- Hard to search and share: every question needs a new program.
- Poor security: hard to control who sees what.
- Data isolation and dependence: files in different formats; changing the file layout breaks programs.
A database is an organised collection of related data stored in one place, so it can be shared, kept consistent and protected.
Database Management System (DBMS)
A DBMS is software to create, store, update, search and manage a database, and to control who can use it. Examples: MySQL, Oracle, PostgreSQL, SQLite, Microsoft Access.
Advantages: less redundancy, consistency, sharing by many users, security with passwords and permissions, backup and recovery, and easy searching with a query language (SQL).
Everyday uses: banking, railway reservation, school records, online shopping, hospitals.
Relational model: relation, attribute, tuple, domain
In the relational data model, data is kept in tables.
- Relation: a table with a name, e.g. STUDENT.
- Attribute: a column, e.g. Name.
- Tuple: a row, i.e. one full record.
- Domain: the set of allowed values for an attribute, e.g. Class ∈ {9, 10, 11, 12}.
- Degree: number of attributes (columns).
- Cardinality: number of tuples (rows).
Rules: each column has a unique name; the order of rows or columns does not matter; no two rows are exactly the same; each cell holds one single value (or NULL, meaning unknown).
Keys: candidate, primary and alternate
- Candidate key: an attribute (or set of attributes) whose value is unique for every row and never NULL. A table can have more than one.
- Primary key: the one candidate key chosen to identify rows. It cannot be NULL or repeat.
- Alternate key: every candidate key that was not chosen as primary key.
Example: STUDENT(AdmNo, Name, Class, Email). Name repeats, Class repeats, so they are not keys. AdmNo and (if always filled and different) Email are candidate keys. Choose AdmNo as primary key; Email becomes the alternate key.
Also useful: a composite key uses two or more columns together (like Class + RollNo); a foreign key is a column in one table that refers to the primary key of another table, linking the two.
Try it: find the key in your ID card
Look at your school ID card or a bus pass. List every field (name, class, roll no, admission no, phone). For each, ask two questions: can two students have the same value? Can it be empty? Write which are candidate keys and which you would pick as primary key. Then test the same idea in step 6 of the 3D.
Key formulas and definitions
- Relation = table; attribute = column; tuple = row
- Degree = number of columns; cardinality = number of rows
- Candidate key: unique + not NULL
- Primary key = chosen candidate key; alternate keys = the rest
Worked examples
1. A table has 5 columns and 30 rows. Give its degree and cardinality.
Degree = 5, cardinality = 30.
2. After adding 2 rows and 1 column to a 4 × 10 (degree 4, cardinality 10) table, what are the new values?
Degree = 5, cardinality = 12.
3. EMPLOYEE(EmpID, Name, Aadhaar, Dept). Identify candidate, primary and alternate keys.
EmpID and Aadhaar are unique → candidate keys. Choose EmpID as primary key; Aadhaar is the alternate key. Name and Dept can repeat.
4. Why can't Name be a primary key in STUDENT?
Two students can have the same name, so it does not identify a row uniquely.
5. Give the domain of an attribute Gender stored as one letter.
{"M", "F", "O"} (or whatever codes the school allows).
Common mistakes
- Calling a column a tuple. Tuple = row; attribute = column.
- Saying degree is the number of rows. Degree counts columns.
- Thinking a table can have only one candidate key. It can have many, but only one primary key.
- Allowing NULL in a primary key column.