Press m or Esc to close
Chapter 4 stored data in a file. This unit stores it in a way that can be questioned, checked and shared — and that starts with getting the vocabulary exactly right.
You stored the five students in marks.csv and read them back with
csv.reader. It worked.
marks.csv — the Chapter 4 versionName,Marks,Stream,HOD
Riya,88,Computer Science,Sharma
Aarav,91,Computer Science,Sharma
Kabir,76,Informatics,Iyer
Meera,94,Computer Science,Sharma
Dev,59,Informatics,Iyer
① Redundancy. "Computer Science, Sharma" is written three times. Ten thousand students means ten thousand copies.
② Inconsistency. When Sharma is replaced, you must find and change every row. Miss one and the file now says two different things.
③ No rules. Nothing stops you typing Marks = 950, or two
students with the same roll number, or a stream that does not exist.
Store each fact once, enforce rules about what is allowed, and let many programs and people ask questions of the same data at the same time without corrupting it.
Database — an organised collection of related data stored in a way that allows it to be accessed, managed and updated efficiently.
DBMS — Database Management System — software that allows users to define, create, store, retrieve and manage the data in a database, and that controls access to it.
RDBMS — Relational DBMS — a DBMS in which data is organised as a set of tables (relations), and relationships between tables are expressed by common columns. MySQL, Oracle and PostgreSQL are RDBMSs.
The relational model was proposed by E. F. Codd at IBM in 1970. Its whole idea is that data should be stored in simple tables and queried mathematically, not navigated by following pointers.
Two tables, ten rows. They appear on every remaining slide of this unit — Chapter 8, Chapter 9 and Chapter 10.
| RollNo (PK) | Name | SCode (FK) | Marks | City | Fee |
|---|---|---|---|---|---|
| 101 | Riya | S01 | 88 | Delhi | 4500 |
| 102 | Aarav | S01 | 91 | Mumbai | 4500 |
| 103 | Kabir | S02 | 76 | Delhi | 3800 |
| 104 | Meera | S01 | 94 | Pune | 4500 |
| 105 | Dev | S02 | 59 | Mumbai | 3800 |
| 106 | Ishan | S03 | 67 | Delhi | 4000 |
| 107 | Nisha | S02 | NULL | Pune | 3800 |
| SCode (PK) | SName | HOD |
|---|---|---|
| S01 | Computer Science | Sharma |
| S02 | Informatics | Iyer |
| S03 | Physics | Rao |
"Computer Science, Sharma" now appears once, in STREAM. STUDENT holds
only the short code S01. Replace Sharma and you change
one cell.
That is the entire relational idea: store each fact once, and refer to it by a key.
Riya, Aarav, Kabir, Meera and Dev have been with us since Chapter 4. Nisha and Ishan join here so that we have a NULL and a third stream to work with.
Relation — a table of data, with rows and columns.
Tuple — a single row of a relation; one record.
Attribute — a single column of a relation; one field.
Domain — the set of permitted values for an
attribute.
Degree — the number of attributes (columns).
Cardinality — the number of tuples (rows).
| RollNo | Name | SCode | Marks | City | Fee |
|---|---|---|---|---|---|
| 101 | Riya | S01 | 88 | Delhi | 4500 |
| 102 | Aarav | S01 | 91 | Mumbai | 4500 |
| 103 | Kabir | S02 | 76 | Delhi | 3800 |
| 104 | Meera | S01 | 94 | Pune | 4500 |
| 105 | Dev | S02 | 59 | Mumbai | 3800 |
| 106 | Ishan | S03 | 67 | Delhi | 4000 |
| 107 | Nisha | S02 | NULL | Pune | 3800 |
State the degree and cardinality of STUDENT, and of STREAM. Then: what happens to each if we insert one student? And if we add a Phone column?
| Degree | Cardinality | |
|---|---|---|
| STUDENT | 6 | 7 |
| STREAM | 3 | 3 |
Insert one student → cardinality becomes 8; the degree is unchanged at 6.
Add a Phone column → degree becomes 7; the cardinality is unchanged at 7.
① A NULL still counts. Nisha's row is a tuple, so the cardinality
is 7, not 6.
② Duplicate values in a column change nothing — three students
live in Delhi, and cardinality is still 7.
Degree = Down the top row, counting columns.
Cardinality = the count of records.
Domain — the set of all permissible atomic values that an attribute may take.
| Attribute | Domain |
|---|---|
| RollNo | positive integers, 101–999 |
| Name | character strings up to 20 characters |
| SCode | S01, S02, S03 — those in STREAM |
| Marks | integers 0–100, or NULL |
| City | character strings up to 15 characters |
It is what lets the DBMS refuse bad data. A CSV file would happily accept
Marks = 950; a database with the domain declared will not. That is
the "data integrity" advantage from §1.2, made concrete.
NULL represents a value that is missing, unknown or not applicable. It is not zero, and not an empty string.
① Marks = NULL is never true — you must write
Marks IS NULL.
② COUNT(Marks) gives 6, but COUNT(*)
gives 7.
③ AVG(Marks) ignores the NULL row entirely — it divides by
6, not 7.
No cell may contain a list. A student with two phone numbers needs two rows or
a second table — not "9811, 9822" in one cell.
The Marks column cannot hold 88 in one row and
"absent" in another. That is what NULL is for.
You cannot have two columns both called Name. Across
different tables the same name is fine — both STUDENT and STREAM have
SCode, and that is exactly how they are joined.
No two rows may be completely identical. This is why every relation needs a primary key — §3.
A relation is a set of rows. Riya's row being first carries no meaning,
and a query may return them in any order unless you write
ORDER BY.
You refer to a column by name, never by position.
SELECT Name, Marks works regardless of how they are stored.
Because order is meaningless, you must name what you want and state how to sort it. There is no "give me the third row" in SQL — and that is deliberate.
"Update Riya's marks to 92."
Which row do you change? What if two students are called Riya? What if three live in Delhi and score 88?
Key — an attribute, or a set of attributes, whose values uniquely identify each tuple in a relation.
Ask: "could two different rows ever share this value?"
Name — yes, two students could both be Riya. Not a key.
City — obviously yes. Not a key.
Marks — yes, and one is even NULL. Not a key.
RollNo — no, the school issues each one once. A key.
In our seven rows every Name happens to be unique. That does
not make Name a key — the question is whether a duplicate is
possible, not whether one is present today. This is the single most
common error in this chapter.
Candidate key — any attribute or minimal set of attributes that can uniquely identify every tuple. A relation may have several.
Primary key — the one candidate key chosen by the designer to identify tuples. It can never be NULL and never duplicated.
Alternate key — a candidate key that was not chosen as the primary key.
Suppose STUDENT also had an AadhaarNo column, which is also unique
to each person.
| Attribute | Unique? | So it is… |
|---|---|---|
| RollNo | Yes | candidate key → chosen as PRIMARY KEY |
| AadhaarNo | Yes | candidate key → ALTERNATE KEY |
| Name | No | not a key |
| SCode | No | not a key — it is a foreign key |
| Marks | No | not a key — and it can be NULL |
Both would work. A designer picks the one that is shorter, stable, always known and controlled by the organisation. The school issues roll numbers, so it can guarantee them; it cannot guarantee anyone has an Aadhaar number.
A composite key is a primary key made of two or more
attributes together, used when no single column is unique. In a
MARKS(RollNo, Subject, Score) table neither RollNo nor Subject alone is
unique — but the pair (RollNo, Subject) is.
Foreign key — an attribute in one relation whose values
must match the values of the primary key of another relation. It is what
establishes a relationship between two tables.
Referential integrity — the rule that a foreign key value
must either match an existing primary key value, or be NULL.
INSERT INTO STUDENT VALUES (108, 'Tara', 'S09', 82, 'Delhi', 4000);
Refused. There is no S09 in STREAM, so this student would
belong to a stream that does not exist.
DELETE FROM STREAM WHERE SCode = 'S01';
Refused. Riya, Aarav and Meera all point at S01. Deleting it would leave three students referring to nothing.
INSERT INTO STUDENT VALUES (108,'Tara','S03',82,'Delhi',4000); —
S03 exists.
DELETE FROM STREAM WHERE SCode = 'S03'; — only if no student
references S03.
UPDATE STUDENT SET SCode = 'S02' WHERE RollNo = 101; — S02 exists,
so Riya may move stream.
The DBMS, automatically, once you declare the foreign key. You do not write a single line of checking code — which is precisely the advantage a database has over the CSV file in §1.1.
If a new student has not yet chosen a stream, SCode may be NULL —
"no relationship yet" is a legitimate state. But RollNo can never be
NULL, because then the row could not be identified at all.
| Key | Definition | NULL allowed? | In our database |
|---|---|---|---|
| Candidate | any attribute set that can uniquely identify a tuple | no | RollNo (and AadhaarNo, if it existed) |
| Primary | the candidate key chosen to identify tuples | Never | STUDENT.RollNo · STREAM.SCode |
| Alternate | a candidate key not chosen as primary | no | AadhaarNo, if it existed |
| Composite | a primary key made of two or more attributes | Never | (RollNo, Subject) in a marks table |
| Foreign | matches the primary key of another relation | Yes | STUDENT.SCode → STREAM.SCode |
In a table LIBRARY(BookID, ISBN, Title, Author, MemberID) —
BookID and ISBN are both unique, and
MemberID refers to the MEMBER table's primary key.
Identify each kind of key.
Candidate keys: BookID and ISBN — both uniquely
identify a book.
Primary key: BookID — chosen by the library, short and under
its own control.
Alternate key: ISBN — a candidate key that was not chosen.
Foreign key: MemberID — it refers to the primary key of MEMBER.
It may be NULL, which is exactly how the table records a book that is
currently on the shelf.
Not keys: Title and Author — two books can share
either.
“Degree is the number of attributes and cardinality is the number of tuples in a
relation.”
“A primary key uniquely identifies each tuple and can neither be NULL nor
duplicated.”
“A foreign key is an attribute whose values must match the primary key of another
relation; this rule is called referential integrity.”
Consider the table HOSPITAL.
| PatID | Name | Dept | Age | Doctor |
|---|---|---|---|---|
| P01 | Anil | Cardiology | 54 | Nair |
| P02 | Sara | Ortho | 31 | Bose |
| P03 | Anil | Ortho | 47 | Bose |
| P04 | Zoya | Cardiology | NULL | Nair |
Name be a primary key? Give a reason from the data.1. Degree = 5 (PatID, Name, Dept, Age, Doctor). Cardinality = 4. The NULL in Zoya's Age does not reduce the count — the tuple exists.
2. PatID. Its values — P01 to P04 — are unique and never NULL, so it identifies each patient uniquely.
3. No. Anil appears in both P01 and P03, so Name is not
unique. Here the data itself proves it, which is the strongest form of
justification.
4. 5. Cardinality changes with rows: 4 + 2 − 1 = 5. The degree is still 5, because no column was added or removed.
A candidate key is any attribute or minimal set of attributes capable of uniquely identifying every tuple of a relation. A relation may have several.
An alternate key is a candidate key that was not selected as the primary key.
In STUDENT(RollNo, AadhaarNo, Name, …) both RollNo and AadhaarNo are
candidate keys. If RollNo is chosen as the primary key, then AadhaarNo is the
alternate key.
A foreign key is an attribute in one relation whose values must match the values of the primary key of another relation. It creates the relationship between the two tables.
It enforces referential integrity by ensuring that every foreign key value actually exists in the referenced table. The DBMS therefore refuses an insert that names a non-existent parent row, and refuses a delete of a parent row that children still refer to. This prevents orphan and dangling records.
A primary key can never be NULL — a tuple with no identifying value could not be distinguished from another.
A foreign key may be NULL, which simply means the tuple is not currently related to any row in the other table.
You now have the vocabulary and the two tables. Chapter 9 is the language for asking them questions — and every single query will be run against the STUDENT and STREAM tables you have just met.
① NULL in Nisha's Marks — it changes WHERE,
COUNT and AVG, and each one is examined.
② SCode is the foreign key — it is the column every
JOIN in Chapter 9 uses.
③ Row order is meaningless, so any question about ordering must be
answered with ORDER BY.