Computer Science and Engineering - Tutorials, Notes, MCQs, Questions and Answers

Wednesday, March 18, 2015

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.

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

Tuesday, March 17, 2015

Database Management Systems University Question 2

Database Management Systems

Time : Three Hours Max.Marks:100

PART – A (8 X 5 = 40 Marks)

Answer ALL Questions

1. Discuss the main categories of data models.

2. What is meant by a recursive relationship type? Give some examples of recursive relationship type.

3. What is meant by super key? Differentiate between super key and primary key.

4. What is the use of GRANT and REVOKE privileges? Give one example for each.

5. Draw the query tree for the following query.

SELECT a.x, b.y FROM a, b WHERE a.m = b.n AND a.g >300;

6. List out the various types of data storage media.

7. Draw the state transition diagram and explain each state.

8. Describe the three phases of validation based protocols.

PART – B (6 X 10 = 60 Marks)

Answer any SIX Questions

9. Discuss the main characteristics of the database approach and how it differs from traditional file systems.

10. Draw an ER diagram for the following Bioinformatics application.

• Patient: has a unique MSP number, a Patient name, a Date of Birth, a Tissue Type and an indicator denoting whether the tissue is cancerous or normal.

• A patient library associates a patient with multiple tags

• Each tag has a unique tag number and a unique nucleotide sequence.

• For each tag in the patient library, a count is given to record the number of times the tag occurs in the library. In general, the same tag can be associated with any number of patients.

• A tag may be mapped to a gene. Each gene has a unique gene name and a type.

• In general, multiple tags may be mapped to the same gene. However, two different Genes cannot be mapped to the same tag.

• Finally, an article is identified by a unique article number and a journal name. An article may analyze multiple genes and a gene may be analyzed by multiple articles.

11. Describe the various Join operations with suitable symbols. Give one example for each.

12. Consider the universal relation R= {A,B,C,D,E,F,G,H,I,J} and a set of functional dependencies F= { {AB→ C}, { A → DE}, { B→ F}, {F→ GH}, {D→ IJ}}. What is the key for R? Decompose R into 2NF, then 3NF relations.

13. Write short notes on primary index and secondary index with suitable examples.

14. Explain how hashing technique and B tree are used in index structure.

15. Describe how concurrency control achieved with two phase lock.

16. Explain the various types of log based recovery techniques.

*********

DBMS Question Bank

Database Management Systems - Question Bank

Database Management Systems (DBMS) Question 1

Database Management Systems (DBMS) Question 2

Database Management Systems (DBMS) Question 3

Database Management Systems (DBMS) Question 4

Database Management Systems (DBMS) Question 5

Database Management Systems (DBMS) Question 6

Database Management Systems (DBMS) Question 7

Database Management Systems (DBMS) Question 8

Database Management Systems (DBMS) Question 9

Database Management Systems (DBMS) Question 10

Database Management Systems University Question 1

[Click on the links to visit the answer page]

Database Management Systems

Time : Three Hours Max.Marks:100

PART – A (8 X 5 = 40 Marks)

Answer ALL Questions

1. Describe the three levels of data abstraction with a neat diagram.

2. Consider the following relation schema

Depositor(customername, accountnumber)

Borrower(customername, loanno)

Loan(loanno, branchname, amount)

Write the following queries in relational algebra:

a) Find the customers that have both account and loan. [2.5]

b) Find the names of the customers who have loan at Chennai branch. [2.5]

3. How do you map a binary 1:1, 1: Nand M: N relationship type into a relation database schema. Describe with asuitable example.

4. For a relation R(A,B,C,D,E,H) having the set of functional dependencies

F={A->BC, CD->E, E->C, D->AEH, ABH->BD, DH->BC}

Find the key for the relation R.

5. Since indices speed query processing, why might they not be kept on several search keys? Give reasons.

6. Briefly describe the stepsinvolved in query processing.

7. Database-system implementers have paid much more attention to the ACID properties than have file-system implementers. Why might this be the case?

8. Discuss the need for object oriented Databases. Explain with example.

PART – B (6 X 10 = 60 Marks)

Answer any SIX Questions

9. a) Differentiate between traditional file processing systems and database systems. [5]

b) Discuss, briefly, the features of different data models. [5]

10. A university registrar’s office maintains data about the following entities: (a) courses, including number, title, credits, syllabus, and prerequisites; (b) course offerings, including course number, year, semester, section number, instructor(s), timings, and classroom; (c) students, including student-id, name, and program; and (d) instructors, including identification number, name, department, and title.

Further, the enrollment of students in courses and grades awarded to students in each course they are enrolled for must be appropriately modeled. Construct an E-R diagram for the registrar’s office. Document all assumptions that you make about the mapping constraints.

11. Prove that

a) If X ⊇ Y then X → Y [5]

b) If X → Y and Y → Z then X → Z [5]

12. Write an equivalent relational algebra query for the following SQL query and show the steps of Query Optimization from initial query tree to final query tree.

SELECT P.PNUMBER,P.DNUM,E.ENAME, E.ADDRESS, E.BDATE

FROM PROJECT AS P,DEPARTMENT AS D, EMPLOYEE AS E WHERE

P.DNUM=D.DNUMBER AND D.MGRSSN=E.SSN AND

P.PLOCATION=‘BANGALORE’;

13. Differentiate between different single level indexing methods with suitable examples.

14. a) List the ACID properties. Explain the usefulness of each with example. [5]

b) What benefit does strict two-phase locking and rigorous two phase locking provide. Discuss if they provide any disadvantages. [5]

15. Compare the deferred- and immediate-modification versions of the log-based recovery scheme in terms of ease of implementation and overhead cost.

16. a) Differentiate between OLAP and OLTP. [5]

b) Explain various types of OLAP operations. [5]

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