Business Rules
A business rule denote organizations’ laws that are applicable to information kept in business’ database. The business rules reflect how the business organization company identifies its use of informational data (Coronel & Morris, 2018). The laws are applied to hinder operations disruption in an organization. They are deployed daily to define constraints, relationships, attributes and entities of a database (Herbst, 2012). Business rules for our given case study Australian Institute of Music database are as follows:
- A student is provided with only one invoice.
- A number of invoices are prepared for different students.
- One or more instrument course is taught by a teacher.
- A course runs or 30 minutes minimum and one hour maximum.
- A course is scheduled to take one hour, however, for exemplary reasons, a course may take thirty minutes.
- Only one teaching staff teaches a course.
- More than one teaching instruments can be used by a teaching staff.
Assumptions made
Assumptions denotes to non-proven truth and is accepted by society to be true without questioning. In database implementation, assumptions are deployed to structure a database project by introducing possibilities (Bryla & Loney, 2013). For our Australian Institute of Music database system, assumptions made are:
- A student may decide take one and only one course
- It is assumed that a course has to be taken by a number of students.
- Students make course payments at end of course teaching.
- Invoices are distributed to students at end month.
- Invoices issued inform students of the fee balance.
Normalization
Normalization refers to the process of database arrangement to avoid deletion, insertion, redundancy and updation anomalies. Our case study Australian college database is normalized as below. (Fong, 2015).
Normalisation process takes the steps below:
- Choose source of data and alter the tables to the desired normal form.
- The table entities that are in non-normal form manipulated to 1NF by elimination of repeated attributes.
- The 2NF is then transformed to second normal form by removal of every non-zero column.
- The 2NF is afterwards elevated to 3NF removing any transitive key column (Coxall & Caswell, 2013).
Australian Institute of Music database system normalization
First Normal Form
Observing our Australian college database ERD, no entity which has repeated fields. It therefore interprets that our database table passes first normal form test.
Second Normal Form
By keen look of our Australian Institute of Music entity relationship model, it is in first normal form as justified above and there is no non-key depended field on part of the table. This implies that our Entity Relation Diagram tables are in second normal form.
Third 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.
Functional Dependencies
A functional dependency refers to the relationship that exists when only single attribute is used to define other attributes (Alagic, 2012 ). For our case study given Australian Institute of Music database system, the following are possible functional dependencies:
- CourseID>Name, Cost, CourseDate
- StudentID> First_Name, Last_Name, DOB, Phone_Number, Address
- InstrumentID> Name, ManufacturerName, Quantity
- PaymentID> Description, PaymentDate, Amount, Paid
- InvoiceID> Description, AmountDue, InvoiceDate
- StaffID> FirstName, Last_Name, DOB, Phone_Number, Address, Degree
Table Creation Queries
Course Table
This SQL creates a table called Course, the table has PK (CourseID) with constraint of being a text with a maximum of ten characters, and the column must not be blank. Other fields created by the query cost of taking the course, are name of the course, when is course taken, and time of the day when course is planned to take place. It is good also to mention that foreign keys fields are created, that is StudentID and StaffID
CREATE TABLE CourseTable (
CourseID char (10) NOT NULL UNIQUE,
Name char (100),
Cost Currency,
DateCourseTaken date,
TimeOfCourse time,
StudentID char (10),
StaffID char (10),
PRIMARY KEY (StudentID),
CONSTRAINT StudentID FOREIGN KEY (StudentID)
REFERENCES StudentTable (StudentID),
CONSTRAINT StaffID FOREIGN KEY (StaffID)
REFERENCES TeachingStaffTable (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, quantity of the instrument available.
Assumptions made
CREATE TABLE InstrumentTable (
InstrumentID char (10) NOT NULL UNIQUE,
Name char (200) NOT NULL ,
NameOfManufacturer text (200),
Quantity char (6),
PRIMARY KEY (InstrumentID)
Invoice Table
This SQL query creates a table called Invoice, the entity has PK (InvoiceID) with constraint of being a character with a maximum of ten characters, and the field must not be blank. Other fields geneted by the query are AmountDue which the amount which not yet paid, DateOfInvoice which is the date when the invoice was created and Description of the invoice (the field must have a maximum of 200 characters).
CREATE TABLE InvoiceTable (
InvoiceID char (10) NOT NULL UNIQUE,
Description text (100) NOT NULL ,
AmountDue Currency,
DateOfInvoice date,
PRIMARY KEY (InvoiceID)
Payment Table
This SQL query creates a table named PaymentTable, the table has PK (PaymentID) with constraint of being a characters with a maximum of 10 characters, and the field must not be blank. Other fields generated by the query are DateOfPayment which is the day the payment is made, Descrition which gives elaboration of what the payment is for,amountPaid which is how much money is paid, and paid field which states whether payment is made or not. This field is either YES or NO.
CREATE TABLE PaymentTable (
PaymentID char (10) NOT NULL UNIQUE,
Description char (200) NOT NULL,
DateOfPayment date,
AmountPaid Currency,
Paid char (3),
PRIMARY KEY (PaymentID)
Student table
This SQL query creates a table named StudentTable, the table has PK (StudentID) with constraint of being a text with a maximum of ten characters, and the field must not be blank.
CREATE TABLE StudentTable (
StudentID char (10) NOT NULL UNIQUE,
First_Name char (100),
Last_Name char (100),
DateOfBirth date,
Phone_Number char (100),
Address char (100),
InvoiceID char (10),
PaymentID char (10),
PRIMARY KEY (StudentID),
CONSTRAINT InvoiceID FOREIGN KEY (InvoiceID)
REFERENCES InvoiceTable (InvoiceID),
CONSTRAINT PaymentID FOREIGN KEY (PaymentID)
REFERENCES PaymentTable (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 TeachingStaffTable (
StaffID char (10) NOT NULL UNIQUE,
First_Name char (200),
Last_Name char (200),
DateOfBirth date,
Phone_Number char (200),
Address char (200),
Degree char (10),
InstrumentID char (10),
PRIMARY KEY (StaffID),
CONSTRAINT InstrumentID FOREIGN KEY (InstrumentID)
REFERENCES InstrumentTable (InstrumentID)
- Table Population
These queries populate 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
Normalization
INSERT INTO InvoiceTable
VALUES (‘I30’, ‘Payment for Guitar Lessons’,’47’, ‘9/9/2018’);
INSERT INTO InvoiceTable
VALUES (‘I33’, ‘Payment for Piano Lessons’,’72’, ’10/9/2018′);
INSERT INTO InvoiceTable
VALUES (‘I33’, ‘Payment for Singing Lessons’,’72’, ’10/9/2018′);
INSERT INTO InvoiceTable
VALUES (‘I33’, ‘Payment for Violin Lessons’,’72’, ’10/9/2018′);
INSERT INTO InvoiceTable
VALUES (‘I34’, ‘Payment for Piano Lessons’, ’75’, ’10/10/2018′);
INSERT INTO InvoiceTable
VALUES (‘I35’, ‘Payment for Guitar Lessons’, ’55’, ’10/10/2018′);
INSERT INTO CourseTable
INSERT INTO InvoiceTable
VALUES (‘I36’, ‘Payment for Singing Lessons’, ’50’, ’11/10/2018′);
INSERT INTO InvoiceTable
VALUES (‘I37’, ‘Payment for Violin Lessons’, ’68’, ’11/10/2018′);
INSERT INTO InvoiceTable
VALUES (‘I38’, ‘Payment for Guitar Lessons’, ’69’, ‘9/10/2018’);
INSERT INTO InvoiceTable
VALUES (‘I39’, ‘Payment for Violin Lessons’, ’70’, ‘9/11/2018’);
INSERT INTO PaymentTable
VALUES (‘P033’, ‘Payment for Guitar Lessons’, ‘9/10/2018′, ’20’, ‘Yes’);
INSERT INTO PaymentTable
VALUES (‘P034’, ‘Payment for Vialon Lessons’, ’10/10/2018′, ’20’, ‘NO’);
INSERT INTO PaymentTable
VALUES (‘P035’, ‘Payment for Singing Lessons’, ’10/10/2018′, ’50’, ‘NO’);
INSERT INTO PaymentTable
VALUES (‘P036’, ‘Payment for Guitar Lessons’, ‘1/10/2018′, ’50’, ‘YES’);
VALUES (‘C030′,’Singing’,’100′,’21/09/2018′,null,’S033′,’T033′);
INSERT INTO Course
INSERT INTO CourseTable
VALUES (‘C031′,’Piano’,’75’,’21/09/2018′,null,’S032′,’T032′);
INSERT INTO CourseTable
VALUES (‘C035’, ‘Vialon With Singing’, ‘264’, ‘9/27/2018’, null, ‘S034’, ‘T034’);
INSERT INTO CourseTable
VALUES (‘C036’, ‘Vialon With Guitar’, ‘264’, ‘9/27/2018’, null, ‘S036’, ‘T036’);
INSERT INTO CourseTable
VALUES (‘C037’, ‘Guitar with Sing’, ‘264’, ‘9/27/2018’, null, ‘S037’, ‘T037’);
INSERT INTO CourseTable
VALUES (‘C038’, ‘Guitar with Piano’, ‘264’, ‘9/27/2018’, null, ‘S038’, ‘T038’);
Instrument Table
INSERT INTO InstrumentTable
VALUES (‘IM30’, ‘Vialon’,’Yamaha’, ’15’);
INSERT INTO InstrumentTable
VALUES (‘IM31’, ‘Singing’,’Yamaha’, ’10’);
INSERT INTO InstrumentTable
VALUES (‘IM32’, ‘Piano’,’Yamaha’, ’60’);
INSERT INTO InstrumentTable
VALUES (‘IM35’, ‘Vialon Type A’, ‘Yamaha’, ‘5’);
INSERT INTO InstrumentTable
VALUES (‘IM37’, ‘Guitar Type A’, ‘Yamaha’, ‘6’);
INSERT INTO InstrumentTable
VALUES (‘IM38’, ‘Singin Type A’, ‘Yamaha’, ‘3’);
INSERT INTO InstrumentTable
VALUES (‘IM39’, ‘Violin Type A’, ‘Yamaha’, ‘1’);
INSERT INTO InstrumentTable
VALUES (‘IM40’, ‘Guitar Type B’, ‘Yamaha’, ‘4’);
INSERT INTO PaymentTable
VALUES (‘P030’, ‘Payment for Singing Lessons’,’8/9/2018′, ’20’, ‘YES’);
INSERT INTO PaymentTable
VALUES (‘P031’, ‘Payment for Piano Lessons’,’9/9/2018′, ’20’, ‘NO’);
INSERT INTO PaymentTable
VALUES (‘P032’, ‘Payment for Violin 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
INSERT INTO StudentTable
VALUES (‘S030’, ‘Pitchaiah’, ‘Joseph’, ‘6/6/2006′,’+4346172179’, ‘P.O. BOX 432 Melbourne’,’I30′,’P030′);
INSERT INTO StudentTable
VALUES (‘S031’, ‘Arun’, ‘Kumar’, ‘6/6/1999′,’+4346172679’, ‘P.O. BOX 432 Melbourne’,’I31′,’P031′);
INSERT INTO StudentTable
VALUES (‘S032’, ‘Pter’, ‘Santosha’, ‘6/6/2005′,’+4346172679’, ‘P.O. BOX 432 Brisbane’,’I32′,’P032′);
INSERT INTO StudentTable
VALUES (‘S033’, ‘Moses’, ‘Santosha’, ‘6/6/1995′,’+4346542679’, ‘P.O. BOX 432 Brisbane’,’I30′,’P030′);
INSERT INTO StudentTable
VALUES (‘S034’, ‘Mau’, ‘Santosha’, ’12/7/1995′, ‘+4346565679’, ‘P.O. BOX 432 Marie’, ‘I34’, ‘P034’);
INSERT INTO StudentTable
VALUES (‘S041’, ‘Mau’, Sim, ’12/7/1995′, ‘+4346565679’, ‘P.O. BOX 432 Marie’, ‘I34’, ‘P034’);
INSERT INTO StudentTable
VALUES (‘S041’, ‘Mulit’, ‘Sosha’, ’12/7/1985′, ‘+43426679’, ‘P.O. BOX 432 NEW YORK’, ‘I34’, ‘P034’);
INSERT INTO StudentTable
VALUES (‘S042’, ‘MaRu’, ‘Asha’, ’12/7/2000′, ‘+4346565679’, ‘P.O. BOX 432 Marie’, ‘I34’, ‘P034’);
INSERT INTO StudentTable
VALUES (‘S042’, ‘MEau’, ‘Antosha’, ’12/7/1995′, ‘+4346565679’, ‘P.O. BOX 832 Sau’, ‘I34’, ‘P034’);
INSERT INTO StudentTable
VALUES (‘S035’, ‘Fred’, ‘Ans’, ’12/7/2005′, ‘+4346565679’, ‘P.O. BOX 432 Marie’, ‘I35’, ‘P035’);
INSERT INTO StudentTable
VALUES (‘S036’, ‘Excel’, ‘Pans’, ’12/8/2002′, ‘+4346565679’, ‘P.O. BOX 432 Brisna’, ‘I36’, ‘P036’);
INSERT INTO StudentTable
VALUES (‘S037’, ‘Excel’, ‘Mike’, ’12/8/2001′, ‘+4346565679’, ‘P.O. BOX 432 Brisna’, ‘I36’, ‘P036’);
Functional Dependencies
INSERT INTO StudentTable
VALUES (‘S038’, ‘Simson’, ‘Mike’, ’12/9/2001′, ‘+4346565679’, ‘P.O. BOX 432 Brisna’, ‘I35’, ‘P035’);
INSERT INTO StudentTable
VALUES (‘S039’, ‘Diana’, ‘Melly’, ’12/10/2001′, ‘+4346565679’, ‘P.O. BOX 432 Melbourne’, ‘I39’, ‘P030’);
INSERT INTO StudentTable
VALUES (‘S040’, ‘Peterson’, ‘Melly’, ’12/10/1996′, ‘+4346565679’, ‘P.O. BOX 432 Melbourne’, ‘I39’, ‘P030’);
INSERT INTO TeachingStaffTable
VALUES (‘T033’, ‘Moses’, ‘ARUN’, ‘6/6/1985′,’+4345642679’, ‘P.O. BOX 432 Brisbane’,’Bachelor Of Science(Instrumentation)’, ‘IM30’);
INSERT INTO TeachingStaffTable
VALUES (‘T032’, ‘Arry’, ‘ARUN’, ‘6/6/1988′,’+4345242679’, ‘P.O. BOX 432 Brisbane’,’Bachelor Of Science(Instrumentation)’, ‘IM30’);
INSERT INTO TeachingStaffTable
VALUES (‘T0340’, ‘Grace’, ‘Kumar’, ‘6/6/1995′,’+4345642679’, ‘P.O. BOX 432 Brisbane’,’Bachelor Of Science(Instrumentation)’, ‘IM30’);
INSERT INTO TeachingStaffTable
VALUES (‘T0341’, ‘Mary’, ‘ARUN’, ‘6/6/1995′,’+4345642679’, ‘P.O. BOX 432 Brisbane’,’Bachelor Of Science(Instrumentation)’, ‘IM30’);
INSERT INTO TeachingStaffTable
VALUES (‘T031’, ‘Caro’, ‘Mek’, ‘6/6/2001′,’+4345242679’, ‘P.O. BOX 432 Brisbane’,’Bachelor Of Science(Instrumentation)’, ‘IM31’);
INSERT INTO TeachingStaffTable
VALUES (‘T030’, ‘Aggie’, ‘Pop’, ‘6/6/1994′,’+4345242679’, ‘P.O. BOX 432 Brisbane’,’Bachelor Of Science(Instrumentation)’, ‘IM31’);
INSERT INTO TeachingStaffTable
VALUES (‘T034’, ‘Marley’, ‘Pop’, ‘6/6/1994’, ‘+4345242679’, ‘P.O. BOX 532 Brisbane’, ‘Bachelor Of Science(Communication)’, ‘IM35’);
INSERT INTO TeachingStaffTable
VALUES (‘T035’, ‘Marley’, ‘Andrew’, ‘6/6/2005’, ‘+4345242679’, ‘P.O. BOX 532 Brisbane’, ‘Bachelor Of Science(Communication)’, ‘IM35’);
INSERT INTO TeachingStaffTable
VALUES (‘T042’, ‘Sismo’, ‘Maake’, ‘6/6/1995′,’+4345652679’, ‘P.O. BOX 432 Brisbane’,’Bachelor Of Science(Instrumentation)’, ‘IM30’);
INSERT INTO TeachingStaffTable
VALUES (‘T036’, ‘Mark’, ‘Meakey’, ‘6/6/2005’, ‘+4345242679’, ‘P.O. BOX 532 Brisbane’, ‘Bachelor Of Science(Communication)’, ‘IM36’);
INSERT INTO TeachingStaffTable
VALUES (‘T037’, ‘Pedro’, ‘Meakey’, ‘6/6/1990’, ‘+4345242679’, ‘P.O. BOX 532 Brisbane’, ‘Bachelor Of Science(Communication)’, ‘IM38’);
INSERT INTO TeachingStaffTable
VALUES (‘T038’, ‘Sander’, ‘Chilo’, ‘6/6/2000’, ‘+4345242679’, ‘P.O. BOX 532 Brisbane’, ‘Bachelor Of Science(Communication)’, ‘IM39’);
INSERT INTO TeachingStaffTable
VALUES (‘T039’, ‘San’, ‘Kilo’, ‘6/6/2001’, ‘+43452423579’, ‘P.O. BOX 532 Brisbane’, ‘Bachelor Of Science(Communication)’, ‘IM37’);
Information Requests
- List details of students who are under 18 years of age.
This query lists students whose age is below eighteen years
SELECT StudentTable.StudentID, StudentTable.First_Name, StudentTable.Last_Name, StudentTable.DateOfBirth, StudentTable.Phone_Number, StudentTable.Address, StudentTable.InvoiceID, StudentTable.PaymentID
FROM StudentTable
WHERE (((StudentTable.DateOfBirth)>#1/1/2000#));
StudentID |
First_Name |
Last_Name |
DateOfBirth |
Phone_Number |
Address |
InvoiceID |
PaymentID |
S030 |
Pitchaiah |
Joseph |
6/6/2006 |
+4346172179 |
P.O. BOX 432 Melbourne |
I30 |
P030 |
S032 |
Pter |
Santosha |
6/6/2005 |
+4346172679 |
P.O. BOX 432 Brisbane |
I32 |
P032 |
S035 |
Fred |
Ans |
12/7/2005 |
+4346565679 |
P.O. BOX 432 Marie |
I35 |
P035 |
S036 |
Excel |
Pans |
12/8/2002 |
+4346565679 |
P.O. BOX 432 Brisna |
I36 |
P036 |
S037 |
Excel |
Mike |
12/8/2001 |
+4346565679 |
P.O. BOX 432 Brisna |
I36 |
P036 |
S038 |
Simson |
Mike |
12/9/2001 |
+4346565679 |
P.O. BOX 432 Brisna |
I35 |
P035 |
S039 |
Diana |
Melly |
12/10/2001 |
+4346565679 |
P.O. BOX 432 Melbourne |
I39 |
P030 |
Show details of lessons coming up in the next 7 days (if any).
SELECT CourseTable.CourseID, CourseTable.Name, CourseTable.Cost, CourseTable.DateCourseTaken, CourseTable.TimeOfCourse, CourseTable.StudentID, CourseTable.StaffID, DateDiff(“d”,Date(),[DateCourseTaken]) AS 7_Days
FROM CourseTable
WHERE (((DateDiff(“d”,Date(),[DateCourseTaken]))<=7));
CourseID |
Name |
Cost |
DateCourseTaken |
TimeOfCourse |
StudentID |
StaffID |
7_Days |
C030 |
Singing |
100.00 |
9/21/2018 |
S033 |
T033 |
6 |
|
C031 |
Piano |
75.00 |
9/21/2018 |
S032 |
T032 |
6 |
- 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 StudentTable.StudentID, StudentTable.First_Name, StudentTable.Last_Name, Count(CourseTable.CourseID) AS TotalNumberOf
FROM StudentTable INNER JOIN CourseTable ON StudentTable.StudentID = CourseTable.StudentID
GROUP BY StudentTable.StudentID, StudentTable.First_Name, StudentTable.Last_Name;
SELECT Student.StudentID, Student.First_Name, Student.Last_Name, Course.CourseDate, Count([CourseDate]) AS TotalNumberOfLessons
FROM Student INNER JOIN Course ON Student.StudentID = Course.StudentID;
StudentID |
First_Name |
Last_Name |
TotalNumberOf |
S032 |
Pter |
Santosha |
1 |
S033 |
Moses |
Santosha |
1 |
S034 |
Mau |
Santosha |
1 |
S036 |
Excel |
Pans |
1 |
S037 |
Excel |
Mike |
1 |
S038 |
Simson |
Mike |
1 |
- Show details of any student who has had piano lesson.
StudentTable.StudentID, StudentTable.First_Name, StudentTable.Last_Name, StudentTable.DateOfBirth, StudentTable.Phone_Number, StudentTable.Address, StudentTable.InvoiceID, StudentTable.PaymentID, CourseTable.Name
FROM StudentTable INNER JOIN CourseTable ON StudentTable.StudentID = CourseTable.StudentID
WHERE (((CourseTable.Name) =”piano”));
StudentID |
First_Name |
Last_Name |
DateOfBirth |
Phone_Number |
Address |
InvoiceID |
PaymentID |
Name |
S032 |
Pter |
Santosha |
06-Jun-05 |
+4346172679 |
P.O. BOX 432 Brisbane |
I32 |
P032 |
Piano |
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. s.l.: 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. s.l.:AlphaPress.
Eckstein, J. & Schultz, . R., 2018. Introductory Relational Database Design for Business, with Microsoft Access. Hoboken: John Wiley & Sons.
Fong, J. S. P., 2015. Data Normalization. In: Information Systems Reengineering, Integration and Normalization. New York City: Springer, pp. 343-254.
Herbst, H., 2012. Rules in Database Management Systems. In: Business Rule-Oriented Conceptual Modeling. Heidelberg: Springer Science & Business Media, pp. 35-37.
Tavana & Madjid, 2017 . Enterprise Information Systems and the Digitalization of Business Functions. Pennsylvania: IGI Global.
Zygiaris, S., 2018. Database Management Systems: A Business-Oriented Approach. Bingley: Emerald Group Publishing.