IT244assignment3.docx - Course Title Introduction to...

Course Title : Introduction to Database Course Code : IT244
Q. 1 Normalize the following schema, with given constraints, to BCNF. (2 Point)
books(accessionn, isbn, title, author, publisher)
users(userid, name, deptid,deptname)
accessionno isbn
isbn title
isbn publisher
isbn author
userid name
userid deptid
deptid deptname
Books( Acession, Isbn)
Isbn (Title, Author)
Author (Publisher)
Users( User ID, Name)
Name( Dept ID, Deptname)
Q. 2 Consider the relational database given below, where the primary keys are underlined.
Give an expression in Tuple Relational Calculus and Domain Relational Calculus
For each of the following queries: (0.5+0.5 = 1 Point)
a. Find all employees who work directly for “STC”
t | works (t) ^ t. Company name = ‘STC’
b. Find all cities of residence of all employees who work directly for “STC”
e.name | employee (e) ^ e.street ^ e.city ∃ w | works (w) ^
w.name = e.name ^ w.company name = ‘STC’
Q. 3 Consider the following database: (0.5+0.5+1= 2 Points)
person ( driver_id , name , address
car ( license , model
)