Database Concepts

a) A database is a collection of logically related records.

b) The file that contains description about the data stored in the database is called meta-data or data dictionary.

c) An attribute that can uniquely identify tuples in a relation is a candidate key.

d) The special value stored when actual data is unknown is NULL.

e) An attribute that can uniquely identify tuples but is not chosen as the primary key is an alternate key.

f) The software used to create, manipulate, and maintain a relational database is a DBMS.

A foreign key is allowed to have NULL value when the referencing attribute is not a part of the primary key of that table. This is because the relation may not always have a matching record in the referenced table.

For example, in the STUDENT table, GUID is a foreign key referring to GUARDIAN(GUID). If a student’s guardian details are not available, then GUID can be NULL. The chapter also states that a foreign key can take NULL value if it is not part of the primary key of the foreign table.

### a) Database state and database schema
- Database schema is the design or skeleton of the database. It describes table names, columns, data types, constraints, and relationships.
- Database state or database instance is the snapshot of the database at a given time after data is loaded.

### b) Primary key and foreign key
- A primary key uniquely identifies tuples within the same relation.
- A foreign key is an attribute whose value is derived from the primary key of another relation and is used to relate two tables.

### c) Degree and cardinality of a relation
- Degree = number of attributes in a relation.
- Cardinality = number of tuples/rows in a relation.

A DBMS avoids redundancy by storing related data in related tables and using common fields to connect them.

In the school example, instead of repeating the student name and guardian details in the attendance file, the database keeps:
- STUDENT table for student details
- GUARDIAN table for guardian details
- ATTENDANCE table for attendance records

The RollNumber links ATTENDANCE with STUDENT, and GUID links STUDENT with GUARDIAN. Also, guardian details are stored only once in the GUARDIAN table, so the same guardian information is not repeated for siblings. This reduces redundancy and helps avoid inconsistency.

The limitations of a file system that can be overcome by a relational DBMS are:

- Difficulty in access
- Data redundancy
- Data inconsistency
- Data isolation
- Data dependence
- Controlled data sharing

A relational DBMS helps manage related data efficiently, supports linking between tables, and allows controlled access through keys and queries.

The table shown is a relation of sports preferences. The important idea is that each student should give only one preference and the relation must not contain duplicate or invalid tuples. The answers to the subparts are based on relation properties, NULL values, and keys.

Yes. NULL may be assigned because the student may not be interested in sports, so the preference is unknown/not applicable. The chapter states that NULL is used to represent values that are unknown or non-applicable to certain attributes.

The property of a relation violated is that each tuple must be distinct and each attribute should have a single atomic value. Here, roll number 17 has given two preferences, so one student appears in more than one tuple, which breaks the idea that one tuple should represent one distinct record.

Yes, we can use a constraint/key to check this. Since the school rule says each student should give only one preference, we can make Roll_no a primary key or at least place a UNIQUE constraint on it so that the same roll number cannot appear twice in the relation.

Yes, it is possible. A tuple can exist even if no student chose that sport, because the relation can contain data that are not referenced by another relation. The chapter does not forbid such a tuple. Since the relation is about sports preferences, Kabaddi with a roll number value can still be stored as a record, provided it follows the relation’s rules.

The two tables are not equivalent as states, even though they contain the same information.

Reason:
- The sequence of attributes is different in the two relations, and according to the chapter, attribute order is immaterial.
- The sequence of tuples is also immaterial.
- However, to be equivalent as relation states, they must represent the same set of tuples with the same attributes and values.

Here, both tables contain the same roll numbers and sports values, just arranged differently. So as relations, they represent the same information and are equivalent.

For a canteen database, we need to store item details and purchase details separately so that each item is stored once and purchases can be recorded repeatedly.

A suitable design is:
- ITEM relation for item name, price, and calories
- BILL/ORDER relation for purchase details such as item bought and quantity

This separation avoids redundancy and allows billing for multiple orders.

To store each item name with its price, use a relation such as ITEM(ItemID, ItemName, Price).

A suitable choice of attributes is:
- ItemID: integer, unique identifier
- ItemName: string/text
- Price: numeric/decimal

Since each item and its price should be stored only once, the ItemID should be defined as the primary key or at least UNIQUE, so duplicate item records are not inserted.

The quantity of an item purchased should be kept in a new relation, because it belongs to the billing/purchase transaction, not to the item master list.

A suitable relation can be BILL or ORDER with attributes like:
- BillNo / OrderNo: integer
- ItemID: integer
- Quantity: integer
- optionally Date: date

To satisfy the restrictions:
1. The same bill cannot be generated for different orders → use BillNo/OrderNo as the primary key.
2. Bill can be generated only for available items → make ItemID a foreign key referencing ITEM(ItemID).

This way only valid items can appear in billing records.

The attribute calories should be stored in the ITEM relation, because calories are a property of the item itself, not of the individual purchase. So the item master table should include it along with item name and price.

For the EMP-DEPENDENT database, the given relations are:
- EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID)
- DEPENDENT(EmployeeID, DependentName, Relationship)

These relations can be used to store employee details and dependent details. The key idea is that EmployeeID links the dependent to the correct employee.

The candidate keys are the attributes that can uniquely identify an employee. In the given relation EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID), the attributes that can be used as candidate keys are AadharNumber and EmployeeID, because both can uniquely identify tuples.

To retrieve details of a dependent of a particular employee, we need the tables:
- EMPLOYEE
- DEPENDENT

The required key is EmployeeID, because it is the common link between the two tables and is the primary key in EMPLOYEE and a foreign key in DEPENDENT.

The degree of a relation is the number of attributes.

- EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID) has 5 attributes, so its degree is 5.
- DEPENDENT(EmployeeID, DependentName, Relationship) has 3 attributes, so its degree is 3.