Website: [Link].

in
YouTube: [Link]/c/techtipnow

Chapter-7 Solution
Database Concepts

1. Give the terms for each of the following:

a. Collection of logically related records.
Ans: Database
b. DBMS creates a file that contains description about the data stored in the
database.
Ans: Meta-data or Data Dictionary
c. Attribute that can uniquely identify tuples in a relation.
Ans: Primary Key
d. Special value that is stored when actual data value is unknown for an attribute.
Ans: NULL
e. An attribute which can uniquely identify tuples of the table but is not defined as
primary key of the table.
Ans: UNIQUE Key
f. Software that is used to create, manipulate and maintain a relational database.
Ans: RDBMS
2. Why foreign keys are allowed to have NULL values? Explain with an example.
Ans: Foreign Key is used to represent the relation between two tables. Because in the
field with Foreign key attribute data comes from Primary key field in another related
table and so Foreign key are allowed to have Null Values if it not the part of Primary
key field of the related table. For example-

Ccode(PK) Cname Caddress Ono Ccode(FK) Amt

1 Sanjay Delhi 1 1 250
2 Raman Mumbai 2 3 400
3 Farooq Kolkata 3 NULL 300

Here both the table „Customer‟ and „Order‟ are related with each other with a common
field „CCode‟ which is Primary key in Customer table and Foreign key in Order table.
So we cannot insert a new CCode in Order table if it does not exist in Customer table
but we can have null values instead.

3. Differentiate between:
a. Database state and database schema
Ans: Difference between Database state and database schema is given below:
Database Schema Database State
Database Schema represent overall Database state represent current state
design of the database. of data in database.
Database schema is changed Database state is changed frequently
ocassionally

1
 Website: [Link]
YouTube: [Link]/c/techtipnow

Initially when defining a database, only Initially when defining a database,

Database schema is specified database state is empty state.

b. Primary key and foreign key

Ans: Difference between Primary key and Foreign key is given below:
Primary Key Foreign Key
It identifies the each record in a table It maintains the relation between two
uniquely. tables. A foreign key is primary key in
another related table.
A table can have only one Primary key. A table can have more than one
Foreign key.
Primary key cannot have NULL Foreign key can have NULL values.
values.

c. Degree and cardinality of a relation

Ans: Difference between Degree and cardinality is given below:
Degree Cardinality
Total no of columns(attribute) in table Total no of rows (tuple) in a table
Degree of multiple tables are always Cardinality of multiple tables are
added always multiplied.

4. Compared a file system, how does a database management system avoid redundancy in
data through a database?
Ans: By storing data in different tables which are logically related, a database
management system avoids redundancy in data through a database.
5. What are the limitations of file system that can be overcome by a relational DBMS?
Ans: The limitation if file systems that can be overcome by a relational DBMS are:
Data Redundancy: redundancy means repetition of same data in different tables.
Redundancy leads to excess data storage and may cause data inconsistency.

Data Inconsistency: When same data is managed differently in different places leads to
data inconsistency. Data inconsistency creates unreliable information, because it will
be difficult to determine which version of information is correct.

Data Isolation: In file system because data mapping (link) is not supported, so all the
files (table with records) are created and kept in isolation. This is called data isolation
and it becomes very difficult to keep database in same state after the execution of a
transaction.
Data Dependence: data are stored in a specific format or structure in a file. If the
structure or format is changed, all the existing application programs accessing that file
also need to change. Otherwise, the programs may not work correctly.
Controlled Data Sharing: it is very difficult to provide access control of data at different
level for different user in a file system.

2
 Website: [Link]
YouTube: [Link]/c/techtipnow

6. A school has a rule that each student must participate in a sports activity. So each one
should give only one performance for sport 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 Preferences
9 Cricket
13 Football
17 Badminton
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 preferences field?
Ans: Yes, a NULL value can be assigned.
b. Roll no 17 has given two preferences 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?
Ans: “Each tuple in a relation is distinct” is the property of RDBMS is violated
here. We can use Primary Key constraint in the RDBMS to check against such
violation.
c. Kabaddi was not chosen by any student. Is it possible to have this tuple in the
Sports Preferences relation?
Ans: No we cannot have this tuple in the relation because Roll No is a primary
key field and it cannot have null values.
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: Sport 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

3
 Website: [Link]
YouTube: [Link]/c/techtipnow

Are the states of both the relations equivalent?

Ans: Yes, the states of both the relations are equivalent because current state of both
the relations is 5 and structure of both the relations is also same.
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?
Ans: We will create ITEM relation to manage item details which scheme is given
below-
Attribute Data type Constaint
ICode Int Primary Key
IName Varchar Not Null
QTY Int Not Null
IRate Int Not Null

We will apply Primary key constraint on ICode attribute to store each item and
its price only once.
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.

Ans: We will create new relation BILL to keep bill details as per the scheme
given below-
Attribute Data type Constraint
BillNo Int Primary Key
BillDate Date Not Null
ICode Int Foreign Key (Refrences ITEM)
Rate Int Not Null
QTY_Sold Int Not Null
Total Int Not Null

i. We will apply Primary key to BillNo attribute to avoid same bill for
different order.
ii. We will make ICode to Foreign key (References ITEM relation) to generate
bill for available items.

4
 Website: [Link]
YouTube: [Link]/c/techtipnow

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?
Ans: We have use attribute „calories‟ in both the relations.
9. An organization wants to create a database EMPDEPENDENT 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.

Ans: AadharNumber, EmployeeID
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.
Ans: to retrieve the details of dependent of a particular employee we need both
the tables EMPLOYEE and DEPENDENT and EmployeeID as key.
c. What is the degree of EMPLOYEE and DEPENDENT relation?
Ans: The degree of EMPLOYEE and DEPENDENT relation is 8.
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.
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 680
1 XL 620
2 M 810

5
 Website: [Link]
YouTube: [Link]/c/techtipnow

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
Ans: Yes, it can be inserted as UColor does not have Not Null constraint.
ii. 4, Ribbon, Red
Ans: No, it cannot be inserted as UCode is Primary key and 4 is already
existing and so we can insert duplicate record in it.
iii. 8, NULL, White
Ans: No, it cannot be inserted as UName has Not Null constraint.
b. Can they insert the following tuples to the COST Relation? Give reasons in
support of your answer.
i. 7, S, 0
Ans: No, it cannot be inserted as Price should always be greater than 0.
ii. 9, XL, 100
Ans: No, it cannot be inserted as UCode is Foreign key references from
Uniform relation and UNIFORM relation 9 UCode does not exist and so
we cannot insert it in COST relation.
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.
Ans: Yes
b. Is it correct to assign AudiNo as the primary key in the AUDI relation? If no,
then suggest appropriate primary key.
Ans: Yes
c. Is there any foreign key in any of these relations?
Ans: Yes Movie_ID in Audi relation is Foreign key.
6
 Website: [Link]
YouTube: [Link]/c/techtipnow

12. For the given database STUDENT-PROJECT, answer the following:

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 Table : PROJECT

ProjectNo PName SubmissionDate ASSIGNED
101 Airline Database 12/01/2018 Registration_ID ProjectNo
102 Library 12/01/2018 IP-101-15 101
Database IP-104-15 103
103 Employee 15/01/2018 CS-103-14 102
Database CS-101-14 105
104 Student 12/01/2018 CS-101-10 104
Database
105 Inventory 15/01/2018
Database
106 Railway 15/01/2018
Database
a. Name primary key of each table.
Ans-
Table Primary key
Student Registration_ID
Project ProjectNo
Project_assigned -
b. Find foreign key(s) in table PROJECT-ASSIGNED.
Ans: Registration_ID and ProjectNo
c. Is there any alternate key in table STUDENT? Give justification for your answer.
Ans: Yes, RollNo can be alternate key in table STUDENT because RollNo can not
have Null value and always be Unique.
d. Can a user assign duplicate value to the field RollNo of STUDENT table? Justify.
Ans: Yes, RollNo can be assigned duplicate values as it is not a primary key..
13. For the above given database STUDENT-PROJECT, can we perform the following
operations?
a. Insert a student record with missing roll number value.
Ans: Yes
b. Insert a student record with missing registration number value.
Ans: No
c. Insert a project detail without submission-date.
Ans: Yes
Insert a record with registration ID IP-101-19 and ProjectNo 206 in table PROJECT-
ASSIGNED. Ans: No