DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING (22CS201) DATABASE MANAGEMENT SYSTEMS

MODULE BANK -2

1a. Briefly answer the following questions:

1) Define the term functional dependency.
2) Why are some functional dependencies called trivial?
3) Give a set of FDs for the relation schema R(A,B,C,D) with primary key AB
under which R is in 1NF but not in 2NF.
4) Give a set of FDs for the relation schema R(A,B,C,D) with primary key AB
under which R is in 2NF but not in 3NF.
5) Consider the relation schema R(A,B,C), which has the FD B → C. If A is a
candidate key for R, is it possible for R to be in BCNF? If so, under what
conditions? If not, explain why not.
6) Suppose we have a relation schema R(A,B,C) representing a relationship
between two entity sets with keys A and B, respectively, and suppose that R has (among
others) the FDs A → B and B → A. Explain what such a pair of dependencies means (i.e.,
what they imply about the relationship that the relation models)

b. Consider the following relations:

Student (snum: integer, sname: string, major: string, level: string, age: integer)
Class (name: string, meets at: string, room: string, fid: integer)
Enrolled (snum: integer, cname: string)
Faculty (fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, enrolled has one
record per student-class pair such that the student is enrolled in the class.
Design the SQL queries for information retrieval of the following statements. No
duplicates should be printed in any of the answers.
1) Find the names of all classes that either meet in room R128 or have five or more
students enrolled. (Order them alphabetically)
2) For each level, print the level and the average age of students for that level.
3) For all levels except JR, print the level and the average age of students for that
level.
4) Find the names of students enrolled in the maximum number of classes.
5) For each age value that appears in Students, find the level value that appears
most often. For example, if there are more FR level students aged 18 than SR,
JR, or SO students aged 18, you should print the pair (18, FR)

2a. . Consider the relation R, which has attributes that hold schedules of courses and sections
at a university; R = {Course_no, Sec_no, Offering_dept, Credit_hours, Course_level,
Instructor_ssn, Semester, Year, Days_hours, Room_no, No_of_students}. Suppose that the
following functional dependencies hold on R:
{Course_no} → {Offering_dept, Credit_hours, Course_level}
{Course_no, Sec_no, Semester, Year} → {Days_hours, Room_no, No_of_students,
Instructor_ssn}
{Room_no, Days_hours, Semester, Year} → {Instructor_ssn, Course_no, Sec_no}
Determine which sets of attributes form keys of R. List the possible number of super keys
too. How would you normalize this relationship?
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING (22CS201) DATABASE MANAGEMENT SYSTEMS
MODULE BANK -2

b. Consider the following relations:

Student (snum: integer, sname: string, major: string, level: string, age: integer)
Class (name: string, meets at: string, room: string, fid: integer)
Enrolled (snum: integer, cname: string)
Faculty (fid: integer, fname: string, deptid: integer)
The meaning of these relations is straightforward; for example, enrolled has one record per
student-class pair such that the student is enrolled in the class.
Design the SQL queries for information retrieval of the following statements. No duplicates
should be printed in any of the answers.

[Link] the names of faculty members who teach in every room in which some class is taught.
[Link] the names of faculty members for whom the combined enrolment of the courses that
they teach is less than five. (Display the results in alphabetical order).
[Link] each level, print the level and the average age of students for that level.
[Link] all levels except JR, print the level and the average age of students for that level.
[Link] a view to display the names and majors of all students, sorted by their last name.

3a. Given a relational Schema R (A, B, C, D) and set of Function Dependency

FD = {B → A, AD → BC, C → ABD}.
Find the canonical cover.
b. The following relations keep track of airline flight information:
Flights (flno: integer, from: string, to: string, distance: integer,
departs: time, arrives: time, price: real)
Aircraft (aid: integer, a name: string, cruising range: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename: string, salary: integer)
Note that the Employees relation describes pilots and other kinds of employees as well;
every pilot is certified for some aircraft, and only pilots are certified to fly.
Design the SQL queries for information retrieval of the following statements.:
1) Compute the difference between the average salary of a pilot and the average salary of
all employees (including pilots).
2) Print the name and salary of every nonpilot whose salary is more than the average salary
for pilots. (Order the names alphabetically descending)
3) Print the names of employees who are certified only on aircrafts with cruising range
longer than 1000 miles. (Sort the result based on the crusiing range)
4) Print the names of employees who are certified only on aircrafts with cruising range
longer than 1000 miles, but on at least two such aircrafts.
5) Create a view to list the flights from New York City to Los Angeles

4a. Consider a relation R with attributes ABCDE. Let the following FDs be given:
A → BC, BC → E, and E → DA. Similarly, let S be a relation with attributes ABCDE
and let the following FDs be given A → BC, B → E, and E → DA. (Only the second
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING (22CS201) DATABASE MANAGEMENT SYSTEMS
MODULE BANK -2
dependency differs from those that hold over R.) You do not know whether or which other
(join) dependencies hold.
1) Is R in BCNF? Justify it.
2) Is R in 4NF? Justify it.
3) Is R in 5NF? Justify it.
4) Is S in BCNF?

b. Consider the following schema:

Suppliers(sid: integer, sname: string, address: string)Parts(pid: integer,
pname: string, color: string) Catalog(sid: integer, pid: integer, cost: real)
The Catalog relation lists the prices charged for parts by Suppliers. Design the SQL
queries for information retrieval of the following statements.
1 ) Find the pnames of parts for which there is some supplier. (Displaythe results in
alphabetical order).
2) Find the sids of suppliers who charge more for some part than the average cost of that
part (averaged over all the suppliers who supply that part).
3) Find the sids of suppliers who supply only red parts.
4) Group the sailors to retrieve the count of suppliers based on the partname.
5) Create a view to list the suppliers who supply parts with a cost greater than $100.

5a. Consider the following relation for published books: BOOK (Book_title, Author_name,
Book_type, List_price, Author_affil, Publisher)
Author_affil refers to the affiliation of the author. Suppose the following dependencies exist:
Book_title → Publisher, Book_type
Book_type → List_price
Author_name → Author_affil
a. What normal form is the relation in? Justify your answer.
b. Apply normalization until you cannot decompose the relations further.
c. State the reasons behind each decomposition.

b. Consider the following precedence graph. Is the corresponding Schedule Conflict

Serializable? State Reasons also. Find the possible serializable orders.
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING (22CS201) DATABASE MANAGEMENT SYSTEMS
MODULE BANK -2
6a. Consider the following relational schema. An employee can work in more than one
department; the pct time field of the Works relation shows the percentage of time that a
given employee works in each department.
Emp(eid: integer, ename: string, age: integer, salary: real)
Works(eid: integer, did: integer, pct time: integer)
Dept(did: integer, dname: string, budget: real, managerid: integer)
Design the SQL queries for information retrieval of the following statements.
1. Define a table constraint on Emp that will ensure that every employee makes at least
$10,000.
2. Define an assertion on Dept that will ensure that all managers are age > 30.
3. Employees must make a minimum salary of $1000.
4. Whenever an employee is given a raise, the manager’s salary must be increased to
be at least as much. Further, whenever an employee is given a raise, the department’s
budget must be increased to be greater than the sum of salaries of all employees in
the department.
b. Consider a relation R with attributes ABCDE. Let the following FDs be given:
A → BC, BC → E, and E → DA.
Similarly, let S be a relation with attributes ABCDE and let the following FDs be given
A → BC, B → E, and E → DA.
a. Is R in BCNF? Justify it.
b. Is R in 4NF? Justify it.
c. Is S in 5NF? Justify it.

7a. Consider the following collection of relations and dependencies. Assume thateach relation
is obtained through decomposition from a relation with attributes ABCDEFGHI and that all
the known dependencies over relation ABCDEFGHI are listed for each question. (The
questions are `independent of each other, obviously, since the given dependencies over
ABCDEFGHI are different.)
For each (sub)relation:
1) State the strongest normal form that the relation is in.
2) If it is not in BCNF, decompose it into a collection of BCNF relations.
i) R1(A,C,B,D,E), A → B, C → D
ii) R2(A,B,F), AC → E, B → F

b. Analyse and decide whether the given schedule S is View serializable or not.
R2(Z),R2(Y),W2(Y),R3(Y),R2(Z),R1(X),W1(X),W3(Y),W2(Z),R2(X), R1(Y),W1(Y),W2(X).

8a. Consider the three transactions T1, T2, and T3, and the schedules S1 and S2 given below.
Draw the serializability (precedence) graphs for S1 and S2, and state whether each schedule is
serializable or not. If a schedule is serializable, write down the equivalent serial schedule(s).
T1: r1 (X); r1 (Z); w1 (X);
T2: r2 (Z); r2 (Y); w2 (Z); w2 (Y);
T3: r3 (X); r3 (Y); w3 (Y);
S1: r1 (X); r2 (Z); r1 (Z); r3 (X); r3 (Y); w1 (X); w3 (Y); r2 (Y); w2 (Z); w2 (Y);
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING (22CS201) DATABASE MANAGEMENT SYSTEMS
MODULE BANK -2
S2: r1 (X); r2 (Z); r3 (X); r1 (Z); r2 (Y); r3 (Y); w1 (X); w2 (Z); w3 (Y); w2 (Y);

b. Consider the following relation:

CAR_SALE(Car#, Date_sold, Salesperson#, Commission%, Discount_amt)

Assume that a car may be sold by multiple salespeople, and hence!

{Car#, Salesperson#} is the primary key.

Additional dependencies are

Date_sold → Discount_amt and
Salesperson# → Commission%

a) Based on the given primary key, is this relation in 1NF, 2NF, or 3NF?
b) Why or why not? How would you successively normalize it completely?
c) Discuss Partial Dependency with an example.

9a. Consider the relation:

DISK_DRIVE (Serial_number, Manufacturer, Model, Batch, Capacity, Retailer).
Each tuple in the relation DISK_DRIVE contains information about a disk drive with a unique
Serial_number, made by a manufacturer, with a particular model number, released in a
certain batch, which has a certain storage capacity and is sold by a certain retailer.
For example, the tuple Disk_drive (‘1978619’, ‘WesternDigital’, ‘A2235X’, ‘765234’, 500,
‘CompUSA’) specifies that WesternDigital made a disk drive with serial number 1978619
and model number A2235X, released in batch 765234; it is 500GB and sold by CompUSA.
Develop each of the following dependencies as an FD:
1) The manufacturer and serial number uniquely identify the drive.
2) A model number is registered by a manufacturer and therefore can’t be used by another
manufacturer.
3) All disk drives in a particular batch are the same model.
4) All disk drives of a certain model of a particular manufacturer have the same capacity.
5) Identify the highest normal form for the above relation.

b. Which of the following schedules is (conflict) serializable? Explain why?

1) r1(X); r3(X); w1(X); r2(X); w3(X);
2) r1(X); r3(X); w3(X); w1(X); r2(X);
3) r3(X); r2(X); w3(X); r1(X); w1(X);
4) r3(X); r2(X); r1(X); w3(X); w1(X)

10a. Consider the three transactions T1, T2, and T3, and the schedules S1 and S2 given below.
Draw the serializability (precedence) graphs for S1 and S2, and state whether each
schedule is serializable or not. If a schedule is serializable, pick the equivalent serial
schedule(s).
T1: r1 (X); r1 (Z); w1 (X);
T2: r2 (Z); r2 (Y); w2 (Z); w2 (Y);
T3: r3 (X); r3 (Y); w3 (Y);
S1: r1 (X); r2 (Z); r1 (Z); r3 (X); r3 (Y); w1 (X); w3 (Y); r2 (Y); w2 (Z); w2 (Y);
DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING (22CS201) DATABASE MANAGEMENT SYSTEMS
MODULE BANK -2
S2: r1 (X); r2 (Z); r3 (X); r1 (Z); r2 (Y); r3 (Y); w1 (X); w2 (Z); w3 (Y);w2 (Y);

b. Consider the following relations for an order-processing application database at ABC, Inc.
ORDER (O#, Odate, Cust#, Total_amount)
ORDER_ITEM(O#, I#, Qty_ordered, Total price, Discount%)
Assume that each item has a different discount.
The Total price refers to one item.
Odate is the date on which the order was placed, and the Total amount is the amount of the order.
a) If we apply a natural join on the relations ORDER_ITEM and ORDER in this database,
what does the resulting relation schema RES look like?
b) What will be its key? Show the FDs in this resulting relation.
c) Is RES in 2NF? Is it in 3NF? Why or why not? (State assumptions if you make any.)