SOMAIYA
VIDYAVIHAR
K J Somaiya Institute of Engineering and Information Technology
An Autonomous Institute Permanently Affiliated to the University of Mumbai.
Department of Information Technology
SYIT –SEM-III
Assignment – II
Batch-I
Course code: ITC303 Course Name: Database Management System
QN Question Marks CO BTL
Qu-1 Explain the following:
a) Why the Relation R1 is in 1NF but not in 2NF, where
R1 = ({A, B, C, D }, {B D, AB C}) 5 CO5 2
b) Why the Relation R2 is in 3NF but not in BCNF, where
R2 = ({A, B, C, D }, {A C, D B})
Qu-2 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 a given department.
Write the following queries in SQL:
1. Print the names and ages of each employee who works in both the Hardware
department and the Software department.
10 CO4 3
2. For each department with more than 20 full-time-equivalent employees (i.e.,
where the part-time and full-time employees add up to at least that many
full-time employees), print the did together with the number of employees
that work in that department.
3. Print the name of each employee whose salary exceeds the budget of all of
the departments that he or she works in.
4. Find the managerids of managers who manage only departments with
budgets greater than Rs. 10,00,000.
5. Find the enames of managers who manage the departments with the largest
budget.
a) Illustrate how the two-phase locking protocol works with a suitable example. 5
Qu-3 CO6 2
Last date of Submission: 31st October 2022.
SOMAIYA
VIDYAVIHAR
K J Somaiya Institute of Engineering and Information Technology
An Autonomous Institute Permanently Affiliated to the University of Mumbai.
Department of Information Technology
SYIT –SEM-III
Assignment – II
Batch-II
Course code: ITC303 Course Name: Database Management System
QN Question Marks CO BTL
Qu-1 Explain the following:
a) What is the highest form for the R3 relation? 2
R3 = ({A, B, C, D }, {A C, CD B})
b) Consider a relation R4 is in 3NF and not in BCNF. Demonstrate the 5 CO5
redundancy with the suitable instance of R4, and normalize the R4 to BCNF. 3
Qu-2 Write the following simple SQL Queries on the University Schema
1. Each offering of a course (i.e. a section) can have many Teaching assistants;
each teaching assistant is a student. Extend the existing schema by adding
or altering tables to accommodate this requirement.
2. According to the existing schema, one student can have only one advisor.
a) Alter the schema to allow a student to have multiple advisors and make
sure that you are able to insert multiple advisors for a student. 10 CO4 3
b) Write SQL queries on the modified schema. You will need to insert data
to ensure the query results are not empty.
1. Find all students who have more than 3 advisors
2. Find all students who are co-advised by Prof. Srinivas and Prof.
Ashok.
3. Delete the course CS 101. All courses which have this as a prereq should
remove this from its prereq set. Create a cascade constraint and verify.
Qu-3 Consider the following two transactions:
T1: READ(A) T2: READ(B)
READ(B) READ(A)
IF A = 0 THEN B = B + 1 IF B = 0 THEN A = A + 1
WRITE(B) WRITE(A) 5 CO6 2
a) Add lock and unlock instructions to transactions T1 and T2 so that they
observe the two-phase locking protocol.
b) Can the execution of these transactions result in a deadlock? Explain.
Last date of Submission: 31st October 2022.
SOMAIYA
VIDYAVIHAR
K J Somaiya Institute of Engineering and Information Technology
An Autonomous Institute Permanently Affiliated to the University of Mumbai.
Department of Information Technology
SYIT –SEM-III
Assignment – II
Batch-III
Course code: ITC303 Course Name: Database Management System
QN Question Marks CO BTL
Qu-1 Compute the closure of the following set F of functional dependencies for
relation schema R (A, B, C, D, E).
A→BC
CD→E 5 CO5 2
B→D
E→A
List the candidate keys for R.
Qu-2 Create a relational schema for A railway system, which needs to model the
following:
1. Stations
2. Tracks, connecting stations. You can assume for simplicity that only one
track exists between any two stations. All the tracks put together form a
graph.
3. Trains, with an ID and a Name
4. Train schedules recording what time a train passes through each station on
its route. You can assume for simplicity that each train reaches its
destination on thesame day, and that every train runs every day. Also for
simplicity, assume that for each train, for each station on its route, you
store (a) time in, (b) time out (same as time in if it does not stop), and (c)
a sequence number so the stations in the route of a train can be ordered by
sequence number. 10 CO4 3
5. Passenger booking consists of train, date, from-station, to-station, coach,
seat and passenger name; for simplicity, don't bother to model passengers
as entities.
Write the following Queries for Railway Schema
1. Find the pairs of stations(station codes) which have a track with
distance less than 20Kms between them.
2. Find the IDs of all the trains which have a stop at THANE
3. Find the names of all trains that start at MUMBAI.
4. List all the stations in order of visit by the train 'CST-AMR_LOCAL'.
5. Find the name of the trains which stop at Thane, before the 6th stop in
the route of the train.
Qu-3 Draw a state diagram of the transaction and discuss the typical states that a 5 CO6 2
transaction goes through during execution.
Last date of Submission: 31st October 2022.
