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

Seat No. : _

I

TBCA-02

April-2008

B.C.A. (302) Database Management System (New Course)

Time : 3 Hours]    (Max. Marks : 70

Instructions: (1) All the questions are compulsory.

(2)    Figures to the right indicate marks.

(3)    Start writing new question on new page.

(4)    Draw diagrams wherever required.

1. Answer the following questions: (any seven)    14

Write any four characteristics of a Relational Tabic.

' l)a) Define Composite Key and Foreign Key.

(ef State Entity Integrity Rule.

Explain PROJECT' relational operator.

What is a Theta Join ?

Give an example of 1 : 1 and I : M Relationships.

(g)    Differentiate between Homonym and Synonym.

(h)    Prepare a sample data dictionary for the CUSTOMER table. It should contain at least four fields.

<i) Discuss Left and Right Outer Joins.

tfT Name any four Data Types which arc supported by various DBMSs.

2. (a) Answer the question : {any one)    7

Discuss different Types of Databases.

(ii) Explain Hierarchical and Network Data Models.

(b) Answer the following: (any one)    7

(i) Draw E-R Model for the procedure given below :

A customer contacts a reservation officer about a car rental. The customer quotes the start and end dates needed, the preferred vehicle and the pickup offjee. The reservation officer looks up a prices file and quotCsS a price. The customer agrees to the pricc. The vehicle availability file is checked to see if an appropriate vehicle is available for the required time at the required office. If the requested vehicle is available at the nominated pickup office, then it is reserved for \he customer. An entry i- mad n the vehicle availability registering the reservation. The ieserva!wu officcr a iemai number to the customer. A rental agreement is then creatcd on the rental file, including the rental j*umber, the rental period, the vehicle type and the pickup office.

(ii) Draw E-R Model for the procedure given below :

A customer requests an item from a salesperson. The salesperson looks up the inventory 10 see if the item is available. The salesperson replies to the customer. The customer places an order with the salesperson. The salesperson forwards the order to the inventory. The inventory placcs the item on hold and informs the salesperson. The salesperson invoices the customer. When payment on the invoice is received, the salesperson requests inventory to make the delivery. Inventory sends a delivery note to the customer.

3. (a) Answer the following : (any one)

What is Functional .and Transitive Dependency ? Define 2NF and 3NF. Explain both by giving an appropriate example.

(ii) Normalize the following table up to 3NF and drawall dependency diagrams.

Kilt No. Tabic No. Bill Date F.mp No! Dish No. Dish Dcsc. Dish Price- Emp Skill Kmp Name Qty Total Tip 1 Bill I Tul.nl 34 12 2*4-04 56 50 Coffee 10.00 Waiicr Pat 2 20.00 38.00 190.00 84 12 2-4-04 56 250 Club Sand witch 85.00 Waiter Pat 2 170.00 38.00 190.00 35 17 3-4-04 61 300 Pi2za 105.00 Waiter Ian 1 105.00 21.00 105.00 36 14 3-4-04 61 50 Coffee 10.00 Waiter lan 2 20.00 63.00 315.00 36 14 3-4-04 6! 100 Scraitiblcd Eggs 25.00 Waiter Ian 4 100.00 63.00 315.00 36 )4 3-4-04 61 200 French Fries 30.00 Waiicr lan 2 60.00 63.00 315.00 36 14 3-4-04 61 9 150 Chocolate Thick Shake 4 S.00 Waiter lau 3 135.00 63.00 315.00 37 i: 4-4-04 59 300 Pizza 105.00 Waiter Don 2 210.00 42.00 210.00 3S 8 4-4-04 56 50 Coffee 10.00 Waiter Pat 2 20.00 14.1)0 70.00 38 a 4-4-04 56 25 Black Tea 25.00 Waiter Pat 50.00 14.00 70.00

(b) Answer the following : (any one)

Explain advantages and disadvantages of DDBMS.

(ti) Draw diagram of Components of DDBMS and explain.

TBCA-02

   V

   3

5. (a) Answer the following w.r.t.SQL: (any four)    10

(i)    Discuss Spccial Operators Between, Is Null, Like, In and Exists that ore used in SELECT statement.

f*-    Explain Logical Operators.

(inf Write any two examples of Check Constraint.

Jjtff Describe Aggregate Functions. ¹

<t) Briefly explain Views in SQL.

[(b) Solve the following queries : (any four)    4

Tables : JOBfJob code. Job desc. Job che hour)

PROJECT(Proj_no, Proj_name, Proj_value, Emp_no)

EMPLOYEE(Emp_no, Emp_name, Emp_hiredate, Job codc)

p)* Display employee number and employee name who are not working on any project.

(ii)    Display pair of employees whose designation is same.

Display total number of years of job experience of each employee.

Jv)'Display information of all programmers. ffi    iyy Display name of the employee whose name is the longest.

0    ¹ (vi) Display job code wise total number of employees.

r\

1

TBCA-02

1

Attachment: gujarat-university-2008-b-c-a-computer-application-32845-1.jpg gujarat-university-2008-b-c-a-computer-application-32845-2.jpg gujarat-university-2008-b-c-a-computer-application-32845-3.jpg

Earning:   Approval pending.