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.
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:
- Staff teaches one or more instrument courses
- Course is taught by one and only one teaching staff.
- A teaching staff may use more than one teaching instruments.
- Students can be issued with one and only one invoice.
- 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:
- A student can take one or more courses.
- Course is taken by more than one students.
- Payments are made by students at the end of the course.
- Invoices are issued at the end of the month.
- 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.
- Select data source and convert it to the un-normalized form entities.
- Un-normalized data is then transformed to the first normal form by law of eliminating repeated attributes to a new table.
- 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.
- 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:
- StaffID> FirstName, Last_Name, DOB, Phone_Number, Address, Degree
- CourseID>Name, Cost, CourseDate
- InstrumentID> Name, ManufacturerName, Quantity
- InvoiceID> Description, AmountDue, InvoiceDate
- PaymentID> Description, PaymentDate, Amount, Paid
- StudentID> First_Name, Last_Name, DOB, Phone_Number, Address
- Table Creation Queries
- 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)
);
- 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)
);
- 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)
);
- 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)
);
- 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)
);
- 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
- 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 )
- 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)
- 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)
- 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.
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.