Normalization Assignment Details

To keep track of Organizations, Professional Contacts, Events and Event Participation, the IOTM department uses a table with the following structure. The Primary Key of the data is underlined in red.

Event_Participation Table: Primary Key = EventID & ContactID

1. Given the table structure illustrated above, write the relational schema of the table.

2. The table is susceptible to modification anomalies. Provide an example of insertion, deletion, and update anomalies (one of each).

3. Identify the Functional Dependencies illustrated in the table above. List the functional dependencies using the A->B notation.

4. Analyze the table structure and determine its current state of normalization. Present your rationale and conclusion using the PRECISE methodology demonstrated in the class lecture.

5. Normalize the table to 3NF. Present your solution in a set of relational schemas and a set of functional dependency diagrams that meet 3NF requirements. Create new entities and attributes as necessary.

6. Draw your solution as a crow's foot ERD using Visio and copy and paste (snip it) it into this document.

\begin{tabular}{|c|c|c|c|c|c|c|c|c|c|c|} \hline EventID & ContactiD & EventName & EventDate & ContactLname & ContactFName & ContactTitle & OrgID & OrgName & OrgAddress & ParticipantComment \\ \hline 1 & 18 & Meet and Greet & 19-Jan-18 & Anderson & Dave & Chief Information Officer & 5 & Libbey Glass & 300 Madison Ave. & New employer \\ \hline 1 & 20 & Meet and Greet & 19-Jan- 18 & Bauman & Trey & Assistant Controller & 2 & Dana Inc. & 580 Longbow Drive & Great Event, thanks! \\ \hline 1 & 12 & Meet and Greet & 19-Jan- 18 & Dowd & Troy & Application Services Manager & 74 & Adrian Steel & 906 James St. & Thanks for the invite! \\ \hline 1 & 28 & Meet and Greet & 19-Jan-18 & Drennan & Brian & Application Developer & 3 & Ol-Glass & One Michael Owens Way, & New position. \\ \hline 1 & 5 & Meet and Greet & 19-Jan-18 & Farah & Amalie & Global Information Services & 12 & Owens Corning & 1 Owens Corning Pkwy. & Interested in ISAC \\ \hline 1 & 15 & Meet and Greet & 19-Jan-18 & Goldman & Jon & Database Administrator & 10 & First Solar & 28101 Cedar Park Blvd. & Keep up the good work \\ \hline 2 & 36 & Spring, 2018 ISAC & 16-Mar-18 & Carroll & Charles & Full Stack Web Developer & 62 & Toledo Zoo & & Thanks for the invite! \\ \hline 2 & 50 & Spring, 2018 ISAC & 16-Mar-18 & Eskandari & Kathy & Service Delivery Manager - Aut & 3 & Ol-Glass & One Michael Owens Way, & Email me about a project \\ \hline 2 & 45 & Spring, 2018 ISAC & 16-Mar-18 & Weiker & Tyler & Associate Consultant & 80 & Apex Systems & & New position. \\ \hline 2 & 51 & Spring, 2018 ISAC & 16-Mar-18 & Hill & Patrick & Solutions Analyst & 3 & Ol-Glass & One Michael Owens Way, & Count me in for the projec \\ \hline 3 & 33 & Meet and Greet & 14-Sep-18 & Alvarado & Emilio & IT Business Analyst & 2 & Dana Inc. & 580 Longbow Drive & new title \\ \hline 3 & 18 & Meet and Greet & 14-Sep-18 & Anderson & Dave & Chief Information Officer & 5 & Libbey Glass & 300 Madison Ave. & Rod said hi \\ \hline 3 & 27 & Meet and Greet & 14-Sep-18 & Collins & Katelyn & Senior Analyst, IT Audit & 6 & Nationwide Financ & cOne Nationwide Blvd., & New position. \\ \hline 3 & 5 & Meet and Greet & 14-Sep-18 & Farah & Amalie & Global Information Services & 12 & Owens Corning & 1 Owens Corning Pkwy. & New employer \\ \hline 3 & 51 & Meet and Greet & 14-Sep-18 & Hill & Patrick & Solutions Analyst & 3 & Ol-Glass & One Michael Owens Way, & Great Event, thanks! \\ \hline 3 & 39 & Meet and Greet & 14-Sep-18 & Hood & Jason & Chief Technology Officer & 75 & Stratascale & & Thanks for the invite! \\ \hline 3 & 57 & Meet and Greet & 14-Sep-18 & Janik & Kenneth & Database Administrator II & 81 & United Wholesale & & New position. \\ \hline 3 & 6 & Meet and Greet & 14-Sep-18 & Smith & Alyssa & Project Manager & 3 & Ol-Glass & One Michael Owens Way, & new title \\ \hline 3 & 60 & Meet and Greet & 14-Sep-18 & Taylor & Ken & VP of IT Applications & 49 & Other/Unknown & & new title \\ \hline 3 & 16 & Meet and Greet & 14-Sep-18 & Winters & Bryan & Consultant at Blue Horseshoe & 49 & Other/Unknown & & New position. \\ \hline 3 & 53 & Meet and Greet & 14-Sep-18 & Wittenauer & Lindsey & Senior Business Analyst - Afterl & 2 & Dana Inc. & 580 Longbow Drive & New employer \\ \hline 7 & 36 & Skills and Knowledg & 16-Mar-18 & Carroll & Charles & Full Stack Web Developer & 62 & Toledo Zoo & & Great Event, thanks! \\ \hline 7 & 50 & Skills and Knowledg & 16-Mar-18 & Eskandari & Kathy & Service Delivery Manager - Aut & 3 & Ol-Glass & One Michael Owens Way, & Thanks for the invite! \\ \hline 7 & 51 & Skills and Knowledg & 16-Mar-18 & Hill & Patrick & Solutions Analyst & 3 & OI-Glass & One Michael Owens Way, & New position. \\ \hline 6 & 16 & Skills and Knowledg & 24-Mar-17 & Winters & Bryan & Consultant at Blue Horseshoe & 49 & Other/Unknown & & Count me in for the projec \\ \hline 5 & 26 & Skills and Knowledg & 08-Apr-16 & Herrett & David & Sr. Associate, Section Manager, & 17 & SSOE Group & 1001 Madison Ave & new title \\ \hline \end{tabular}