Solved–Assignment #3:– Creating a Data Model in MS Visio, Part 3: Normalization– Solution

Description

 

For this assignment you will draw from an MS Excel worksheet (available via Canvas along with these instructions) to create a data model that conforms to third normal form (3NF).

 

Instructions

 

Unlike the first two assignments, you aren’t provided any explicit user requirements, though a number of hints and tips are provided below. Beyond the hints and tips, though, you must infer the user requirements based on the information in the associated MS Excel worksheet. Specifically, you must:

 

1. Not violate first normal form (i.e., you must identify and eliminate multi-valued attributes);

 

2. Not violate second normal form (i.e., you must eliminate partial dependencies); and

 

3. Not violate third normal form (i.e., you must eliminate transitive dependencies).

 

In addition, some spreadsheet columns represent composite attributes, and it’s your job to determine which ones should be broken down into simple attributes. (Recall that just because an attribute can be broken down into simpler attributes doesn’t mean it should be.) Some of the hints and tips help in this regard.

 

Note that where there are missing values (in the spreadsheet), they are there for a reason. That is, it’s anticipated that data will be collected for those columns, so they must be taken into account in your ER model.

 

Finally, for this assignment you will not be graded with regard to your optionality decisions.

 

Hints and Tips

 

• General hints/guidelines

 

• Use surrogate PKs (e.g., museumID or museum_ID)

 

• Remember that the crow’s foot should abut the child, not the parent (and remember that

 

foreign keys reside in the child entity)

 

• Entity and attribute names shouldn’t have any spaces in them, and they should be singular,

 

not plural (e.g., exhibit, not exhibits)

 

• An attribute name should be prefixed with the name of its entity (e.g., objectYear, not year)

 

• A foreign key should have the same name as the primary key it’s derived from

 

• My solution model comprises 10 entities, including 4 bridge entities

• Specific hints

 

• There are no 1:1 relationships in the model.

 

• Hints about relationships

 

 

• A museum can host many exhibits, and an exhibit can appear (over time of course) in many museums

 

• A curator is a professional who selects the pieces/items of art that will appear in an exhibit. Thus, a curator curates exhibits, not objects. (Thus, curator should not be tied to object.)

 

• An object isn’t tied to a museum, rather, it’s tied to an exhibit (because this database is all about keeping track of exhibits). Also, an object can appear in

 

multiple exhibits over time.

 

• Hints about composite attributes

 

• In my solution model, five composite attributes were broken down into simple attributes. Of these five attributes, one (exhibit_location) is optional, i.e., you can break it down or not.

 

• Owner name cannot be broken down. (There are other names that can and should be broken down, though.)

 

• Other hints

 

• Recognize that an exhibit’s location/room, start date, and end date are not attributes of the exhibit per se. Think about it: an exhibit travels around over time from museum to museum; its location, start date, and end date will vary by museum. So if these attributes don’t belong in the museum entity or the exhibit entity, it should be obvious (given the relationship between museum and exhibit!) where they belong.

 

• The fact that there are columns in the spreadsheet titled curator1 and curator2, and artist1 and artist2, doesn’t mean that there should be an attribute named curator1 and another attribute named curator2 in the curator entity (or that there should be an attribute named artist1 and another one named artist2 in the artist entity). (Keep in mind that this is how someone who hasn’t taken a relational database course might record and store data!) The fact that there is a curator1 column and curator2 column simply implies that curator is a multi-valued attribute associated with exhibit (i.e., that an exhibit can have more than one curator). Similarly, the fact that there’s an artist1 and artist2 column implies that an object can be created by more than one artist.

 

As with A1 and A2, please submit your data model as a PDF. Finally, take care to use the workspace in

 

MS Visio in a way that aids the reader (i.e., me!):

 

• Related entities should, to the extent possible, be located close to each other.

 

• If you are not able to limit your data model to a single page, then make sure that no entity spans across two pages. The only shape that should span across two pages is the relationship shape.

 

• Make sure that every relationship between two entities has its own relationship line (and corresponding notation symbols). In other words, a relationship line should not be shared by two relationships. (Unfortunately, MS Visio sometimes tries to make you do this.)