Designing a Relational Database

Key Terms

This list of key words are explained using every day language.

  • Schema is the structure (architecture) of the whole database
  • Entity is a table
  • Attribute is a column
  • Tuple is a row or record
  • Data Type is the type of data which will be stored in the attribute
  • Primary Key is a unique number value
  • Entity Integrity means every table has a primary key (Codd’s Rule 2)
  • Foreign Key is a primary key from a different table
  • Referential Integrity means tables are connected using primary key (Codd’s Rule 10)
  • Entity Relationship how two tables are connected
  • First Normal Form (1NF) group your attributes in a meaningful way and add a primary key
  • Second Normal From (2NF) remove all duplicate attributes and rows
  • Third Normal From (3NF) remove any attributes which can be created using a SQL query

Task One: What data do I need?

You have been asked to create a booking systems for a local taxi which will be used internally by the phone operator only.

  1. Make a list of attributes (data) which you will need
  2. Group the attributes in a logical way e.g. Booking, Drivers (1NF)
  3. Add a primary key to each group of attributes (1NF)
  4. Remove any duplicate attributes from all groups (2NF)