CS2255 Database Management Systems question paper - May/June 2013

CS2255 Database Management Systems question paper - May/June 2013 / Anna University Previous Year 2013 Exam Questions / Anna University Previous Year 2013 Computer Science and Information Technology Question Papers

Question Paper Code : 21305

B.E./B.Tech. DEGREE EXAMINATION, MAY/JUNE 2013

Fourth Semester

Computer Science and Engineering

CS 2255/CS 46/CS 1254/10144 CS 406/08025009 — DATABASE MANAGEMENT SYSTEMS

(Common to Information Technology)

(Regulation 2008/2010)

(Common to PTCS 2255 – Database Management Systems for B.E. (Part-Time) Third Semester – Computer Science and Engineering (Regulation 2009))

Time: Three hours                                                       Maximum: 100 Marks

Answer ALL Questions

PART A — (10 × 2 = 20 Marks)

1. Define Atomicity in Transaction management.

2. Give example for one to one and one to many relationship.

3. What are primary key constraints?

4. Write the purpose of Trigger.

5. Define Boyce Codd Normal Form.

6. What is the need for Normalization?

7. List ACID properties.

8. Define two phase locking.

9. What is the need for RAID?

10. What is the basic difference between static hashing and dynamic hashing?

PART B — (5 × 16 = 80 Marks)

11. (a) Explain the purpose of database system

Or

 (b) Write about the structure of database system architecture with block diagram.

12. Consider the following relational database

          Employee (Employee-Name, Street, City)

          Works (Employee-Name, Company-Name, Salary)

          Company (Company-Name, City)

          Manager (Employee-Name, Manager-Name)

Give an SQL DDL definition of this database. Identify referential integrity constraints that should hold, and include them in the DDL definition.

Or

 (b) Consider the following relation

Employee (Employee-Name, Company-Name, Salary)

Write SQL for the following:                                                                 (4 X 4 = 16)

          (i) Find the total salary of each company

          (ii) Find the employee name who is getting lowest salary

          (iii) Find the company name which has lowest average salary

          (iv) Find the employee name whose salary is higher than average salary of TCS.

13. (a) Consider the following relation

CAR-SALE (Car#, Date-Sold, Salesman#, Commission%, Discount-amount)

Assume that a car may be sold by multiple salesmen, and hence (Car#, Salesman#) is the primary key.

Additional dependencies are,

Date-Sold --> Discount-amount

and

Salesman# --> Commission%

Based on the given primary key, is this relation in 1NF, 2NF, or 3NF? Why or why not? How would you successively normalize it completely?

Or

 (b) Explain the principles of

          (i) Loss less join decomposition                                                            (5)

          (ii) Join dependencies                                                                         (5)

          (iii) Fifth normal form                                                                         (6)

14. (a) Illustrate deadlock and conflict serializability with suitable example.

Or

(b) (i) Explain two phase commit protocol.                                                    (10)

     (ii) Write different SQL facilities for recovery                                            (6)

15. (a) Construct B+ tree to insert the following (order of the tree is 3)

26, 27, 28, 3, 4, 7, 9, 46, 48, 51, 2, 6.

Or

 (b) Let relations r1(A, B, C) and r2(C, D, E) have the following properties:

r1 has 20,000 tuples, r2 has 45,000 tuples, 25 tuples of r1 fit on one block and 30 tuples of r2 fit on one block. Estimate the number of block transfers and seeks required, using each of the following Join strategies for r1⋈ r2:

          (i) Nested-loop join.                                                                           (4)

          (ii) Block nested-loop join.                                                                  (8)

          (iii) Merge join.                                                                                  (4)

_____________________________

CS2255 Database Management Systems question paper - May/June 2012

CS2255 Database Management Systems question paper - May/June 2012 / Anna University Previous Year 2012 Exam Questions / Anna University Previous Year 2012 Computer Science and Information Technology Question Papers

Question Paper Code : 10267

B.E./B.Tech. DEGREE EXAMINATION, MAY/JUNE 2012

Fourth Semester

Computer Science and Engineering

CS 2255/141405/CS 46/CS 1254/10144 CS 406/08025009 — 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. List four significant differences between a file-processing system and a DBMS.

2. What are the different types of Data Models?

3. Describe a circumstance in which you would choose to use embedded SQL rather than using SQL alone.

4. List two major problems with processing of update operations expressed in terms of views.

5. Give an example of a relation schema R and a set of dependencies such that R is in BCNF, but not in 4NF.

6. Why are certain functional dependencies called as trivial functional dependencies?

7. List down the SQL facilities for concurrency.

8. What benefit does strict two-phase locking provide? What disadvantages result?

9. Mention the different Hashing techniques.

10. When is it preferable to use a dense index rather than a sparse index? Explain your answer.

PART B — (5 × 16 = 80 Marks)

11. (a) Discuss in detail about database system architecture with neat diagram.

Or

(b) Draw an E-R diagram for a banking enterprise with almost all components and explain.

12. (a) Explain in detail about Relational Algebra, Domain Relational Calculus, and Tuple Relational Calculus with suitable examples.

Or

(b) Briefly present a survey on Integrity and Security.

13. (a) Explain in detail about 1NF, 2NF, 3NF, and BCNF with suitable examples.

Or

(b) Describe about the Multi-Valued Dependencies and Fourth normal form with suitable example.

14. (a) Discuss in detail about Transaction Recovery, System Recovery and Media Recovery.

Or

(b) Write down in detail about Deadlock and Serializability.

15. (a) Construct a B+ tree to insert the following key elements (order of the tree is 3) 5, 3, 4, 9, 7, 15, 14, 21, 22, 23.

Or

(b) Describe in detail about how records are represented in a file and how to organize them in a file.

_____________________________

Latest Information - Regarding Technical Education

BE / B.Tech./B.Arch/Polytechnic Admission Information 2014 - Important links / தமிழ்நாடு பொறியியல் நுழைவுத்தேர்வு 2014

Tamilnadu Engineering Admissions 2014

Minimum Cutoff Required for Various Engineering Colleges in Tamilnadu

Cutoff Links - Find in Latest Notifications

Tamilnadu Engineering Admissions 2014 - Important Dates

தமிழ்நாடு பொறியியல் நுழைவுத்தேர்வு 2014 - முக்கிய தேதிகள்

Tamilnadu Engineering Admissions 2014 - Certificates to be enclosed

தமிழ்நாடு பொறியியல் நுழைவுத்தேர்வு 2014 - சமர்பிக்க வேண்டிய கோப்புகள்

Tamilnadu Engineering Admissions 2014 - Online Application

தமிழ்நாடு பொறியியல் நுழைவுத்தேர்வு 2014 - ஆன்லைன் விண்ணப்பம்

List of Engineering colleges in Tamilnadu

தமிழ்நாடு  பொறியியல் கல்லூரிகள்  பட்டியல்

Direct Second Year - Lateral Entry B.E/B.Tech 2014-15 - Admission Guidelines

Tamilnadu Polytechnic Admissions 2014

தமிழ்நாடு பல்தொழில்நுட்ப பயிலகம் - நுழைவுத்தேர்வு 2014

 

Direct Second Year Diploma application form

List of Polytechnic Colleges in Tamilnadu

CS2255 Database Management Systems question paper - Apr/May 2011

CS2255 Database Management Systems question paper - Apr/May 2011/ Anna University Previous Year Exam Questions / Anna University Previous Year Computer Science and Information Technology Question Papers

Question Paper Code : 11259

B.E./B.Tech. DEGREE EXAMINATION, APRIL/MAY 2011

Fourth Semester

Computer Science and Engineering

CS2255 — 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. Who is a DBA? What are the responsibilities of a DBA?

2. What is a data model? List the types of data models used.

3. What is embedded SQL? What are its advantages?

4. What is the difference between tuple relational calculus and domain relational calculus?

5. What is meant by lossless-join decomposition?

6. A relation R = {A, B, C, D} has FD’s F = {AB → C, C → D, D → A}. Is R is in 3NF?

7. What are the ACID properties?

8. What are two pitfalls (problems) of lock-based protocols?

9. What are the advantages and disadvantages of indexed sequential file?

10. What is database tuning?

PART B — (5 × 16 = 80 Marks)

11. (a) (i) With a neat diagram, explain the structure of a DBMS. (9)

(ii) Draw an E-R diagram for a small marketing company database, assuming your own data requirements. (7)

Or

(b) (i) Compare the features of file system with database system. (8)

(ii) Explain the differences between physical level, conceptual level and view level of data abstraction. (4)

(iii) Mention any four major responsibilities of DBA. (4)

12. (a) (i) Consider the following relational database

Employee(employee-name, street, city)

Works( employee-name, company-name, salary)

Company(company-name, city)

Manages(employee-name, manager-name)

Give an expression in SQL to express each of the following queries :

Find the names and cities of residence of all employees who work for XYZ Bank.

Find the names, street address, and cities of residence of all employees who work for XYZ Bank and earn more than Rs. 10,000 per annum.

Find the names of all employees in this database who live in the same city as the company for which they work.

Find the names of all employees who live in the same city and on the same street as do their managers. (4 × 3 = 12)

(ii) Define the term distributed database management system and mention the issues to be considered in the design of the same. (4)

Or

(b) (i) What are the relational algebra operations supported in SQL? Write the SQL statement for each operation. (12)

(ii) What is data integrity? Explain the types of integrity constraints. (4)

13. (a) (i) Explain 1NF, 2NF, 3NF and BCNF with suitable example. (8)

(ii) Consider the universal relation R = {A, B, C, D, E, F, G, H, I} and the set of functional dependencies F = {(A, B) → C, A → (D, E), B → F, F → (G, H), D → (I, J)}. What is the key for R? Decompose R into 2NF, then 3NF relations. (8)

Or

(b) What are the pitfalls in relational database design? With a suitable example, explain the role of functional dependency in the process of normalization. (16)

14. (a) (i) Explain about immediate update and deferred update recovery techniques. (8)

(ii) Explain the concepts of serializability. (8)

Or

(b) (i) Explain Two-phase locking protocol. (8)

(ii) Describe about the deadlock prevention schemes. (8)

15. (a) (i) List the different levels in RAID technology and explain its features. (12)

(ii) Describe the different methods of implementing variable length records. (4)

Or

(b) (i) Explain the various indexing schemes used in database environment. (12)

(ii) Let relations r1(A, B, C) and r2(C, D, E) have the following properties : r1 has 20,000 tuples, r2 has 45,000 tuples, 25 tuples of r1 fit on one block, and 30 tuples of r2 fit on one block. Estimate the number of block accesses required, using each of the following join strategies for r1 Natural Join r2 : (4)

(1) Nested-loop join with r1 as outer relation

(2) Block nested-loop join with r1 as outer relation

(3) Merge join if r1 and r2 are initially sorted

(4) Hash join (assuming that no overflow occurs).