Database Concepts

This page contains the NCERT Computer Science class 12 chapter 8 Database Concepts. You can find the solutions for the chapter 8 of NCERT class 12 Computer Science Exercise. So is the case if you are looking for NCERT class 12 Computer Science related topic Database Concepts questions and answers for the Exercise
Buy Class 12 Computer Science Books NOW!
Exercise
Question 1
1. Give the terms for each of the following:
a)
Collection of logically related records.
b)
DBMS creates a file that contains description about the data stored in the database.
c)
Attribute that can uniquely identify the tuples in a relation.
d)
Special value that is stored when actual data value is unknown for an attribute.
e)
An attribute which can uniquely identify tuples of the table but is not defined as primary key of the table.
f)
Software that is used to create, manipulate and maintain a relational database.
Answer 1
a)
Database.
b)
Meta-data / Database catalog / Data dictionary.
c)
Primary key.
d)
NULL.
e)
Alternate key (candidate key not chosen as primary key).
f)
DBMS (Database Management System).
Question 2
2. Why foreign keys are allowed to have NULL values? Explain with an example.
Answer 2
A foreign key represents a relationship between two tables.
If the relationship is unknown or not applicable, the FK can store NULL (when the FK is not part of the primary key).
Example
•
In STUDENT(StudentID, GUID), GUID references GUARDIAN(GuardianID). If guardian details are not yet available, GUID can be NULL.
Question 3
3. Differentiate between:
a)
Database state and database schema.
b)
Primary key and foreign key.
c)
Degree and cardinality of a relation.
Answer 3
a) Database Schema vs Database State (Instance)
Basis
Database Schema
Database State / Instance
Meaning
The design/structure (blueprint) of the database.
The actual data (snapshot) stored at a particular time.
Changes
Changes rarely (only when structure is modified).
Changes frequently (insert/update/delete).
Includes
Tables, attributes, constraints, relationships.
Current set of tuples (records) in the tables.
Also called
Database schema.
Database instance / database state.
b) Primary Key vs Foreign Key
Basis
Primary Key
Foreign Key
Meaning
Attribute that uniquely identifies tuples in the same table.
Attribute that links two tables by referring to a primary key.
Where it exists
In the same relation (table) it identifies.
In the related relation (table) for mapping.
Uniqueness
Must be unique and generally NOT NULL.
Can repeat; may be NULL in some cases.
Purpose
Identification of records (tuples).
Maintains relationship / referential integrity.
c) Degree vs Cardinality of a Relation
Basis
Degree
Cardinality
Meaning
Number of attributes (columns) in a relation.
Number of tuples (rows) in a relation.
Depends on
Table structure.
Amount of data stored.
Example
If a table has 5 columns → degree = 5.
If a table has 20 rows → cardinality = 20.
Question 4
4. Compared to a file system, how does a database management system avoid redundancy in data through a database?
Answer 4
In a file system, the same data can be repeated in multiple files (redundancy).
In a DBMS, related data is stored once in separate tables and linked using keys.
Example: Student name is stored in STUDENT and referenced in ATTENDANCE using RollNo.
Question 5
5. What are the limitations of file system that can be overcome by a relational DBMS?
Answer 5
i.
Difficulty in access (needs programs for every new requirement).
ii.
Data redundancy.
iii.
Data inconsistency.
iv.
Data isolation (no proper linking between files).
v.
Data dependence (file format change breaks programs).
vi.
Controlled data sharing is difficult.
Question 6
6. A school has a rule that each student must participate in a sports activity. So each one should give only one preference for sports activity. Suppose there are five students in a class, each having a unique roll number. The class representative has prepared a list of sports preferences as shown below. Answer the following:
Table: Sports Preferences
Roll_no
Preference
9
Cricket
13
Football
17
Badminton
17
Football
21
Hockey
24
NULL
NULL
Kabaddi
a)
Roll no 24 may not be interested in sports. Can a NULL value be assigned to that student’s preference field?
b)
Roll no 17 has given two preferences in sports. Which property of relational DBMS is violated here? Can we use any constraint or key in the relational DBMS to check against such violation, if any?
c)
Kabaddi was not chosen by any student. Is it possible to have this tuple in the Sports Preferences relation?
Answer 6
a) NULL in preference for Roll no 24
No — since the school rule requires every student to give exactly one preference, the Preference attribute should not allow NULL. Enforce this by making Preference NOT NULL (and ensure Roll_no is PRIMARY KEY or UNIQUE + NOT NULL).
b) Roll no 17 has two preferences
•
Violates the rule “one preference per student” and uniqueness of Roll_no.
•
Use PRIMARY KEY / UNIQUE on Roll_no to prevent duplicates.
c) Tuple (NULL, Kabaddi)
•
Not valid as Roll_no is a primary key (PK cannot be NULL).
•
Also, it does not represent any actual student.
Question 7
7. In another class having 2 sections, the two respective class representatives have prepared 2 separate Sports Preferences tables, as shown below:
Sports preference of section 1 (arranged on roll number column)
Table: Sports Preferences
Roll_no
Sports
9
Cricket
13
Football
17
Badminton
21
Hockey
24
Cricket
Sports preference of section 2 (arranged on sports name column, and column order is also different)
Table: Sports Preferences
Sports
Roll_no
Badminton
17
Cricket
9
Cricket
24
Football
13
Hockey
21
Are the states of both the relations equivalent? Justify.
Answer 7
•
Yes, the states are equivalent because a relation is a set of tuples.
•
Order of rows and columns does not matter as long as attributes match.
Question 8
8. The school canteen wants to maintain records of items available in the school canteen and generate bills when students purchase any item from the canteen. The school wants to create a canteen database to keep track of items in the canteen and the items purchased by students. Design a database by answering the following questions:
a)
To store each item name along with its price, what relation should be used? Decide appropriate attribute names along with their data type. Each item and its price should be stored only once. What restriction should be used while defining the relation?
b)
In order to generate bill, we should know the quantity of an item purchased. Should this information be in a new relation or a part of the previous relation? If a new relation is required, decide appropriate name and data type for attributes. Also, identify appropriate primary key and foreign key so that the following two restrictions are satisfied:
i)
The same bill cannot be generated for different orders.
ii)
Bill can be generated only for available items in the canteen.
c)
The school wants to find out how many calories students intake when they order an item. In which relation should the attribute ‘calories’ be stored?
Answer 8
Step 1: Identify what data we are storing
Real-world object
What we store
Why
Item
Item details (name, price, calories)
These details are the same for everyone and should be stored only once.
Bill/Order
Bill number, student info, date
One bill = one order, so bill details should be separate.
Items in a bill
Which items were bought + quantity
Quantity changes from bill to bill, so it must be stored per bill.
(a) Relation for Items (store name and price only once)
Relation 1: ITEM
Attribute
Suggested Data Type
Restriction (Constraint)
Reason
ItemID
INTEGER
PRIMARY KEY, NOT NULL, UNIQUE
Uniquely identifies each item.
ItemName
VARCHAR/TEXT
NOT NULL, (optional) UNIQUE
Item name must exist; avoid duplicate item names.
Price
INTEGER/DECIMAL
NOT NULL, CHECK (Price > 0)
Price should be positive.
Calories (used in part c)
INTEGER
NOT NULL, CHECK (Calories >= 0)
Calories are part of item details.
This satisfies: Item name and price are stored only once because each item appears once in ITEM.
(b) Billing and Quantity: Same table or new relation?
Decision: Create a new relation
Do not add Quantity to ITEM because quantity is not a property of the item.
Quantity depends on the order/bill (it changes every time someone buys).
Relation 2: BILL
Attribute
Suggested Data Type
Restriction (Constraint)
Why
BillNo
INTEGER
PRIMARY KEY, NOT NULL, UNIQUE
Ensures one bill = one order (condition i).
RollNo
INTEGER
NOT NULL
Identifies student (simple design).
BillDate
DATE
NOT NULL
Bill date required.
Condition (i): Same bill cannot be generated for different orders is ensured by PRIMARY KEY (BillNo).
Relation 3: BILL_ITEM (Items in each bill + quantity)
Attribute
Suggested Data Type
Restriction (Constraint)
Why
BillNo
INTEGER
FOREIGN KEY → BILL(BillNo), NOT NULL
Links bill items to the correct bill.
ItemID
INTEGER
FOREIGN KEY → ITEM(ItemID), NOT NULL
Ensures only available items can be billed (condition ii).
Quantity
INTEGER
NOT NULL, CHECK (Quantity > 0)
Quantity must be positive.
Primary Key
—
(BillNo, ItemID) (Composite PK)
Prevents same item repeating twice in the same bill.
Condition (ii): Bill can only be generated for available items is ensured by foreign key ItemID referencing ITEM(ItemID).
(c) Where should calories be stored?
Calories should be stored in ITEM because calories are a property of the item itself, not of the bill/order.
So calories belongs in: ITEM(ItemID, ItemName, Price, Calories).
Final database structure (clean summary)
Relation (Table)
Primary Key
Important Foreign Keys
Main purpose
ITEM(ItemID, ItemName, Price, Calories)
ItemID
—
Stores item details once.
BILL(BillNo, RollNo, BillDate)
BillNo
—
Stores bill/order details.
BILL_ITEM(BillNo, ItemID, Quantity)
(BillNo, ItemID)
BillNo → BILL, ItemID → ITEM
Stores purchased items and quantity for each bill.
Question 9
9. An organisation wants to create a database EMP-DEPENDENT to maintain following details about its employees and their dependent.
EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID)
DEPENDENT(EmployeeID, DependentName, Relationship)
a)
Name the attributes of EMPLOYEE, which can be used as candidate keys.
b)
The company wants to retrieve details of dependent of a particular employee. Name the tables and the key which are required to retrieve this detail.
c)
What is the degree of EMPLOYEE and DEPENDENT relation?
Answer 9
(a)
Candidate keys in EMPLOYEE
A candidate key is an attribute that can uniquely identify a row.
In EMPLOYEE, both of these can uniquely identify an employee:
•
AadharNumber
•
EmployeeID
So, the candidate keys are: AadharNumber, EmployeeID.
(b)
Tables and key(s) needed to get dependent details
To get dependents of an employee, we use:
•
EMPLOYEE
•
DEPENDENT
The common linking key is EmployeeID:
EMPLOYEE.EmployeeID matches DEPENDENT.EmployeeID (this is how the relationship is formed).
(c)
Degree of EMPLOYEE and DEPENDENT
Degree = number of attributes (columns).
Degree of EMPLOYEE = 5.
Degree of DEPENDENT = 3.
Question 10
10. School uniform is available at M/s Sheetal Private Limited. They have maintained SCHOOL_UNIFORM Database with two relations viz. UNIFORM and COST. The following figure shows database schema and its state.
School Uniform Database
Attributes and Constraints
Table: UNIFORM
Attribute
UCode
UName
UColor
Constraints
Primary Key
Not Null
–
Table: COST
Attribute
UCode
Size
Price
Constraints
Composite Primary Key
–
> 0
Table: UNIFORM
UCode
UName
UColor
1
Shirt
White
2
Pant
Grey
3
Skirt
Grey
4
Tie
Blue
5
Socks
Blue
6
Belt
Blue
Table: COST
UCode
Size
Price
1
M
500
1
L
580
1
XL
620
2
M
810
2
L
890
2
XL
940
3
M
770
3
L
830
3
XL
910
4
S
150
4
L
170
5
S
180
5
L
210
6
M
110
6
L
140
6
XL
160
a)
Can they insert the following tuples to the UNIFORM relation? Give reasons in support of your answer.
i)
7, Handkerchief, NULL
ii)
4, Ribbon, Red
iii)
8, NULL, White
b)
Can they insert the following tuples to the COST relation? Give reasons in support of your answer.
i)
7, S, 0
ii)
9, XL, 100
Answer 10
(a)
Insert into UNIFORM
i)
(7, Handkerchief, NULL) → YES
•
UCode is new (PK ok), UName is NOT NULL, UColor can be NULL because there is no NOT NULL constraint on UColor.
ii)
(4, Ribbon, Red) → NO
•
UCode = 4 already exists (Tie, Blue). Primary key would be duplicated.
iii)
(8, NULL, White) → NO
•
UName has NOT NULL constraint, so NULL is not allowed.
(b)
Insert into COST
i)
(7, S, 0) → NO
•
Price must be > 0, but here it is 0.
ii)
(9, XL, 100) → YES (based only on given constraints)
•
Price > 0 is satisfied and (UCode, Size) as composite key is new.
Note: If a foreign key constraint UCode → UNIFORM(UCode) exists, then this row
would be rejected.
Question 11
11. In a multiplex, movies are screened in different auditoriums. One movie can be shown in more than one auditorium. In order to maintain the record of movies, the multiplex maintains a relational database consisting of two relations viz. MOVIE and AUDI respectively as shown below:
Movie(Movie_ID, MovieName, ReleaseDate)
Audi(AudiNo, Movie_ID, Seats, ScreenType, TicketPrice)
a)
Is it correct to assign Movie_ID as the primary key in the MOVIE relation? If no, then suggest an appropriate primary key.
b)
Is it correct to assign AudiNo as the primary key in the AUDI relation? If no, then suggest appropriate primary key.
c)
Is there any foreign key in any of these relations?
Answer 11
(a) Yes, it is correct to assign Movie_ID as the primary key in MOVIE, because a primary key should uniquely identify each tuple (record), and Movie_ID is meant to uniquely identify each movie.
(b) No, it is not correct to assign only AudiNo as the primary key in AUDI.
Because in the AUDI relation, the same AudiNo can appear again when the auditorium screens another movie (since Movie_ID is also part of the record). To uniquely identify each tuple in AUDI, we should use a composite primary key:
Primary Key for AUDI = (AudiNo, Movie_ID)
(Using more than one attribute together helps in uniquely identifying tuples when one attribute alone may repeat.)
(c) Yes. In the AUDI relation, Movie_ID is a foreign key that refers to Movie(Movie_ID). This links the auditorium record with the movie record.
Question 12
Table: STUDENT
Roll No
Name
class
Section
Registration_ID
11
Mohan
XI
1
IP-101-15
12
Sohan
XI
2
IP-104-15
21
John
XII
1
CS-103-14
22
Meena
XII
2
CS-101-14
23
Juhi
XII
2
CS-101-10
Table: PROJECT
ProjectNo
ProjectName
SubmissionDate
101
Database Design
12/01/2018
102
Library Database
12/01/2018
103
Employee Database
15/01/2018
104
Student Database
12/01/2018
105
Inventory Database
15/01/2018
106
Railway Database
15/01/2018
Table: PROJECT ASSIGNED
Registration_ID
ProjectNo
IP-101-15
101
IP-104-15
103
CS-103-14
102
CS-101-14
105
CS-101-10
104
12. For the above given database STUDENT-PROJECT, answer the following:
a)
Name primary key of each table.
b)
Find foreign key(s) in table PROJECT-ASSIGNED.
c)
Is there any alternate key in table STUDENT? Give justification for your answer.
d)
Can a user assign duplicate value to the field RollNo of STUDENT table? Justify.
Answer 12
(a) Primary key of each table
•
STUDENT: RollNo (uniquely identifies each student record).
•
PROJECT: ProjectNo.
•
PROJECT_ASSIGNED: (Registration_ID, ProjectNo) (composite primary key).
(b) Foreign key(s) in PROJECT_ASSIGNED
•
Registration_ID → refers to STUDENT(Registration_ID).
•
ProjectNo → refers to PROJECT(ProjectNo).
(These are used to link related tables.)
(c) Yes. Registration_ID can be an alternate key in STUDENT because it can also uniquely identify a student, but it is not chosen as the primary key (RollNo is chosen as primary key).
(d) No. Duplicate RollNo cannot be assigned because RollNo is the primary key, and a primary key must be unique and cannot be NULL.
Question 13
13. For the above given database STUDENT-PROJECT, can we perform the following operations?
a)
Insert a student record with missing roll number value.
b)
Insert a student record with missing registration number value.
c)
Insert a project detail without submission-date.
d)
Insert a record with registration ID IP-101-19 and ProjectNo 206 in table PROJECT-ASSIGNED.
Answer 13
•
(a) No. RollNo is the primary key of STUDENT, so it cannot be NULL.
•
(b) Yes. A missing value can be stored as NULL when the actual value is unknown/not available (provided the attribute is not declared NOT NULL / not part of primary key).
•
(c) Yes. SubmissionDate can be left NULL if it is not specified as NOT NULL (NULL is used for missing/unknown values).
•
(d) No. In PROJECT_ASSIGNED, ProjectNo should refer to an existing project (foreign key concept). Since ProjectNo 206 is not present in the given PROJECT table (projects shown are 101–106), this insert should not be allowed.