LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships/English-timed
From Script | Spoken-Tutorial
Revision as of 22:36, 4 October 2015 by Sandhya.np14 (Talk | contribs)
Time | Narration |
00:00 | Welcome to the Spoken tutorial on LibreOffice Base. |
00:04 | This tutorial is a continuation of the previous tutorial on Database Design. |
00:10 | And we will learn the following topics here: |
00:13 | * Turn information items into columns |
00:17 | * Specify primary keys |
00:20 | * Set up the table relationships. |
00:23 | In the last tutorial, we started the process of database design for a simple Library application. |
00:30 | We first determined the purpose of building the Library database. |
00:36 | Then we continued the process by finding and organizing the information about the library and |
00:44 | we divided the information into Tables. |
00:49 | And so, we had identified four tables in our Library database: Books, Authors, Publications and Members. |
01:00 | Let us now go to the next step which is turning the information items into columns. |
01:07 | Here, we will decide which information item we want to store in each table. |
01:13 | Each information item which we identified earlier, becomes a field and is displayed as a column in the table. |
01:23 | As shown in the image on the screen, the Books table has 5 columns which are also called fields. |
01:31 | So, each row or record here contains information about exactly one book in its columns. |
01:40 | Similarly, each record in the Authors table contains information about just one author. |
01:49 | And each record in the Publishers table contains information about just one publisher. |
01:58 | Now, we can further refine the columns based on our requirements. |
02:04 | For example, we can split the Author name into First Name and Last Name so that we can search or sort by these columns. |
02:17 | And, we don’t have to store results of calculations as separate columns in tables |
02:24 | because Base can perform the calculations whenever we want to see the results. |
02:31 | Now that we are clear about tables and columns, let us see how we can specify primary keys next. |
02:41 | What is a primary key? |
02:44 | Each table should include a column or a set of columns that uniquely identifies each row stored in the table. |
02:54 | This column or set of columns is the primary key of the table. |
03:00 | This is often a unique identification number such as a Book Id or an Author Id. |
03:08 | We can use the 'primary key' fields to quickly associate logical related data from multiple tables and bring the data together for us. |
03:21 | And we cannot have duplicate values in a primary key. |
03:26 | For example, we cannot use people’s names as a primary key because names are not unique. |
03:34 | There can be two people with the same name in the same table. |
03:40 | Next, a primary key should always have a value. |
03:45 | If it is empty or Null then we cannot consider it a 'primary key'. |
03:52 | 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. |
04:09 | As shown in the image on the screen, we can set up the primary keys for our tables as follows: |
04:20 | BookId for Books table, |
04:24 | AuthorId for Authors table, |
04:28 | PublishersId for Publishers table. |
04:33 | Similarly, though not shown here, MemberId will be the primary key for the Members table. |
04:42 | Finally, by setting up the primary keys in the tables, we are enforcing Entity Integrity. |
04:52 | 'Entity Integrity' ensures that there are no duplicate records within the table. |
05:00 | It also ensures that the field that identifies each record within the table is unique and never dull. |
05:10 | Now that we have the primary keys in the three tables, we can bring them all together by setting up the relationships. |
05:20 | Since Base supports this concept, Base is called a Relational Database Management System, RDBMS in short. |
05:32 | There are a few types of relationships and we’ll see them now. |
05:37 | First we will see what a One-to-Many relationship is. |
05:43 | Let us consider the Books and the Authors tables in the image shown now. |
05:49 | A book is written by exactly one author. |
05:55 | Now, there are cases where two or several people co-author a single book. |
06:02 | But we will limit our example to just one person authoring one book. |
06:10 | Continuing on with our example, an author can write several books. |
06:17 | So, for one author represented in the Authors table, there can be many books represented in the Books table written by the author. |
06:28 | This is therefore, a one-to-many relationship. |
06:32 | And we can represent this in our 'Library database'. |
06:36 | By taking the Author Id which is the 'primary key' in the Authors table and adding it to the Books table. Simple. |
06:46 | So, the Author Id in the Books table is called the Foreign key. |
06:53 | Similarly, the Publisher Id which is the primary key in the Publishers table becomes the foreign key in the Books table by adding it there. |
07:06 | So, by sharing a column or a set of columns, we can represent one-to-many relationships in the database. |
07:17 | And table relationships can be set up using the Foreign keys. |
07:23 | So, a 'primary key' in a table can be represented as a 'foreign key' in a second table to establish the relationship. |
07:34 | Thereby we enforce Referential integrity. |
07:39 | Meaning, every foreign key value in a table will have a matching primary key value in the related tables. |
07:50 | Next, let us see what a Many-to-Many relationship is. |
07:56 | Let us go back to the table design now. |
07:59 | A book can be issued to any number of library members (considering there are many copies available). |
08:09 | Similarly, a member can borrow any number of books (of course, considering books are available). |
08:17 | So, here we have an example of multiple books being issued to multiple members |
08:25 | which represents a Many-to-many relationship. |
08:29 | So, we can represent this many-to-many relationship in our database |
08:35 | by creating a third table, BooksIssued table, which is also called a Junction table. |
08:45 | And here, we will insert the primary keys from each of the two tables, Books and Members, into the BooksIssued table. |
08:57 | As a result, the BooksIssued table records each book issued to a member. |
09:05 | So, by creating a third junction table we can represent many-to-many relationships. |
09:13 | And finally there is the One-to-one relationship. |
09:18 | Sometimes, some attributes or columns are specific to certain data only and so are rarely filled up with data. |
09:30 | Let us consider the case where only one author has a website address and the rest of them do not. |
09:38 | And leaving the new website column in the Authors table mostly empty, we will consume disk space. |
09:47 | So, we can move this column into a new supplemental table whose primary key will be the same Author Id. |
09:58 | Each record in the supplemental table will correspond to exactly one record in the main table |
10:06 | which represents a One-to-one relationship. |
10:10 | So there, we have learnt to set up relationships in our database. |
10:15 | This brings us to the end of this tutorial on the second part of Database Design in LibreOffice Base. |
10:23 | To summarize, we learned the following topics on database design: |
10:28 | * Turn information items into columns |
10:32 | * Specify primary keys |
10:34 | * Set up the table relationships |
10:38 | 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. |
10:48 | This project is coordinated by http://spoken-tutorial.org. |
10:54 | More information on the same is available at the following link. |
10:58 | This script has been contributed by Priya Suresh, DesiCrew Solutions. And this is Soundharya, DesiCrew Solutions, signing off.
Thanks for joining. |