CS2255 Database Management Systems question paper - Nov/Dec 2013

CS2255 Database Management Systems question paper - Nov/Dec 2013 / Anna University Previous Year 2013 Exam Questions / Anna University Previous Year 2013 Computer Science and Information Technology Question Papers / CS 2255/CS 46/CS 1254/080250009/10144 CS 406 — Database Management Systems Question

Question Paper Code : 31305

B.E./B.Tech. DEGREE EXAMINATION, NOVEMBER/DECEMBER 2013.

Fourth Semester

Computer Science and Engineering

CS 2255/CS 46/CS 1254/080250009/10144 CS 406 — 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. What do you mean by simple and composite attribute?
2. Define query.

3. State the difference between security and integrity.

4. Which operators are called as unary operators and why are they called so?

5. Define trivial functional dependency.

6. Define functional dependency.

7. Brief about cascading rollback.

8. What is rigorous two phase locking protocol?

9. What is slotted page sheet? Draw the diagram.

10. What is the content of update log record?

PART B — (5 × 16 = 80 marks)

11. (a) (i) Discuss the main characteristics of the database approach and how does it differ from traditional file system.

(ii) What are the three levels of abstraction in DBMS? (8 + 8)

Or

(b) (i) A database is being constructed to keep track of teams and games of a sport league. A team has a number of players, not all of whom participate in each game. It is described to keep track of players participating in each game in each team, the positions they played on that game and the result of the game. Draw the ER diagram and list its entities and attributes. (10)

(ii) Briefly explain mapping cardinality in detail. (6)

12. (a) Consider the database schema

Emp(emp-name, type, birthday, set of(exam-names), set of(skills))

Children(Emp-name, Ch-name, birthday)

Skills(type, set of(Exam-names))

Exams(Exam-name, year, city)

Write SQL statements for the following queries.

(i) Find the names of all employees who have birthday in March as their children.

(ii) Find those employees who took an examination for the skill type “typing” in the city “Chennai”.

(iii) List all exam names under specific skill type for the given employee other than his exam names.

(iv) Find the names of the city and year where the examination is going to held for the given skill type. (8)

(v) Explain referential integrity with an example. (8)

Or

(b) What is the need for building distributed database? Explain important issues in building distributed database with an example. Explain how distributed database is used in client/server environment. (16)

13. (a) (i) What is redundant data? What are the problems caused by redundant data? (6)

(ii) Explain the process of normalization from 1NF to BCNF stage with example. (10)

Or

(b) Consider the relation R(A,B,C,D,E) with functional dependencies.

{A→BC, CD→E, B→D, E→A}. Identify super keys. Find Fc, F⁺. (16)

14. (a) Explain the following;

(i) Different locking mechanism used in lock based concurrency control. (10)

(ii) Validation based protocol with an example. (6)

Or

(b) (i) What is the difference between conflict serializability and view serializability? Explain in detail with an example. (12)

(ii) Briefly explain ACID property with an example. (4)

15. (a) What is RAID? Briefly explain different levels of RAID. Discuss the factors to be considered in choosing a RAID level. (16)

Or

(b) (i) Explain three kinds of database tuning in detail. (6)

(ii) Explain the structure of B+ tree and how to process queries in B+ tree. (10)

***********

Partitioned Parallel Hash Join

Partitioned Parallel Hash Join in Parallel Database / Parallel Hash Join Technique

Partitioned Parallel Hash Join

The Sequential Hash Join technique discussed in the post Hash Join technique in DBMS can be parallelized. .

The Technique

Please go through the post Hash Join technique in DBMS for better understanding of Parallel Hash-Join.

Assume that we have,

·         n processors, P0, P1, …, Pn-1

·         two tables, r and s (r and s are already partitioned into disks of n processors),

·         s is the smaller table

Parallel Hash-Join Algorithm:

Step 1: Choose a hash function h1. h1 should be able to take the join attribute value of each tuple of relations r and s, and maps them to one of the n processors.

Step 2: Smaller relations s is taken as the Build relation.

Step 3: Each processor Pi reads the tuples of smaller relation s in it’s disk Di, and sends them to different processors based on the hash function h1.

Step 4: On receiving the records of s_i at every destination processor Pi, the Pi further partitions the tuples using another hash function h2. This hash function is used for joining the tuples locally at every processor. This step is independent for any processor.

Step 5: On completion of the distribution of all the records of smaller relation s, it is now the turn for the larger relation r. Step 3 is done for all the records of relation r.

Step 6: On receiving the records of r_i at every destination processor Pi, the Pi further partitions the tuples using another hash function h2. This phase is called the Probe Phase.

Step 7: At last, all the records which can be joined will be in same processors’ same partitions. Each processor Pi executes the build and probe phases of the hash–join algorithm on the local partitions r_i and s_i of r and s to produce a partition of the final result of the hash–join.

Points to remember:

Hash Join at each processor is independent of the other.

It works for equi-join and natural join conditions

Android Apps for Database Study Materials

Database Study Materials Android Apps / Study Materials through Android Apps / Free Study Material Android Applications / Freely Downloadable Database Related Study Materials for Your Android Devices

Database Management Systems / ADBMS Study Materials as Android Apps

I have listed few of the available study material Android Apps' links in the domain of DBMS, ADBMS, and related areas. For some Apps, I have provided direct download links too.
Click on the links to go the download/install page.

Sl. No.	App Names, Links and Direct Download Links	Free	Description
1	Advanced Database Management 1 https://play.google.com/store/apps/details?id=com.faadooengineers.free_advanceddatabase Direct Download - Direct APK Download Link	Free	It provides notes for 133 topics in ADBMS Also contains diagrams, and detailed description on every topic
2	SQL FYI https://play.google.com/store/apps/details?id=com.sql_fyi.myapp Direct Download - Direct APK Download Link	Free	SQL For Your Information
3	Database Management Systems https://play.google.com/store/apps/details?id=com.faad ooengineers.databasemanagementsystems	Not Free	DBMS materials on 150 topics Also contains diagrams, and detailed description on every topic
4	SQL Quick Reference https://play.google.com/store/apps/details?id=in.mettletech.sqlreference Direct Download - Direct APK Download Link	Free	Description with examples and comments Difference in syntax of MySQL, SQL Server, Oracle, Access is discussed.
5	SQL TUTORIAL https://play.google.com/store/apps/details?id=com.sqlstudy	Not Free	Complete SQL training with 150+ Quiz questions
6	SQL Quick Tutorial https://play.google.com/store/apps/details?id=com.sql.infoglobit Direct Download - Direct APK Download Link	Free	Simple EXAMPLES, SYNTAX and DEFINITIONS
7	MySQL Database Basics https://play.google.com/store/apps/details?id=com.udemy.android.sa.introductionToMysqlD	Free	Basics of MySQL
8	SQL and Database in Hindi https://play.google.com/store/apps/details?id=com.ratcatch.sqlanddb Direct Download - Direct APK Download Link	Free	Learn SQL query, database , relational database and different concept of database in Hindi