Q1. For the given problem definition:
A company database requires storing information regarding employees (identified through ssn, with salary and phone as attributes); departments (identified through dno, with dname and budget as attributes); and children of employees (by using name and age as attributes). Employees work in departments; each department is managed by an employee; a child should be identified exclusively by name when the parent (who is an employee; suppose that only one parent works for the company) is known. The information regarding a child is not needed once the parent leaves the company.
a) Sketch an E-R diagram.
b) Map the E-R diagram to the relational model.
Q2. Let consider the given relations with key underlined:
Customer (C#, Cname, Address)
Item (I#, Iname, Price, Weight)
Order (O#, C#, I#, Quantity)
Write down the SQL queries for the given:
a) List out all the names of customers who have ordered items weighing more than 1000.
b) List out all the names of customers who have ordered at least one item priced over Rs.500.
c) List out all the customers who have ordered the similar item as the customer named ‘John’.
d) Make a view called “orders” which has the total cost of every order.
Q3. Write down the fundamental operations in the Relational Algebra? Describe different joins in relational algebra with the help of appropriate illustrations.