This is an individual assignment. The aim is to provide experience in the steps involved with the design of a simple database. The marks obtained for this exercise count as 33.33% of the aggregate continuous assessment marks for the trimester. The proportion of marks for each task is shown below. You are strongly encouraged to make a start on this assignment by the end of the third week of trimester, and you should progress thoughtfully through the steps. Hasty decisions made early in the design process may result in much more work later. Feel free to discuss concepts and ideas with peers but remember your submission must be your own work. Be careful not to allow anyone to copy your work.
There are several notation styles of ER diagrams in common usage (Refer to Appendix). You may use whichever style you feel comfortable. Your ER diagram should contain the following components: Entities, named relationships, attributes (keys) and cardinality for relationships. Logic behind the ER model is what attracts marks not the style of notation used. A faster way of doing the ER diagram is to sketch it up on a blackboard, take a digital photograph and paste it in 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 ER diagram is legible.
The assignment must be submitted as soft copy in the form of a Microsoft Word or PDF document via Cloud Deakin by the due date. File should be named your firstname_lastname with docx or pdf file extension (e.g. John_smith.docx). You do not have to submit an actual database!
Jim’s Music is a small, but rapidly growing business operating out of Burwood. The business began as a means for Jim to book his band into venues for live performances. Live music is not a very profitable occupation, so Jim also started to teach music. Within a year of beginning his operation Jim 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 his musician is mostly from Victoria, the venues he deals with all Australian states and territories.
Three years later, Jim 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.
Jim’s Music is now:
- Acting on behalf of musicians to arrange 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, but these tools are becoming inefficient, if not impossible to manage the business. A transition to an information system is urgently needed. You are employed as business analysts at Deakin Innovative Solutions Pty Ltd, a business consulting firm. You are assigned to investigate and develop an ER model for the system.
The system needs to be able to provide the following outputs:
- List of all new teachers who have joined Jim`s music after the 1st of January 2015 ordered by the joined date.
- List of all female students who are adults and their age sorted by the last name.
- List of all current lesson bookings sorted by the style of music and booking date (Most recent first).
- Count of venues from each postcode in the database.
- A monthly report on students enrolled and the style of music and teacher.
Hint: You may have to join various tables in SQL to achieve the desired output, lookup join command.
You are required to perform the following tasks in this assignment.
- Construct an entity-relationship (ER) model for the database. Make sure you include in your model details of entities, relationships, attributes, keys and cardinality for relationships.
- List any assumptions made and ensure that you give adequate justification.
- List any important business rules.
- Show by providing SQL statements, that all of the reports listed in the "Operations" section above can be produced from your ER diagrams.
- 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.
- Produce a business report (with ER diagram and SQL queries included within) according to the format outlined in " Guide to Writing a Business Report.pdf"
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 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.