Difference between revisions of "LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships/English-timed"
From Script | Spoken-Tutorial
PoojaMoolya (Talk | contribs) |
|||
(2 intermediate revisions by one other user not shown) | |||
Line 5: | Line 5: | ||
|- | |- | ||
|00:00 | |00:00 | ||
− | |Welcome to the Spoken tutorial on LibreOffice Base. | + | |Welcome to the '''Spoken tutorial''' on '''LibreOffice Base'''. |
|- | |- | ||
|00:04 | |00:04 | ||
− | |This tutorial is a continuation of the previous tutorial on Database Design. | + | |This tutorial is a continuation of the previous tutorial on '''Database Design'''. |
|- | |- | ||
Line 17: | Line 17: | ||
|- | |- | ||
|00:13 | |00:13 | ||
− | |Turn information items into | + | |Turn information items into '''column'''s |
|- | |- | ||
|00:17 | |00:17 | ||
− | |Specify primary keys | + | |Specify '''primary keys''' |
|- | |- | ||
|00:20 | |00:20 | ||
− | |Set up the table relationships | + | |Set up the '''table relationships'''. |
|- | |- | ||
|00:23 | |00:23 | ||
− | |In the last tutorial, we started the process of database design for a simple Library application. | + | |In the last tutorial, we started the process of database design for a simple '''Library application'''. |
|- | |- | ||
|00:30 | |00:30 | ||
− | |We first determined the purpose of building the Library database. | + | |We first determined the purpose of building the '''Library database'''. |
|- | |- | ||
Line 41: | Line 41: | ||
|- | |- | ||
|00:44 | |00:44 | ||
− | |we divided the information into Tables. | + | |we divided the information into '''Tables'''. |
|- | |- | ||
|00:49 | |00:49 | ||
− | |And so, we had identified four tables in our Library database: Books, Authors, Publications and Members. | + | |And so, we had identified four tables in our Library database: '''Books, Authors, Publications''' and '''Members'''. |
|- | |- | ||
|01:00 | |01:00 | ||
− | |Let us now go to the next step | + | |Let us now go to the next step which is turning the information items into '''columns'''. |
|- | |- | ||
|01:07 | |01:07 | ||
− | |Here, we will decide which information item we want to store in each table. | + | |Here, we will decide which information item we want to store in each '''table'''. |
|- | |- | ||
|01:13 | |01:13 | ||
− | |Each information item which we identified earlier, becomes a field | + | |Each information item which we identified earlier, becomes a '''field''' and is displayed as a column in the table. |
|- | |- | ||
|01:23 | |01:23 | ||
− | |As shown in the image on the screen, the Books table has 5 | + | |As shown in the image on the screen, the '''Books''' table has 5 '''column'''s which are also called '''fields'''. |
|- | |- | ||
|01:31 | |01:31 | ||
− | |So each row or record here contains information about exactly one book in its columns. | + | |So, each row or '''record''' here contains information about exactly one book in its columns. |
|- | |- | ||
|01:40 | |01:40 | ||
− | |Similarly, each record in the Authors table contains information about just one author. | + | |Similarly, each record in the '''Authors''' table contains information about just one author. |
|- | |- | ||
|01:49 | |01:49 | ||
− | |And each record in the Publishers table contains information about just one publisher. | + | |And each record in the '''Publishers''' table contains information about just one publisher. |
|- | |- | ||
Line 81: | Line 81: | ||
|- | |- | ||
|02:04 | |02:04 | ||
− | |For example, we can split the Author name into First Name and Last Name | + | |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 | |02:17 | ||
− | |And we don’t have to store results of calculations as separate columns in tables | + | |And, we don’t have to store results of calculations as separate columns in tables |
|- | |- | ||
|02:24 | |02:24 | ||
− | |because Base can perform the calculations whenever we want to see the results. | + | |because '''Base''' can perform the calculations whenever we want to see the results. |
|- | |- | ||
|02:31 | |02:31 | ||
− | |Now that we are clear about tables and columns, let us see how we can specify primary keys next. | + | |Now that we are clear about tables and columns, let us see how we can specify '''primary keys''' next. |
|- | |- | ||
|02:41 | |02:41 | ||
− | |What is a primary key? | + | |What is a '''primary key'''? |
|- | |- | ||
|02:44 | |02:44 | ||
− | |Each table should include a column or a set of columns that uniquely identifies each row stored in the table. | + | |Each '''table''' should include a column or a set of columns that uniquely identifies each row stored in the table. |
|- | |- | ||
|02:54 | |02:54 | ||
− | |This column or set of columns is the primary key of the table. | + | |This column or set of columns is the '''primary key''' of the '''table'''. |
|- | |- | ||
|03:00 | |03:00 | ||
− | |This is often a unique identification number | + | |This is often a unique identification number such as a '''Book Id''' or an '''Author Id'''. |
|- | |- | ||
|03:08 | |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. | + | |We can use the 'primary key' fields to quickly associate logical related data from multiple tables and bring the data together for us. |
|- | |- | ||
Line 121: | Line 121: | ||
|- | |- | ||
|03:26 | |03:26 | ||
− | |For example, we cannot use people’s names as a primary key | + | |For example, we cannot use people’s names as a primary key because names are not unique. |
|- | |- | ||
Line 129: | Line 129: | ||
|- | |- | ||
|03:40 | |03:40 | ||
− | |Next, a primary key should always have a value. | + | |Next, a '''primary key''' should always have a value. |
|- | |- | ||
|03:45 | |03:45 | ||
− | |If it is empty or Null | + | |If it is empty or '''Null''' then we cannot consider it a 'primary key'. |
|- | |- | ||
|03:52 | |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. | + | |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. |
|- | |- | ||
Line 145: | Line 145: | ||
|- | |- | ||
|04:20 | |04:20 | ||
− | |BookId for Books table, | + | |'''BookId''' for '''Books''' table, |
|- | |- | ||
|04:24 | |04:24 | ||
− | |AuthorId for Authors table, | + | |'''AuthorId''' for '''Authors''' table, |
|- | |- | ||
|04:28 | |04:28 | ||
− | |PublishersId for Publishers table | + | |'''PublishersId''' for '''Publishers table'''. |
|- | |- | ||
|04:33 | |04:33 | ||
− | |Similarly, though not shown here, MemberId will be the primary key for the Members table. | + | |Similarly, though not shown here, '''MemberId''' will be the primary key for the '''Members''' table. |
|- | |- | ||
|04:42 | |04:42 | ||
− | |Finally, by setting up the primary keys in the tables, we are enforcing Entity Integrity. | + | |Finally, by setting up the primary keys in the tables, we are enforcing '''Entity Integrity'''. |
|- | |- | ||
|04:52 | |04:52 | ||
− | |Entity Integrity ensures that there are no duplicate records within the table. | + | |'Entity Integrity' ensures that there are no duplicate records within the table. |
|- | |- | ||
|05:00 | |05:00 | ||
− | |It also ensures that the field that identifies each record within the table is unique and never | + | |It also ensures that the '''field''' that identifies each record, within the table, is unique and never Null. |
|- | |- | ||
|05:10 | |05:10 | ||
− | | Now that we have the primary keys in the three tables, we can bring them all together by setting up the | + | | Now that we have the primary keys in the three tables, we can bring them all together by setting up the '''relationship'''s. |
|- | |- | ||
|05:20 | |05:20 | ||
− | |Since Base supports this concept, Base is called a Relational Database Management System, RDBMS in short. | + | |Since '''Base''' supports this concept, Base is called a '''Relational Database Management System, RDBMS''' in short. |
|- | |- | ||
|05:32 | |05:32 | ||
− | |There are a few types of | + | |There are a few types of '''relationship'''s and we’ll see them now. |
|- | |- | ||
|05:37 | |05:37 | ||
− | |First we will see what a One-to-Many relationship is. | + | |First we will see what a '''One-to-Many relationship''' is. |
|- | |- | ||
|05:43 | |05:43 | ||
− | |Let us consider the Books and the Authors tables in the image shown now. | + | |Let us consider the '''Books''' and the '''Authors''' tables in the image shown now. |
|- | |- | ||
Line 209: | Line 209: | ||
|- | |- | ||
|06:17 | |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. | + | |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 | |06:28 | ||
− | |This is therefore, a one-to-many relationship. | + | |This is therefore, a '''one-to-many relationship'''. |
|- | |- | ||
|06:32 | |06:32 | ||
− | |And we can represent this in our Library database | + | |And we can represent this in our 'Library database'. |
|- | |- | ||
|06:36 | |06:36 | ||
− | |By taking the Author Id which is the primary key in the Authors table and adding it to the Books table. Simple. | + | |By taking the '''Author Id''' which is the 'primary key' in the '''Authors''' table and adding it to the '''Books''' table. Simple. |
|- | |- | ||
|06:46 | |06:46 | ||
− | |So the Author Id in the Books table is called the Foreign key. | + | |So, the '''Author Id''' in the '''Books''' table is called the '''Foreign key'''. |
|- | |- | ||
|06:53 | |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. | + | |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 | |07:06 | ||
− | |So by sharing a column or a set of columns, we can represent one-to-many | + | |So, by sharing a column or a set of columns, we can represent '''one-to-many relationship'''s in the database. |
|- | |- | ||
|07:17 | |07:17 | ||
− | |And table | + | |And '''table relationship'''s can be set up using the '''Foreign keys'''. |
|- | |- | ||
|07:23 | |07:23 | ||
− | |So a primary key in a table can be represented as a foreign key in a second table to establish the relationship. | + | |So, a 'primary key' in a table can be represented as a 'foreign key' in a second table to establish the '''relationship'''. |
|- | |- | ||
|07:34 | |07:34 | ||
− | |Thereby we enforce Referential integrity. | + | |Thereby we enforce '''Referential integrity'''. |
|- | |- | ||
Line 253: | Line 253: | ||
|- | |- | ||
|07:50 | |07:50 | ||
− | |Next, let us see what a Many-to-Many relationship is. | + | |Next, let us see what a '''Many-to-Many relationship''' is. |
|- | |- | ||
|07:56 | |07:56 | ||
− | |Let us go back to the table design now. | + | |Let us go back to the '''table design''' now. |
|- | |- | ||
|07:59 | |07:59 | ||
− | |A book can be issued to any number of library members | + | |A book can be issued to any number of library members (considering there are many copies available). |
|- | |- | ||
|08:09 | |08:09 | ||
− | |Similarly a member can borrow any number of books (of course, considering | + | |Similarly, a member can borrow any number of books (of course, considering books are available). |
|- | |- | ||
|08:17 | |08:17 | ||
− | |So here we have an example of multiple books being issued to multiple members | + | |So, here we have an example of multiple books being issued to multiple members |
|- | |- | ||
|08:25 | |08:25 | ||
− | |which represents a Many-to-many relationship. | + | |which represents a '''Many-to-many relationship'''. |
|- | |- | ||
|08:29 | |08:29 | ||
− | |So we can represent this many-to-many relationship in our database | + | |So, we can represent this '''many-to-many relationship''' in our database |
|- | |- | ||
|08:35 | |08:35 | ||
− | |by creating a third table, BooksIssued table, which is also called a Junction table. | + | |by creating a third table, '''BooksIssued''' table, which is also called a '''Junction table'''. |
|- | |- | ||
|08:45 | |08:45 | ||
− | |And here, we will insert the primary keys from each of the two tables | + | |And here, we will insert the primary keys from each of the two tables, '''Books''' and '''Members''', into the '''BooksIssued''' table. |
|- | |- | ||
|08:57 | |08:57 | ||
− | |As a result, the BooksIssued table records each book issued to a member. | + | |As a result, the '''BooksIssued''' table records each book issued to a member. |
|- | |- | ||
|09:05 | |09:05 | ||
− | |So by creating a third junction table | + | |So, by creating a third junction table we can represent '''many-to-many relationship'''s. |
|- | |- | ||
|09:13 | |09:13 | ||
− | |And finally there is the One-to-one relationship. | + | |And finally there is the '''One-to-one relationship'''. |
|- | |- | ||
Line 309: | Line 309: | ||
|- | |- | ||
|09:38 | |09:38 | ||
− | |And leaving the new website column in the Authors table mostly empty, we will consume disk space. | + | |And leaving the new website column in the '''Authors''' table mostly empty, we will consume disk space. |
|- | |- | ||
|09:47 | |09:47 | ||
− | |So we can move this column into a new supplemental table | + | |So, we can move this column into a new supplemental table whose primary key will be the same '''Author Id'''. |
|- | |- | ||
Line 321: | Line 321: | ||
|- | |- | ||
|10:06 | |10:06 | ||
− | |which represents a One-to-one relationship. | + | |which represents a '''One-to-one relationship'''. |
|- | |- | ||
|10:10 | |10:10 | ||
− | |So there, we have learnt to set up | + | |So there, we have learnt to set up '''relationship'''s in our database. |
|- | |- | ||
|10:15 | |10:15 | ||
− | |This brings us to the end of this tutorial on the | + | |This brings us to the end of this tutorial on the second part of '''Database Design''' in '''LibreOffice Base'''. |
|- | |- | ||
Line 337: | Line 337: | ||
|- | |- | ||
|10:28 | |10:28 | ||
− | | Turn information items into columns | + | |Turn information items into columns |
|- | |- | ||
Line 349: | Line 349: | ||
|- | |- | ||
|10:38 | |10:38 | ||
− | |Spoken Tutorial | + | |'''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 | |10:48 | ||
− | |This project is | + | |This project is coordinated by http://spoken-tutorial.org. |
|- | |- | ||
Line 361: | Line 361: | ||
|- | |- | ||
|10:58 | |10:58 | ||
− | |This script has been contributed by Priya Suresh, DesiCrew Solutions.And this is Soundharya, DesiCrew Solutions, signing off. | + | |This script has been contributed by Priya Suresh, DesiCrew Solutions. And this is Soundharya, DesiCrew Solutions, signing off. |
− | Thanks for joining | + | Thanks for joining. |
|- | |- |
Latest revision as of 14:51, 23 March 2017
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 Null. |
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. |