Database Storage & Design Chapter 16 Homework Coursework Requirements Part Design The Database Create

PART IV

(f) Approximately 50 new video orders are placed each week. The details of video orders are

destroyed two years after the creation of the record.

The types and average number of record searches

(a) Searching for the details of a branch approximately 10 per day.

(b) Searching for the details of a member of staff at a branch approximately 20 per day.

15.7 (a) Create a conceptual schema for each view of StayHome using the concepts of the Enhanced

Entity Relationship (EER) model. To simplify each diagram, only show entities, relationships

and the primary key attributes. Specify the cardinality ratio and participation constraint of

each relationship type. State any assumptions you make when creating the EER model (if

necessary).

(b) Validate the conceptual data model.

(c) Map your high-level local conceptual data models to local logical data models.

Identify primary, alternate, and foreign keys.

PART IV

Branch View

1..1

Registers

1..1

Telephone

Director

Directs

PART IV

Tables for local logical data model for the Branch view

Actor (actorNo, actorName)

Primary Key actorNo

Branch (branchNo, street, city, state, zipCode, mgrStaffNo)

Primary Key branchNo

Registration (branchNo, memberNo, staffNo,

dateJoined)

RentalAgreement (rentalNo, dateOut, dateReturn, memberNo,

videoNo)

Role (catalogNo, actorNo, character)

Staff (staffNo, name, position, salary, branchNo, supervisorStaffNo)

Telephone (telNo, branchNo)

deoNo, available, catalogNo, branchNo)

Video (catalogNo, title, category, dailyRental, price, directorNo)

PART IV

Business View

Supplier

supplierNo

PART IV

Tables for local logical data model for Business view

Branch (branchNo, address, telNo, mgrStaffNo)

Primary Key branchNo

Alternate Key telNo

Foreign Key mgrStaffNo references Staff(staffNo)

Member (memberNo, name, address)

Primary Key memberNo

Registration (branchNo, memberNo, dateJoined)

RentalAgreement (rentalNo, dateOut, dateReturn,

Video (catalogNo, title, category, dailyRental, price,

VideoForRent (videoNo, available, catalogNo, branchNo)

PART IV

(d) Merge the two logical data models together to produce a global logical data model.

Global logical data model

Supplier

supplierNo

1..1

1..*

Directs

1..*

1..* 1..*

Is

0..*

Has

Manages

0..1

1..*

1..1

1..3

PART IV

Table structures for global logical data model

Actor (actorNo, actorName)

Primary Key actorNo

Branch (branchNo, street, city, state, zipCode, mgrStaffNo)

Primary Key branchNo

Role (catalogNo, actorNo, character)

Primary Key catalogNo, actorNo

Foreign Key catalogNo references Video(catalogNo)

Foreign Key actorNo references Actor(actorNo)

Staff (staffNo, name, posiiton, salary, branchNo, supervisorStaffNo)

Primary Key staffNo

Foreign Key branchNo references Branch(branchNo)

Foreign Key supervisorStaffNo references Staff(staffNo)

Supplier (supplierNo, name, address, telNo, status)

Telephone (telNo, branchNo)

PART IV

18.1 Which of the three basic file organizations (heap, ordered, hash) would you choose for a file

where the most frequent operations were as follows:

(a) Inserts and scans where the order of records does not matter.

(b) Record searches based on a range of field values.

(c) Record searches based on a particular field value.

18.2 Discuss the difference between each of the following types of indexes:

(a) Dense versus sparse indexes.

(b) Primary versus secondary indexes.

(c) Clustered versus unclustered indexes.

(a) A dense index has at least one data entry for every search key value that appears in a

PART IV

Database Systems Coursework 1 (Case Study 6 Fastcabs Cab Company)

DATABASE SYSTEMS

COURSEWORK

GROUP WORK: 2 3 Students

CONTRIBUTION: 50% of Final Mark

SUBMISSION DATE: Week 11

DEMONSTRATION DATE: Lab Time Week 11

Introduction to Coursework

You and your group members are part of a consultancy company that specialises in the provision of

database applications. The Director of FastCabs has recently approached your company to undertake a

project to design and partially implement a database management system for the company.

Notes

1. You are in the initial stages of user requirements collection and analysis and are required to read

Coursework Requirements

Part 1 Design the Database

1. Create an Entity Relationship (ER) model of the data requirements for the FastCabs case study

using the UML notation. Note: if necessary, use the additional concepts of the Enhanced Entity

Relationship (EER) model. State any assumptions necessary to support your design.

2. Derive relational schema from your ER model that represents the entities and relationships.

Identify primary, alternate and foreign keys. Note: use the following notation to describe your

relational schema, as shown in the example of a Staff relation given below.

Staff (staffNo, fName, lName, address, NIN, sex, DOB, deptNo)

PART IV

3. Use the technique of normalization to validate the structure of your relational schema.

Demonstrate that each of your relations is in third normal form (3NF) by displaying the functional

dependencies between attributes in each relation. Note, if any of your relations are not in 3NF, this

may indicate that your ER model is structurally incorrect or that you have introduced errors in the

process of deriving relations from your model.

4. To further demonstrate your knowledge of normalization, assume that a proposed (badly structured)

relation for the FastCabs database has the following structure.

jobID pickupDateTime

driverID

dFName dLName clientID cFName cLName cAddress

1001 25/07/00.10.00 I456 Jane Watt C034 Anne Way 111 Storrie Road, Paisley

(Submit hardcopy)

Part 2 Implement the Database

1. Create the tables for the FastCabs database. Where appropriate set field and table properties,

including any required indexes. Ensure that referential integrity is established between related

tables.

Part 3 Query the Database

Before starting this section, please ensure that your tables contain sufficient data to enable you to test

the query transactions described in the FastCabs case study.

1. Create and save the query transactions.

PART IV

3. Provide 10 additional examples of queries, which retrieve useful data from the FastCabs

database. State the purpose of each query and attempt to use each example to demonstrate the

breadth of your knowledge of Access QBE/SQL.

Part 4 Implement Database Application

Implement a prototype database application for the FastCabs. The purpose of this prototype is to allow

the Director to provide feedback on your proposed design.

Part 5 Document Database Application

1. Create a user manual that describes your prototype for the FastCabs database application.

2. The user manual should introduce the database application to the users, describe the functionality

of the database application and clearly demonstrate how to use the application.

Part 6 Demonstrate Database Application

You and the members of your group are required to demonstrate your database application to your Lab

Tutor during your usual lab time in Week 11.

Part 7 Individual Critical Evaluation

Each student should submit his or her own critical assessment of the coursework. The evaluation

should include a discussion on how the coursework has reinforced (or otherwise) his or her

appreciation of the techniques and processes employed in undertaking a database project. In addition

the evaluation may include a wider discussion on topics such as:

How the Database Systems module relates to the other modules on your course.

Marking Scheme

The assessment of this coursework will be carried out on the following components of the work. Please

note that each student should submit his or her own critical evaluation of the coursework and will

PART IV

receive an individual mark for this component (out of 10%). This individual student mark will be

combined with the mark for the groupwork component (out of 90%) for the coursework.

Part 1 Design the Database (30)

The FastCabs Case Study

A private taxi company called FastCabs was established in Glasgow in 1992. Since then, the company

has grown steadily and now has offices in most of the main cities of Scotland. However, the company

is now so large that more and more administrative staff are being employed to cope with the ever–

increasing amount of paperwork. Furthermore, the communication and sharing of information within

the company is poor. The Director of the company, Paddy MacKay feels that too many mistakes are

being made and that the success of his company will be short-lived if he does not do something to

remedy the situation. He knows that a database could help in part to solve the problem and has

approached you and your team to help in creating a database application to support the running of

FastCabs.

The Director has provided the following brief description of how FastCabs operates.

There are two kinds of clients, namely private and business. The business provided by private clients is

PART IV

The Director has provided some examples of typical queries that the database application for FastCabs

must support.

a) The names and phone numbers of the Managers at each office.

b) The names of all female drivers based in the Glasgow office.