BSc (Hons) Information Technology Enabled Services Cohort: ITES/05/PT

Examinations for 2005 - 2006 / Semester 2

MODULE: DATABASE MANAGEMENT SYSTEMS MODULE CODE: DBT101 Duration: 2 Hours and 30 minutes

Instructions to Candidates: 1.

Answer any four questions.

2.

All question carry equal marks.

3.

Always start a new question on a fresh page.

5.

Total marks 100.

This question paper contains 5 questions and 6 pages.

Page 1 of 6 SB/100

ANSWER ANY FOUR QUESTIONS QUESTION 1: (25 MARKS) The table below list customer/car-hire data. Each customer may hire cars from various outlets. A car is registered at a particular outlet and can be hired out to a customer on a given date. a)

The data in the table is subject to anomalies. Provide examples of how insertion, deletion, and modification anomalies could occur on this table. [3 marks]

b)

Identify the functional dependencies represented by the data in the table. State any assumptions you make about the data. [3 marks]

c)

Using the functional dependencies identified, describe and illustrate the process of normalization by converting the table to Third Normal Form (3NF) relations. [12 marks]

d)

Draw an Entity-Relationship model for the data in the table. Show all the entities, relationships, and attributes. [7 marks]

CarReg

Make

Model

CustNo CustName HireDate OutletNo OutletLoc

W565CDC Ford

Escort

C100

Smith J.

14/05/03

21 Woodstock

W565CDC Ford

Escort

C222

Patel V.

15/05/03

21 Woodstock

V734HSB Nissan Sunny

C100

Smith J.

14/05/03

21 Woodstock

W104RSM Ford

Escort

C303

Brown F.

14/05/03

24 Denham

W104RSM Ford

Escort

C100

Smith J.

16/05/03

24 Denham

W611SBH Nissan Sunny

C222

Patel V.

15/05/03

24 Denham

Page 2 of 6 SB/100

QUESTION 2: (25 MARKS) a)

Discuss the concept of data independence, explaining its importance in a database environment. [6 marks]

b)

To address the issue of data independence, the ANSI-SPARC three-level architecture was proposed. Compare and contrast the three levels of this model. Make use of a diagram in your discussion. [10 marks]

c)

The term integrity refers to the accuracy or correctness of the data in the database. Define the following terms: (i)

Domain Integrity

(ii)

Entity Integrity

(iii)

Referential Integrity [9 marks]

QUESTION 3: (25 MARKS) a)

What is Client/Server and what benefits can be expected from Client/Server systems? [8 marks]

b)

Explain what middleware is and what it does. Why would MIS managers be particularly interested in such software? [6 marks]

c)

What is a 3-Tier architecture, and what are the benefits of setting up one. Support your answer with appropriate diagram. [8 marks]

d)

Discuss the different levels of security that should be established in a client/server database system. [3 marks]

Page 3 of 6 SB/100

QUESTION 4: (25 MARKS) (a)

Discuss the difference between a composite key and a composite attribute. How would each be indicated in an E-R diagram? Support your answer with appropriate example. [5 marks]

(b)

Use the following business rules to produce the E-R diagram. Identify at least two attributes for each of the identified entity. You should clearly show all the minimum and cardinality constraint on each side of the relationship.

(i)

A department employs many employees, but each employee is employed by one department.

(ii)

Some employees, known as "rovers," are not assigned to any department.

(iii) A division operates many departments, but each department is operated by one division. (iv) An employee may be assigned to many projects and a project may have many (v)

employees assigned to it.

A project must have at least one employee assigned to it.

(vi) One of the employees manages each department. (vii) One of the employees runs each division. [10 marks] (c)

Map the ERD produced into its equivalent Relational Schema. [10 marks]

Page 4 of 6 SB/100

QUESTION 5: (25 MARKS) This question refers to the database schema given in the Appendix (attached). a)

Express the following queries in SQL: (i)

Write the SQL code that will create the table Company. Your answer should contain the primary key constraint declaration. [3 marks]

(ii)

Having created the table structure in question (i), write the SQL code that will enter the first data row into the Company table. [2 marks]

(iii) Write the SQL code to change the Bank code to 880808 for the company whose CompanyNo is 01. [2 marks] (iv) Write the SQL code required to list all car details whose manufacturer’s name start with the letter ‘M’. In other words, the rows for both Mecerdes and Maserati should be included in the listing. [2 marks] (v)

Write the SQL code that will list the manufacturers of cars with the nationality “Italian” and with an engine size greater than 3500. [3 marks]

(b)

Express the following queries in relational algebra. i.) Find the manufacturers of cars with the nationality “Italian” and with an engine size greater than 3500. [3 marks] ii.) Find the names, together with the registration number of cars they have on loan, for those drivers who are employed by the company with the bankcode 880513. [4 marks] iii.) Find the names of those companies who hire all the different models of car manufactured in Italy. [6 marks]

Page 5 of 6 SB/100

APPENDIX A hire company rents cars to commercial customers. These commercial customers provide the cars on long term loan to their employees. Some of the tables in the hire company’s database appear below. The database schema is defined as: DRIVER (CompanyNo, EmployeeNo, DName, Address) CAR (CarTypeCode, manufacturer, Model, nationality, EngineSize) COMPANY (CompanyNo, CName, Address, BankCode) FLEET (CarRegNo, CompanyNo, CarTypeCode, EmployeeNo, DateDelivered) You may assume that there are no null values in the database. The table Company contains details of the commercial companies with which the hire company deals. The table Driver provides data about the employees of these commercial companies. Whereas each value of CompanyNo is unique, the same value of EmplyeeNo may be used simultaneously by several commercial customers. The table Car is a catalogue of all the different types of car hired by the company. The table Fleet contains details of all cars at present on hire. CarTypeCode is a foreign key, which cross references the table Car. Not all entries in the table Car may be currently on hire. At present time the following values are included in the table: CAR CarTypeCode 01 02 03 04 05 06 07

Manufacturer Ferrari Ferrari Ferrari Bentley Mercedes Maserati Bentley

Model 512 BB 328 Azure SLK Ghibili Brooklands

Nationality Italian Italian Italian English German Italian English

EngineSize 4000 5000 3500 6000 4000 3500 5000

FLEET CarRegNo T42 XYZ1 ABC1 CKG535 PP1

CompanyNo 01 01 01 07 05

CarTypeCode 01 02 03 03 07

EmployeeNo 1678 6590 3761 7902 101

DateDelivered 12-May-99 05-Feb-99 18-Jun-99 25-Mar-99 20-Feb-99

DRIVER CompanyNo 01 01 01 07 05

EmployeeNo 1678 6590 3761 7902 101

DName Robert Paul Raj Abdul Elvis

Address Bracknell Reading Redhill Surrey Neath

CompanyNo 01 07 05 08

CName St Gerant Rogers IBM UTM

COMPANY Address Belle Mare Port Louis Rose Hill Port Louis

BankCode 880513 552091 000001 770707

***END OF QUESTION PAPER*** Page 6 of 6 SB/100