Databases: Building a Relational Database

Building a Relational Database - Mr. Stevenson´s ITGS Classroom

Building a Relational Database

Bookshop Database

Task

You have been asked to set up a database for a bookstore which has their data currently stored in a Flatfile database.

The problem of redundant data occurs because the current database is a flatfile database. All the date is in one table.

Flatfile databases are only used for storing simple data.

In the example above, Publisher Country is redundant because once we know the Publisher, we know the Publisher Country.

A better way of representing the data is to create a relational database. The process of creating a relational database which has no redundant data (repeated) is known as normalization.

In the bookshop database three clear entities are represented: Books, Authors & Publishers.


You will need to create three tables:

  • Books
  • Authors
  • Publishers

Your tables should look as follows:






You now need to choose the correct Field types.


Relationships

Now you have normalised the data into three separate tables these tables must be linked, or related together.

For example when looking a t a book, we need a way to find out information about its author.

Consider the relationship between Authors & Books. There is a one-to-many relationship: one author has many books, but one book has only one author.