Difference between revisions of "LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships/English"

Spoken tutorial on LibreOffice Base Database Design : 2

Learning Objectives

Database Design: 2 (continuation of Database Design: Part 1)

4. Turn information items into columns

5. Specify primary keys

6. Set up the table relationships

This tutorial is a continuation of the previous tutorial on Database Design.

And we will learn the following topics here:

4. Turn information items into columns

5. Specify primary keys

6. Set up the table relationships

Database Design Process

Step 1. Determine the purpose of our database

Step 2. Find and organize the information required

Step 3. Divide the information into tables

Step 4. Turn information items into columns

We first determined the purpose of building the Library database.

Then we continued the process by finding and organizing the information about the library and

we divided the information into Tables.

And so, we had identified four tables in our Library database: Books, Authors, Publications and Members.

Let us now go to the next step, which is turning the information items into columns.

Here, we will decide which information item we want to store in each table.

Show BooksAuthorsPublishersTableDataNoId.ppt

Show cursor movements over the slide as narration goes forward.

As shown in the image on the screen, the Books table has 5 columns which are also called fields.

So each row or record here contains information about exactly one book in its columns.

Similarly, each record in the Authors table contains information about just one author.

And each record in the Publishers table contains information about just one publisher.

Step 4. Turn information items into columns

Refine columns based on requirements

Splitting the Author name into First Name and Last Name, helps to search or sort by these columns.

No need to store results of calculations as separate columns in tables, as Base can perform the calculations whenever we want to see the results.

For example, we can split the Author name into First Name and Last Name, so that we can search or sort by these columns.

And we don’t have to store results of calculations as separate columns in tables

because Base can perform the calculations whenever we want to see the results.

Step 5. Specify primary keys

Primary key of a table is a column or set of columns that uniquely identifies each row stored in the table.

This is often a unique identification number, such as a Book Id or an Author Id.

With primary key fields we can quickly associate logically related data from multiple tables and bring the data together

• Primary key does not allow duplicate values.

• Do not use names for primary keys, as two people can share the same name.

• A primary key should always have a value.

• If it is empty or Null, then we can’t consider it a primary key.

• We can force a primary key column to have a value always.

• Base supports the ‘AutoNumber’ data type

What is a primary key?

Each table should include a column or a set of columns that uniquely identifies each row stored in the table.

This column or set of columns is the primary key of the table.

This is often a unique identification number, such as a Book Id or an Author Id.

We can use the primary key fields is to quickly associate logically related data from multiple tables and bring the data together for us.

And we cannot have duplicate values in a primary key.

For example, we cannot use people’s names as a primary key, because names are not unique.

There can be two people with the same name in the same table.

Next, a primary key should always have a value.

If it is empty or Null, then we cannot consider it a primary key.

And we can force a primary key column to have a value always by setting the column’s data type as an ‘AutoNumber’ which Base will generate automatically.

Show BooksAuthorsPublishersTableDataWithId.ppt

Show cursor movements over the slide as narration goes forward.

BookId for Books table,

AuthorId for Authors table,

PublishersId for Publishers table

Similarly, though not shown here, MemberId will be the primary key for the Members table.

Retain slide 5 content. And make the following appear:

Base forbids use of null values in primary key columns and that enforces Entity Integrity.

Entity Integrity ensures that there are no duplicate records within the table.

It also ensures that the field that identifies each record within the table is unique and never null.

Step 6. Set up the table relationships

• Base is called a Relational Database Management System (RDBMS), as it supports setting up of table relations

• One-to-Many Relationship

Since Base supports this concept, Base is called a Relational Database Management System, RDBMS in short.

There are a few types of relationships and we’ll see them now.

First we will see what a One-to-Many relationship is.

Show BooksAuthorsPublishersTableDataWithId.ppt

Show cursor movements over the slide as narration goes forward.

A book is written by exactly one author.

Now, there are cases where two or several people co-author a single book.

But we will limit our example to just one person authoring one book.

Continuing on with our example, an author can write several books.

So for one author represented in the Authors table, there can be many books represented in the Books table written by the author.

This is therefore, a one-to-many relationship.

Show BooksAuthorsPublishersTableDataWithIdForeignKeys.ppt

Show cursor movements over the slide as narration goes forward.

By taking the Author Id which is the primary key in the Authors table and adding it to the Books table. Simple.

So the Author Id in the Books table is called the Foreign key.

Similarly the Publisher Id which is a primary key in the Publishers table becomes the foreign key in the Books table by adding it there.

Retain slide 7 content. And make the following appear:

• One-to-Many Relationship can be represented by sharing a column or a set of columns between two tables

• Table relationships are set up using Foreign keys

• A Primary Key in a table can be represented as a Foreign Key in a second table to establish the relationship.

• Referential Integrity: every foreign key value in a table will have a matching primary key value in the related tables.

And table relationships can be set up using the Foreign keys.

So a primary key in a table can be represented as a foreign key in a second table to establish the relationship.

Thereby we enforce Referential integrity.

Meaning, every foreign key value in a table will have a matching primary key value in the related tables.

Show BooksMembersDesignWithId.ppt

Show cursor movements over the slide as narration goes forward.

Let us go back to the table design now.

A book can be issued to any number of library members, (considering there are many copies available).

Similarly a member can borrow any number of books (of course, considering, books are available).

Show Many-Manyrelationship.ppt

Show cursor movements over the slide as narration goes forward.

which represents a Many-to-many relationship.

So we can represent this many-to-many relationship in our database

by creating a third table, BooksIssued table, which is also called a Junction table.

And here, we will insert the primary keys from each of the two tables - Books and Members, into the BooksIssued table.

As a result, the BooksIssued table records each book issued to a member.

Retain slide 7 content. And make the following appear:

• Many-to-Many relationships can be represented in the database, by creating a third junction table

• The Junction table will have the primary key fields from each of the related tables.

Retain slide 7 content. And make the following appear:

• One-to-one relationship

• It is represented when there are rarely used attributes/columns in a table. These can be moved to a new supplemental table with the primary key being the same as in the main table.

• Each record in the supplemental table will correspond to exactly one record in the main table.

Sometimes, some attributes or columns are specific to certain data only and so are rarely filled up with data.

Let us consider the case where only one author has a website address and the rest of them do not.

And leaving the new website column in the Authors table mostly empty, will consume disk space.

So we can move this column into a new supplemental table, whose primary key will be the same Author Id.

Each record in the supplemental table will correspond to exactly one record in the main table

which represents a One-to-one relationship.

So there, we have learnt to set up relationships in our database.

Summary:

Database Design

4. Turn information items into columns

5. Specify primary keys

6. Set up the table relationships

To summarize, we learned the following topics on database design: 4. Turn information items into columns

5. Specify primary keys

6. Set up the table relationships