NCERT Solutions for Class 12 Computer Science Chapter 8 Database Concepts

(a) Table

(b) Data dictionary

(c) Primary key

(d) NULL

(e) Alternate Key

(f) Database Management System (DBMS)

A null value can be entered in a foreign key, indicating that the records are not related. In certain situations, a foreign key may accept a NULL value if it's not a part of the primary key of the foreign table.

For example, consider an Orders table in a database. Each order may or may not be associated with a customer. If an order is placed by a guest or a new customer who hasn't been added to the system yet, the CustomerID foreign key in the Orders table can be NULL to indicate that there is no associated customer record for that order.

A database stores the data at a central location wherefrom all application programs can access data. This removes the need of saving own data by application program and thus it reduces data redundancy.

The limitations of file system overcome by a relational DBMS are as follows:

1. Data Redundancy

2. Data Inconsistency

3. Data Isolation

4. Data Dependence

5. Difficulty in data access

6. Controlled data sharing

1. In a relational database model where each student is required to have exactly one preference for a sports activity, assigning a NULL value to Roll no 24's preference field is not permitted.

2. The primary key constraint ensures uniqueness in a relational database table. If Roll no 17 has two sports preferences, it violates this rule because a primary key constraint on "Roll_no" would not allow different values for the same roll number. By using a primary key constraint on "Roll_no," the relational database management system (DBMS) can prevent such violations by rejecting attempts to insert rows with duplicate roll numbers.

3. No, since no student has selected Kabaddi as their preferred sport, there should not be an entry for Kabaddi in the Sports Preferences table.

Yes, the states of both the relations are equivalent as the order of rows and columns does not matter and data is just the same in both the relations.

(a) To store each item name along with its price in the canteen database, we can create a relation (table) called "Items" with the following attributes:

Items table

The restriction that should be used while defining the "Items" relation is to set the "ItemNo" attribute as the primary key. This ensures that each item number is unique and that each item and its price are stored only once in the database.

(b) Yes, the item sale information should be stored in a separate relation, say "SaleOrders".

SaleOrders table

With this design, we satisfy both restrictions:

Each order has a unique OrderNo, ensuring that the same bill cannot be generated for different orders. The foreign key constraint on ItemNo ensures that bills can only be generated for available items in the canteen.

(c) 'Calories' should be stored in the "Items" table because they are directly associated with specific items.

(a) In the EMPLOYEE table, the attributes AadharNumber and EmployeeID can be used as candidate keys. This means that either AadharNumber or EmployeeID can uniquely identify each record in the EMPLOYEE table.

(b) The EMPLOYEE and DEPENDENT tables are linked using the EmployeeID key, which is utilized to retrieve details of dependents associated with a specific employee.

(c) In the EMPLOYEE relation, there are five attributes, resulting in a degree of 5. Similarly, the DEPENDENT relation has three attributes, making its degree 3.

(a)

1. Tuple (7, Handkerchief, NULL): This tuple can be inserted because there is no constraint mentioned in the schema that prohibits NULL values for the UColor attribute.

2. Tuple (4, Ribbon, Red): This tuple can be inserted as all attributes have valid non-null values.

3. Tuple (8, NULL, White): This tuple cannot be inserted because UName attribute cannot be NULL as per the schema constraints.

(b)

1. Tuple (7, S, 0): This tuple cannot be inserted because the COST Price attribute must be greater than 0 as per the schema constraints.

2. Tuple (9, XL, 100): This tuple can be inserted as all attributes have valid values and the COST Price is greater than 0.

(a) Yes, assigning Movie_ID as the primary key in the MOVIE relation is correct because each movie has a unique Movie_ID.

(b) It is not correct to assign AudiNo as the primary key in the AUDI relation because an AudiNo can be repeated for different movies screened in different auditoriums. To uniquely identify each record in the AUDI relation, a composite primary key consisting of AudiNo and Movie_ID should be used.

(c) Yes, there is a foreign key in the AUDI relation. The Movie_ID attribute in the AUDI relation is a foreign key that references the Movie_ID primary key in the MOVIE relation.

(a) Primary key of each table:

STUDENT: Roll No.
PROJECT: ProjectNo.
PROJECT ASSIGNED: Registration_ID.

(b) The ProjectNo column in the PROJECT ASSIGNED table is a foreign key that references the ProjectNo column in the PROJECT table.

(c) In the STUDENT table, the Registration_ID column serves as an alternate key since it uniquely identifies each student.

(d) No, a user cannot assign a duplicate value to the Roll No field of the STUDENT table because Roll No is the primary key of the table and it must be unique.

(a) No. the Roll No attribute in the STUDENT table is marked as primary key and NOT NULL. Therefore, inserting a student record with a missing Roll No value would violate the NOT NULL constraint and is not allowed.

(b) Yes, the registration_ID attribute in the STUDENT table does not have a NOT NULL constraint specified in the schema. Therefore, it is possible to insert a student record without registration number value.

(c) Yes, the SubmissionDate attribute in the PROJECT table does not have a NOT NULL constraint specified in the schema. Therefore, it is possible to insert a project detail without a SubmissionDate value.

(d) No, we cannot perform this operation. ProjectNo in PROJECT ASSIGNED table serves as a foreign key that references the primary key in the PROJECT table. Since ProjectNo "206" is not present in the PROJECT table, it cannot be inserted into the PROJECT ASSIGNED table.