Reference no: EM133133879

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.

Instructions

This assessment will be completed in groups of 3 students (or as determined by the unit coordinator). Groups will be formed within the first 2 weeks of class. If, in the view of the coordinator, groups are not forming within a reasonable time period, the coordinator will randomly allocate students to groups. Students will be expected to work within these groups for the remainder of the semester on the group case study. Each group will choose a particular type of Information System, research the storage requirements then design and build a possible database for that system. For instance, a group may choose a POS system for a supermarket, a small online savings bank, a stock management system for a financial firm etc... Each group should choose a different system. To ensure this uniqueness, each group must decide on a system and email their idea to their tutor within the first 3 weeks. Your tutor will respond with an approval or with a message that you will either need to choose a different system or your tutor may decide alter your idea slightly for you.
Once it has been approved you should begin by working towards the first deliverable.

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.

Here is the full list of groups for Sydney. Please make contact with your group members and decide on a topic for the case study. Please email me your topic when you have decided. Note that time is short - the first group assessment task is due on May 1 (in 9 days). No group can do the same topic, so the earlier you do this the more chance you have of getting your choice. Please do not pick university / school as we have covered that in class. Also do not pick an IT company as one group is already doing that. Some possible ideas (also feel free to pick your own topic):
Bank
Stockbroker
Supermarket
Railway
Airline
Covid testing laboratory
Hospital
Online retailer
Television station
Uber
Deliveroo
Wheat export company
Accounting firm
Toll road operator
Pharmaceutical research laboratory

Attachment:- Database Systems.rar