LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships/English
| Visual Cue | Narration |
|---|---|
| Show Slide Number 1
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 |
Welcome to the Spoken tutorial on LibreOffice Base.
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 |
| Show Slide Number 2
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 |
In the last tutorial, we started the process of database design for a simple Library application.
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 Slide Number 3
Show BooksAuthorsPublishersTableDataNoId.ppt Show cursor movements over the slide as narration goes forward. |
Each information item which we identified earlier, becomes a field, and is displayed as a column in the table.
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. |
| Show Slide Number 4
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. |
Now, we can further refine the columns based on our requirements.
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. |
| Show Slide Number 5
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
|
Now that we are clear about tables and columns, let us see how we can specify primary keys next.
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 Slide Number 6
Show BooksAuthorsPublishersTableDataWithId.ppt Show cursor movements over the slide as narration goes forward. |
As shown in the image on the screen, we can set up the primary keys for our tables as follows:
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. |
| Go back to Slide Number 5
Retain slide 5 content. And make the following appear: Base forbids use of null values in primary key columns and that enforces Entity Integrity. |
Finally, by setting up the primary keys in the tables, we are enforcing 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. |
| Show Slide Number 7
Step 6. Set up the table relationships
|
Now that we have the primary keys in the three tables, we can bring them all together by setting up the relationships.
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 Slide Number 6 again
Show BooksAuthorsPublishersTableDataWithId.ppt Show cursor movements over the slide as narration goes forward. |
Let us consider the Books and the Authors tables in the image shown now.
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 Slide Number 8
Show BooksAuthorsPublishersTableDataWithIdForeignKeys.ppt Show cursor movements over the slide as narration goes forward. |
And we can represent this in our Library database
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. |
| Show Slide Number 7 again.
Retain slide 7 content. And make the following appear:
|
So by sharing a column or a set of columns, we can represent one-to-many relationships in the database.
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 Slide Number 9
Show BooksMembersDesignWithId.ppt Show cursor movements over the slide as narration goes forward. |
Next, let us see what a Many-to-Many relationship is.
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 Slide Number 10
Show Many-Manyrelationship.ppt Show cursor movements over the slide as narration goes forward. |
So here we have an example of multiple books being issued to multiple members
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. |
| Show Slide Number 7 again.
Retain slide 7 content. And make the following appear:
|
So by creating a third junction table, we can represent many-to-many relationships. |
| Show Slide Number 7 again.
Retain slide 7 content. And make the following appear:
|
And finally there is the One-to-one relationship.
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. |
| Show Slide Number 11
Summary: Database Design 4. Turn information items into columns 5. Specify primary keys 6. Set up the table relationships |
This brings us to the end of this tutorial on the Second part of Database Design in LibreOffice Base
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 |
| Acknowledgement Slide | Spoken Tutorial Project is a part of the Talk to a Teacher project, supported by the National Mission on Education through ICT, MHRD, Government of India. This project is co-ordinated by http://spoken-tutorial.org. More information on the same is available at the following link http://spoken-tutorial.org/NMEICT-Intro. This script has been contributed by Priya Suresh, DesiCrew Solutions. signing off. Thanks for joining. |
