Reference no: EM133627474

Assignment: Intro to Databases

Supplemental reading:

Bagui and Earp, Ch. 4 pp. 80-98, Ch. 5 pp. 115-125, Ch. 6 pp. 133-141

Ch. 4 pp. 80-98 "Tighter English" to "ER Design Methodology" (inclusive)

Ch. 5 pp. 115-125 "Chapter Topics" to "Weak Entity Grammar" (inclusive)

Ch. 6 pp. 133-141 "Chapter Topics" to "More Entities and Relationships" (inclusive)

Bagui and Earp, 2nd Edition Readings:

Ch. 4 pp. 83-84 (4.6: "English Description of the Entity")

Ch. 6 pp. 128-141 (there are also some examples that start at page 141) (6.3 "Participation: Full/Partial", 6.4 "English Descriptions", 6.5 "Tighter English")

Ch. 7 pp. 171-181 (7: "The Weak Entity", stop right before 7.8: "Mapping Weak Entities to a Relational Database")

Ch. 8 pp. 193-198 (8.1: "Introduction", 8.2: "Attributes of Relationship", 8.3 "Relationship Developing into Entities: The M:N Relationship revisited")

Part I:

Draw a diagram and write the corresponding formal written description for the following (very small) database: An airline asked you to model a dedicated database that is going to keep track of passengers and their luggage; this database will be used to handle transportation and recovery of lost bags. Each passenger has a frequent flyer id number (that identifies them) a name and an address where the bags may be mailed (there may be several addresses listed in the passenger's profile). Bags, of course, do not have a unique identifier but they do get numbered at check-in time to distinguish between different bags for any particular passenger. Thus the bag# identifier is guaranteed to be unique per passenger. For each bag we keep track of its color (possibly several colors listed), material and size. Bag size is always specified, while material information does not have to be specified.

Part II:

In this assignment, you will create an E-R Diagram and a corresponding formal written description for a small database of information about college students. Here is the verbal description of the data to be modeled:

"The main thing we keep track of is students. Each student has a name and an ID number. We also keep information about the high school that each student graduated from: its name and location (the location is made up of the city and state). We may not know every student's high school. Every student has a faculty member who serves as their advisor; we keep track of their names and departments (faculty members also have ID numbers). Not all faculty members advise students, though we wish they would. Most students live on campus, so we also keep track of the rooms in our one dormitory - it's brand new - all of the rooms are single occupancy. Each room has a room number, and is in either the South or North wing. The dormitory isn't full, even though we still have some students who do not live on campus."

Your task is to draw an E-R diagram containing the four entities discussed above, their attributes and keys, and the relationships among them. Also give a formal written description (similar to what you see in text or what I showed on slides during class) of your E-R model, with all of the necessary information for each entity (including its attributes and key if one exists) and each relationship.

Note: The verbal description may not have all of the information you need to determine some of the properties of the entities, attributes, or relationships. You may have to use your own judgment, or think of what follow-up questions you might ask and decide what you think the reasonable answers would be. If you find that you make any assumptions that are not stated in the verbal description, list them under a heading "Additional Assumptions" at the end of your formal written description. For example, you might include assumptions such as "A faculty member can belong to more than one department." or "Can two students have the same name? Yes."