Entity-Relationship (ER) Model And SQL Queries For Jim's Music Business Essay.

Construct an entity-relationship (ER) model for the database. Make sure you include in your model details of entities, relationships, attributes, keys and cardinality of the relationships.
a. List any assumptions made and ensure that you give adequate justification.
b. List any important business rules.

2. Show by providing SQL statements, that all of the reports listed in the "Operations" section above can be produced from your ER diagrams. 
a. NOTE: You do not need to actually create the database and run the queries, this is a thought exercise to show that the queries are possible based on your ER diagram.

3. Produce a business report (with ER diagram and SQL queries included within) according to the format outlined in " A    Guide to Writing Business Reports.pdf"

Proposed data model

The purpose of this document is to show an analysis done on the specified case study for Jim’s Music which is a small but rapidly growing business in Burwood. The document show the model entity relationship diagram derived from the case study and queries for the database implementation of the implemented database. For purposes of query demonstration a database has been implemented based on the relations gotten from the entity relationship diagram.

  Jims is a small but rapidly growing business operating in Burwood. Initially, the business had only one client which was the band that Jim belonged to where his only business was to book his band into venues for live performances. Later on Jim started to teach music. The business continued to grow exponentially over the years.

Currently, Jims is experiencing growth at a high rate thus the current method of recording information which is using a ledger book and a calendar are proofing to be inefficient and ineffective to the business. Thus there is need for an application which can be used to manage the activities of the business. The application will have to run on a very compact database which is highly normalized to ensure data integrity and data consistency. For this reason my company Deakin Innovative Solutions Pty Ltd where I work has tasked with the task of analyzing the business requirement from the case study to come up with the database.

The diagram below shows the entity relationship diagram derived from the business operations of Jim’s business. The diagram has been modelled using Crow’s Foot Notation to show relationships existing between the classes identified. During the modelling, some new tables have been introduced and have been given appropriate attributes.

The following were the business rules identified from the case study;

• A musician can also be a teacher where by a teacher can have more than one skill indifferent styles of music.
• A student I supposed to book for a class where by there are supposed to book by selecting the type of music they want to do and the teacher they want.
• A musician books for a venue.
• A venue can book musicians. A venue can book more than one musician for the same event on the same venue.
• The musicians booking is not the same as venue booking as they two entities can make bookings and the bookings are not the same thus the two entities are separate.

The following assumptions were made to back up the business rules in order for the entity relationship diagram to be modelled and implemented to form a database;

• For classes, the student is the one supposed to book thus the teacher should teach those students who have booked for a his/her class/
• A teacher can have more than one skill in more than one style of music.
• Bookings made by venues are different from booking made by musicians.

For the Entity relationship diagram to be implemented as a database normalization has to be done. Normalization was done up to 3NF which is enough for relations to be implemented as tables in a database. Normalization up to 3NF involves the following procedures;

• Elimination of repeating groups to translate relations into 1NF.
• Elimination of partial dependencies to translate relations gotten from 1NF into 2NF. For the relation to be in 2NF it means that the all the non-candidate keys of the relation are dependent on the candidate key which has to be defined (Rumbaugh, 1999).
• Translation of the relation gotten from 2NF into 3NF. This involves eliminating all the transitive dependencies present in the relation. Elimination of partial dependencies means that all the non key attributes are determined by the key attribute which is the key attribute (Rosenberg, 1999).

After translating all the relations to 3NF, the relations were implemented as tables and populated with sample data which was used to demonstrate query operations below.

• List of all new teachers who have joined Jim's music after the 1st of January 2015 ordered by the joined date.

SELECT musicians.*,teachers.date_joined FROM musicians INNER JOIN teachers ONmusicians.musicianID=teachers.teacherID WHERE teachers.date_joined>DATE('2015-01-01');

• List of all female students who are adults and their age sorted by the last name

For this query its assumed that adults are above the age of 18.

SELECT * FROM `students` WHERE age>=18 ORDER BY last_name DESC;

• List of all current lesson bookings sorted by the style of music and booking date (Most recent first).

SELECT DATE(class_booking.date), students.last_name,music.style,musicians.stage_name

FROM class_Booking

INNER JOIN students ON students.studentID=class_booking.studentID

INNER JOIN music ON music.musicID=class_booking.musicID

INNER JOIN teachers ON class_booking.teacherID=teachers.teacherID

INNER JOIN musicians ON teachers.teacherID=musicians.musicianID

ORDER BY class_booking.date ASC,music.style;

• Count of venues from each postcode in the database.

SELECT postal_code, COUNT(venueID) FROM venues GROUP BY postal_code; 

• A monthly report on students enrolled and the style of music and the teacher

SELECT students.last_name,music.style,musicians.stage_name FROM class_Booking INNER JOIN students ON students.studentID=class_booking.studentID INNER JOIN music ON music.musicID=class_booking.musicID INNER JOIN teachers ON class_booking.teacherID=teachers.teacherID INNER JOINmusicians ON teachers.teacherID=musicians.musicianID;

The above queries can be applied in the business because all the tables have been normalized to form tables that were formed from doing an analysis on the case study provided for Jim’s business. The first query can be useful to the business where by it can be used to show teachers show have joined the business during a given period. This query can also be expanded and modified to show how many teachers there are for a style of music. This information can be useful in determining how the classes will operate.

The second query can be used to list all the students who are adults. This query can be useful to the business as it can be modified to show any type of students if the business wants this information. The third query be used to show bookings that have taken place in the business recently. This information can be useful as the query can be modified to show the report of bookings from the recent up to the last specified date. The fourth query can be used to show how many venues are in each postcode. This information can be useful as it can help determine which area of the country most venues thus this can be used to make business decisions intelligently. The fifth query can be used to show which student is enrolled to which class and which teacher is teaching class.

The limitation of the proposed data model is that the data model has been designed according to the proposed case study of the business which is the current situation of the business. The model has not been designed possible growths of the business where more entities might be introduced and some relationships might have to be modified thus changing the structure of the database. The other drawback of the model is that when it is initially implemented as a physical database, the underlying hardware like a database server on which the database will run on can be very huge.

The major benefit of the model is that the model has been designed as a relational model which is easy to implement as a physical database (Johnson and Jones, 2008). Relational databases are very easy to implement and understand thus the business will not need an expert to manage but someone with knowledge of databases and database administration. If implemented the database will help the business to handle operations in an efficient and fast way compared to the old method of using ledger books and calendars. The data stored in the database is also secure than the data stored in physical books.

For Jim’s business, the move to embrace technology and transition from using ledger books is an important to the business because creating a database to be used by an application will help facilitate better data management and will make work easier for the employees of the business. If Jims is going to implement the database, I would recommend the business to consider deploying the database on the cloud. This will make it easy and save on money because they will not have to worry about hardware’s like servers and the maintenance that comes along with the whole infrastructure.

References

Johnson, E., & Jones, J. (2008). A Developer's Guide to Data Modeling for SQL Server. Addison-Wesley.

Rosenberg, D. (1999). Use case driven object modeling with UML.

Rumbaugh, J. (1999). The unified modeling language reference manual.