Database Design

For 3rd Semester Multimedia Designer

In this lecture you’ll learn:

  • How to use phpmyadmin (?)
  • How to design a database
  • How to normalize a database
  • Preparations before implementing a database

Tools

Agenda

The Workshop

(individual or in groups of max three students)

Design a research database for multimedia design students. The database should be able to produce a list of sources ordered by author for an academic report.

In “How to Write a Thesis” Umberto Eco suggests that the student should make a file system on cardboard – and organize the system in archive boxes. In our time this is a perfect job for a database. Let’s try to construct a database that is able to handle notes and print the lists that you need in a report.

Normal form one

Use the “Rules of Data Normalization” poster as inspiration. Start with a sketch of the entire system. List up whatever the entire system should be able to do.

Start with a list of unnormalized items. When you’re satisfied with the list begin to eliminate repeating groups. Make a separate entity for each group. Give the groups attributes. Remember to add primary keys.

Pizza from unnormalized list to first normal form
Pizza from unnormalized list to first normal form

 

Normal form two

“Eliminate redundant (repeating) data. Remove them to a separate table.”

In the list many items are repeating. Sizes or toppings could be separate tables. We get something like this:

Pizza normal form two.
Pizza normal form two.

 

Normal form three

“Eliminate the columns that are not dependent on a key.”

If the attributes do not contribute to the description of the entity, remove them to a separate entity.

Pizzerias in the third normal form.
Pizzerias in the third normal form.

In the example above I have removed the contact informations of the pizzeria to a separate table. An address has nothing to do with a pizza, so it is more logic til have these informations in a separate table. If the pizzeria is part of a chain, then all pizzerias would be able to fit into this model.

At this point the database should work. If you dare to and want to it is permissible to continue to the fourth and fifth normal form.

Visualize the entire system

A good ER-diagram will visualize the entire organization.
A good ER-diagram will visualize the entire organization.

The ER-diagram should be able to handle the entire workflow. So you can draw in what’s happening from the client makes a call to the pizzeria to the finished delivery of the pizza.

Here you can get inspiration from the traditional UML “Use Case Scenario”.