Kevin’s Music is a small, but rapidly growing business operating out of Burwood. The business began as a means for Kevin to book his band into venues for live performances. Live music is not a very profitable occupation, so Kevin also started to teach music.
Within a year of beginning his operation Kevin was approached by other musicians for help in getting bookings at live music venues. At the same time, some music venues were also asking him for help in getting bands for them. While the musicians are mostly from Victoria, the venues he deals with all Australian states and territories.
Three years later, Kevin finds he has become an agent, the go-between for over 50 musicians or bands and over 100 venues, and is making a reasonable income from his fees. The music school side of the business has also expanded and he has 20 musicians who are doing the teaching, and the demand is still increasing.
Kevin’s Music is now:
- Acting on behalf of musicians to arrange venue bookings
- Acting on behalf of venues to book musicians
- Booking students with musicians for lessons
- Booking musicians to teach students
So far, his business has been run with a ledger book and a calendar (a paper-based process). But these tools are becoming inefficient to manage the business. A transition to an information system is urgently needed.
You are employed as a business analyst at Deakin Innovative Solutions, a business consulting firm. You are assigned to investigate and develop an EER model for the system.
Please note: The design of any payments system is outside the scope of this project.
The system needs to be able to provide the following outputs:
- List of all new students who have joined Kevin’s music after the 1st of July 2017 ordered by the joined date.
- List of all underage male musicians and their age sorted by the first name.
- Count of teachers from each postcode.
- List of all current lesson bookings sorted by the style of music and the booking date (Most recent first). Hint: You may have to join various tables in SQL to achieve the desired output, lookup join
- (Research Required) A report on the students enrolled, the style of music and the teacher for the current calendar month. Current calendar month refers to the month in which this SQL query is run.
You are required to perform the following tasks in this assignment.
|1. Construct an Enhanced Entity-Relationship (EER) model for the database. Make sure you include in your model details of entities, relationships, attributes, keys and cardinality of the relationships. (GLO1&5)
a. List any assumptions made and ensure that you give adequate justification.
|2. Show by providing SQL statements, that all of the reports listed in the
“Operations” section above can be produced from your EER diagrams. (GLO1&5)
NOTE: No need to actually create a database. This is a thought exercise to demonstrate that the queries are possible based on your EER diagram.
|3. Produce a business report (with EER diagram and SQL queries included within) according to the format outlined in the document “A Guide to Writing Business Reports.pdf” (GLO1&3)||10 Marks|
EER diagram should contain the following components: Entities, named relationships, attributes (keys) and cardinality for relationships.
A faster way of doing the EER diagram is to sketch it up on a blackboard, take a digital photograph and paste it into your report. Alternatively, you may use any software tools at your disposal – Lucid Charts is a good one. If hand-drawn please make sure your EER diagram is legible.
Appendix – EER Diagram Symbols
(adapted from Lucid Charts, https://www.lucidchart.com/pages/EER-diagram-symbols-and-meaning)
Entities are objects or concepts that represent important data. They are typically nouns, e.g. customer, supervisor, location, or promotion.
- Strong entities exist independently from other entity types. They always possess one or more attributes that uniquely distinguish each occurrence of the entity.
- Weak entities depend on some other entity type. They don’t possess unique attributes (also known as a primary key) and have no meaning in the diagram without depending on another entity. This other entity is known as the owner.
- Associative entities are entities that associate the instances of one or more entity types. They also contain attributes that are unique to the relationship between those entity instances. (we’ll cover this in week 3)
- Relationships are meaningful associations between or among entities. They are usually verbs, e.g. assign, associate, or track. A relationship provides useful information that could not be discerned with just the entity types.
- Weak relationships, or identifying relationships, are connections that exist between a weak entity type and its owner.
- Attributes are characteristics of either an entity, a many-to-many relationship, or a one-to-one relationship.
- Multivalued attributes are those that are capable of taking on more than one value.
- Derived attributes are attributes whose value can be calculated from related attribute values.
- Cardinality (limits of participation or sometimes called multiplicity) refer to the maximum number of times an instance in one entity can be associated with instances in the related entity, and the minimum number of times an instance in one entity can be associated with an instance in the related entity. Cardinality represented by the styling of a line and its endpoint, as denoted by the chosen notation style.