Data modelling and schema design
You derive entities and relationships from a business’s needs, draw an ER diagram, remove duplication and inconsistency through normalisation and let constraints enforce correctness in the database.
Level 201 · 18 lessons · 15 practice labs
Lessons
The conceptual model
- From requirements to entities
- Drawing an ER diagram
- Relationship types: one to one, one to many, many to many
- Many-to-many with junction tables
- Short task: an ER diagram for a vet clinic
Normalisation
- Insert, update and delete anomalies
- First normal form
- Second and third normal form
- Boyce-Codd normal form
- Deliberate denormalisation
- Short task: normalise a spreadsheet
Physical design
- Choosing the right data type
- Natural and surrogate keys
- Enforcing rules with CHECK, UNIQUE and NOT NULL
- Foreign key actions: CASCADE and RESTRICT
- Naming conventions
- Deleted records and history tables
- Capstone: the schema of an event ticketing system
Practice labs
- Extract entities for a gym: List the entities and attributes from Ilgın Spor's requirements text.
- Draw an ER diagram for course enrolment: Draw Çamlık Okulu's student, course and teacher relationships with correct cardinalities and turn them into tables.
- Add attributes to a many-to-many link: Model the player-team relationship with a junction table that holds season and shirt number.
- Split out repeating columns: Move phone1, phone2 and phone3 columns into a separate table that meets first normal form.
- Remove an update anomaly: The supplier address repeats on every product row; bring it to third normal form, proving and removing the anomaly.
- Normalise a sales sheet step by step: Decompose Zeytin Kooperatifi's single-sheet sales table step by step from 1NF to BCNF.
- Store or compute the order total: Measure query speed for storing versus computing the order total and decide.
- Keep a summary table consistent: Set up a method that keeps a daily sales summary table consistent with changes to the main table.
- Reconcile drifting duplicated data: Find records where a deliberately duplicated customer name diverged between two tables and write a repair rule.
- Pick the right type for money: Show that prices stored as FLOAT lose cents in totals and switch them to DECIMAL.
- Natural key or surrogate key: Redesign a schema keyed on a changeable email address using a surrogate key and a UNIQUE constraint.
- Enforce rules with constraints: Ticket price must be positive, seats unique and end after start; enforce these with CHECK and UNIQUE and test them.
- Decide what happens to a deleted customer's orders: Apply RESTRICT and CASCADE to a foreign key in turn and compare the results.
- Add soft deletes: Add a deleted flag to Selvi Emlak's listings table and write a view that shows active listings correctly.
- Keep a dated price history: Build a table that keeps product price history with validity dates and query the price on a past date.
About this course
You derive entities and relationships from a business’s needs, draw an ER diagram, remove duplication and inconsistency through normalisation and let constraints enforce correctness in the database.
What you will learn
- The conceptual model
- Normalisation
- Physical design
Prerequisites
Finishing the level 101 courses of the same path, or knowing those topics, is enough.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 201; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.