Assignment 4 – Modelling and Normalization

Due Date: Feb 28, 2017

Introduction

  • In this exercise you’ll be working with data posted on the course website.
  • This exercise is worth 6 points. The diagram is worth 4 points, the write-up is worth 1 point, and the normalization question is worth 1 point.
  • As this assignment does not require SQL statements, the normal submission guidelines do not apply. Sketch your ER diagram by hand, type up the questions and print them out. Submit both to me as hardcopies at the beginning of class.

Part I – ER Diagram – Modeling GIS Workshops

For this part, you will sketch an entity relationship diagram to model a database to store information about GIS workshops that I teach. Use pencil, ruler, and paper and sketch an ER diagram by hand using Chen’s ER methods that we covered in class and in the readings. If you prefer to use software or online tools to do the sketching you may, but it’s purely optional.

I have placed a spreadsheet on the course website that contains data related to the workshops – on the Data and Software page under Individual class exercises. Use this information and the following description to create the diagram:

Every semester I offer two or three day-long workshops called Introduction to GIS Using Open Source Software. Students, faculty, and staff from throughout CUNY are able to register. Each participant is required to submit their name and email address, and identify their: status, department or field of study, and CUNY school affiliation (for each option they must choose one; i.e. they can't identify multiple fields or schools).

I've held the workshops in a few different rooms at different campuses (but so far, only at Baruch and Lehman). If the rooms are equipped with computers then participants can use them; if the room does not have computers then the participants must bring their own laptops.

At the end of the workshop many of the participants fill out a course evaluation form. This form is anonymous and cannot be tied back to the participant list. For the evaluators all I know is their status, and the status category is more generic than what appears on the participant list.

The goal is to create a database where I can store all of this information in one place, and create queries where I can summarize the data in different ways. Up until now I have used individual spreadsheets, but as I teach these workshops year after year there is no efficient way for me to pull all the data together.

Some pointers:

  • Do NOT worry about the difference between registrants (people who sign up) and participants (people who actually show up). Ignore the registration aspect completely and just look at people who participated.
  • The participant and evaluation data cannot be directly tied together, as evaluations are anonymous. Treat participants and evaluators separately.
  • The evaluation data in the spreadsheet is summarized from individual paper forms that people fill in. For the database model, you can imagine that you have an anonymous record for each individual respondent with their responses.
  • A participant can’t take the workshop more than once.
  • I am the only instructor. There’s no need to store any instructor information.
  • Keep normalization guidelines in mind. If a lot of standardized information would be repeated within an entity, create a separate entity for it.
  • In your diagram you MUST show cardinality and participation, but you do NOT have to show whether an entity or relationship is strong or weak.

Part II – Diagram Write-up

In a short paragraph, summarize how you created the ER diagram. Discuss your decision making process and describe some of the key factors in creating the various entities, relationships, and attributes in the model.

Part III – Normalization

This part is completely separate from the preceding exercise – base your decisions solely on the data provided below. Take the following workshop participant data and put it into 1st, then 2nd, then 3rd normal form. Show each step of the transformation (note – you can keep the person’s name as a single field).

name email department / college workshop_date workshop_room
Darth Vader dv@deathstar.org Economics, Baruch 9/30/2016 951
Eleanor Roosevelt omg@gmail.com Marketing, Baruch 10/31/2016 322
Peter Parker spidey@yahoo.com Criminal Justice, John Jay 9/30/2016 951
Genghis Khan gkhan@aol.com Geography, Lehman 10/31/2016 322
Roy Rogers kingcowboy@gmail.com Geography, Lehman 10/31/2016 322
Eleanor Roosevelt omg@gmail.com Marketing, Baruch 10/31/2016 322

Leave a Reply

Your email address will not be published. Required fields are marked *