🗄️ Unit 4 – Database Concepts & SQL · Lesson 15

Relational Model & Keys

The relational model

In the relational data model, proposed by E. F. Codd, all data is stored in tables. Each table is called a relation. Tables are linked through common columns, so data is not repeated.

Relation STUDENT
RollNo	Name	Class	City
101	Aarav	XI-A	Jaipur
102	Sana	XI-A	Lucknow
103	Kabir	XI-B	Jaipur
104	Diya	XI-B	Pune

A relation is a table of rows and columns

Relational terms

Term	Everyday word	In STUDENT
Relation	Table	STUDENT
Attribute	Column / field	RollNo, Name, Class, City
Tuple	Row / record	(102, 'Sana', 'XI-A', 'Lucknow')
Domain	Set of allowed values of an attribute	Class ∈ {XI-A, XI-B, XI-C}
Relation schema	Table design	STUDENT(RollNo, Name, Class, City)
Relation state / instance	Data present right now	the 4 rows shown

Degree and cardinality

Degree

Number of attributes (columns)
STUDENT has degree 4
Changes only when the design changes

Cardinality

Number of tuples (rows)
STUDENT has cardinality 4
Changes every time rows are added or deleted

Columns vs rows

Properties of a relation

Each attribute has a unique name within the table.
The order of columns and of rows does not matter.
No two tuples are exactly the same.
Each cell holds a single (atomic) value — not a list.
All values in a column come from the same domain (same type).

Keys in a relational database

A key is an attribute (or a set of attributes) that helps identify rows uniquely or link tables.

🗝️

Candidate keyAny attribute(s) that can uniquely identify each tuple. STUDENT: RollNo, AadhaarNo.

🔑

Primary keyThe candidate key chosen to identify rows. Unique and NOT NULL. STUDENT: RollNo.

🔁

Alternate keyCandidate keys not chosen as primary. STUDENT: AadhaarNo.

🔗

Foreign keyAttribute in one table that refers to the primary key of another table.

🧩

Composite keyA primary key made of two or more columns, e.g. (RollNo, Subject) in a MARKS table.

Kinds of keys

Foreign key and relationships

The FEE table stores RollNo to say whose fee it is. RollNo in FEE is a foreign key that refers to the primary key RollNo of STUDENT. A foreign-key value must either match an existing primary-key value or be NULL — you cannot record a fee for a student who does not exist. This rule is called referential integrity.

STUDENT is the parent table; FEE and LIBRARY_ISSUE refer to it

FEE
ReceiptNo	RollNo	Month	Amount
R1	101	April	3500
R2	102	April	3500
R3	101	May	3500

RollNo repeats in FEE (many receipts for one student) but each value exists in STUDENT

NULL values

NULL means "value unknown or not applicable". It is not zero and not an empty string. Example: a new student whose phone number is not yet known. A primary key can never be NULL.

Summary & practice

Relation = table; attribute = column; tuple = row; domain = allowed values.
Degree = number of columns; cardinality = number of rows.
Candidate keys → one chosen as primary (unique, not null); rest are alternate keys.
Foreign key links a child table to a parent table's primary key.

✅ Practice

Table EMPLOYEE(EmpID, Name, Email, Phone, DeptID) has 25 rows. DEPARTMENT(DeptID, DeptName) has 5 rows. 1) Degree and cardinality of EMPLOYEE? 2) Candidate keys of EMPLOYEE? 3) Which attribute is a foreign key?

Answers: 1) degree 5, cardinality 25. 2) EmpID, Email, Phone (if each is unique). 3) DeptID in EMPLOYEE.

Frequently Asked Questions

What is the difference between degree and cardinality?

Degree is the number of attributes (columns) in a relation. Cardinality is the number of tuples (rows). A table with 5 columns and 30 rows has degree 5 and cardinality 30.

Can a primary key have NULL or duplicate values?

No. A primary key must be unique for every row and can never be NULL.

What is an alternate key?

A candidate key that was not chosen as the primary key. If both RollNo and AadhaarNo can identify a student and RollNo is the primary key, AadhaarNo is an alternate key.

← Class 11 Informatics Practices