Wednesday, March 18, 2015

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.


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






No comments:

Post a Comment