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;
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 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;
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.
SELECT musicians.*,teachers.date_joined FROM musicians INNER JOIN teachers ONmusicians.musicianID=teachers.teacherID WHERE teachers.date_joined>DATE(‘2015-01-01’);
For this query its assumed that adults are above the age of 18.
SELECT * FROM `students` WHERE age>=18 ORDER BY last_name DESC;
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;
SELECT postal_code, COUNT(venueID) FROM venues GROUP BY postal_code;
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.
Essay Writing Service Features
Our Experience
No matter how complex your assignment is, we can find the right professional for your specific task. Contact Essay is an essay writing company that hires only the smartest minds to help you with your projects. Our expertise allows us to provide students with high-quality academic writing, editing & proofreading services.
Free Features
Free revision policy
$10Free bibliography & reference
$8Free title page
$8Free formatting
$8How Our Essay Writing Service Works
First, you will need to complete an order form. It's not difficult but, in case there is anything you find not to be clear, you may always call us so that we can guide you through it. On the order form, you will need to include some basic information concerning your order: subject, topic, number of pages, etc. We also encourage our clients to upload any relevant information or sources that will help.
Complete the order form
Once we have all the information and instructions that we need, we select the most suitable writer for your assignment. While everything seems to be clear, the writer, who has complete knowledge of the subject, may need clarification from you. It is at that point that you would receive a call or email from us.
Writer’s assignment
As soon as the writer has finished, it will be delivered both to the website and to your email address so that you will not miss it. If your deadline is close at hand, we will place a call to you to make sure that you receive the paper on time.
Completing the order and download