DATABASE MANAGEMENT SYSTEMS CS2255 MODEL QUESTION PAPER | CS 2255 DBMS QUESTION PAPER NOVEMBER/DECEMBER 2011
2. With an example explain what a derived attribute is?
3. Consider the following relation :
EMP (ENO, NAME, DATE_OF_BIRTH, SEX, DATE_OF_JOINING, BASIC_PAY, DEPT) Develop an SQL query that will find and display the average BASIC_PAY in each DEPT.
4. List the two types of embedded SQL SELECT statements.
5. Consider the following relation :
R (A, B, C, D, E)
The primary key of the relation is AB. The following functional
dependencies hold :
A →C
B →D
AB → E
Is the above relation in second normal form?
6. Consider the following relation : R(A, B, C, D)
The primary key of the relation is A. The following functional dependencies
hold :
A →B,C
B →D
Is the above relation in third normal form?
7. List the two commonly used Concurrency Control techniques.
8. List the SQL statements used for transaction control.
9. What are ordered indices?
10. Distinguish between sparse index and dense index.
(ii) A university registrar’s office maintains data about the following entities :
(1) Courses, including number, title, credits, syllabus, and prerequisites;
(2) Course offerings, including course number, year,
semester, section number, instructor, timings, and classroom;
(3) Students, including student-id, name, and program; and
(4) 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) What is aggregation in an ER model? Develop an ER diagram using aggregation that captures the following information :
Employees work for projects. An employee working for a particular project uses various machinery. Assume necessary attributes. State any assumptions you make. Also discuss about the ER diagram you have designed. (2 + 6)
12. (a) (i) Explain the distinctions among the terms primary key, candidate key, and super key. Give relevant examples. (6)
(ii) What is referential integrity? Give relevant example. (4)
(iii) Consider the following six relations for an Order-processing Database Application in a Company :
CUSTOMER (CUSTNO, CNAME, CITY)
ORDER (ORDERNO, ODATE, CUSTNO, ORD_AMT)
ORDER_ITEM (ORDERNO, ITEMNO, QTY)
ITEM (ITEMNO, ITEM_NAME, UNIT_PRICE)
SHIPMENT (ORDERNO, ITEMNO, WAREHOUSENO,
SHIP_DATE)
WAREHOUSE (WAREHOUSENO, CITY)
Here, ORD_AMT refers to total amount of an order; ODATE is the date the order was placed; SHIP_DATE is the date an order is shipped from the warehouse. Assume that an order can be shipped from several warehouses. Specify the foreign keys for
this schema, stating any assumptions you make. (6)
13. (a) Define a functional dependency. List and discuss the six inference rules for functional dependencies. Give relevant examples. (16)
Or
(b) (i) Give a set of Functional dependencies for the relation schema R(A,B,C,D,E) with primary key AB under which R is in 2NF but not in 3NF. (5)
(ii) Prove that any relation schema with two attributes is in BCNF.(5)
(iii) Consider a relation R that has three attributes ABC. It is decomposed into relations R1 with attributes AB and R2 with attributes BC. State the definition of lossless-join decomposition with respect to this example. Answer this question concisely by
writing a relational algebra equation involving R, R1, and R2. (6)
14. (a) (i) Define a transaction. Then discuss the following with relevant examples : (8)
(1) A read only transaction
(2) A read write transaction
(3) An aborted transaction
(ii) With a neat sketch discuss the states a transaction can be in. (4)
(iii) Explain the distinction between the terms serial schedule and serializable schedule. Give relevant example. (4)
(ii) Discuss two phase locking protocol. Give relevant example. (8)
15. (a) (i) When is it preferable to use a dense index rather than a sparse index? Explain your answer. (4)
(ii) Since indices speed query processing, why might they not be kept on several search keys? List as many reasons as possible.(6)
(iii) Explain the distinction between closed and open hashing. Discuss the relative merits of each technique in database applications. (6)
B.E./B.Tech. DEGREE EXAMINATION, NOVEMBER/DECEMBER 2011.
Fourth Semester
Computer Science and Engineering
CS 2255 — DATABASE MANAGEMENT SYSTEMS
(Common to Information Technology)
(Regulation 2008)
Time : Three hours Maximum : 100 marks
Answer ALL questions.
PART A — (10 × 2 = 20 marks)
1. What is a data model?2. With an example explain what a derived attribute is?
3. Consider the following relation :
EMP (ENO, NAME, DATE_OF_BIRTH, SEX, DATE_OF_JOINING, BASIC_PAY, DEPT) Develop an SQL query that will find and display the average BASIC_PAY in each DEPT.
4. List the two types of embedded SQL SELECT statements.
5. Consider the following relation :
R (A, B, C, D, E)
The primary key of the relation is AB. The following functional
dependencies hold :
A →C
B →D
AB → E
Is the above relation in second normal form?
6. Consider the following relation : R(A, B, C, D)
The primary key of the relation is A. The following functional dependencies
hold :
A →B,C
B →D
Is the above relation in third normal form?
7. List the two commonly used Concurrency Control techniques.
8. List the SQL statements used for transaction control.
9. What are ordered indices?
10. Distinguish between sparse index and dense index.
PART B — (5 × 16 = 80 marks)
11. (a) (i) Construct an E-R diagram for a car-insurance company whose customers own one or more cars each. Each car has associated with it zero to any number of recorded accidents. State any assumptions you make. (6)(ii) A university registrar’s office maintains data about the following entities :
(1) Courses, including number, title, credits, syllabus, and prerequisites;
(2) Course offerings, including course number, year,
semester, section number, instructor, timings, and classroom;
(3) Students, including student-id, name, and program; and
(4) 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)
Or
(b) (i) With a neat sketch discuss the three-schema architecture of a DBMS. (8)(ii) What is aggregation in an ER model? Develop an ER diagram using aggregation that captures the following information :
Employees work for projects. An employee working for a particular project uses various machinery. Assume necessary attributes. State any assumptions you make. Also discuss about the ER diagram you have designed. (2 + 6)
12. (a) (i) Explain the distinctions among the terms primary key, candidate key, and super key. Give relevant examples. (6)
(ii) What is referential integrity? Give relevant example. (4)
(iii) Consider the following six relations for an Order-processing Database Application in a Company :
CUSTOMER (CUSTNO, CNAME, CITY)
ORDER (ORDERNO, ODATE, CUSTNO, ORD_AMT)
ORDER_ITEM (ORDERNO, ITEMNO, QTY)
ITEM (ITEMNO, ITEM_NAME, UNIT_PRICE)
SHIPMENT (ORDERNO, ITEMNO, WAREHOUSENO,
SHIP_DATE)
WAREHOUSE (WAREHOUSENO, CITY)
Here, ORD_AMT refers to total amount of an order; ODATE is the date the order was placed; SHIP_DATE is the date an order is shipped from the warehouse. Assume that an order can be shipped from several warehouses. Specify the foreign keys for
this schema, stating any assumptions you make. (6)
Or
(b) With relevant examples discuss the various operations in Relational Algebra. (16)13. (a) Define a functional dependency. List and discuss the six inference rules for functional dependencies. Give relevant examples. (16)
Or
(b) (i) Give a set of Functional dependencies for the relation schema R(A,B,C,D,E) with primary key AB under which R is in 2NF but not in 3NF. (5)
(ii) Prove that any relation schema with two attributes is in BCNF.(5)
(iii) Consider a relation R that has three attributes ABC. It is decomposed into relations R1 with attributes AB and R2 with attributes BC. State the definition of lossless-join decomposition with respect to this example. Answer this question concisely by
writing a relational algebra equation involving R, R1, and R2. (6)
14. (a) (i) Define a transaction. Then discuss the following with relevant examples : (8)
(1) A read only transaction
(2) A read write transaction
(3) An aborted transaction
(ii) With a neat sketch discuss the states a transaction can be in. (4)
(iii) Explain the distinction between the terms serial schedule and serializable schedule. Give relevant example. (4)
Or
(b) (i) Discuss the ACID properties of a transaction. Give relevant example. (8)(ii) Discuss two phase locking protocol. Give relevant example. (8)
15. (a) (i) When is it preferable to use a dense index rather than a sparse index? Explain your answer. (4)
(ii) Since indices speed query processing, why might they not be kept on several search keys? List as many reasons as possible.(6)
(iii) Explain the distinction between closed and open hashing. Discuss the relative merits of each technique in database applications. (6)
Or
(b) Diagrammatically illustrate and discuss the steps involved in processing a query.(16)
