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

---

Self Assessment

Outcomes

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

People

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

Addresses

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

People_xref_Addresses

xref_id	address_type	person_id	address_id
001	Home	001	002
002	Work	001	004
012	Home	002	003

Activity

Portfolio Activity

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