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 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 co-ordinated 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