DBMS

Page 1 of 2

Course Project

Introduction

In the course project, you will select one of the case studies from Appendix B of your textbook.

You'll design and implement a relational database for this case study. You'll supply a set of

queries that extract information from your database.

The course project is a group project. Your instructor will divide you in to groups of two or

three students per group. Each group will select one of the case studies to implement. Each

group will submit a single project document.

While you may work together with your group members, cooperation between groups is strictly

forbidden. You may not discuss any aspect of your course project with a student outside of your

group. Plagiarism between groups will result in a grade of zero for the project.

The Case Studies

The three case studies are

B1. A case study describing the data and operational needs of the the University

Accommodation Office.

B2. A case study describing the data and operational needs of the EasyDrive School of

Motoring.

B3. A case study describing the data and operational needs of the Wellmeadows Hospital.

Select your case study and report your selection through the assignment link provided on

Canvas.

Project Outline

Your project has five major steps.

1 Data modeling design

In this step, you'll model the data requirements of your case study system. You deliverable for

this step will be the entity types and relationship types that describe your case study system. You

must include an ER diagram drawn using the Dia drawing application as part of your deliverable

for this step.

2 Relational Database Schema Development

In this step, you'll produce a set of relational database table schema from your ER diagram.

Your schema must identify the tables required with the table schema: the table attributes along

with their domains, primary key constraints, alternate key constraints, foreign key constraints,

Page 2 of 2

and any general column or table constraints. Be sure also to specify whether attributes are

nullable.

In this step, you will also ensure that your tables are in 3NF.

Your deliverable for this step is an English language description of the database schema not a

diagram. You should include the functional dependencies that you identified for your tables as

evidence that your tables in 3NF. You are not to implement the tables using SQL for this

deliverable.

3 Relational Database Implementation

In this step, you will implement the table schema developed in step 2, using the Postgres SQL

DDL language. Your deliverable will be a Postgres SQL DDL script which when run in

Postgres creates the tables for your case study.

4 Table Population

In this step, you'll create a data set for your database. Your deliverable will be a Postgres SQL

DML script which when run, inserts your data set into the database tables and also a text

document with the data presented in a tabular format. You must provide enough data to

adequately demonstrate the use of your database in part 5.

5 Database Operational testing

In this final step, you'll create a Postgres SQL DML script which performs a set of queries on

your populated database. Each case study has a set of sample queries. Choose any ten of these

queries and implement them. Your deliverable will be a Postgres SQL DML script, which when

run, performs the queries on your database as well as a text document with the query results

presented in a tabular form. Note, you can capture the query results from the result window of

pgAdminIII.

Project Submission

Your submission for this project will be a Zip file archive containing the deliverables from the

five steps discussed above. I should be able to create, populate, and test your database using the

scripts provided in your Zip file. Submit your Zip file through Canvas by the due date.