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