Relationships, Entities and Database Design - Entity Relationship Modeling Examples
(Page 3 of 4 )
Earlier in this chapter, we showed you how to design a database and understand an Entity Relationship (ER) diagram. This section explains the requirements for our three example databases—music, university, and flight—and shows you their Entity Relationship diagrams:
The music database is designed to store details of a music collection, including the albums in the collection, the artists who made them, the tracks on the albums, and when each track was last played.
The university database captures the details of students, courses, and grades for a university.
The flight database stores an airline timetable of flight routes, times, and the plane types.
Figure 4-9. The ER diagram representation of a weak entity
The next section explains these databases, each with its ER diagram and an explanation of the motivation for its design. You’ll find that understanding the ER diagrams and the explanations of the database designs is sufficient to work with the material in this chapter. We’ll show you how to create the music database on your MySQL server in Chapter 5 .
The Music Database
The music database stores details of a personal music library, and could be used to manage your MP3, CD, or vinyl collection. Because this database is for a personal collection, it’s relatively simple and stores only the relationships between artists, albums, and tracks. It ignores the requirements of many music genres, making it most useful for storing popular music and less useful for storing jazz or classical music. (We discuss some shortcomings of these requirements at the end of the section in “What it doesn’t do.”)
We first draw up a clear list of requirements for our database:
The collection consists of albums.
An album is made by exactly one artist.
An artist makes one or more albums.
An album contains one or more tracks
Artists, albums, and tracks each have a name.
Figure 4-10. Quick summary of the ER diagram symbols
Each track is on exactly one album.
Each track has a time length, measured in seconds.
When a track is played, the date and time the playback began (to the nearest second) should be recorded; this is used for reporting when a track was last played, as well as the number of times music by an artist, from an album, or a track has been played.
There’s no requirement to capture composers, group members or sidemen, recording date or location, the source media, or any other details of artists, albums, or tracks.
The ER diagram derived from our requirements is shown in Figure 4-11. You’ll notice that it consists of only one-to-many relationships: one artist can make many albums, one album can contain many tracks, and one track can be played many times. Conversely, each play is associated with one track, a track is on one album, and an album is by one artist. The attributes are straightforward: artists, albums, and tracks have names, as well as identifiers to uniquely identify each entity. The track entity has a time attribute to store the duration, and the played entity has a timestamp to store when the track was played.
The only strong entity in the database is artist, which has an artist_id attribute that uniquely identifies it. Each album entity is uniquely identified by its album_id combined with the artist_id of the corresponding artist entity. A track entity is similarly uniquely identified by its track_id combined with the related album_id and artist_id attributes. The played entity is uniquely identified by a combination of its played time, and the related track_id, album_id, and artist_id attributes.
Figure 4-11. The ER diagram of the music database
What it doesn’t do
We’ve kept the music database simple because adding extra features doesn’t help you learn anything new, it just makes the explanations longer. If you wanted to use the music database in practice, then you might consider adding the following features:
Support for compilations or various-artists albums, where each track may be by a different artist and may then have its own associated album-like details such as a recording date and time. Under this model, the album would be a strong entity, with many-to-many relationships between artists and albums.
Playlists, a user-controlled collection of tracks. For example, you might create a playlist of your favorite tracks from an artist.
Track ratings, to record your opinion on how good a track is.
Source details, such as when you bought an album, what media it came on, how much you paid, and so on.
Album details, such as when and where it was recorded, the producer and label, the band members or sidemen who played on the album, and even its artwork.
Smarter track management, such as modeling that allows the same track to appear on many albums.