CS 8303 – DATABASE MANAGEMENT SYSTEMS April May 2014

Anna University CS 8303 – DATABASE MANAGEMENT SYSTEMS April May 2014, Computer Science and Engineering, Third Semester, Regulation 2012. Database Management System Question Paper April May 2014

Exam	B.E/B.Tech. (Full Time) DEGREE END SEMESTER EXAMINATIONS
Academic Year	April May 2014
Subject Code	CS 8303
Subject Name	Database Management Systems
Branch	Computer Science and Engineering
Semester	Third Semester
Regulation	2012

B.E / B.Tech. (Full Time) DEGREE END SEMESTER EXAMINATIONS, APRIL / MAY 2014

Computer Science and Engineering

Third Semester

CS 8303 – DATABASE MANAGEMENT SYSTEMS

(Regulations 2012)

Time : 3 Hours                      Answer A L L Questions                Max. Marks 100

PART-A (10 x 2 = 20 Marks)

1. List the responsibilities of a database administrator.

2. List four advantages of database systems over file systems.

3. List the properties to be satisfied by a decomposition of relation

4. Define PJNF. Give an example.

5. What is dynamic SQL?

6. Can views be modified? Justify.

7. What is savepoint?

8. What is the advantage of indent locking?

9. What are the two types of spatial queries? Give examples.

10. What are the issues in mobile databases?

Part-B (5* 16 = 80 Marks)

11. (i) Explain the architecture of database systems with a neat diagram. (10)

(ii) Write short notes on data models. (6)

12. a) (i) 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. (10)

(ii) Convert the ER schema shown in figure 12 (a) (ii) into relational schema. Document all assumptions that you make about the mapping constraints. (6)

 (OR)

(i) Consider a database the following relational schema: (10)

Customer (Cust#, Cname, City)

Order(Order#, Odate, Cust#, Ord_Amt)

Order_ltem(Order#, ltem#, Qty)

ltem(ltem#, Unit_Price)

Shipment(Order#, Warehouse#, Ship_date)

Warehouse(Warehouse#, City)

Ofd_Amt refers to total dollar amount of an order; Odate is the date the order was placed; Ship_date is the date an order is shipped from the warehouse. An order can be shipped from any warehouse.

Express the following queries in RA:

A) List the Order# and Ship_date for all orders shipped from Warehouse# "W2".

B) Delete all orders for customer named "Jose"

C) List all customer names whose orders were shipped from a warehouse in the same city as they live in.

D) List the Order# for orders that were shipped from all warehouses in New York.

E) Produce a listing: Cname, #ofOrders, Avg_Order_Amt, where the middle column is the total number of orders by the customer and the last column is-the average order amount for that-customer.

(ii) Consider the relation for published-books: (6)

BOOK (Title, Author, Type, Price, Affil, Publisher)

Affil refers to the affiliation of the author.

Suppose the following dependencies exist:

Title -> Publisher, Type

Type -»Price

Author -> Affil

What normal form is the relation in? Explain your answer. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

13. a) ( i ) Explain how database security is achieved using SQL. (10)

(ii) What are triggers? Discuss the use of triggers with an example. (6)

(OR)

(i) Consider a database the following relational schema:  (10)

CUSTOMER (Cust Id, Cust_Name, Annual_Revenue, Cust_Type)

SHIPMENT (Shipments#, Cust Id, Weight, Truck#, Destination, Ship_Date)

Note: Cust_id references CUSTOMER

Truck# references TRUCK

Destination references CITY

Assume each shipment contains one package.

TRUCK (Truck#, Driver_Name)

CITY (City Name, Population)

Express the following queries in SQL:

A) List the name and id of customers for whom the driver, Kennedy has delivered shipments.

B) List the cities to which the driver, Kennedy has delivered shipments.

C) List the average weight of the shipments received by each customer.

D) For each city which has received atleast 10 packages, what is the average weight of a package sent to that city?

E) List the drivers who have delivered shipments to every city.

(ii) Explain referential integrity constraint, and the ways in which updates of such attributes are handled. (6)

14. a) (i) Explain 2PL and its variants with the advantages and disadvantages. (10)

(ii) Explain the properties of a transaction with suitable examples.  (6)

(OR)

b) (i) What are deadlocks? How are they handled? (10)

(ii) Explain 2PC protocol. (6)  

15. a) (i) Explain the different algorithms used for selection operation and their associated cost. (10)

(ii) Discuss the steps involved in query processing with a neat diagram. (6)

(OR)

b) (i) What are temporal databases? Explain insertion and deletion in temporal databases. (10)

(ii) Write notes on data mining. (6)

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

Go back to Database Management Systems Question Papers page

Go back to Anna University B.E Computer Science and Engineering Questions April May 2014 Regulation 2012  page

Go back to Anna University B.E Computer Science and Engineering Questions April May 2014  page

Go back to Anna University B.E Computer Science and Engineering Questions page