Reference no: EM132910015

HI5033 Database Systems - Holmes Institute

Objective(s)

This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to reinforce the lecture material and give students practice at applying Database design techniques, as well as providing a relational schema using DDL statements and relational DML statements to demonstrate sophisticated access of the Database to be built.

Database ER Model

The ER Model deliverable is due in week 6 and represents the database structure you intend to build for the remainder of the group case study.

The ER model is to use the crows foot notation, and must represent the database you will build. It needs to represent a realistic depiction of the data requirements for the system you have chosen. I would expect there to be at least 5- 7 tables in this ER model and perhaps more depending on the system you are building it for. The feedback you receive may instruct you to add more tables if it is not detailed enough. This will need to be submitted through blackboard.

Database Schema DDL statements

For this assessment you are to create the Schema DDL statement to create your database. These should be placed in a script, and it is to be demonstrated in class to your tutor. You will need to run the script to show the creation of the database to your tutor. You will also need to submit the script through blackboard. The tables created in the script should be normalized to (and including) third normal form. Your tables should have all appropriate Primary and Foreign keys detailed as well as any appropriate constraints.

Sophisticated DML statements

In this component you will need to provide a script with appropriate SQL insert statements to insert the initial data into all your tables. This will be designed to be run after the database creation to insert the data so you can demonstrate the following component:

You are to construct a minimum of 5 SQL statements demonstrating a competency in SQL and sophisticated access to the data within your tables. At least 3 of your SQL statements must join multiple tables (Inner Join only is OK). You should also demonstrate a script to update and delete a record as well, taking care to maintain database integrity.

This will need to be demonstrated in class to your tutor, additionally, all the scripts need to be submitted to blackboard.

The amount of test data loaded into your system needs to be enough to demonstrate a good use of your SQL statements.

Attachment:- Database Systems-Case Study.rar