INFO20003 Week 4 Lab – Solutions Section 1: Develop an ER model
◆ Task 1.1 Build a physical model using MySQL Workbench for the bus company case study that was covered in Tutorial 4. Be sure to choose suitable attribute names and data types, paying attention to the length and precision of data types.
A physical model solution to Task 1.1 is provided in the Tutorial 4 solutions.
◆ Task 1.2 Consider the ‘MusicMaker’ case study. Build a physical ER model using MySQL Workbench. Be sure to choose suitable attribute names and data types, paying attention to the length and precision of data types.
Note: The “all tracks on an album must be categorised as the one genre” business rule is challenging to solve. In this model, it is up to the application to ensure that this rule is satisfied.
Another way of solving the rule would be to create a relationship between Album and Genre. The foreign key GenreID on Track would then become optional, only applying to tracks which are not on any album. Care would still need to be taken to ensure a track is not part of two albums of different genres.
In reality, as a database designer, you might suggest to the client or application designer that the business rule is difficult to enforce and should be reconsidered.
INFO20003 LA-Wk4 1 © The University of Melbourne