Database Storage & Design Chapter 16 Homework List Name Sex And Salary For Each

PART IV

FastCabs Sample ER Model

Staff

staffNo

WorksAt

1..*

Manages

1..1

1..*

1..1

1..*

1.

.1

1..1

1..*

1..1

1..*

1..1

1..*

1..1

1..*

0..*

1..

1

PART IV

FastCabs Sample Relational Schema

Staff (staffNo, fName, lName, sAddress, jobDescription, salary, NIN, sex, DOB, officeNo)

Primary Key staffNo

PrivateClient (pClientNo, fName, lName, street, city, postcode, telNo)

Primary Key pClientNo

Contract (contractNo, bClientNo, startDate, finishDate, totalCharge, maxNoOfJobs, officeNo)

Job (jobID, vehRegNo, driverNo, pClientNo, contractNo, pickupDate, pickupTime,

pickupAddress, dropOffAddress, mileageUsed, Charge)

Primary Key jobID

PART IV

Database Systems Coursework 2 (Case Study 7 University Database)

DATABASE SYSTEMS

COURSEWORK

GROUP WORK: 2 3 Students

Introduction to Coursework

You have been approached by a University for the design and implementation of a relational database

Coursework Requirements

Each department runs a number of courses. The university provides a set of modules used in different

courses. Each course uses a number of modules but not every module is used. A course is assigned a

unique course code and a module is identified by a unique module code. A module can be used in one

course only, but can be studied by many students. In addition to the module code each module unique

title, start date, end date, texts (books), and assessment scheme (i.e. coursework and exam marks

percentages) are also stored.

Each department is managed by a member of academic staff. The database should record the date

he/she started managing the department. Each department has a name, phone number, fax number, and

location (e.g. E Block). Each department employs many members of academic staff.

Part 1 Design the Database

1. Create an Entity Relationship (ER) model of the data requirements for the University Database

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.

(Submit hardcopy)

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)

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 University Database database has the following structure.

matricNo name sex moduleTitle module startDate performance flatNo address

00/5021 Mcleod, A F BITS 27/09/01 Pass F001 6 lady Lane, Paisley

00/4647 Smith, J M Software Dev. 01/10/01 Pass F001 6 lady Lane, Paisley

Part 2 Implement the Database

1. Create the tables for the University Database database. Where appropriate set field and table

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 University Database case study.

1. Create and save the following query transactions:

(a) List details of all departments located in E Block.

(b) List title, start and end dates of all modules run in the PgDIT course.

(c) List name, address, and salary for each female member of academic staff who

manages a department.

PART IV

2. Create a customised form or a report for each saved query.

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

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

demonstrate the breadth of your knowledge of QBE/SQL.

Part 4 Implement Database Application

Implement a prototype database application for the University Database. 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 University Database database

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.

PART IV

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

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)

PART IV

Partial ER diagrams

Staff

Student

matricNo {PK}

Course

Module

Next-Of-Kin

Department

PART IV

‘Global’ EER diagram

3. Logical Design (map ER to Relational)

1. Map entities and their attributes

Department {deptName, phone, faxNo, location}

Runs

Manages

0..1

2. Final relational schema

Department(deptName, phone, faxNo, location, mgrStaffNo, mgrStartDate)

Primary key: deptName

Foreign key: mgrStaffNo references Staff(staffNo)

Staff(staffNo, fName, lName, address, phone, officeNo, sex, salary, post, computerId,

Module(mCode, title, startDate, endDate, coursework, exam, courseCode, cordStaffNo)

Primary key: mCode

Next-Of-Kin(matricNo, name, phone, relationship)

Primary key: matricNo, name

Texts(moduleCode, text)

PART IV

Qualifications(qualStaffNo, qualification)

4. Normalisation

Functional dependencies

fd1: (matricNo, moduleTitle) {name, sex, modSartDate, performance, flatNo, address}

Primary key: (matricNo, moduleTitle)

3NF fd4 violate 3NF definition. Decompose relation.

5. Query the Database

(a) SELECT *

FROM Department

WHERE location ;

(e) SELECT fName, lName, post, qualification

(f) SELECT matricNo, lName, sex

FROM Student, Undertake, Module

(i) SELECT sex, COUNT (sex)

PART IV

(j) SELECT lName, title, hours