Map the ERD into a set of relations
Course:- Database Management System
Length: word count:2500
Reference No.:- EM131984954

Assignment Help
Expertsmind Rated 4.9 / 5 based on 47215 reviews.
Review Site
Assignment Help >> Database Management System

Data and Information Management - Database Report

Read the case study below

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.











- It should be noted that some courses run for half an hour while others run for an hour. The minimum course time to teach is 30 minutes.

- The college is also needs to maintain information about teaching staff. For example, it is required to record which staff teach which instruments. For example, David teaches singing and piano. Information related to staff includes staff id, name and family, date of birth, phone number, address, and degree. Each staff has a unique ID to ensure easy identification.

- In addition, information related to students is required to maintain in this database. Students has name, postal address, date of birth, and contact phone number. Once a student is joined to the college, he or she can enrol in to interested instruments course. The date and time of the course(s) should be recorded, as well as the duration.

- Furthermore, it is necessary to record which staff member will be taking the course and instrument the course is defined for. Students pay for their courses once the course is finished. It is necessary to record the condition for the course payment and the date paid.

- Another type of information which is required to store is the available instruments in the college stock. This includes the instrument name, manufacture name, quantity or number of each instrument in the stock. The staff may use one or more instrument for teaching purpose.

- At the end of every month, the staff issues invoice statements. An invoice, which has a unique number, is issued for those students who have one or more unpaid courses in the previous month. An invoice, for example, contains date, student information, and total amount due.


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


Verified Expert

In this assignment we have discussed the given case study and designed database and Er Diagram for the given case study which shows all relationship between all tables as given in case study.we have discussed three major part for this process. So all pages show all details of case study.

Put your comment
View Conversion
  1. user image

    Criteria 4 (10%) Presentation - Discussing and justifying entities, attributes, unique identifiers, and relationships in ERD - Discussing steps to achieve 3NF (from non-normalized ERD to 3NF) - Discussing data integrity constraints and describing the logic of defined queries (SQL) - Overall quality of presentation

  2. user image

    Criteria 2 (20%) Relational database implementation - Showing the Third Normal Form (3NF) - List of functional dependencies - Showing data integrity constraint (for lesson and invoice statement entities) Criteria 3 (10%) Structure of report - Clarity of report, diagrams, names, and the content, - Grammar (spelling mostly used well, not distracting the reader) - Length: approximately 2500 words

  3. user image

    What you need to should submit A Microsoft Access database Tables with sample data 1. Relationships 2. Queries 3. Report. Marking Criteria: Assessment Criteria HD Excellent D Very Good C Good P Satisfactory F Poor Marks Obtained Criteria 1 (20%) Entity-relationship data model - Showing necessary entities, attributes and relationships - Showing unique identifiers and cardinalities - Definition of entities - Showing Entity-relationship model (ERD)

Ask Question & Get Answers from Experts
Browse some more (Database Management System) Materials
Briefly explain the difference between data, information, and knowledge. -  Briefly explain the difference between explicit and tacit knowledge. Give an example of each.
Create a database design specification (Enhanced Entity Relationship Diagram (EERD) and Relational Data Model (RDM)) from the given business description. The RDM must be in
You can create your database on Nova or any other Oracle system you wish, but you must use the Oracle RDBMS. Populate the VEHICLES and CUSTOMERS tables with at least ten rows
Create the corresponding relational data model by implementing the database for myFlicks.com within the course's mySQL site, linked on the course's homepage within Blackboar
The first part is to create a database and some tables which will be appropriate for a home-budgeting application. That portion of the assignment should be completed from
In turning moment diagram. why we start to draw curve from o degree - Discuss how the Oracle database in-memory option is used to improve the performance of analytic queries a
Determine if you should reject or accept the following dataset if the true value is 6.272 at both 95% and 99% confidence scenarios: [3.991, 1.207, 3.972, 4.038, 3.836, 3.492
Do not use the Main Switchboard page that is provided for you, but rather create a new Switchboard named with your first name and the word Switchboard. (For example,