Database Concepts

Given: Descriptions of various database-related terms.

Answers:

a) Database — A database is a collection of logically related records (data) organised so that it can be easily accessed, managed, and updated.

b) Database Catalog (Data Dictionary) — The DBMS creates a file called the database catalog (or data dictionary) that stores the description (metadata) about the data stored in the database, including schema, constraints, and structure.

c) Primary Key (or Candidate Key) — An attribute (or a set of attributes) that can uniquely identify each tuple in a relation is called a Primary Key. More generally, any such attribute is called a Candidate Key.

d) NULL value — A special value called NULL is stored when the actual data value for an attribute is unknown, missing, or not applicable.

e) Alternate Key — An attribute that can uniquely identify tuples of the table but has not been selected as the primary key is called an Alternate Key. (It is a candidate key that is not the primary key.)

f) Relational Database Management System (RDBMS) — Software such as MySQL that is used to create, manipulate, and maintain a relational database is called an RDBMS.

Concept: A foreign key is an attribute in one relation (referencing relation) whose value is derived from the primary key of another relation (referenced/primary relation). A foreign key is allowed to have NULL values because a NULL foreign key simply means that the relationship between the two records does not exist yet or is not known at the time of insertion.

Reason: If a foreign key were not allowed to take NULL, then every record in the referencing table would be forced to have a corresponding record in the referenced table. This is unnecessarily restrictive in many real-world scenarios.

Example: Consider the STUDENT table from the chapter:

$$\text{STUDENT(RollNumber, SName, SDateofBirth, GUID)}$$

Here, GUID is a foreign key referencing the GUARDIAN table. In Table 8.4, the student Taleem Shah (RollNumber = 3) has a NULL value for GUID. This means that the guardian details for Taleem Shah are not yet known or not yet entered into the system. Forcing a non-NULL value would prevent adding the student record until guardian details are available, which is impractical.

Conclusion: Therefore, foreign keys are allowed to have NULL values to handle situations where the relationship information is temporarily unavailable or not applicable, without violating referential integrity.

Differentiation between Database State and Database Schema:

| Basis | Database Schema | Database State (Instance) |
|---|---|---|
| Definition | The overall design or structure of the database (tables, attributes, constraints, relationships). | The actual data stored in the database at a particular moment in time. |
| Changes | Schema rarely changes; it is defined at the time of database design. | State changes frequently as data is inserted, deleted, or updated. |
| Also called | Intension of the database. | Extension or instance of the database. |
| Example | STUDENT(RollNumber, SName, SDateofBirth, GUID) — this structure is the schema. | The actual rows/tuples present in the STUDENT table at any given time is the database state. |

Summary: Schema is the blueprint (design) of the database, while the database state is the current snapshot of actual data stored according to that blueprint.

Differentiation between Primary Key and Foreign Key:

| Basis | Primary Key | Foreign Key |
|---|---|---|
| Definition | An attribute (or set of attributes) that uniquely identifies each tuple in a relation. | An attribute in one relation whose value is derived from the primary key of another relation. |
| Purpose | Used for unique identification of tuples within the same table. | Used to establish and enforce a link (relationship) between two tables. |
| NULL values | Cannot have NULL values. | Can have NULL values (if not part of its own table's primary key). |
| Duplicate values | Cannot have duplicate values. | Can have duplicate values. |
| Location | Defined within the same relation. | Defined in the referencing (foreign) relation; references the primary relation. |
| Example | RollNumber in STUDENT table. | GUID in STUDENT table references GUID (primary key) of GUARDIAN table. |

Summary: Primary key uniquely identifies records in its own table, while a foreign key creates a relationship between two tables by referencing the primary key of another table.

Differentiation between Degree and Cardinality of a Relation:

| Basis | Degree | Cardinality |
|---|---|---|
| Definition | The number of attributes (columns) in a relation. | The number of tuples (rows) in a relation. |
| Also called | Arity of the relation. | Number of records in the relation. |
| Changes with | Changes only when the schema (structure) is modified (column added/removed). | Changes frequently as data is inserted or deleted. |
| Example | STUDENT(RollNumber, SName, SDateofBirth, GUID) has degree = 4 (4 columns). | If STUDENT table has 6 rows (as in Table 8.4), its cardinality = 6. |

Summary: Degree refers to the number of columns (attributes) in a table, whereas cardinality refers to the number of rows (tuples) in a table.

Given: Comparison of file system and DBMS with respect to data redundancy.

Data Redundancy in File System:
In a file system, the same data may be stored in multiple files. For example, if a student's guardian details (GName, GPhone, GAddress) are stored in the STUDENT file, and two siblings are in the same class, the same guardian's details are stored twice — once for each sibling. This is data redundancy.

How DBMS Avoids Redundancy:

1. Normalisation / Table Splitting: DBMS allows splitting data into multiple related tables. In our example, the STUDENT file is split into STUDENT and GUARDIAN tables. Guardian details are stored only once in the GUARDIAN table, and each student record simply stores the GUID (Guardian ID) as a foreign key.

2. Use of Keys (Primary Key and Foreign Key): By using primary keys and foreign keys, related data from different tables can be linked. This eliminates the need to repeat the same data in multiple places.

3. Centralised Data Storage: All data is stored centrally in a database. Any application or user accesses the same single copy of data, so there is no need to duplicate data across multiple files.

Example: In the STUDENT-ATTENDANCE database:
- Guardian details are stored only once in the GUARDIAN table.
- The STUDENT table stores only the GUID (foreign key) to refer to the guardian.
- This avoids repeating GName, GPhone, GAddress for siblings.

Conclusion: DBMS avoids redundancy by organising data into related tables using keys, ensuring each piece of information is stored only once.

Limitations of File System overcome by Relational DBMS:

1. Data Redundancy:
- *File System:* Same data is stored in multiple files, leading to wastage of storage space.
- *RDBMS:* Data is stored in related tables using primary and foreign keys, so each piece of data is stored only once.

2. Data Inconsistency:
- *File System:* When the same data is stored in multiple files, updating one file may leave others unchanged, causing inconsistency.
- *RDBMS:* Since data is stored at one place, updating it once reflects everywhere, maintaining consistency.

3. Data Isolation:
- *File System:* Data is scattered across different files in different formats, making it difficult to retrieve related data together.
- *RDBMS:* Tables are related through keys, so data from multiple tables can be retrieved together using queries (SQL).

4. Data Dependence:
- *File System:* Application programs are tightly coupled with the file structure. Any change in file structure requires changes in all programs.
- *RDBMS:* DBMS provides data abstraction; changes in storage structure do not affect application programs.

5. Controlled Data Sharing:
- *File System:* It is difficult to control who can access which data.
- *RDBMS:* DBMS provides access control mechanisms (user privileges, roles) to allow controlled and secure data sharing among multiple users.

6. Difficulty in Querying:
- *File System:* Retrieving specific data requires writing complex programs.
- *RDBMS:* SQL (Structured Query Language) allows easy and powerful querying of data.

Conclusion: RDBMS overcomes all major limitations of the file system by providing a structured, consistent, and secure way to store and manage data.

Given: Sports Preferences table with Roll_no and Preference columns.

a) Can NULL be assigned to Roll_no 24's Preference field?

Answer: The school rule states that *each student must participate in a sports activity*, meaning every student must give a preference. Therefore, according to the school's rule, a NULL value should NOT be assigned to the Preference field.

However, from a purely technical/DBMS perspective, if the Preference column does not have a NOT NULL constraint defined, then NULL can be stored. But to enforce the school rule, a NOT NULL constraint should be applied to the Preference column so that no student can have a missing preference.

Conclusion: NULL should not be allowed for Preference; a NOT NULL constraint must be applied.

---

b) Roll no 17 has two preferences — which property is violated?

Property Violated: The school rule states each student gives only one preference. Having two rows for Roll_no 17 (Badminton and Football) violates the entity integrity and the concept of functional dependency (Roll_no should uniquely determine Preference).

More specifically, this violates the property that each tuple in a relation must be unique (no duplicate or conflicting tuples for the same entity).

Constraint/Key to prevent this: If Roll_no is defined as the Primary Key of the Sports Preferences relation, then the DBMS will not allow two rows with the same Roll_no. The primary key constraint ensures that each student (identified by Roll_no) can have only one entry (one preference) in the table.

$$\text{PRIMARY KEY (Roll\_no)}$$

This will prevent Roll_no 17 from appearing twice.

---

c) Can the tuple (NULL, Kabaddi) exist in the Sports Preferences relation?

Answer: No, this tuple cannot exist in the Sports Preferences relation.

Reason: Roll_no is the Primary Key of the relation. A primary key cannot have a NULL value (Entity Integrity Rule). Since the Roll_no for the Kabaddi tuple is NULL, it violates the primary key constraint. Therefore, this tuple cannot be inserted into the relation.

Also, logically, a sports preference without a student's roll number is meaningless in this context.

Given:

Section 1 Table (ordered by Roll_no):
| Roll_no | Sports |
|---|---|
| 9 | Cricket |
| 13 | Football |
| 17 | Badminton |
| 21 | Hockey |
| 24 | Cricket |

Section 2 Table (ordered by Sports, columns in different order):
| Sports | Roll_no |
|---|---|
| Badminton | 17 |
| Cricket | 9 |
| Cricket | 24 |
| Football | 13 |
| Hockey | 21 |

Answer: Yes, the states of both relations are equivalent.

Justification:

In the Relational Data Model, the following two properties hold:

1. Order of rows (tuples) does not matter: A relation is defined as a set of tuples. Sets are unordered, so the order in which rows appear does not affect the state of the relation. Both tables contain the same 5 tuples — just arranged differently (Section 1 by Roll_no, Section 2 by Sports name).

2. Order of columns (attributes) does not matter: A relation's attributes can be in any order. The actual data values associated with each attribute name remain the same regardless of column order. Section 1 has (Roll_no, Sports) order while Section 2 has (Sports, Roll_no) order, but the data is identical.

Verification: Both tables contain exactly the same 5 data pairs:
- (9, Cricket), (13, Football), (17, Badminton), (21, Hockey), (24, Cricket)

Since both tables have the same set of tuples (same data, same attribute values), their database states are equivalent.

Given: A school canteen database needs to be designed to track items and generate bills.

---

a) Relation to store item name and price:

Relation Name: ITEM

| Attribute Name | Data Type | Description |
|---|---|---|
| ItemID | INTEGER | Unique identifier for each item |
| ItemName | VARCHAR(50) | Name of the item (NOT NULL) |
| Price | DECIMAL(8,2) | Price of the item (NOT NULL, > 0) |

Schema:
$$\text{ITEM(\underline{ItemID}, ItemName, Price)}$$

Restriction:
- ItemID should be defined as the Primary Key to ensure each item is stored only once (no duplicate items).
- ItemName should have a NOT NULL constraint.
- Price should have a NOT NULL constraint and a CHECK constraint (Price > 0).
- Optionally, ItemName can have a UNIQUE constraint so no two items have the same name.

---

b) Relation for bill generation (quantity of item purchased):

This information should be in a new relation because the quantity purchased is related to a specific order/bill, not to the item itself. Adding it to the ITEM relation would cause redundancy.

Relation Name: BILL

| Attribute Name | Data Type | Description |
|---|---|---|
| BillNo | INTEGER | Unique bill number for each order |
| ItemID | INTEGER | ID of the item purchased (Foreign Key) |
| Quantity | INTEGER | Number of units purchased (NOT NULL, > 0) |
| BillDate | DATE | Date of purchase |

Schema:
$$\text{BILL(\underline{BillNo}, ItemID, Quantity, BillDate)}$$

Primary Key: BillNo — ensures each bill is unique (satisfies restriction i).

Foreign Key: ItemID references ITEM(ItemID) — ensures a bill can only be generated for items that exist in the ITEM relation (satisfies restriction ii).

Restrictions satisfied:
- (i) BillNo as Primary Key ensures the same bill number cannot be generated for different orders.
- (ii) ItemID as Foreign Key referencing ITEM ensures bills can only be generated for items available in the canteen.

---

c) Where should the 'calories' attribute be stored?

The attribute 'calories' should be stored in the ITEM relation.

Reason: Calories is a property of the item itself (e.g., a samosa always has a fixed caloric value), not of a particular bill or order. Since it is an attribute that describes an item, it logically belongs to the ITEM table.

Updated ITEM schema:
$$\text{ITEM(\underline{ItemID}, ItemName, Price, Calories)}$$

Given:
$$\text{EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID)}$$
$$\text{DEPENDENT(EmployeeID, DependentName, Relationship)}$$

---

a) Candidate Keys of EMPLOYEE relation:

A candidate key is an attribute (or set of attributes) that can uniquely identify each tuple in the relation.

- AadharNumber — Every citizen has a unique Aadhar number. It can uniquely identify each employee. ✓ (Candidate Key)
- EmployeeID — Every employee is assigned a unique Employee ID by the organisation. It can uniquely identify each employee. ✓ (Candidate Key)
- Name, Address, Department — These cannot guarantee uniqueness (two employees can have the same name, address, or department). ✗

Candidate Keys of EMPLOYEE: AadharNumber and EmployeeID

One of these (say EmployeeID) will be chosen as the Primary Key; the other (AadharNumber) becomes the Alternate Key.

---

b) Tables and key required to retrieve dependent details of a particular employee:

To retrieve the details of dependents of a particular employee, we need:

- Tables required: EMPLOYEE and DEPENDENT
- Key required:
- EmployeeID is the Primary Key in the EMPLOYEE table.
- EmployeeID is the Foreign Key in the DEPENDENT table (it references the primary key of EMPLOYEE).

By joining the two tables on EmployeeID, we can retrieve the dependent details (DependentName, Relationship) for any specific employee.

Summary: Tables — EMPLOYEE and DEPENDENT; Key — EmployeeID (Primary Key in EMPLOYEE, Foreign Key in DEPENDENT).

---

c) Degree of EMPLOYEE and DEPENDENT relations:

- Degree = Number of attributes (columns) in a relation.

- Degree of EMPLOYEE = 5
(Attributes: AadharNumber, Name, Address, Department, EmployeeID)

- Degree of DEPENDENT = 3
(Attributes: EmployeeID, DependentName, Relationship)

Given: SCHOOL_UNIFORM database with:
- UNIFORM(UCode [PK], UName [NOT NULL], UColor)
- COST(UCode, Size [Composite PK = {UCode, Size}], Price [CHECK: Price > 0])

---

a) Inserting tuples into UNIFORM relation:

i) (7, Handkerchief, NULL)

Answer: Yes, this tuple CAN be inserted.

Reason:
- UCode = 7 is a new value not present in the existing UNIFORM table, so the Primary Key constraint is satisfied.
- UName = 'Handkerchief' is not NULL, so the NOT NULL constraint on UName is satisfied.
- UColor = NULL is allowed because there is no NOT NULL constraint defined on the UColor column.

$$\therefore \text{Insertion is valid.}$$

---

ii) (4, Ribbon, Red)

Answer: No, this tuple CANNOT be inserted.

Reason:
- UCode = 4 already exists in the UNIFORM table (Tie, Blue). Since UCode is the Primary Key, it must be unique. Inserting another record with UCode = 4 would violate the Primary Key constraint.

$$\therefore \text{Insertion is NOT valid — Primary Key violation.}$$

---

iii) (8, NULL, White)

Answer: No, this tuple CANNOT be inserted.

Reason:
- UCode = 8 is new, so Primary Key constraint is satisfied.
- However, UName = NULL violates the NOT NULL constraint defined on the UName column.

$$\therefore \text{Insertion is NOT valid — NOT NULL constraint violation on UName.}$$

---

b) Inserting tuples into COST relation:

i) (7, S, 0)

Answer: No, this tuple CANNOT be inserted.

Reason:
- The composite primary key {UCode, Size} = {7, S} does not already exist in COST, so no Primary Key violation.
- However, Price = 0 violates the CHECK constraint (Price > 0). Price must be strictly greater than 0.

\therefore \text{Insertion is NOT valid — CHECK constraint violation (Price must be > 0).}

---

ii) (9, XL, 100)

Answer: No, this tuple CANNOT be inserted.

Reason:
- The composite primary key {UCode = 9, Size = XL} is new, so no Primary Key violation.
- Price = 100 > 0, so CHECK constraint is satisfied.
- However, UCode = 9 does not exist in the UNIFORM table. Since UCode in COST is a Foreign Key referencing UCode (Primary Key) of UNIFORM, inserting a UCode value that does not exist in UNIFORM violates the Referential Integrity (Foreign Key constraint).

$$\therefore \text{Insertion is NOT valid — Foreign Key (Referential Integrity) constraint violation.}$$

Given:
$$\text{Movie(Movie\_ID, MovieName, ReleaseDate)}$$
$$\text{Audi(AudiNo, Movie\_ID, Seats, ScreenType, TicketPrice)}$$

Context: One movie can be shown in more than one auditorium.

---

a) Is Movie_ID correct as Primary Key of MOVIE relation?

Answer: Yes, it is correct to assign Movie_ID as the Primary Key of the MOVIE relation.

Reason: Each movie has a unique Movie_ID assigned to it. Since Movie_ID uniquely identifies each movie record in the MOVIE table, it satisfies the requirements of a primary key (unique and not null).

$$\text{Primary Key of MOVIE} = \text{Movie\_ID}$$

---

b) Is AudiNo correct as Primary Key of AUDI relation?

Answer: No, AudiNo alone is NOT the correct primary key for the AUDI relation.

Reason: Since one movie can be shown in more than one auditorium, the same AudiNo can screen different movies at different times. Also, the same auditorium (AudiNo) can show different movies, meaning AudiNo alone does not uniquely identify a tuple in the AUDI table.

Suggested Primary Key: The combination of {AudiNo, Movie_ID} should be used as the Composite Primary Key. This combination uniquely identifies which movie is being screened in which auditorium.

$$\text{Composite Primary Key of AUDI} = \{\text{AudiNo, Movie\_ID}\}$$

---

c) Is there any foreign key in these relations?

Answer: Yes.

- Movie_ID in the AUDI relation is a Foreign Key.
- It references Movie_ID (Primary Key) in the MOVIE relation.
- This foreign key establishes the relationship between the two tables — it ensures that a movie screened in an auditorium must exist in the MOVIE table.

$$\text{Foreign Key: AUDI.Movie\_ID} \rightarrow \text{MOVIE.Movie\_ID}$$

There is no foreign key in the MOVIE relation.

Given: STUDENT-PROJECT database with three tables:
- STUDENT(Roll No, Name, Class, Section, Registration ID)
- PROJECT(ProjectNo, PName, SubmissionDate)
- PROJECT ASSIGNED(Registration ID, ProjectNo)

---

a) Primary Key of each table:

- STUDENT table: Registration ID is the Primary Key.
- Reason: Roll No is unique within a class/section but may not be unique across the entire school (e.g., Roll No 11 could exist in both Class XI and XII). Registration ID is unique for every student across the school.
- (Note: If Roll No is unique across the entire school, it can also serve as a candidate key.)

- PROJECT table: ProjectNo is the Primary Key.
- Reason: Each project has a unique project number that identifies it.

- PROJECT ASSIGNED table: {Registration ID, ProjectNo} is the Composite Primary Key.
- Reason: A student (Registration ID) can be assigned only one project, and a project can be assigned to multiple students. The combination uniquely identifies each assignment.

---

b) Foreign Keys in PROJECT-ASSIGNED table:

The PROJECT ASSIGNED table has two foreign keys:

1. Registration ID — Foreign Key referencing Registration ID (Primary Key) of the STUDENT table.
2. ProjectNo — Foreign Key referencing ProjectNo (Primary Key) of the PROJECT table.

These foreign keys ensure that a project can only be assigned to existing students and existing projects.

---

c) Is there any alternate key in STUDENT table?

Answer: Yes.

Justification: The STUDENT table has the following attributes: Roll No, Name, Class, Section, Registration ID.

- Registration ID is chosen as the Primary Key.
- Roll No can also uniquely identify a student (assuming Roll No is unique across the entire school or within a class). Since Roll No can uniquely identify tuples but is not chosen as the primary key, it becomes an Alternate Key.

$$\text{Alternate Key of STUDENT} = \text{Roll No}$$

(An alternate key is a candidate key that is not selected as the primary key.)

---

d) Can a user assign duplicate value to RollNo in STUDENT table?

Answer: No, a user cannot assign duplicate values to RollNo.

Justification: Roll No is a candidate key (alternate key) of the STUDENT table, meaning it can uniquely identify each student. Even though Registration ID is the primary key, Roll No should have a UNIQUE constraint applied to it to prevent duplicate values. If the UNIQUE constraint is defined, the DBMS will reject any attempt to insert a duplicate Roll No.

Furthermore, from the given data, each student has a distinct Roll No (11, 12, 21, 22, 23), confirming that Roll No values are unique. Assigning duplicate Roll No values would violate the uniqueness property and make it impossible to distinguish between students.

Given: STUDENT-PROJECT database:
- STUDENT(Roll No, Name, Class, Section, Registration ID) — Primary Key: Registration ID
- PROJECT(ProjectNo, PName, SubmissionDate) — Primary Key: ProjectNo
- PROJECT ASSIGNED(Registration ID, ProjectNo) — Composite Primary Key; Foreign Keys: Registration ID → STUDENT, ProjectNo → PROJECT

---

a) Insert a student record with missing roll number value:

Answer: Yes, this operation CAN be performed (subject to constraints).

Reason: Roll No is not the primary key of the STUDENT table; Registration ID is the primary key. If no NOT NULL constraint has been explicitly defined on the Roll No column, then a NULL value can be stored for Roll No. The record can be inserted as long as Registration ID (primary key) has a valid, unique, non-null value.

$$\therefore \text{Insertion is possible if Roll No has no NOT NULL constraint.}$$

---

b) Insert a student record with missing registration number value:

Answer: No, this operation CANNOT be performed.

Reason: Registration ID is the Primary Key of the STUDENT table. A primary key cannot have a NULL value (Entity Integrity Rule). Therefore, inserting a student record without a Registration ID value would violate the primary key constraint, and the DBMS will reject this operation.

$$\therefore \text{Insertion is NOT valid — Primary Key cannot be NULL.}$$

---

c) Insert a project detail without submission-date:

Answer: Yes, this operation CAN be performed (subject to constraints).

Reason: SubmissionDate is not the primary key of the PROJECT table; ProjectNo is the primary key. If no NOT NULL constraint has been explicitly defined on the SubmissionDate column, then a NULL value can be stored for SubmissionDate. The record can be inserted as long as ProjectNo (primary key) has a valid, unique, non-null value.

$$\therefore \text{Insertion is possible if SubmissionDate has no NOT NULL constraint.}$$

---

d) Insert a record (IP-101-19, 206) in PROJECT-ASSIGNED table:

Answer: No, this operation CANNOT be performed.

Reason: The PROJECT ASSIGNED table has two foreign keys:
1. Registration ID references STUDENT(Registration ID)
2. ProjectNo references PROJECT(ProjectNo)

Checking the given data:
- Registration ID = IP-101-19 does not exist in the STUDENT table (existing IDs are IP-101-15, IP-104-15, CS-103-14, CS-101-14, CS-101-10).
- ProjectNo = 206 does not exist in the PROJECT table (existing ProjectNos are 101–106).

Inserting this record would violate the Referential Integrity (Foreign Key) constraint for both foreign keys. The DBMS will reject this insertion.

$$\therefore \text{Insertion is NOT valid — Foreign Key constraint violation for both Registration ID and ProjectNo.}$$