SBM4102 @ Data and Information Management
|Due date:|| Week 13 |
|Learning outcomes:||[ULO1], [ULO2], [ULO3], [ULO4], [ULO5], [ULO6], [ULO7]|
You must work in group (max 3 people) on this assignment.
Read the case study below please
Australian Institute of Music is a music college across the Australia. It provides wide ranges of courses related to music. The top level management of the college has recently decided to computerise the existing paper-based data to a database in order to keep record of all staff, students, courses, etc. Such database will facilitate saving, restoring, and reporting whenever required and reduced paper consumption in the college. The following describes information that is required to save in the database.
Cost of each course is presented in the table below.
YOU NEED TO DO THE FOLLOWINGS
Part-1Entity-relationship data model
You are required to develop an ER model to represent the information requirements for given case study.
Your ER model must:
· show all necessary entities, attributes and relationships
· show unique identifiers
· show minimum and maximum cardinality.
· show associative entities, if applicable
Some business rules given in the case study may not be clear to you when you read the case study. You may simply make an assumption and then develop your ER model.
You are required to write brief definitions for the entities and attributes in your model. For each entity and attribute in your model, write a sentence to describe the meaning of that entity or attribute.
What you need to should submit
You should submit a Microsoft Word document containing:
Part-II Relational database implementation
You must implement the entity relationship model based on Assignment 1.
The recommended tool for this assignment is Microsoft Access.
In implementing the ERD provided, you must complete the following tasks:
·Map the ERD into a set of relations in at least Third Normal Form (3NF). You must ensure that your relations meet 3NF. Show your working.
·List all the functional dependencies present in the relation
· Create all the relations in a Microsoft Access database. Consider each attribute in every table and make appropriate choices regarding data types and sizes, indexes, required/not required, and validation rules. Your choices should be appropriate for each attribute and should support data integrity.
·Create relationships as appropriate.Enforce referential integrity for all appropriate relationships in the database.
·Populate the database with sample data. You must include sufficient sample data to test your queries and report(s). You should aim for at least 4-5 rows in every table.
You are required to implement integrity constraints within the database to enforce the following requirements:
·Lesson duration cannot be less than 30 minutes (half hour).
·Cost cannot be negative (but it can be 0).
·Paid must be either yes or no.
·Date paid cannot be in the future.
For invoice statements:
·Statement date must be supplied.
·Statement date cannot be in the future.
·Paid must be either yes or no, it cannot be null.
·Statement total must be supplied.
·Statement total must be greater than 0.
·The payment date must be on or later than the statement date.
Create queriesmanually using SQL view.
What you need to should submit
A Microsoft Access database
Tables with sample data
To get answer of this question.
Chat with us.