Gujarat University 2009-3rd Year B.C.A Computer Application Database Management System - Question Paper

Instructions 

All the questions are compulsory.

Figures to the right indicate full marks.

Start writing new question on new page.

Draw figures wherever required.

1

Answer the following.

14

(A)

Answer the following in 'SQL' (Any five)

10

1

List any four Data Manipulation commands.

2

Explain ALTER TABLE command with its various options.

3

Explain GROUP BY clause within SELECT statement.

4

Explain ADD_MONTHS, LAST_DAY function with an example.

5

Define subquery and co-related subquery.

6

Explain Primary key and Foreign key constraint with an example.

(B)

Solve the following queries (any four)

4

TABLES : STUDENT (RollNo, Name, DeptNo, DoB)

                  DEPARTMENT (DeptNo, Dname , Loc)

                  MARKS (RollNo, Totalmarks)

1

Display information of all students department wise

2

Display RollNo, Name of all students having same marks.

3

Display all students born in 'January' month.

4

Display all students having an 'a' in their Name.

5

Display total number of students in each department.

6

Display age of each student.

2

Answer the following (Any seven).

14

1

Define a Table and list any 3 characteristics of a Relational Table.

2

Define Super key and Candidate key.

3

What is Referential Integrity Rule?

4

Explain 'SELECT' relational operator.

5

Define Right and Left Outer Joins.

6

What is difference between a database and a table?

7

Explain 1:1 and 1:M relationship within the relational database.

8

Define Homonyms and Synonym.

9

Define Data Dictionary and System Catalog.

3

Answer the following.

14

(A)

Answer any one of the following.

7

1

What is Normalization and why do we need Normalization ? Define BCNF and 4NF by giving an appropriate example.

2

Normalize the following table upto 3NF and draw all dependency diagrams:

(B)

Answer the following (any one)

7

1

Explain Distributed Database and disributed processing with appropriate diagrams.

2

Discuss the components of DDBMS with a diagram.

4

Answer the following.

14

(A)

Answer any one of the following.

7

1

Explain the five major parts of database system enviornment.

2

Discuss Hierarchical and Relational Data Models.

(B)

Explain any one of the following.

7

1

Draw E-R Model for the Scenario given below:

A customer comes to a hotel and checks for room availabilityfrom the ho tel manager. The hotel manager enquires ab out number of persons and e x plain him different typesof rooms available and their teriffs.The customer books a room and gives his contract and stay details. At the end of the stay, the customer enquires about payment options. The manager confirms thatpayment options can be made by cash or card and gives him the bill. The customer makes the payment to the manager.

2

Draw E-R Model for the Scenario given below:

A customer requests a video on rent. The salesperson explains him various membership plans. The customer selects one of the membership plan by giving his contact details and paying membership fees. The member enquires about films in different lanbuages. The salesperson shows the list of film videos in different languages. The member selects the video. After the video is rented, the salesperson updates the number of film copies already rented.

5

Answer the following (Any two).

14

1

What is Transaction? List and discuss ACID properties of a Transaction. Define Transaction Log and Whatis its function.

2

Explain types of locks in detail. Discuss the locking protocol to ensure serializability.

3

Explain concurrency control with Time Stamp Method. Define Database buffer and Database Checkpoint.