PHASE 1

Learning how to design a relational database really requires that you roll up your sleeves and practice, practice, practice. To help you with this, youll be designing a relational database that might be used at your school. Following is a description of the requirements for a hypothetical relational database.

In conjunction with taking an introductory computer concepts course, your school has decided to test the idea of offering weekend seminars to cover the basics of the Internet and the Web. Initially, your school will offer two such seminars:

Web101 The Basics of the Web and Internet

Web205 Building a Web Site

Web101 will have five different sections, and Web 205 will have four different sections.

Although they are not required to, students can enroll in one or both seminars. The seminars are held for eight hours on a single day. There is no cost associated with taking the seminars. One teacher, from a pool of qualified teachers, will be assigned to each section of each seminar. Some teachers will obviously not be assigned to teach any of the sections, and some teachers may be assigned to several different sections.

Finally, the system should track the final grades assigned to each student. One more thing. Your school is simply testing this idea in this particular term. So, dont worry about the term (e.g., Fall, Winter, Spring, or Summer) or the year.

For this particular scenario, you are to create a simple two-column table that lists all the entity classes in the left column and their associated primary keys in the right column. Finally, record as many business rules as you can find provided in the description.

PHASE 2

In this phase, you are to define and draw the basic relationships among the entity classes.

If you correctly completed Phase #1, you know there are four entity classes.

Your task is to define among which of those entity classes relationships exist and then write some verbs that describe each relationship just as we did in Figure C.2 in the textbook.

Specifically

Draw 4 rectangles and place individual entity class names in each

Connect the appropriate rectangles with dotted lines where relationships exist

Write the appropriate verbs to describe the relationships.

PHASE 3

In this phase, you are to define the cardinality among entity classes.

Your tasks is to create the numerical relationships by adding the symbols of |, O, and (crows foot) in the appropriate places.

Specifically

Use the E-R diagram you completed in Phase #2 as a starting point

Define the cardinality among the entity classes by adding the symbols of |, O, and in the appropriate places

Provide a narrative description of each numerical relationship.

PHASE 4

In this phase, you are to eliminate a many-to-many relationship.

If you correctly completed phase #3, you are now able to identify a many-to-many relationship between Student and Seminar Section. That is, a given Seminar Section may have many Students, and a given Student can enroll in many different Seminar Sections (two, to be exact). So, that portion of your E-R diagram looks like what follows.

Your task s to eliminate the above many-to-many relationship by creating an intersection relation. As you do, we encourage you to follow the guidelines presented in the text. What did you name the intersection relation? What does the completed E-R diagram look like?

PHASE 5

If youve successfully completed all the previous phases, you should have a relational database model with five relations. The first four you identified early on; the last one is the result of eliminating the many-to-many relationship.

Your task is to complete a table for each relation. Weve provided a sample table below. That is, you must first fill in the column headings by identifying what information belongs in each relation. Then, in the remaining rows, please add some actual data entries.

Limit each relation to at most five pieces of information.

Seminar Relation