Database Management Systems University Question 3

Database Management Systems University Question 3

Database Management Systems

Time : Three Hours                                                  Max.Marks:100

PART – A (8 X 5 = 40 Marks)

Answer ALL Questions

1. Consider the following tables:

Employee

Empid	Name	Dept_id
123	Sam	5
124	Ram	4
125	Tom	5
126	Jeff	4

Department

D_id	Dept_name
4	CSE
5	IT

Construct queries in relational algebra for the following scenarios:

a) Display employee id and employee name from Employee table.

b) Display all employee names from the employee table who work for department 5.

c) Display Employee name and Department name for all employees.

2. The following table gives the details of products sold in a shop for two days.

Date	ItemName	Unit_Sold
9/3/13	Pencils	100
9/3/13	Pen	500
10/3/13	Pencils	50
10/3/13	Pen	900
10/3/13	Eraser	75

Construct SQL queries for the following scenarios:

a) Display the total number of items sold for each item name.

b) Display the item names and total number of items sold for only those items where total of sold items is greater than 1000.

c) Display the table sorted in the descending order on the number of quantities sold.

3. For a given relation R(ABCDE) , the following functional dependencies have been identified

F = {A →D, D →B, B →C, E →B}

Determine the key for this relation using closure of attributes.

4. Explain the following concepts in EER diagram with examples:

a) Disjoint Total

b) Disjoint Partial

c) Overlap Total

d) Overlap Partial

5. How hash functions are used in static hashing? Explain how you can handle bucket overflows in hashing.

6. Consider the following schema:

i. Employee (Eno, Ename, Experience)

ii. Work_Assignment(Eno, Pno, Role, Duration)

iii. Project(Pno,Pname, Location)

a) Write a SQL Query to Find the names of Employees who have been working in Automation project for more than 2 years.

b) Draw an equivalent query evaluation plan for the SQL query.

7. How does Timestamp Ordering protocol work? Explain with an example.

8. Elaborate on Vertical and horizontal fragmentation of a database in a distributed environment.

PART – B (6 X 10 = 60 Marks)

Answer any SIX Questions

9. Considering the following tables given below: Product Table

Prod_ID	Prod_Name
1	PRO11
2	PRO22
3	PRO34
4	PRO51
5	PRO64
6	PRO74

Products_Detail Table

Prod_ID	Price	Qty	Defect_Rate
1	4000	25	12
2	3000	40	2
3	4000	35	8
4	3000	45	14
5	4000	30	0

Quality Table

Quality_Index	Min_Defect_Rate	Max_Defect_Rate
1	0	4
2	5	10
3	11	100

Sold Table

Prod_ID	Date	Qty_Sold
1	12-Aug	45
2	12-Aug	5
3	12-Aug	300
1	13-Aug	25
2	13-Aug	4
3	13-Aug	75

Construct SQL queries for the following statements below:

a) Display the Product ID, Product Name and Price and Quantity from the product and product details tables.

b) Display the Product ID, Product Name and Quantity of only those products whose price is 4000 rupees.

c) Display the Product ID, Price and Quality Index of all the products based on the Defect rate.

d) Display the ID, Name and Price of products whose defect rate is greater than 8.

e) Display the ID, name, price of products and also products that are not having the price details.

10. Construct an ER diagram for the given scenario:

• A university has several departments.

• Each department is managed by a Faculty.

• Each faculty is assigned to only one department.

• Faculty are of different designations as given

➢ Assistant Professor

➢ Associate Professor

➢ Professor

• Only Assistant Professors are assigned courses. Every Assistant professor can teach maximum of five courses.

• A course is taught by a maximum of one assistant Professor.

• Only Associate Professors can conduct seminars.

• Professors can only work on funded projects.

• Only faculty who is a professor can manage a department.

11. Identify the current Normal forms of the given tables and decompose the tables to attain 3rd Normal Form.

Employee Table

Emp_ID	Emp_Name	Salary	Dept_ID	Dept_Name
123	Steve	5000	4	CSE
124	Kumar	2000	5	IT
125	Tom	6000	4	CSE
126	Mani	7000	5	IT

Department Table

Dept_ID	Dept_Name	Location
4	CSE	ST
4	CSE	IT
4	CSE	KT
5	IT	MT

Project Table

Emp_ID	Emp_Name	Proj_ID	Hours
123	Steve	1	40
123	Steve	2	20
124	Kumar	1	50
124	Kumar	2	10

12. What are the advantages of RAID? Discuss in detail the following RAID Levels:

• RAID 0

• RAID 1

• RAID 2

• RAID 5

• RAID 10

13. What is the advantage of using a B+ tree index structure? Discuss the B+ tree index structure and write a procedure for querying a B+ tree.

14. What is deadlock detection and Recovery? Explain with an Example.

15. Generate precedence graphs for the following schedules and state if they are serializable or not.

 16. Apply Apriori algorithm and determine the frequent item sets from the given table. Consider Minimum support to be 0.3.

************