1 / 37

Week 10

Database Design . Week 10. Agenda – Week 10. Review Hybrid Review Table Instance Charts Primary Keys Normalization. Database Design Steps Review. Determine the purpose of the database Determine the entities Determine the fields & keys Determine the relationships Refine the design

buffy
Download Presentation

Week 10

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Design Week 10

  2. Agenda – Week 10 Review Hybrid Review Table Instance Charts Primary Keys Normalization

  3. Database Design Steps Review Determine the purpose of the database Determine the entities Determine the fields & keys Determine the relationships Refine the design This is an iterative process that is completed several times

  4. Review Entities are…. Attributes are…. Primary key is….. Foreign key is …… Information is accessed according to its structure, not its location Use keys to identify relationships

  5. Planning the Design • ERD – • Serves as a blueprint for implementation • Reduces problems resulting from poor design • Determines how data is stored • Ensures that the design meets the user’s needs

  6. Review Solution to Hybrid Homework

  7. Table Instance Charts

  8. Table Instance Chart Sample • One chart completed for each table • Wk10_TableInstanceChart.xlsx

  9. Exercise • Create table instance chart for: • the puzzle (together)

  10. Types of Data

  11. Common Data Types in Access Autonumber Text Memo Number Currency Date/Time Yes/No

  12. Autonumber Use for unique sequential numbering Commonly used for PK Increments by 1 Automatically inserted when a record is added.

  13. Text Used for text or combinations of text and numbers, such as addresses, or for numbers that do not require calculations, such as phone numbers, part numbers, or postal codes. Stores up to 255 characters.

  14. Memo Use for lengthy text and numbers, such as notes or descriptions. Should be used sparingly Stores up to 65,536 characters (if not more)

  15. Number Use for data to be included in mathematical calculations, except calculations involving money (use Currency type).

  16. Yes/No • Use for data that can be only one of two possible values, such as • Yes/No, • True/False, • On/Off. • Null values are not allowed

  17. Exercise • Create table instance chart for: • Wine Database • Curling Bonspiel scenario

  18. Relationships

  19. Three Basic Types of Relationships • One to One (1:1) • One to Many (1:M) • Many to Many (M:M) • Called the cardinality of the relationship • The cardinality indicates the maximum number of relationships between the entities, therefore "many" can be explained as "one or many" or sometimes "zero, one, or many".

  20. One to One Relationships 1:1 relationships exists when a single record in one table has 1 and only 1 corresponding record in another table, and vice versa

  21. One to One Example Voter Ballot A voter can cast only one vote in an election. A ballot paper can belong to only one voter. So there will be a 1:1 relationship between a Voter and a Ballot Paper.

  22. One to Many Relationships Most common 1:m or m:1 Relationship exists when a record can relate to 1 or more records in a second table but.. A record in the 2nd table can only relate to 1 record in the first table

  23. One to Many Example Owner Vehicle A person can own more than one car. A car can only have one owner.

  24. Many to Many Relationships M:M Relationship exists when a record in either table can relate to more than one record in the other table

  25. Many to Many Example Student Professor A student can have more than one professor; the same professor can have many students

  26. Resolving Many to Many Student Stud_Prof Professor M:M is not allowed We create a junction or joiner entity The name of the joiner entity is often a combination of the 2 entities

  27. Many to Many Relationships

  28. Relationship Review

  29. Normalization Refining the ERD Task of analyzing entities and the relationships created have been formalized into a process called normalization.

  30. Exercise: ERD & Relationships • Draw an ERD for the puzzle • Describe the relationships using the following format

  31. Exercise: ERD & Relationships • Draw an ERD for the wine scenario • Describe the relationships using the following format • Describe the relationships in the curling bonspiel

  32. Review Relationship Essentials • Need a primary key • Need a foreign key • A foreign key is a column in a table used to reference a primary key in another table. • Not all tables will require a foreign key • Primary key and foreign keys are essential when you create relationships that join together multiple tables in a database

  33. Build Time • For each of the following scenarios: • Create and write out a business narrative • Create list of tables • Create the attributes for the table • Create a simple ERD and the relationship rules • Create a table instance chart

  34. Scenarios • Political Canvassing • Human Resource Dept. • Private Elementary School • Yoga Studio *** note each database should be around 5 tables

  35. Access DB • Create a database • Create tables • Build relationships • Populate tables

  36. Hybrid / Homework • Hybrid: Refer to the 14 Week schedule • Database Assignment Part 1

  37. Quiz Time

More Related