https://ibb.co/i5E0rV

For this assignment you will draw from an MS Excel worksheet to create a data model that conforms to third normal form (3NF).

Instructions

You must infer the user requirements based on the information in the associated MS Excel worksheet. Specifically, you must:

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

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

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

In addition, some spreadsheet columns represent composite attributes, and its 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 doesnt 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, its anticipated that data will be collected for those columns, so they must be taken into account in your ER model.

Hints and Tips

General hints/guidelines o Use surrogate PKs (e.g., museumID or museum_ID) o Remember that the crowsfoot should abut the child, not the parent(and remember that foreign keys reside in the child entity) o Entity and attribute names shouldnt have any spaces in them, and they should be singular, not plural (e.g., exhibit, not exhibits) o An attribute name should be prefixed with the name of its entity (e.g., objectYear, not year) o A foreign key should have the same name as the primary key its derived from o My solution model comprises 10 entities, including 4 bridge entities

Specific hints o There are no 1:1 relationships in the model.

o 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 isnt tied to a museum, rather, its tied to an exhibit (because this database is all about keeping track of exhibits). Also, an object can appear in multiple exhibits over time.

o 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.) o Otherhints

Recognize that an exhibits 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 dont 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, doesnt 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 hasnt 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 theres an artist1 and artist2 column implies that an object can be created by more than one artist.

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.