This page contains the NCERT Informatics Practices class 11 chapter 7 Database Concepts. You can find the solutions for the chapter 7 of NCERT class 11 Informatics Practices Exercise. So is the case if you are looking for NCERT class 11 Informatics Practices related topic Database Concepts questions and answers for the Exercise.
Exercise
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.
The following are the terms for each of the given descriptions.
S.No.
Description
Term
a)
Collection of logically related records.
Database
b)
DBMS creates a file that contains description about the data stored in the database.
Meta-data or Data Dictionary
c)
Attribute that can uniquely identify the tuples in a relation.
Primary Key
d)
Special value that is stored when actual data value is unknown for an attribute.
NULL
e)
An attribute which can uniquely identify tuples of the table but is not defined as primary key of the table.
Unique key
f)
Software that is used to create, manipulate and maintain a relational database.
RDBMS
2. Why foreign keys are allowed to have NULL values? Explain with an example.
Reason: A foreign key is an attribute whose value is derived from the primary key of another relation. This means that any attribute of a (referencing) relation, which is used to refer contents from another (referenced) relation, becomes foreign key if it refers to the primary key of referenced relation. In some cases there might not be any associated data available in the referenced table for a particular record in the referring table. Due to this reason, foreign keys are allowed to have NULL values.
As an example, consider the database for a hospital where in there are two tables one for the paients and one for Doctors. The patient table has a foreign key reference to the doctor table to indicate which doctor is responsible for a particular patient’s treatment.
PATIENT
PatientId
PhoneNo
DoctorId
↓
DOCTOR
DoctorId
Department
PhoneNo
Now, when a new patient is admitted into the hospital, he may not be immediately assigned with a doctor. However, that shouldn’t prevent the entering of the details of the patient into the hospital database just because the patient is not yet assigned to a doctor (In other words, the database should allow the patient record to be created with NULL value for the column DoctorId). So, it makes sense to have the foreign key that allows NULL so that the patient details are entered into the database and subsquent hospital formalities can be proceeded with.
3. Differentiate between:
a)
Database state and database schema
b)
Primary key and foreign key
c)
Degree and cardinality of a relation
a) The following is the differentiation between database state and database schema.
Basis
State
Schema
Definition
Database state is the snapshot of the database at any given time.
Database Schema is the design of the database.
Content
It contains the actual data that has been inserted/updated/deleted from the database.
It contains the structure of the database (table names and their fields/columns), the type of data each column can hold, constraints on the data to be stored (if any), and the relationships among the tables.
Persistence
Database state is dynamic and changes frequently as the data is modified.
The schema is persistent and stored in the system catalog/data dictionary of the database management system.
Usage
Database state is used to query, update, modify or delete the data from the database.
Database schema is used to create, modify and manage the database structure and metadata.
b) The following is the differentiation between primary key and foreign key.
Basis
Primary Key
Foreign Key
Definition
Primary key is the attribute chosen from one or more candidate keys to uniquely identify the tuples in a relation.
A foreign key is an attribute whose value is derived from the primary key of another relation.
Purpose
Primary key is used to ensure that each tuple in a relation can be uniquely identified.
It is used to establish a relationship between two relations by linking the primary key of one relation as a foreign key of another relation.
Uniqueness
A primary key must be unique. No two tuples in a relation can have the same primary key value.
A foreign key value can be duplicated in a relation. However, it must correspond to an existing primary key value in another relation.
Modification
A primary key value can not be modified once it has been assigned to a tuple in a relation.
A foreign key value can be modified as long as it still refers to a valid primary key value in another relation.
Assigning NULL Value
A primary key can never be NULL.
A foreign key can have a NULL value.
c) The following is the differentiation between degree and carinality of a relation
Basis
Degree
Cardinality
Definition
The degree of a relation refers to the number of attributes or columns that are present in a relation.
Cardinality is the number of tuples or rows in a relation or table.
Representation
It represents the number of columns in a table or the number of fields in a record.
It defines the number of tuples in a relation or the number of rows in a table.
Belonging
It is property of the relation schema
It is property of the relation instance.
Stands For
Structure of the relation.
Data contained in the relation.
Change of Value
Mostly it remains fixed or changes very less frequently.
Mostly it changes over time as new tuples are added or existing tuples are deleted.
4. Compared to a file system, how does a database management system avoid redundancy in data through a database?
In a file system, the same data are duplicated in different places (files) and is difficult to avoid. This creates redundancy.
A database management system elminates the redundancy by organizing data into relations and ensuring that each relation stores information about a specific relationship. This will ensure that the data is not duplicatied across multiple relations.
Another way in which the redundancy is avoided is by maintaining the relationship between two relations using foreign key. When the foreign key is used, it ensures that the foreign key values in one tables match the primary key values in another table. This ensures that the related data is stored in separate tables, reducing the amount of redundant data in the database.
Thus, as compared to a file system, a database management system avoid redundancy in data through a database.
5. What are the limitations of file system that can be overcome by a relational DBMS?
The following are the limiations of a file system that can be overcome by the relational DBMS.
(A)
Difficulty in Access: Files do not provide a mechanism to retrieve data, and application programs may not anticipate all possible ways of accessing data, making it difficult to access data in the required format.
(B)
Data Redundancy: Redundancy occurs when the same data is duplicated in different files, which can lead to excess storage use and data inconsistency.
(C)
Data Inconsistency: Data inconsistency occurs when the same data maintained in different places does not match, leading to errors and inaccuracies in the data.
(D)
Data Isolation: Data files in a file system are often created in isolation with no link or mapping between them, making it difficult to write new application programs to retrieve data from different files maintained at multiple places.
(E)
Data Dependence: Data is stored in a specific format or structure in a file, and updating the structure of a data file requires modification in all the application programs accessing that file.
(F)
Controlled Data Sharing: It can be challenging to enforce access control in a file system while accessing files through application programs, making it difficult to restrict user access based on their roles or permissions.
Overall, a database management system provides a more organized and efficient way of managing data compared to a file system, reducing data redundancy, inconsistency, and access difficulties while allowing controlled data sharing and eliminating data dependence.
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 NULLvalue be assigned to that student’s preference field?
b)
Roll no 17 has given two preferences sports. Which property of relational DBMC 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?
a)
No. A NULL value can not be assigned to student’s preference field as the school has a rule that each student must participate in a sports activity. The only option for the student with Roll_no 24 is to choose a preference that is least boring to him/her.
b)
Roll_no 17 has violated the property of “UNIQUE constraint” as each student is allowed to give only one preference for sports activity. This violation can be checked using a “Primary Key constraint” or “Primary key” in the relational DBMS. A primary key constraint ensures that the values in a column or set of columns are unique and cannot be duplicated.
c)
No. The field “Roll_no” is primary key. Also, the primary purpose of preparing this relation is to identify the student preference. As NULL value for Roll_no doesn’t represent any student, it doesn’t make sense to have NULL as a value for the “Roll_no”.
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.
Yes. As both the relations are representing the same data, the state of both the relations is same as the state doesn’t consider the order of rows and columns.
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?
a) Relation to store the Item Details:
The following relation can be used to store the Item details. It contains the attribute name, data type and what constraints/restrictions are used to store the item details.
Relation Name: ItemDetails
Atribute
Data Type
Constraint
ItemId
INT
Primary Key
Name
VARCHAR
NOT NULL
Price
DECIMAL
NOT NULL
b) Relation to store the Order Details:
In order to store the quantity information, we should use a new relation (other than the ItemDetails relation). The following are the details of the new relation:
Relation Name: BillingInfo
Attribute
Data Type
Constraint
BillId
Int
Primary Key
ItemId
Int
Foreign Key (references Item Details Relation)
QuantitySold
Decimal
NOT NULL
Total
Decimal
Not NULL
The following are the restrictions that are applied to satisfy the given restrictions.
S.No
Restriction
Implementation
i)
The same bill cannot be generated for different orders
The BillId is made primary key. So, the same bill can not be generated for two different orders.
ii)
Bill can be generated only for available items in the canteen
The ItemId field in the BillingInfo relation is a foreign key. So, it’ll ensure that the bill is generated only for those items that are available in the itemDetails table. In otherwords, this means that the bill is generated only for those items which are available in the school canteen.
c) Relation where the ‘calories’ attribute should be stored:
As the calories associated with an item will be different for each item, the ‘calories’ attribute should be maintained in the ‘ItemDetails’ relation. The calories of each item multiplied with the total quantity of item consumed will give the school an idea about how many calories students intake when they order an item.
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?
a) Candidate Keys:
As both the attributes AadharNumber and EmployeeID are can be used to uniquely identity the tuples in the relation EMPLOYEE, they can be used as candidate keys of the employee relation.
b) Tables and keys that are required to retrieve the details of a dependent of a particular employee:
To retrieve the details of dependent of a particular employee, we would use both the EMPLOYEE and DEPENDENT relation with the EmployeeID attribute serving as a foreign key in the DPENDENT relation. We can perform a join (you’ll learn about the joins later) between the two relations on the EmployeeID attribute to retrieve the required details.
c) Degree of EMPLOYEE and DEPENDENT relation:
Relation
Degree
EMPLOYEE
5
DEPENDENT
3
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
COST 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
a) Validation of insertion of Tuples into the UNIFORM Relation:
The following are the contraints for the UNIFORM Relation.
Table: UNIFORM
Attribute
UCode
UName
UColor
Constraints
Primary Key
Not Null
–
S.No.
Tuple
Valid/Not
Reason
i)
7, Handkerchief, NULL
Yes
7 – is a new unique primary key. Hence valid.
Handkerchief – is UName and meets the Not NULL constraint. Hence Valid.
UColor can accept NULL as there’re no constraints.
Handkerchief – is UName and meets the Not NULL constraint. Hence Valid.
UColor can accept NULL as there’re no constraints.
ii)
4, Ribbo, Red
No
The primary key 4 is already existing in the UNIFORM table. So, trying to insert a tuple that has 4 as the primary key violates the unique constraint. Hence not valid.
iii)
8, NULL, White
No
This tuple has the UName as NULL which violates the ‘Not NULL’ constraint for UName. Hence invalid.
a) Validation of insertion of Tuples into the COST Relation:
The following are the contraints for the COST Relation.
Table: COST
Attribute
UCode
Size
Price
Constraints
Composite
Primary Key
> 0
S.No.
Tuple
Valid/Not
Reason
i)
7, S, 0
No
This tuple has the size as ‘0’ which violates the constraint that the size should be greater than zero. Hence invalid.
ii)
9, XL, 100
No
The table UNIFORM does not have a tuple with UCode ‘9’. So, the value ‘9’ for the UCode in this tuple is invalid as it violates the foreign key constraint. Hence invalid.
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?
The following are the conclusions regarding the given statements.
a)
Yes. As every movie is different and unique, it makes sense to assign the Movie_ID as the primary key in the MOVIE relation.
b)
Yes. As the auditorium numbers will be unique and can not be NULL, we can assign the AudiNo as the primary key in the AUDI relation.
c)
Yes. The Movie_ID attribute in the AUDI relation is the foreign key referring to the Movie_ID attribute in the Movie relation.
Note: The following tables will be the reference for the next two questions.
Student Project Database
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
PName
SubmissionDate
101
Airline Database
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? Jusify.
a) The following are primary keys of each table:
S.No.
Table
Primary Key
i
STUDENT
Roll No
ii
PROJECT
ProjectNo
iii
PROJECT ASSIGNED
Registration_ID
They qualify as the primary keys as they are unique for each tuple in the relation and can not be NULL.
b) Foreign key(s) in the PROJECT-ASSIGNED table:
The following are the freign key(s) in the PROJECT ASSIGNED table
S.No.
Foreign Key Attribute
Reference Table
i.
Registration_ID
STUDENT
ii
ProjectNo
PROJECT
c) Alternate key in table STUDENT:
Yes. The key Registration_ID will be UNIQUE for each student registered. Hence it can be considered as an alternate key.
d) Assigning duplicate value to the field RollNo of STUDENT table:
No. The RollNo of STUDENT table is primary key for the table STUDENT. So, it should be unique and can not have duplicate values.
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.
a) Inserting a student record with missing roll number value: No. The RollNo attribute is primary key of the STUDENT table and hence can not be NULL. So, we can not insert a student record with missing roll number value.
b) Inserting a student record with missing registration number value: No. the Registration_ID attribute is an alternate key and hence can not be NULL. So, we can not insert a student record with missing registration number value.
c) Inserting a project detail without submission-date: Yes. The project submission date details may not be readily available at the time the project is created. So, we can insert a project detail with SubmissionDate value as NULL
d) Inserting a record with registration ID IP-101-19 and Project No 206 in table PROJECT-ASSIGNED: No. Both the Registration_ID and ProjectNo attributes are foreign keys referring to the tables STUDENT and PROJECT respectively. As the Registration_ID IP-101-19 is not available in the STUDENT table and ProjectNo 206 is not available in the Project table, we can not insert a record with registration ID IP-101-19 and ProjectNo 206 in table PROJECT-ASSIGNED.