Construct tables for relational databases

• A relational table should:

  • represent one entity
  • have a primary key
  • use suitable data types
  • include foreign keys where relationships exist
  • avoid repeating groups

• When constructing tables:

  1. Identify the entities
  2. List the attributes for each entity
  3. Choose a primary key for each table
  4. Identify foreign keys to link related tables
  5. Assign suitable data types
  6. Add constraints if needed

• Example tables:

Students

Field	Data Type	Key
StudentID	INT	PK
Name	VARCHAR(50)
DOB	DATE
Email	VARCHAR(100)

Classes

Field	Data Type	Key
ClassID	INT	PK
ClassName	VARCHAR(50)

Enrollments

Field	Data Type	Key
StudentID	INT	PK, FK
ClassID	INT	PK, FK

• Here:
  • Enrollments is a linking table
  • the composite primary key is (StudentID, ClassID)
  • both fields are also foreign keys
• Common constraints to consider:
  • NOT NULL
    • field must contain a value
  • UNIQUE
    • no duplicate values allowed
  • PRIMARY KEY
    • uniquely identifies each record
  • FOREIGN KEY
    • links tables and enforces referential integrity