In STUDENT(RollNo, AadhaarNo, Name, Class), both RollNo and AadhaarNo are unique for every student, and RollNo is chosen as the primary key. What is AadhaarNo?

Not a key, since only one primary key is allowed

Which of the following best defines a foreign key?

An attribute whose values must be unique and never NULL within its own relation

A key made by combining two or more attributes of the same relation

Any candidate key that was not chosen as the primary key

Assertion (A): A foreign key attribute can never contain a NULL value. Reason (R): A foreign key refers to the primary key of another relation.

Both A and R are true, and R is the correct explanation of A

Both A and R are true, but R is not the correct explanation of A

Database Concepts

A school can keep its records in separate files — one for admissions, one for fees, one for results — and many once did. This chapter starts from the problems that approach causes and shows how a database management system solves them. It then sets out the vocabulary of the relational model, in which data lives in tables: attributes and tuples, domain, degree and cardinality, and the properties every relation must have. Finally it covers keys, the attributes that identify a row and link one table to another. These terms are tested as definitions and as small worked examples, and the SQL chapter that follows uses every one of them.

Why Not Just Files? Limitations of a File System

In a file system, each application stores its own data in its own files. That works for a handful of records, but as data and users grow the book identifies six problems:

Limitations of a file system
Problem	What it means
Difficulty in access	every new question needs a new application program written to read the files
Data redundancy	the same data is stored in several files, wasting space
Data inconsistency	copies of the same data disagree, because one was updated and another was not
Data isolation	related data sits in separate files, often in different formats, with no link between them
Data dependence	a change to a file's structure forces changes to every program that uses it
Controlled data sharing	it is hard to let different users see only what they are allowed to see

⚠️ Redundancy causes inconsistency

They are different problems with a cause-and-effect link. Redundancy is the duplication itself — a student's address in both the fees file and the admissions file. Inconsistency is what happens when the address is changed in one file and not the other, so the two copies disagree.

Database Management Systems

A database is an organised collection of related data, and a database management system (DBMS) is the software used to create, store, update and retrieve it — MySQL, Oracle, PostgreSQL, SQL Server, Microsoft Access and MongoDB are examples. Because the DBMS keeps data in one place and controls access to it, it reduces redundancy and inconsistency, lets any authorised user ask new questions without new programs, and enforces who may see what.

Database schema: the design of the database — its tables, their attributes and types, and the constraints on them.
Data constraint: a rule restricting the values an attribute may take, such as "marks cannot be negative" or "roll number cannot be empty".
Meta-data (data dictionary): data about the data — the schema and constraints, stored by the DBMS.
Database instance: the data actually stored in the database at a particular moment; the schema stays the same while the instance changes with every insertion or update.
Query and data manipulation: a query is a request to retrieve data; manipulation means inserting, deleting and updating it. The database engine is the component that actually carries these out.

The Relational Data Model

The relational model, proposed by E. F. Codd in 1970, stores data in relations — tables of rows and columns. Each column is an attribute with a name; each row is a tuple, one record. The set of values an attribute may take is its domain — for an attribute Gender perhaps {M, F, T}. The number of attributes in a relation is its degree, and the number of tuples is its cardinality. A STUDENT relation with the attributes RollNo, Name, Class, Marks and City and eight rows has degree 5 and cardinality 8. Adding rows changes only the cardinality; adding or dropping a column changes only the degree.

Each attribute in a relation has a unique name, and the order of attributes does not matter.
No two tuples are identical, and the order of tuples does not matter.
Every value is atomic — it cannot be divided further; a cell holds one value, not a list.
A value that is unknown or does not apply is represented by NULL, which is not zero and not an empty string.

Keys in a Relational Database

Since no two tuples may be identical, some attribute or set of attributes must tell them apart. Every attribute (or minimal set of attributes) that can uniquely identify each tuple is a candidate key. The one candidate key chosen by the database designer to identify tuples is the primary key; the candidate keys not chosen are alternate keys. A primary key made of more than one attribute is a composite primary key. The primary key can never be NULL, since a missing value cannot identify anything.

A foreign key links two relations: it is an attribute of one relation whose values are drawn from the primary key of another — the referenced relation. In RESULT(RollNo, Subject, Marks), RollNo is a foreign key referring to STUDENT(RollNo), so a result can be entered only for a student who exists. A foreign key may repeat (one student, many results) and may be NULL when it is not part of its own relation's primary key. In ATTENDANCE(RollNo, AttDate, Status) neither RollNo nor AttDate is unique on its own — each student has many dates and each date many students — but together they are, so (RollNo, AttDate) is a composite primary key.

🧠 Candidate = primary + alternates

In STUDENT(RollNo, AadhaarNo, Name, Class), both RollNo and AadhaarNo are unique, so both are candidate keys. Choose RollNo as the primary key and AadhaarNo becomes the alternate key. Name is not a candidate key: two students can share a name.

Key takeaways

A file system suffers difficulty of access, redundancy, inconsistency, isolation, data dependence and poor control over sharing; a DBMS addresses them.
Schema is the design, instance the data at a moment, meta-data the stored description of the schema.
In the relational model (Codd), a column is an attribute, a row a tuple; degree counts attributes, cardinality counts tuples.
Tuples are distinct and unordered, attributes uniquely named and unordered, values atomic, and NULL marks the unknown.
Candidate keys can each identify tuples; the chosen one is the primary key (never NULL), the rest are alternate keys.
A composite key uses several attributes together; a foreign key takes its values from another relation's primary key.

Practice questions (8)

Attempt each one before opening the answer. Every explanation names the tempting wrong option as well as the right one, because that is where marks are lost.

A relation has 5 attributes and 8 tuples. Two new tuples are inserted and one attribute is dropped. What are its degree and cardinality now?
1. Degree 10, cardinality 4
2. Degree 4, cardinality 10
3. Degree 6, cardinality 9
4. Degree 5, cardinality 10
Show answer
Answer: B — Degree 4, cardinality 10
Degree counts attributes: 5 − 1 = 4. Cardinality counts tuples: 8 + 2 = 10. Degree 10 with cardinality 4 has the two terms the wrong way round, the most common slip with this pair. Degree 5 forgets the dropped attribute, and degree 6 with cardinality 9 applies each change to the wrong count.
A student's address is stored in both the fees file and the admissions file. The address is changed in the fees file only, so the two files now show different addresses. Which file-system problem does this illustrate?
1. Data isolation
2. Data dependence
3. Data inconsistency
4. Difficulty in access
Show answer
Answer: C — Data inconsistency
Two copies of the same fact now disagree, and that disagreement is data inconsistency. Its cause is redundancy — storing the address twice — but the question describes the mismatch, not the duplication. Isolation is related data scattered with no link, dependence is programs breaking when a file's structure changes, and difficulty in access is needing a new program for each new query.
In STUDENT(RollNo, AadhaarNo, Name, Class), both RollNo and AadhaarNo are unique for every student, and RollNo is chosen as the primary key. What is AadhaarNo?
1. A foreign key
2. A composite key
3. An alternate key
4. Not a key, since only one primary key is allowed
Show answer
Answer: C — An alternate key
AadhaarNo can identify every tuple, so it is a candidate key; a candidate key not chosen as the primary key is an alternate key. It is not a foreign key because it does not refer to another relation, and not composite because it is a single attribute. Only one primary key is allowed, but the other candidate keys remain keys.
Which of the following best defines a foreign key?
1. An attribute whose values must be unique and never NULL within its own relation
2. An attribute of one relation whose values are drawn from the primary key of another relation
3. A key made by combining two or more attributes of the same relation
4. Any candidate key that was not chosen as the primary key
Show answer
Answer: B — An attribute of one relation whose values are drawn from the primary key of another relation
A foreign key is how two relations are linked: its values must match the primary key of the referenced relation, as RollNo in RESULT matches RollNo in STUDENT. "Unique and never NULL" describes a primary key, a combination of attributes is a composite key, and an unchosen candidate key is an alternate key. A foreign key may repeat and may be NULL, which is exactly what a primary key may not do.
ATTENDANCE(RollNo, AttDate, Status) records whether each student was present on each day. Which choice of primary key is correct?
1. RollNo alone
2. AttDate alone
3. Status alone
4. RollNo and AttDate together
Show answer
Answer: D — RollNo and AttDate together
Each student has a row for every day, so RollNo repeats; each day has a row for every student, so AttDate repeats; but one student has only one row per day, so the pair identifies each tuple — a composite primary key. Status takes only a couple of values and repeats constantly, so it identifies nothing.
Consider these statements about a relation. I. The order of its tuples does not matter. II. Two of its tuples may be exactly identical. III. Every value in it is atomic. Which are correct?
1. I and II only
2. II and III only
3. I and III only
4. I, II and III
Show answer
Answer: C — I and III only
A relation is a set of tuples, so their order carries no meaning (I) and each cell holds a single indivisible value (III). II is false: no two tuples may be identical, which is exactly why every relation has a key that tells them apart. Any option containing II accepts duplicate rows that the relational model forbids.
Assertion (A): A foreign key attribute can never contain a NULL value. Reason (R): A foreign key refers to the primary key of another relation.
1. Both A and R are true, and R is the correct explanation of A
2. Both A and R are true, but R is not the correct explanation of A
3. A is true, but R is false
4. A is false, but R is true
Show answer
Answer: D — A is false, but R is true
R is the definition of a foreign key, so it is true. A is false: the no-NULL rule belongs to the primary key, and a foreign key that is not part of its own relation's primary key may be NULL — an employee not yet assigned to a department, for instance. Treating A as true carries the primary key's rule over to the key that merely refers to it.
The overall design of a database — its tables, their attributes and data types, and the constraints on them — is called the database:
1. instance
2. schema
3. engine
4. query
Show answer
Answer: B — schema
The schema is the structure of the database, and it stays the same while data comes and goes. The instance is the opposite idea, the actual data stored at a given moment, which changes with every insertion. The engine is the component that carries out operations, and a query is a single request to retrieve data.