NIELIT A Level Introduction to Database Management System Assignment

NIELIT A Level Introduction to Database Management System Assignment 11

Manufacturing
Consider the following relation that keeps track of the orders placed by a manufacturing
company:
Orders(orderDate, deliveryDate, supplier, partID, material, price).
Suppose the following functional dependencies hold on the relation:
orderDate, supplier -> deliveryDate
partID, supplier, orderDate -> price
partID -> material
material -> supplier.

a) Decompose the relation in smaller relations such that
– each of the smaller relations is in BNCF with respect to the projection
of the original dependencies;
– the decomposition is a lossless join decomposition.
b) Is your decomposition dependency preserving? If your answer is “yes”,
argue why. If your answer is “no”, show which dependencies have been
lost.
Note Solve the assignments from 12 to 20 through Select as well as through relational
algebra.
Database Schema for the exercise:
Professor ( ssn , profname, status, salary)
Course( crscode ,crsname,credits)
Taught(crscode,semester,ssn)

Assumptions:
a) Each course has only one instructor in each semester.
b) All professors have different salaries.
c) All professors have different names
d) All courses have different names
e) Status can take value from “full”,”associate” , and “assistant”.

Questions

1. Return those professors who have taught “csc6710” but never “csc7710”
2. Return those professors who have taught “csc6710” and “csc7710” in same semester.
3. Return those professors who have taught “csc6710 “ or “csc7710” but not both.
4. Return that course which have never been taught.
5. Return that courses that have been taught atleast in two semester.
6. Return the names of all professors who have ever taught “csc7710”.
7. Change all credits to 4 for those courses that are taught in semester “f2006:.
8. Return the professor who earns second highest salary.
9. Delete those professors who have never taught.

Next Assignment