CCNC/CCNC Module 5/The database application/Database Design/Self Assessment

From WikiEducator
Jump to: navigation, search
Tutorial.png Database Design 

Database Keys | Database Indexes | Tables and Relationships | Self Assessment | Summary & FAQs

Self Assessment

Icon objectives.jpg


Upon completion of this self assessment the learner will:
  • Assess their understanding of database keys, indexes and table relationships
  • Identify candidate keys for a given database scenario
  • Select primary keys and foreign keys from candidate keys
  • Identify table indexes to increase query performance
  • Describe the rationale behind table relationships

Example Tables

person_id first_name last_name birth_date gender
001 Bill Smith 28/12/1963 M
005 Fiona Jones 26/10/1961 F
012 Lisa Ballard 11/03/1965 F
address_id address_line1 address_line2 city_name region country post_code
001 1234 Main Street PO Box 1340 Bowen Island BC CANADA V0N1G0
002 #302 1200 Water Street Vancouver BC CANADA V7G1N4
003 450 Gower Street St. John's NL CANADA A1C2H7
004 230 Commerce Way Richmond BC CANADA V6E1H7
xref_id address_type person_id address_id
001 Home 001 002
002 Work 001 004
012 Home 002 003


Icon activity.jpg

Portfolio Activity

  1. After reviewing the above three tables identify all the possible candidate keys.
  2. From this list of candidate keys identify the primary and foreign keys.
  3. From this list of candidate keys identify the fields that could be indexed to increase query performance.
  4. Using pencil and paper write a description of the table relationships for the above three tables.