Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

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.  

Entity Relationship Diagram

According to Margaret & Eshwar entity relationship diagram refers to a data modelling process that pictorially elaborates a database system entities together with their relationships between the entities (MARGARET & ESHWAR, 2015)

Business rules refers to the set business policies that apply to information stored in business organization’s database. The business rules echo how a company identifies its deployment of data (Coronel & Morris, 2018). These rules are anticipated to hinder disruption in a business organization. They used each day to define attributes, entities, constraints and relationships of a database (Herbst, 2012). Business rules for Australian Institute of Music database system are as follows:

  1. Staff teaches one or more instrument courses
  2. Course is taught by one and only one teaching staff.
  3. A teaching staff may use more than one teaching instruments.
  4. Students can be issued with one and only one invoice.
  5. A course runs or 30 minutes minimum and one hour maximum.

An assumption denotes to something that is accepted by society to be true without proving or questioning. In database design and implementation, assumptions are to shape a database project by introducing possibilities (Bryla & Loney, 2013). In our Australian Institute of Music database system, the following assumptions are made:

  1. A student can take one or more courses.
  2. Course is taken by more than one students.
  3. Payments are made by students at the end of the course.
  4. Invoices are issued at the end of the month.
  5. Invoices used to represent student fee balance statements.

Normalization denotes to the technique of organizing data in a database to avoid data update, insertion, deletion and redundancy anomalies (Fong, 2015). Normalization process takes the following steps.

  1. Select data source and convert it to the un-normalized form entities.
  2. Un-normalized data is then transformed to the first normal form by law of eliminating repeated attributes to a new table.
  3. The first normal form is then transformed into second normal form by eliminating each non-key columns are only depended on a part of the table.
  4. The second normal form is then transformed into third normal form by rule of eliminating each new entity with a non-primary key(Coxall & Caswell, 2013).

Australian Institute of Music database system normalization

Looking at our ERD, no table that has repeated attributes. This therefore implies that our database entities passes 1NF test.

By keen observation of our Australian Institute of Music ERD model, it is in first normal form as justified above and no non-key is depended on part of the table. This interprets that our ERD tables are in second normal form.

As explained above, an entity is termed to be in third normal form if and only it passes second normal form test, which our ERD has passed, and there exists no new entity that has a primary key. Our database entities are in third normal form.

A functional dependency denotes to the relationship that is existent when only one attribute is used to determine other attributes (Alagic, 2012 ). For our Australian Institute of Music database system, the following are possible functional dependencies:

  1. StaffID> FirstName, Last_Name, DOB, Phone_Number, Address, Degree
  2. CourseID>Name, Cost, CourseDate
  3. InstrumentID> Name, ManufacturerName, Quantity
  4. InvoiceID> Description, AmountDue, InvoiceDate
  5. PaymentID> Description, PaymentDate, Amount, Paid
  6. StudentID> First_Name, Last_Name, DOB, Phone_Number, Address
  1. Table Creation Queries
  2. Course Table

This SQL query creates a table named Course, the table has Primary Key (CourseID) with constraint of being a text with a maximum of ten characters, and the field must not be blank. Other fields created by the query are name of the course, cost of taking the course, when is course taken, time of the day when course is scheduled to take place. It is worthy also to mention that foreign keys fields are created, that is StaffID and StudentID.

Business Rules

CREATE TABLE Course (

    CourseID text (10) NOT NULL UNIQUE,

    Name text (100),

    Cost Currency,

    CourseDate date,

    CourseTime time,

    StudentID text (10),

    StaffID text (10),

    PRIMARY KEY (StudentID),

    CONSTRAINT StudentID FOREIGN KEY (StudentID)

    REFERENCES Student (StudentID),

    CONSTRAINT StaffID FOREIGN KEY (StaffID)

    REFERENCES TeachingStaff (StaffID)

);

  1. Instrument table

This SQL query creates a table named Instrument, the table has Primary Key (InstrumentID) with constraint of being a text with a maximum of ten characters, and the field must not be blank. Other fields created by the query are name of the instrument, name of manufacturer of the instrument, quantiy of the instrument available.

CREATE TABLE Instrument (

    InstrumentID text (10) NOT NULL UNIQUE,

    Name text (100) NOT NULL ,

    ManufacturerName text (100),

    Quantity text (6),

    PRIMARY KEY (InstrumentID)

);

  • Invoice Table

This SQL query creates a table named Invoice, the table has Primary Key (InvoiceID) with constraint of being a text with a maximum of ten characters, and the field must not be blank. Other fields created by the query are Description of the invoice (the field must have a maximum of 100 text characters), AmountDue which the amount which not yet paid, InvoiceDate which is the date when the invoice was created.

CREATE TABLE Invoice (

    InvoiceID text (10) NOT NULL UNIQUE,

    Description text (100) NOT NULL ,

    AmountDue Currency,

    InvoiceDate date,

    PRIMARY KEY (InvoiceID)

);

  1. Payment Table

This SQL query creates a table named Payment, the table has Primary Key (PaymentID) with constraint of being a text with a maximum of ten characters, and the field must not be blank. Other fields created by the query are Descrition which describes what the payment is for, payment date which is the day the payment is made, amount which is how much money is paid, and paid field which which states whether payment is made or not. This field is either YES or NO.

CREATE TABLE Payment (

    PaymentID text (10) NOT NULL UNIQUE,

    Description text (200) NOT NULL,

    PaymentDate date,

    Amount Currency,

    Paid text (3),

    PRIMARY KEY (PaymentID)

);

  1. Student table

This SQL query creates a table named Student, the table has Primary Key (StudentID) with constraint of being a text with a maximum of ten characters, and the field must not be blank. Other fields created by the query are First_Name, Last_Name, DOB date, Phone_Number and Address of the student. It is worthy also to mention that foreign keys fields are created, that is    InvoiceID and PaymentID.

Assumptions

CREATE TABLE Student (

    StudentID text (10) NOT NULL UNIQUE,

    First_Name text (100),

    Last_Name text (100),

    DOB date,

    Phone_Number text (100),

    Address text (100),

    InvoiceID text (10),

    PaymentID text (10),

    PRIMARY KEY (StudentID),

    CONSTRAINT InvoiceID FOREIGN KEY (InvoiceID)

    REFERENCES Invoice (InvoiceID),

    CONSTRAINT PaymentID FOREIGN KEY (PaymentID)

    REFERENCES Payment (PaymentID)

);

  1. Teaching Staff table

This SQL query creates a table named TeachingStaff, the table has Primary Key (StaffID) with constraint of being a text with a maximum of ten characters, and the field must not be blank. Other fields created by the query are First_Name, Last_Name, DOB date Phone_Number, Address and Degree of the teaching staff. Foreign keys present are instrumentID.

CREATE TABLE TeachingStaff (

    StaffID text (10) NOT NULL UNIQUE,

    First_Name text (100),

    Last_Name text (100),

    DOB date,

    Phone_Number text (100),

    Address text (100),

    Degree text (10),

    InstrumentID text (10),

    PRIMARY KEY (StaffID),

    CONSTRAINT InstrumentID FOREIGN KEY (InstrumentID)

    REFERENCES Instrument (InstrumentID)

);

  1. Data insertion into tables

This queries insert data into created tables above. It should be noted that Ms. Access does not allow insertion of multiple rows and that’s why we are inserting row by row. Additionally more of the queries are implemented in the Ms. Access file attached.

Insert Into Database Queries

INSERT INTO Course

VALUES (' CO0031', ‘Piano’,'50', '21/09/2018', '0800','ST0033','TS0033');

INSERT INTO Course

VALUES (‘CO0030’, ‘Singing’,’ 100’, 9/29/2018, '0800',' ST0030',' TS0030');

INSERT INTO Course

VALUES (‘CO0032’, ‘Vialon’, ‘120’, 9/27/2018, '0800',' ST0032',' TS0032');

INSERT INTO Course

VALUES (‘CO0033’, ‘Guitar’, ‘50’, 9/16/2018, '0800',' ST0033',' TS0033');

Instrument Table

INSERT INTO Instrument VALUES

('INS0033', 'Microphones', ‘ Sennheiser’,‘10’)

INSERT INTO Instrument VALUES

(‘INS0030’, ‘PIANO’, ‘YAMAHA’,‘8’)

INSERT INTO Instrument VALUES

(‘INS0031’, ‘Bass Amplifiers’, ‘Fender Musical’,‘8’)

INSERT INTO Instrument VALUES

(‘’ INS0032, ‘Woodwinds’, ‘Steinway Musical Instruments’,‘5’)

INSERT INTO Payment

VALUES ('PA0033', 'Payment for Singing Lessons','9/9/2018', '20', 'NO');

INSERT INTO Student

VALUES ('ST0033', 'Mark','Mae', '09/05/1997', '+433662179','P.O. BOX 245 Brisbane','Inv0033','PA0033');

INSERT INTO Student

VALUES ('ST0030', ‘Henry’,’ Halif’, ‘6/6/2006’, ' +4346172179’, P.O. BOX 432 Melbourne','Inv0030','PA0030');

INSERT INTO Student

VALUES (‘ST0031’, ‘Caro’,’ Shamir’, ‘9/5/1999’, ' +439172179 ‘, ‘P.O. BOX 432 Brisbane','Inv0031','PA0031');

VALUES (‘ST0032’, ‘Aggy,’ Mae’, ‘9/5/2005’, ' +439162179 ‘, ‘P.O. BOX 432 Brisbane','Inv0032','PA0032');

INSERT INTO TeachingStaff

VALUES ('TS0033', 'Kin','Halif', '6/01/1980', '+4346172176','P.O. BOX 0895 Melbourne', 'Bachelor of Science (Instruments Option)','INS0033');

INSERT INTO TeachingStaff

VALUES (‘TS0030’, ‘Mikel’,’ Vir’, ‘2/21/1987’, ' +4376172176’, ‘P.O. BOX 643 Brisbane', 'Bachelor of Science (Instruments Option)','INS0030');

INSERT INTO TeachingStaff

VALUES (‘TS0031’, ‘Aryan’,’ Nue’, ‘2/1/1982’, ' +4345172176’, ‘P.O. BOX 724 Brisbane’, 'Bachelor of Science (Instruments Option)','INS0030');

INSERT INTO TeachingStaff

VALUES (‘TS0032’, ‘Maria’,’ Halif’ , ‘6/1/1990’, ‘+4347472176’ , ‘P.O. BOX 025 Melbourne’, 'Bachelor of Science (Instruments Option)','INS0031');

Information Requests

  1. List details of students who are under 18 years of age.  

This query lists students whose age is below eighteen years

SELECT Student.StudentID, Student.First_Name, Student.Last_Name, Student.DOB, Student.Phone_Number, Student.Address, Student.InvoiceID, Student.PaymentID

FROM Student

WHERE (((Year (Now ())-Year ([DOB])) <18));

(Allison & Berkowitz, 2010 )

  1. Show details of lessons coming up in the next 7 days (if any).

SELECT Course.CourseID, Course.Name, Course.Cost, Course.CourseDate, Course.StudentID, Course.StaffID, DateDiff("d",Date(),[CourseDate]) AS 7_Days

FROM Course

WHERE (((DateDiff("d",Date(),[CourseDate]))<=7));

(Demertzoglou, 2012)

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

SELECT Student.StudentID, Student.Last_Name, Student.First_Name, Count(Course.CourseID) AS CountOfCourseID

FROM Student INNER JOIN Course ON Student.StudentID = Course.StudentID

GROUP BY Student.StudentID, Student.Last_Name, Student.First_Name; (Eckstein & Schultz, 2018)

  1. Show details of any student who has had piano lesson.

SELECT Student.StudentID, Student.First_Name, Student.Last_Name, Student.DOB, Student.Phone_Number, Student.Address, Student.InvoiceID

FROM Student INNER JOIN Course ON Student.StudentID = Course.StudentID

WHERE (((Course.Name) ="Piano"));

(Zygiaris, 2018)

Relationships

References

Alagic, S. (2012 ). Relational Database Technology. Heidelberg: Springer Science & Business Media.

Allison, C. L., & Berkowitz, ‎. A. (2010 ). SQL for Microsoft Access. Burlington: Jones & Bartlett Learning.

Bryla, B., & Loney, K. (2013). Oracle Database 12c The Complete Reference. New York City: McGraw Hill Professional.

Coronel, C., & Morris, ‎. (2018). Entity Relationship Modelling and Normalization. In Database Systems: Design, Implementation, & Management. Boston: Cengage Learning.

Coxall, M., & Caswell, G. (2013). Oracle Quick Guides - Part 1 - Oracle Basics: Database & Tools. Malcolm Coxall - Cornelio Books.

Decker, H., Lhotská, ‎., & Link, ‎. (2013 ). Database and Expert Systems Applications. New York City: Springer.

Demertzoglou, P. (2012). Microsoft Access SQL Comprehensive: Version 2010. AlphaPress.

Eckstein, J., & Schultz, ‎. R. (2018). Introductory Relational Database Design for Business, with Microsoft Access. Hoboken: John Wiley & Sons.

Fong, J. S. (2015). Data Normalization. In Information Systems Reengineering, Integration and Normalization (pp. 343-254). New York City: Springer.

Herbst, H. (2012). Rules in Database Management Systems. In Business Rule-Oriented Conceptual Modeling (pp. 35-37). Heidelberg: Springer Science & Business Media.

MARGARET, B., & ESHWAR, B. (2015). ER Modelling. In DATABASE SYSTEMS WITH CASE STUDIES (pp. 48-56). Delhi: PHI Learning Pvt. Ltd.

Zygiaris, S. (2018). Database Management Systems: A Business-Oriented Approach. Bingley: Emerald Group Publishing.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2021). Essay: ER Diagram, Business Rules, Normalization, And SQL Queries For Australian Institute Of Music DB System.. Retrieved from https://myassignmenthelp.com/free-samples/sbm4102-data-and-information-management/database-system.html.

"Essay: ER Diagram, Business Rules, Normalization, And SQL Queries For Australian Institute Of Music DB System.." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/sbm4102-data-and-information-management/database-system.html.

My Assignment Help (2021) Essay: ER Diagram, Business Rules, Normalization, And SQL Queries For Australian Institute Of Music DB System. [Online]. Available from: https://myassignmenthelp.com/free-samples/sbm4102-data-and-information-management/database-system.html
[Accessed 29 March 2024].

My Assignment Help. 'Essay: ER Diagram, Business Rules, Normalization, And SQL Queries For Australian Institute Of Music DB System.' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/sbm4102-data-and-information-management/database-system.html> accessed 29 March 2024.

My Assignment Help. Essay: ER Diagram, Business Rules, Normalization, And SQL Queries For Australian Institute Of Music DB System. [Internet]. My Assignment Help. 2021 [cited 29 March 2024]. Available from: https://myassignmenthelp.com/free-samples/sbm4102-data-and-information-management/database-system.html.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
Whatsapp
callback
sales
sales chat
Whatsapp
callback
sales chat
close