1 / 29

Week 10

Database Design . Week 10. Agenda – Week 10. Review Hybrid Review Primary Keys Table Instance Charts. 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.

kalei
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 Primary Keys Table Instance Charts

  3. 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

  4. Review Solution to Hybrid Homework

  5. Relationships

  6. Three Basic Types of Relationships • One to One (1:1) • One to Many (1:M) • Many to Many (M:M)

  7. 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

  8. 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.

  9. 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

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

  11. 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

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

  13. 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

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

  15. Exercise: ERD & Relationships • Describe the relationships in the curling bonspiel

  16. 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

  17. Types of Data

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

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

  20. 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.

  21. 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)

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

  23. 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

  24. Table Instance Charts

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

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

  27. Exercise • Create table instance chart for: • Curling Bonspiel scenario

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

  29. Quiz Time

More Related