Question 1:
Define degree and cardinality. Based upon given table write degree and cardinality.
Patients Degree = 4
Cardinality = 5
Q 2: Find Primary key, Alternate key and candidate key for the table.
(Bank Account Number, Aadhaar Number) is a candidate key for the table
Aadhaar Number ” Primary key
Bank Account Number ” Alternate Key
Question 3:
A table ‘customer’ has 10 columns but no row. Later, 10 new rows are inserted and
3 rows are deleted in the table. What is the degree and cardinality of the table
customer.
Аnswer:
Degree = 10[no.of cols]
Cardinality = 10-3=7[no.of rows]
Q.4 :
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?
Solution:4
a) Yes, because every movie will have it's unique id.
b) Yes, because every auditorium will be assigned a unique id. No two auditoriums
will have same id.
c) Yes, Movie_ID in Audi table is the foreign because it references the Movie_ID in
the Movie table.
Q.5 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.
Sol:5
a) STUDENT - Roll No PROJECT – ProjectNo PROJECT ASSIGNED - Registration_ID
All above stated are primary keys because they are unique and cannot not be NULL.
b) Registration_ID and ProjectNo both are foreign keys because they refer to the
STUDENT and PROJECT table respectively.
c) Yes, Registration_ID is an alternate key because not two students can have the
same registration number.
d) No, because Roll No is a primary key in the student table and duplicate values for
primary keys is not allowed.
Q6:
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.
Q : (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
Sol : (a)
( i) Yes, because 7 value is not present in any row of UCode column which is a
primary key. UName is given as "Handkerchief" which is not a null value. UColor
can be NULL because there is no constraint for that column.
(ii) No, because 4 is already a UCode for Tie, so ribbon can not have the same
UCode.
(iii) No, because UName column can not have NULL values.
(b)
(i) No, because for the cost column value must be more than 0.
(ii) No, because 9 is not present in the UNIFORM table. The UCode in COST table is
foreign key of UNIFORM table, since there is no row in UNIFORM table that has
value 9 so this insertion is not possible.
Q.7 An organization 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, Dependent Name, 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?
Sol:
a) AadharNumber and EmployeeID can be used for candidate keys because they are
unique to every employee.
b) Employee and Dependent tables are required. EmployeeID is the key to retrieve
the required data.
c) Degree of Employee relation = 5 and degree of Dependent relation = 3
