Reference no: EM133958672

Database Design

Objectives
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.

Assignment Case Study
This assessment will be completed in groups of 4 students from the same class. Groups will be formed within the first 2 weeks, and students will be expected to work within these groups for the remainder of the semester on the group case study.

Each group needs to 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. Get AI-free online assignment help from experienced academic experts.

Deliverable Description
The deliverable should consist of four parts, detailed as follows:

Requirement specification
Your group must provide an overview of the selected system and specify its requirements. These requirements should directly relate to the tasks outlined in the subsequent three parts.

Database ER Model
The ER Model should represent the database structure you plan to build for the group case study. It must be presented using Crow's Foot notation and accurately reflect the database to be built. The model should provide a realistic depiction of the data requirements for the chosen system and include at least 10 entity types.

Database Schema DDL statements
You are required to create Schema DDL statements to build your database. Your tables should include all appropriate primary and foreign keys, as well as any relevant constraints.

Sophisticated DML statements
You are required to construct SQL INSERT statements to populate all your tables with initial data, ensuring that each table contains at least 5 rows. You have the freedom to choose appropriate data for insertion. Additionally, you must create 5 SQL statements to demonstrate sophisticated data access. These statements must involve joins between multiple tables (Inner Joins only are acceptable). The test data should be sufficient to effectively demonstrate the use of your SQL statements. These statements should be included in a script and presented in class to your tutor.