Entity Relationship Modeling Examples

Earlier in this chapter, we showed y'all how to design a database and sympathize an Entity Relationship (ER) diagram. This department explains the requirements for our three instance databases—music, university, and flying—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 rail was last played.

  • The academy database captures the details of students, courses, and grades for a academy.

  • The flight database stores an airline timetable of flight routes, times, and the plane types.

The adjacent section explains these databases, each with its ER diagram and an explanation of the motivation for its blueprint. Yous'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 lot 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 drove. Considering this database is for a personal collection, information technology's relatively simple and stores only the relationships betwixt artists, albums, and tracks. Information technology ignores the requirements of many music genres, making it nearly useful for storing popular music and less useful for storing jazz or classical music. (We discuss some shortcomings of these requirements at the cease of the section in What it doesn't exercise.")

We first draw upwards a clear list of requirements for our database:

  • The drove consists of albums.

  • An album is made past exactly one artist.

  • An artist makes one or more albums.

  • An album contains 1 or more tracks

  • Artists, albums, and tracks each have a proper name.

  • Each rails is on exactly i anthology.

  • Each runway has a time length, measured in seconds.

  • When a track is played, the appointment and fourth dimension the playback began (to the nearest 2nd) should exist recorded; this is used for reporting when a track was last played, every bit well equally the number of times music by an creative person, from an anthology, or a track has been played.

There'southward 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 iv-eleven. You'll notice that it consists of only one-to-many relationships: 1 artist tin make many albums, 1 album can incorporate many tracks, and one track tin can be played many times. Conversely, each play is associated with one rail, a track is on 1 album, and an album is by 1 creative person. The attributes are straightforward: artists, albums, and tracks have names, every bit well as identifiers to uniquely identify each entity. The rails entity has a fourth dimension attribute to store the duration, and the played entity has a timestamp to store when the track was played.

The ER diagram of the music database

Figure 4-11. The ER diagram of the music database

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 respective Artist entity. A Track entity is similarly uniquely identified past its track_id combined with the related album_id and artist_id attributes. The Played entity is uniquely identified past a combination of its played time, and the related track_id, album_id, and artist_id attributes.

What information technology doesn't practise

We've kept the music database uncomplicated because adding extra features doesn't assist you acquire annihilation new, it only makes the explanations longer. If you wanted to employ the music database in practise, then you might consider adding the following features:

  • Support for compilations or diverse-artists albums, where each track may be by a dissimilar artist and may and then take its own associated anthology-like details such every bit a recording engagement and fourth dimension. Nether this model, the album would be a stiff entity, with many-to-many relationships between artists and albums.

  • Playlists, a user-controlled collection of tracks. For example, you lot might create a playlist of your favorite tracks from an artist.

  • Track ratings, to record your opinion on how good a rail is.

  • Source details, such every bit when y'all bought an anthology, what media it came on, how much you paid, and so on.

  • Anthology details, such as when and where information technology was recorded, the producer and label, the band members or sidemen who played on the album, and fifty-fifty its artwork.

  • Smarter track management, such as modeling that allows the same track to appear on many albums.

The Academy Database

The university database stores details about academy students, courses, the semester a pupil took a particular course (and his marking and grade if he completed it), and what degree programme each pupil is enrolled in. The database is a long way from one that'd be suitable for a large third institution, just it does illustrate relationships that are interesting to query, and it'southward easy to relate to when you're learning SQL. We explain the requirements next and talk over their shortcomings at the end of this section.

Consider the following requirements list:

  • The academy offers one or more programs.

  • A plan is made up of 1 or more courses.

  • A student must enroll in a program.

  • A student takes the courses that are office of her program.

  • A plan has a name, a plan identifier, the full credit points required to graduate, and the twelvemonth it commenced.

  • A class has a name, a class identifier, a credit point value, and the year it commenced.

  • Students accept one or more given names, a surname, a student identifier, a date of birth, and the year they offset enrolled. We can treat all given names as a unmarried object—for instance, "John Paul."

  • When a student takes a course, the yr and semester he attempted it are recorded. When he finishes the course, a grade (such equally A or B) and a mark (such as sixty percent) are recorded.

  • Each course in a program is sequenced into a yr (for example, yr ane) and a semester (for example, semester 1).

The ER diagram derived from our requirements is shown in Figure four-12. Although it is meaty, the diagram uses some advanced features, including relationships that accept attributes and two many-to-many relationships.

The ER diagram of the university database

Effigy 4-12. The ER diagram of the academy database

In our design:

  • Student is a strong entity, with an identifier, student_id, created to be the primary fundamental used to distinguish between students (remember, we could have several students with the aforementioned name).

  • Plan is a strong entity, with the identifier program_id every bit the primary key used to distinguish between programs.

  • Each student must be enrolled in a programme, and then the Student entity participates totally in the many-to-1 EnrollsIn human relationship with Program. A programme can be without having any enrolled students, so it participates partially in this relationship.

  • A Class has meaning just in the context of a Program, so it's a weak entity, with course_id as a weak key. This means that a Course is uniquely identified using its course_id and the program_id of its owning program.

  • Every bit a weak entity, Grade participates totally in the many-to-one identifying relationship with its owning Program. This relationship has Year and Semester attributes that identify its sequence position.

  • Student and Class are related through the many-to-many Attempts relationships; a grade can exist without a educatee, and a pupil tin can be enrolled without attempting whatsoever courses, then the participation is not total.

  • When a student attempts a form, there are attributes to capture the Year and Semester, and the Mark and Grade.

What it doesn't do

Our database design is rather simple, but this is because the requirements are simple. For a real university, many more aspects would demand to be captured by the database. For example, the requirements don't mention anything well-nigh campus, study mode, grade prerequisites, lecturers, timetabling details, address history, financials, or assessment details. The database also doesn't allow a student to be in more than than one degree program, nor does it let a grade to appear equally role of different programs.

The Flying Database

The flight database stores details about an airline's fleet, flights, and seat bookings. Again, it's a hugely simplified version of what a real airline would use, but the principles are the same.

Consider the following requirements list:

  • The airline has one or more than airplanes.

  • An plane has a model number, a unique registration number, and the capacity to have 1 or more passengers.

  • An airplane flight has a unique flying number, a departure airport, a destination airport, a divergence engagement and time, and an arrival date and time.

  • Each flying is carried out past a unmarried aeroplane.

  • A passenger has given names, a surname, and a unique email address.

  • A passenger can volume a seat on a flight.

The ER diagram derived from our requirements is shown in Effigy four-13:

The ER diagram of the flight database

Figure four-13. The ER diagram of the flight database

  • An Airplane is uniquely identified by its RegistrationNumber, and so nosotros use this equally the primary key.

  • A Flight is uniquely identified by its FlightNumber, and so we use the flying number every bit the principal key. The departure and destination airports are captured in the From and To attributes, and we take separate attributes for the departure and arrival appointment and fourth dimension.

  • Because no ii passengers volition share an email address, we can use the EmailAddress as the principal central for the Rider entity.

  • An aeroplane can be involved in any number of flights, while each flight uses exactly one airplane, so the Flies relationship between the Plane and Flight relationships has cardinality i:N; because a flight cannot be without an airplane, the Flying entity participates totally in this relationship.

  • A passenger tin can book any number of flights, while a flight can be booked by any number of passengers. Every bit discussed earlier in Intermediate Entities," nosotros could specify an M:N Books relationship between the Passenger and Flying relationship, simply considering the issue more carefully shows that there is a hidden entity here: the booking itself. We capture this past creating the intermediate entity Booking and 1:N relationships between it and the Passenger and Flight entities. Identifying such entities allows us to become a better picture of the requirements. Note that even if we didn't notice this subconscious entity, information technology would come out every bit part of the ER-to-tables mapping process nosotros'll depict next in Using the Entity Relationship Model."

What it doesn't do

Again, this is a very simple flight database. There are no requirements to capture rider details such every bit age, gender, or frequent-flier number.

We've treated the chapters of the airplane every bit an aspect of an private aeroplane. If, instead, we assumed that the chapters is determined by the model number, we would have created a new AirplaneModel entity with the attributes ModelNumber and Capacity. The Airplane entity would and so not accept a Capacity attribute.

We've mapped a different flight number to each flight betwixt two destinations. Airlines typically apply a flight number to place a given flying path and schedule, and they specify the appointment of the flight independently of the flight number. For case, there is ane IR655 flight on April 1, another on April 2, and then on. Different airplanes tin operate on the same flight number over time; our model would need to be extended to support this.

The system also assumes that each leg of a multihop flight has a unlike FlightNumber. This means that a flight from Dubai to Christchurch via Singapore and Melbourne would need a unlike FlightNumber for the Dubai-Singapore, Singapore-Melbourne, and Melbourne-Christchurch legs.

Our database also has limited ability to depict airports. In practice, each airport has a name, such every bit "Melbourne Regional Airport," "Mehrabad," or "Tullamarine." The name can be used to differentiate between airports, but well-nigh passengers volition simply apply the name of the boondocks or city. This can atomic number 82 to confusion, when, for example, a passenger could book a flight to Melbourne, Florida, U.s.a., instead of Melbourne, Victoria, Australia. To avert such issues, the International Air Transport Association (IATA) assigns a unique airport code to each airport; the airport code for Melbourne, Florida, USA is MLB, while the code for Melbourne, Victoria, Australia is MEL. If we were to model the airport as a separate entity, we could employ the IATA-assigned airport lawmaking as the primary key. Incidentally, there'southward an culling ready of airport codes assigned by the International Civil Aviation Organization (ICAO); under this code, Melbourne, Florida is KMLB, and Melbourne, Australia is YMML.

Go Learning MySQL now with O'Reilly online learning.

O'Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.