Modeling and Designing Databases - Learning MySQL - page 124

• 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.
The University Database
The university database stores details about university students, courses, the semester
a student took a particular course (and his mark and grade if he completed it), and what
degree program each student is enrolled in. The database is a long way from one that'd
be suitable for a large tertiary institution, but it does illustrate relationships that are
interesting to query, and it's easy to relate to when you're learning SQL. We explain
the requirements next and discuss their shortcomings at the end of this section.
Consider the following requirements list:
• The university offers one or more programs.
• A program is made up of one or more courses.
• A student must enroll in a program.
• A student takes the courses that are part of her program.
• A program has a name, a program identifier, the total credit points required to
graduate, and the year it commenced.
• A course has a name, a course identifier, a credit point value, and the year it
commenced.
• Students have one or more given names, a surname, a student identifier, a date of
birth, and the year they first enrolled. We can treat all given names as a single object
—for example, “John Paul.”
• When a student takes a course, the year and semester he attempted it are recorded.
When he finishes the course, a grade (such as A or B) and a mark (such as 60
percent) are recorded.
• Each course in a program is sequenced into a year (for example, year 1) and a
semester (for example, semester 1).
The ER diagram derived from our requirements is shown in Figure 4-12. Although it
is compact, the diagram uses some advanced features, including relationships that have
attributes and two many-to-many relationships.
In our design:
• Student is a strong entity, with an identifier, student_id , created to be the primary
key used to distinguish between students (remember, we could have several stu-
dents with the same name).