LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships/English-timed

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.

Contributors and Content Editors

Gaurav, Minal, PoojaMoolya, Pratik kamble, Sandhya.np14