Objectives
This assignment relates to the course learning outcomes numbers 1, 4 and 5 as shown in the bottom in this assignment.
You must work individually on this assignment.
Part-1 Entity-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:
• A copy of your entity-relationship model.
• A list of business rules that are apparent from the case study.
• A list of any assumptions that you made with justifications.
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.
Data integrity
You are required to implement integrity constraints within the database to enforce the following requirements:
For lessons:
• 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.
Information requests
Create queries manually using SQL view.
1. List details of students who are under 18 years of age.
2. Show details of lessons coming up in the next 7 days (if any).
3. Show a list of students and the total number of lessons they have had. Show the student ID, family name, given name, and the number of lessons.
4. Show details of any student who has had or will be having a piano lesson.
What you need to should submit
• A Microsoft Access database
Tables with sample data
1. Relationships
2. Queries
3. Report.

Case Study Description

Sydney Music Academy is   music academy in the Sydney Central.  Sydney gives Music lessons. The staffs at Sydney Music want have a computerized database to keep record of all their students, lessons, prices, etc. and to retrieve information as and when required.

It will be necessary to keep a list of all the instruments. Cost of lessons for example, singing lessons cost $65 per hour, piano lessons are $50 per hour and remaining instruments are $45 per hour.

It is also required to keep a list of the teaching staff.  For each staff member, a name and mobile phone number should be recorded.  Each staff member should be given a unique ID to ensure easy identification.  It is required to record which staff teach which instruments, Ex. John teaches piano and violin.

New student are assigned a unique student number.  Each student’s name and postal address are recorded, along with any contact phone numbers they have.

Once a student has joined, he or she can enrol in to interested instruments lessons. The date and time of the lesson must be recorded, as well as the duration.  Some lessons run for half an hour while others run for an hour.  Students need to book minimum lesson time is 30 minutes.  It is also necessary to record which staff member will be taking the lesson; it is also necessary to record which instrument the lesson is for.

Students are expected to pay for their lesson once the lesson has finished.  It is necessary to record   lesson paid condition and the date paid.

At the end of every month, the staff invoices statements.  An invoice statement is created for any student who has one or more unpaid lessons in that previous month.  Invoice statement, it is necessary to record the date of the invoice statement and the total amount due each invoice statement should have a unique number.  Every invoice statement, it is also necessary to record whether or not it has been paid and the date it was paid.