Career Data Exploration Project

Teams of 3 - 4 people

Objective: Learn about the types of data you will encounter in a specific industry. Map out the relationships of some major entities and their attributes. Understand how to create and read an Entity Relationship diagram. Hands on practice with Access tables and queries.

• Process:
• Pick an industry you will explore. Each group needs to choose a different industry.
• Speak to people who work in that industry or are studying that industry to find out what information their companies track.
• Of that information, identify at least 6 entities. Include at least one entity that holds transactions.
  • For example, some entities in a school are students, professors, classes, scholarships, invoices.
  • Hint 1: When you describe the business and what is done each day, the nouns in your description may be entities.
  • Hint 2: Every fact that is required to support the business is probably an attribute attached to some entity
• List some major attributes of each entity
  • Think of the entity as a spreadsheet and determine the columns you would want for each row. For example, if you had a spreadsheet of customers, you would probably want the first name, last name, address, city, state, zip, and phone number for every customer. Those would be attributes of the customer
  • Make your attributes small enough so you would never want to sort by only a part of the attribute. (For example: separate name into first and last names so that you can sort by either part.)
• Determine a key for each entity
  • A key is an attribute (or group of attributes) that will uniquely identify the entity
  • Choose the fewest number of attributes that will uniquely identify the entity
• Figure out which entities relate to each other.
  • Draw a box for each entity and a line between any two boxes that interact with each other directly.
  • (If you want, you can put a phrase to indicate the relationship between the two entities with the left to right relationship above the line and the right to left relationship under the line.)
• Determine the relationship cardinality:
  • To determine the left side's as 1 or Many: Choose "many" or "one" in this sentence: For every row in the <left> entity spreadsheet, there can be <many> < no more than one> matching row in the <right> entity spreadsheet. (If the answer is many, the right side line should end in a crows foot. If the answer is "no more than one", the right side line should just be a single line.)
  • Do the reverse for the right side.
• Determine relationship participation:
  • To determine the left side's participation: Choose "must" or "can" in this sentence: For every row in the <left> entity spreadsheet, there <must> <can> be at least one matching row in the <right> entity spreadsheet. (If the answer is must, the right side line should be solid and have no "o". If the answer is "can", the right side line should either be dotted or have an "o".)
• Draw your ERD diagram.
  • The attributes can be listed inside the entity box.
  • Underline the key attributes.
  • This can be done on paper and should be done in pencil.
• Create an Access Project
  • Create each table on your ERD. An entity will be a table. Each table should have a good primary key.
  • Enter some records into each table.
  • Make queries that join tables in a way that you think people in the industry would like to see the data.
    • Be sure to join the tables the way they were joined in your ERD.
    • Make enough queries so that each table is used at least once
      • If you want a query to do something you don't know how to do, such as count the number of records, please ask.
    • Example: A school might want a list of students, classes and professors, with full names of both the students and professors. This requires joining 3 tables: students, classes and employees.

Helpful website: http://www.getahead-direct.com/gwentrel.htm

Projected timeline:

11/16 - Learn about Entity Relationship Diagrams; Last 15 minutes of class: Form groups; choose industry.

From 11/16 - 11/22: Research your industry by asking questions of people in the industry and possibly professors. Share your industry information descriptions with your group and try to pick out entities. This can all be done via the moodle discussion board. Try to have identified at least a few entities by Thursday 11/18.

11/18 - Learn about Access tables and queries. The last 15 minutes of class will be to let you brainstorm about your Entity Relationship Diagrams.

11/23 - Come to class with an Entity Relationship Diagram complete. Entire class: Work on creating the tables and queries in class. Leave class with a list of queries you intend to create before 11/30.

11/23 - 11/30: Complete the ERD and Access database.

11/30 & 12/2 - Come to class with the ERD and Access database complete and ready to present. Group presentations of the ERD and Access databases. You can draw the ERD on the blackboard as part of the presentation. You can also show the Access database and queries.

(The remaining time on 12/2 will be used for the quiz review. The quiz on 12/7 will contain questions on other people's presentations as well as on how to use Access.)