Differentiate between: a) Database state and database schema b) Primary key and foreign key c) Degree and card

Exercises · Q3

Q. Differentiate between:

a) Database state and database schema

b) Primary key and foreign key

c) Degree and cardinality of a relation

West Bengal WbchseTextbookSubjective· 3mImportance★★★★★est

29% · 4/14 Questions

✓ Free question

Concept understanding — Database Schema vs State

Database Schema vs State: The Blueprint vs The Building

Imagine you are designing a new school. Before a single brick is laid, an architect draws up a detailed blueprint. That blueprint shows exactly where every classroom, corridor, and washroom will go, what size each room is, and how they connect. The blueprint is a plan — it defines the structure, the rules, and the constraints of the school.

Now, once the school is built, actual students and teachers fill those rooms. On Monday, Room 101 has a Physics class; on Tuesday, it is empty. The set of actual people, furniture, and activities present at any given moment is the state of the school.

The blueprint never changes (unless you renovate). The state changes every hour.

The Precise Statement

In a database:

Schema is the structure and rules that define the database. It specifies the tables, the columns in each table, the data types of those columns (integer, text, date), and the constraints (e.g., "every student must have a unique ID", "a course cannot exist without a department"). The schema is the intension — it describes what the database can hold.
State (or instance) is the actual data stored in the database at a particular moment in time. It is the extension — the set of rows currently in the tables.

Note

The schema is stable and changes only when you alter the design (e.g., add a new column). The state changes every time you INSERT, UPDATE, or DELETE a row.

A Concrete Example

Consider a university database with a table for students.

Schema (the blueprint):

Column Name	Data Type	Constraint
student_id	INTEGER	PRIMARY KEY
name	TEXT	NOT NULL
birth_year	INTEGER	> 1900

The schema says: "Every student must have a unique integer ID, a non-empty name, and a birth year after 1900." It does not contain a single actual student.

State (the data on a given day):

student_id	name	birth_year
101	Ananya	2003
102	Rohan	2004
103	Priya	2002

This is the state right now. Tomorrow, you might add a new student (state changes) or delete Rohan (state changes again). The schema remains exactly the same.

Why This Distinction Matters

Schema defines validity. Any state must obey the schema's rules. If you try to insert a student with birth_year = 1800, the database rejects it — the schema says no. The schema is the law; the state is the population.
Schema changes are rare and careful. Changing the schema (e.g., adding a phone_number column) affects all future states and often requires migrating existing data. Changing the state is routine.
Querying works on the state. When you write SELECT * FROM students WHERE birth_year > 2003, you are asking about the current state. The schema tells you what columns exist and what types they are, but the answer depends on the data present.

Important

A database is always in some state, and that state must always satisfy the schema. If the state violates the schema, the database is considered inconsistent — a serious error.

One More Analogy

Think of a spreadsheet template (schema) versus a filled-out spreadsheet (state). The template has column headers and maybe some data validation rules. The filled spreadsheet has actual numbers in the cells. You can reuse the same template for different months — same schema, different states.

Schema = the template.

State = the filled data for January, then for February, and so on.

The Formal Definition (for exams)

Database Schema: The logical description of the database structure, including table names, column names, data types, and constraints. It is the intensional definition.

Database State: The collection of data stored in the database at a particular instant. It is the extensional set of tuples (rows) that satisfy the schema.

Key point: The schema is relatively static; the state is dynamic. Every valid state must conform to the schema.

Three classic pairs: schema is the blueprint while state is the data currently inside it; a primary key identifies tuples in its own relation while a foreign key points to another relation's primary key; degree counts columns while cardinality counts rows.

a) Database schema vs database state

Basis	Database schema	Database state
What it is	The overall design: relation names, attributes, data types, constraints (keys, NOT NULL, CHECK)	The collection of tuples actually stored in the relations at a particular moment
When it changes	Rarely — only when the design is altered (`CREATE`/`ALTER TABLE`)	Continuously — every `INSERT`, `UPDATE`, `DELETE` produces a new state
Defined when	At database design time	At run time, as data flows in
Analogy	The printed blank admission form	The pile of filled-in forms today

Example: STUDENT(RollNo INT PRIMARY KEY, Name VARCHAR(30), Class INT) is schema; the three rows currently stored in STUDENT are its state. The empty database (no data yet) is also a valid state — the empty state.

b) Primary key vs foreign key

Basis	Primary key	Foreign key
Purpose	Uniquely identifies each tuple of its own relation	Links a tuple to a tuple of another (referenced) relation
Uniqueness	Values must be unique	Values may repeat (many tuples can reference the same master tuple)
NULL	Never NULL (entity integrity)	May be NULL, if the relationship is optional
How many	Exactly one per relation (possibly composite)	A relation can have several foreign keys
Integrity rule enforced	Entity integrity	Referential integrity

Example:

CREATE TABLE DEPARTMENT (DeptID INT PRIMARY KEY, DeptName VARCHAR(30));
CREATE TABLE EMPLOYEE  (EmpID  INT PRIMARY KEY, EName VARCHAR(30),
                        DeptID INT, FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID));

EmpID identifies employees; DeptID in EMPLOYEE is the foreign key tying each employee to a department, and several employees may share DeptID = 10.

c) Degree vs cardinality of a relation

Basis	Degree	Cardinality
Counts	Number of attributes (columns)	Number of tuples (rows)
Changes when	The design changes (add/drop a column)	Data changes (insert/delete rows)

Example — this relation:

RollNo	Name	Class
1	Asha	11
2	Vikram	11

has degree 3 (RollNo, Name, Class) and cardinality 2 (two tuples).

✓Final answer

a) Schema = the time-invariant design of the database (relations, attributes, types, constraints); state = the actual data stored at a given instant, changing with every insert/update/delete. b) Primary key = the chosen attribute(s) that uniquely and non-NULL-y identify tuples in their own relation (entity integrity); foreign key = attribute(s) referencing another relation's primary key, possibly repeated or NULL (referential integrity). c) Degree = number of attributes (columns); cardinality = number of tuples (rows). A relation of 3 columns and 2 rows has degree 3, cardinality 2.

←Previous questionQ2 — Why foreign keys are allowed to have NULL values? Explain with an exam…→Next questionQ4 — Compared to a file system, how does a database management system avoid…

Q.Differentiate between:

🎓Unlock everything free for 14 days

Unlock everything free for 14 days