Share
Explore BrainMass

A detailed database design example

A college course may have one or more scheduled sections, or may not have a scheduled section. Attributes of COURSE include Course_ID, Course_Name, and Units. Attributes of SECTION include Section_Number and Semester_ID. Semester_ID is composed of two parts: Semester and Year. Section Number is an integer (such as "1" or "2") that distinguishes one section from another for the same course but does not uniquely identify a section. How did you model SECTION? Why did you choose this way versus alternative ways to model SECTION?

Solution Preview

As stated in the posting, the section information contains a section number and a semester id. The section number is an integer (e.g. "1"). The semester id contains the year and the semester (e.g. "fall2008"). It is possible for a course to have 0 or more sections scheduled.

In order to determine the best way to model this information let's first look at the course information. A course contains an id, a name, and the number of units. Arranged as a table this looks like this:

<pre>
Course_id Course_name Units
-------------------------------------
1 Algebra I 4
2 English I 4
</pre>

The obvious way to add section data is to add additional columns to the table as ...

Solution Summary

This solution provides a very detailed walk through of the database design process for a couple of related tables (course, and section). The solution shows how some of the "obvious" choices lead to problems and how to fix those.

$2.19