ERDs, data types and relational table construction
Turn a scenario into relationship rules, sensible data types and well-defined tables with the correct keys.
Design before SQL
- construct ERDs with relationships, cardinality and modality;
- outline data types, consistency and consequences of poor choices;
- construct tables with primary, foreign, composite and concatenated keys;
- explain how table design supports integrity.
Rapid Recall Deck
Attempt each card first. If a term is new, flip and study it, then answer it again without looking. Mark only secure recall as “Know it”.
ERD: clinic scenario
Cardinality + modality
A clinic has PATIENT, DOCTOR and VISIT. Each VISIT must belong to exactly one patient and exactly one doctor. A patient or doctor may currently have zero visits and can have many over time. Describe or sketch the two relationships, including both maximum cardinality and minimum/optional participation.
PATIENT 1 ↔ 0..* VISIT and DOCTOR 1 ↔ 0..* VISIT. Each VISIT participates mandatorily with exactly one patient and one doctor.
PATIENT 1 ── 0..* VISIT 0..* ── 1 DOCTOR
Data type choices
Choose types from meaning
Outline sensible relational data type categories for PatientID, PatientName, DateOfBirth, Temperature and IsInsured. Then explain one consequence of choosing an inappropriate or inconsistent type.
Reasonable choices: integer/string ID, text/string name, date, numeric/decimal temperature and Boolean insured flag. Poor types can break validation, ordering, calculations, comparisons or storage—for example free text for temperature prevents reliable numeric averaging.
Tables from the ERD
Construct the three tables
Build PATIENT, DOCTOR and VISIT. Each visit needs VisitID, date and treatment. Mark PK/FK.
PATIENT(PatID PK, PatientName, PatientDOB) DOCTOR(DocID PK, DoctorName) VISIT(VisitID PK, PatID FK, DocID FK, VisitDate, Treatment)
PatID and DocID in VISIT preserve the relationships without repeating patient/doctor descriptive data.
Composite and concatenated options
Composite key
If a patient cannot see the same doctor twice on the same date, give a possible composite VISIT key.
(PatID, DocID, VisitDate), because the combined values are guaranteed unique under the stated business rule.
Concatenated key
A legacy system builds VisitKey by joining patient, doctor and date values into one string. What must be controlled?
The format must be unique and unambiguous (for example fixed formats/separators) so different source values cannot create the same key; avoid unnecessary duplicated facts.
Design rules must survive implementation
Bad foreign key
Why is DoctorName a weaker foreign key than DocID?
Names may not be unique and can change. A stable unique DocID avoids ambiguity and repeated descriptive data.
Modality violation
The database allows VISIT with no patient. What rule is broken?
VISIT was defined as mandatory to exactly one PATIENT, so PatID should be required and reference a valid patient row.
Specimen-style relational construction
Hospital design
Construct a 3-table hospital design with primary and foreign keys.
A hospital stores PATIENT(PatID, PatientName, PatientDOB) and DOCTOR(DocID, DoctorName). Each visit has a visit date and treatment, and every visit must belong to exactly one patient and one doctor. Construct three relational tables for PATIENT, DOCTOR and VISIT, marking suitable primary and foreign keys.
PATIENT(PatID PK, PatientName, PatientDOB) DOCTOR(DocID PK, DoctorName) VISIT(VisitID PK, PatID FK, DocID FK, VisitDate, Treatment)
A composite VISIT key is also valid when the scenario guarantees a unique combination.
Finish the learning cycle
IA — main task
Continue Criterion E/final-submission work using observed product evidence.
Paper 1 — short practice
Construct one new ERD + table design with cardinality, modality, sensible data types, PKs and FKs.