DATABASE SYSTEMS

Using the SpecialEvenPermit table (see image below), normalize the table into third normal form and create the 3NF dependency diagram.

Business rules:

• Each event is sponsored by one or more organizations.
• Each organization can sponsor one or more events.
• Each event has zero or one categories.
• Each category has zero or one sub-categories.
• Each event takes place in zero or more neighborhoods.
• Each neighborhood can host zero or more events.
• Each event takes place in zero or more parks.
• Each park can host zero or more events.
• You may discover or infer additional business rules as you decompose the table.

Hints:

• ApplicationNumber is an intelligent key. The 2nd and 3rd digits are the year of the event and the 3rd and 4th characters are the month of the event. Is it suitable as a primary key? What might happen to the value if an event's date is changed?
• You may need to use surrogate primary keys for some of the decomposed entities.

Data source: City of Seattle Open Data Portal - Special Events Permits (the original data set had many multi-valued attributes which have already been dealt with in the version you are working with)

Using the 3NF relationship diagram for SpecialEventPermit created in problem above, create a complete ERD diagram for the normalized tables. Be sure to label the primary keys and foreign keys, and use the correct cardinalities for your crow's foot connectors.

S171A092 Americans for Refu Butler Valet Red Carpet Valet Red Carpet Valet Red Carpet Valet Red Carpet Valet Red Carpet Valet 2017-01-27 Complete Special Event 2017-01-23 Complete Valet Parking 2017-01-18 CompleteValet Parking 2017-01-17 Complete Valet Parking 2017-01-17 Complete 2017-01-17 CompleteValet Parking 017-01-17 Complete 2017-01-17 CompleteValet Parking Free Speech Stand With Im Butler Valet T Red Carpet Val Red Carpet Va Red Carpet Val Red Carpet Va Red Carpet Va Red Cerpet Val 2017-01-29 2017-01-26 2017-01-28 2017-01-28 2017-01-28 2017-01-28 2017-01-28 2017-01-282017-01-28 Wallingford 2017-01-29 Downtown 2017-01-2 Queen Anne 2017-01-28 Downtown 2017-01-28 Cascade 2017-01-28 Eastlake 2017-01-28 South Lake Union 2017-01-28 Pioneer Square Westlake Pa $171A081 5171A079 si 7A079 517JA075 $17JA078 $171A077 Valet Parking Valet Parking S171A092 Americans for Refu Butler Valet Red Carpet Valet Red Carpet Valet Red Carpet Valet Red Carpet Valet Red Carpet Valet 2017-01-27 Complete Special Event 2017-01-23 Complete Valet Parking 2017-01-18 CompleteValet Parking 2017-01-17 Complete Valet Parking 2017-01-17 Complete 2017-01-17 CompleteValet Parking 017-01-17 Complete 2017-01-17 CompleteValet Parking Free Speech Stand With Im Butler Valet T Red Carpet Val Red Carpet Va Red Carpet Val Red Carpet Va Red Carpet Va Red Cerpet Val 2017-01-29 2017-01-26 2017-01-28 2017-01-28 2017-01-28 2017-01-28 2017-01-28 2017-01-282017-01-28 Wallingford 2017-01-29 Downtown 2017-01-2 Queen Anne 2017-01-28 Downtown 2017-01-28 Cascade 2017-01-28 Eastlake 2017-01-28 South Lake Union 2017-01-28 Pioneer Square Westlake Pa $171A081 5171A079 si 7A079 517JA075 $17JA078 $171A077 Valet Parking Valet Parking