Start learning →

Database design

Planning a database with an entity-relationship model, turning it into tables, and normalizing tables so that each fact is stored once.

About 7 hours of study

Learning objectives

Lessons

  1. 1

    Designing a database: the ER model

    Entities, attributes and relationships; one-to-one, one-to-many and many-to-many; participation and weak entities; and the rules that turn an ER diagram into tables.

  2. 2

    Normalization

    Why one big table goes wrong (update, insert and delete anomalies), functional dependencies, and the normal forms 1NF, 2NF, 3NF and BCNF that split it into good tables.

Module practice

Module quiz

1. How is a many-to-many relationship stored?
2. In a one-to-many relationship, where does the foreign key go?
3. A course title is changed in one row but not in the others. Which anomaly is that?
4. The key is (student_id, course), and student_name depends only on student_id. Which normal form does this break?
5. In employees(emp_id, dept_id, dept_name), dept_name depends on dept_id. What is wrong?
6. A student can have several phone numbers. What does that attribute become?
7. What makes a decomposition lossless?

‹ Algebra, subqueries and viewsIndexes and transactions ›