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

Exercises · Q3

Q. Differentiate between:

(a) Database state and database schema

(b) Primary key and foreign key

Tamil Nadu DgeTextbookSubjective· 3mImportance★★★★★

14% · 4/29 Questions

✓ Free question

Concept understanding — Database Fundamentals

Database Fundamentals

Think of a database as a well-organised filing cabinet. In your daily life, you already manage small databases without realising it — your phone's contact list, the catalogue of a library, or even the list of students in a class register. Each of these is a collection of related information stored in a structured way so that you can find what you need quickly.

What Exactly Is a Database?

A database is a collection of related data that is stored and managed systematically. The key word here is related. A random pile of papers on a desk is not a database — but the same papers, sorted into folders with labels and a clear order, become one. The purpose of a database is to allow efficient storage, retrieval, modification, and deletion of data.

Note

A database is not the same as a spreadsheet. A spreadsheet is a single table meant for one person's use. A database is designed for multiple users, handles large volumes of data, and ensures that the data remains consistent even when many people access it at once.

Why Do We Need Databases?

Imagine a small business that keeps customer orders in a notebook. As the business grows, the notebook becomes impossible to manage — pages get lost, two customers might be given the same order number, and finding a specific order takes forever. A database solves all these problems.

Databases provide:

Centralised control — all data lives in one place, so everyone works with the same information
Data consistency — rules can be enforced (for example, an order must belong to a real customer)
Security — different users can be given different levels of access
Reduced redundancy — the same piece of information is not stored in multiple places unnecessarily
Data independence — the way data is stored can change without affecting how users view it

The Core Idea: Tables and Relationships

The most common type of database is the relational database. Think of it as a collection of tables, where each table stores information about one kind of thing.

A table is made up of rows and columns. Each column represents a field (a single piece of information, like a name or a date), and each row represents a record (one complete set of fields, like one customer's full details).

For example, a university might have a Student table with columns: Roll Number, Name, Date of Birth, and Course. Each row is one student.

The real power comes from relationships between tables. The same university might have a Course table and an Enrolment table. The Enrolment table links a student to a course using the student's roll number and the course code — without repeating all the student's details or all the course details. This is what makes a database efficient.

Important

The primary key is a column (or a set of columns) that uniquely identifies each row in a table. For the Student table, Roll Number is the primary key — no two students can share the same roll number. A foreign key is a column in one table that refers to the primary key of another table, creating the link between them.

Database Management System (DBMS)

A Database Management System is the software that lets you create, manage, and interact with a database. You do not directly touch the data files — the DBMS does that for you. Popular examples include MySQL, Oracle, and Microsoft Access.

The DBMS handles:

Data storage and retrieval — it decides how to physically store data on a disk
Query processing — when you ask for "all students born after 2005", the DBMS finds the answer
Transaction management — if two people try to book the last seat on a flight at the same time, the DBMS ensures only one succeeds
Backup and recovery — if the system crashes, the DBMS can restore the data

A Simple Example to Tie It Together

Consider a library. Without a database, the librarian keeps a card catalogue and a handwritten log of who has borrowed which book. With a database, there might be three tables:

Books (Book ID, Title, Author, ISBN, Shelf Number)
Members (Member ID, Name, Phone, Join Date)
Borrowing (Borrow ID, Book ID, Member ID, Issue Date, Return Date)

The Borrowing table uses the Book ID and Member ID to link the other two tables. To find out which books a particular member has, the DBMS looks up the Member ID in the Borrowing table, then finds the corresponding book titles from the Books table — all in a fraction of a second.

What You Should Remember

A database is not just a collection of data — it is a structured, managed, and related collection. The structure (tables, keys, relationships) is what makes it powerful. The DBMS is the tool that makes it usable. And the whole point is to store data once, keep it accurate, and retrieve it quickly whenever needed.

Note

In your exams, you will often be asked to identify tables, primary keys, foreign keys, and relationships from a given scenario. Always start by asking: What are the main "things" in this scenario? Each thing becomes a table. Then ask: How are these things connected? That connection becomes the foreign key.

Here are the differentiations between the requested database concepts:

(a) Database state and database schema

Database Schema: The database schema is the logical design or structure of the database. It defines how the data is organized, including the names of tables, columns, their data types, constraints (like primary keys, foreign keys, unique constraints), and relationships between tables. It is a static description, a blueprint, that changes infrequently.
Database State (or Instance): The database state refers to the actual data stored in the database at a particular moment in time. It is the collection of all data present in the tables according to the defined schema. The database state is dynamic; it changes frequently as data is inserted, updated, or deleted.

Differentiation: The database schema is the definition or structure of the database, while the database state is the content or data stored in the database at any given point. The schema is relatively stable, whereas the state is constantly changing.

(b) Primary key and foreign key

Primary Key: A primary key is a column or a set of columns in a table that uniquely identifies each record (row) in that table.
- It must contain unique values for each row.
- It cannot contain NULL values (it must always have a value).
- A table can have only one primary key.
Foreign Key: A foreign key is a column or a set of columns in one table that refers to the primary key in another table (or sometimes in the same table). It establishes a link or relationship between two tables.
- It can contain duplicate values.
- It can contain NULL values, unless explicitly restricted by a NOT NULL constraint.
- A table can have multiple foreign keys.

Differentiation: A primary key uniquely identifies records within its own table, ensuring data integrity and uniqueness. A foreign key establishes a relationship between two tables by referencing the primary key of another table, enforcing referential integrity.

(c) Degree and cardinality of a relation

Degree of a Relation: The degree of a relation (table) is the number of attributes (columns) it contains. It represents the structure or width of the table.
Cardinality of a Relation: The cardinality of a relation (table) is the number of tuples (rows) it contains. It represents the number of records or entries in the table.

Differentiation: The degree refers to the number of columns in a table, which is fixed by the schema. The cardinality refers to the number of rows in a table, which changes dynamically as data is added or removed.

✓Final answer

Database schema defines the structure, while database state is the actual data; a primary key uniquely identifies records in a table, while a foreign key links tables; and the degree of a relation is its number of columns, while its cardinality is its number of rows.

This explanation differentiates between database schema and state (structure vs. data), primary key and foreign key (unique identification vs. relationship linking), and degree and cardinality of a relation (number of columns vs. number of rows).

Let's break down these fundamental database concepts to understand their distinct roles and characteristics.

(a) Database state and database schema

Understanding the difference between database schema and database state is crucial for grasping how a database is structured and how it holds data.

Database Schema

The database schema is the logical design or blueprint of the entire database. It defines the structure of the database, including the names of tables, the names of columns within each table, their data types (e.g., integer, varchar, date), and the relationships between different tables. It also specifies constraints such as primary keys, foreign keys, unique constraints, and check constraints, which enforce rules on the data.

Think of the schema as the architectural plan of a building. It dictates how many rooms there are, their dimensions, where the doors and windows are placed, and the materials used. This plan is relatively static; it changes only when the fundamental structure of the database needs to be altered (e.g., adding a new table or a new column to an existing table).
Database State (or Instance)

The database state, also known as a database instance, refers to the actual data stored in the database at a particular moment in time. It is the content that populates the structure defined by the schema. As data is inserted, updated, or deleted, the database state changes continuously.

Continuing the building analogy, the database state is the actual building itself, furnished with furniture, occupied by people, and with lights on or off at any given moment. This content is dynamic and changes frequently.

Crucially, the database state must always conform to the rules and structure defined by the database schema. For example, if the schema specifies that a column must store integers, the database state cannot contain text in that column.

(b) Primary key and foreign key

Primary keys and foreign keys are fundamental concepts in relational databases, essential for maintaining data integrity and establishing relationships between tables.

Primary Key

A primary key is a column or a set of columns in a table that uniquely identifies each row (or record) in that table. Its main purpose is to ensure that every record in the table can be distinctly identified.
Important
Key characteristics of a primary key:
- Uniqueness: Each value in the primary key column(s) must be unique across all rows in the table. No two rows can have the same primary key value.
- Non-nullability: A primary key cannot contain NULL values. Every row must have a definite primary key value. This property is often referred to as "entity integrity." For example, in a Students table, StudentID would typically be the primary key. Each student has a unique ID, and this ID is never empty.
Foreign Key

A foreign key is a column or a set of columns in one table that refers to the primary key of another table. It establishes a link or relationship between two tables, allowing data from one table to be related to data in another. The table containing the foreign key is called the "referencing table" or "child table," and the table containing the primary key to which the foreign key refers is called the "referenced table" or "parent table."
Important
Key characteristics of a foreign key:
- Referential Integrity: It ensures that relationships between tables are valid. A foreign key value must either be NULL (if allowed) or match an existing primary key value in the referenced table. This prevents "orphan" records.
- Can be NULL: Unlike primary keys, foreign keys can contain NULL values, unless explicitly constrained otherwise (e.g., using a NOT NULL constraint on the foreign key column).
- Can have duplicates: Multiple rows in the referencing table can refer to the same primary key in the referenced table. For example, if we have a Courses table with CourseID as its primary key, and an Enrollments table, the Enrollments table might have a CourseID column that acts as a foreign key, referring to the CourseID in the Courses table. This links each enrollment record to a specific course.

(c) Degree and cardinality of a relation

In the context of relational databases, a "relation" is essentially a table. The terms degree and cardinality describe fundamental properties of these tables.

Degree of a Relation

The degree of a relation (table) is the number of attributes (columns) it contains. It is a measure of the table's width or structure. The degree is a structural property of the relation and is defined by its schema.

For example, if a Students table has columns StudentID, Name, Age, and City, its degree is 4. If you add a new column like Email, the degree increases to 5.
Cardinality of a Relation

The cardinality of a relation (table) is the number of tuples (rows or records) it contains. It is a measure of the table's length or the number of entries it holds. The cardinality is a property of the current state of the relation and changes dynamically as rows are inserted or deleted.

For example, if the Students table currently contains 100 student records, its cardinality is 100. If 5 new students are added, the cardinality becomes 105. If 10 students are removed, it becomes 95.

✓Final answer

Database schema defines the logical structure and rules of the database, while database state refers to the actual data stored at a given moment. A primary key uniquely identifies each row in a table and cannot be NULL, whereas a foreign key links a row in one table to a primary key in another table, enforcing referential integrity. The degree of a relation is the number of columns (attributes) in a table, and the cardinality of a relation is the number of rows (tuples) in a table.

←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:

(a) Database state and database schema

(b) Primary key and foreign key

(c) Degree and cardinality of a relation

🎓Unlock everything free for 14 days

Unlock everything free for 14 days