LibreOffice-Suite-Base/C4/Design-Refine-Database-Design-and-Normalization-Rules/English-timed

Time	Narration
00:02	Welcome to the Spoken tutorial on LibreOffice Base.
00:06	This tutorial is a continuation of the previous tutorial on Database Design.
00:11	And we will learn the following topics here:
00:15	7. Refine the database design
00:18	8. Apply the normalization rules
00:21	9. Test the database design.
00:25	In the last tutorial, we learnt how to set up primary keys and foreign keys to establish table relationships.
00:34	Let us now continue with the process of 'database design'.
00:38	First, we will refine our database design.
00:42	Now that we have a preliminary design, we can create and populate the tables with sample data.
00:50	We can create sample queries, forms and reports and see if all our initial questions are answered.
00:59	We can check for unnecessary duplications and eliminate them by altering the design.
01:06	We can add columns that we may have forgotten.
01:10	Also, we can include Business rules to the Library database to enforce Database Integrity.
01:19	For example, the Price column in the Books table has to be numeric.
01:24	Another business rule can be: the Return Date should be one month after the date of issue of a book.
01:32	Or, when a specific action occurs, further actions should be triggered.
01:39	So, if a book return date is overdue then we should set up actions in the database to send an email reminder to the member.
01:50	So, as we redesign, we may introduce- new tables, columns, rules or constraints
01:58	and we have to go all over the previous steps to ensure that the Data Integrity is not lost.
02:07	Next, we can apply the normalization rules.
02:13	These are used to see if our tables are:
02:17	a) structured correctly and
02:20	b) free of any modification anomalies that we saw earlier.
02:25	The process of applying the rules or normal forms to a database design is called normalization.
02:33	Let us see the first three Normal forms in our tutorial.
02:38	Let us first see the First Normal Form. 'First Normal Form' or 1NF says that all column values must be atomic.
02:51	For example, each cell in the Price column in the Books table should have only one value.
02:59	Meaning, the column should hold only the price of that book and nothing else.
03:07	Similarly, each First Name cell in the Authors table should have only one author’s first name.
03:16	First Normal form also says that there are no repeating groups of columns.
03:23	As an example, we will suppose that a Publisher has published 3 books.
03:29	And the Publishers table structure has the following columns:
03:34	Publisher Id, Publisher, Book1, Author 1, Book 2, Author 2, Book 3, Author 3.
03:47	Notice the repeating groups: 'Book' and 'Author' three times.
03:52	So, if we see such repeating groups, we should revisit our design.
03:58	Now, if the publisher publishes ten more books, we are forced to change the table structure by adding 20 columns.
04:08	So, we see that the table design is not stable as data changes.
04:14	Also, searching and sorting the table by book or author will become cumbersome.
04:23	So, we solve this flaw by splitting the table into two or three tables.
04:30	In our example, we will split the above table into Publishers, Books and Authors as shown in the image on the screen.
04:41	This design brings the table to the First Normal Form
04:47	and keeps the table structures stable as data on publishers and books keep changing.
04:56	Let us see the Second Normal Form now.
05:00	A 'table' is said to be in Second Normal Form or 2NF, if it is in 1NF
05:07	and every non-key column is fully dependent on the entire primary key.
05:14	This rule applies when we have a primary key that consists of more than one column.
05:22	For example, let us consider the BooksIssued table with the following columns.
05:29	BookId, MemberId, BookTitle and IssueDate with 'BookId' and 'MemberId' forming the primary key of the table.
05:42	Now, notice the BookTitle column.
05:45	We can get the 'BookTitle' by looking up the 'BookId' in the 'Books' table.
05:52	In other words, 'BookTitle' is dependent on 'Book ID' only and not on 'Member ID'.
06:00	So, it is not dependent on the entire primary key.
06:06	To bring this table into Second Normal Form, we must remove 'BookTitle' from this table
06:14	and retain only those columns that are fully dependent on both the 'primary key' and columns.
06:23	IssueDate column will be retained here, as it is fully dependent on both the 'primary key' fields.
06:31	Now, let us see what the Third Normal Form is.
06:35	A table is said to be in Third Normal Form (3NF), if it is in 2NF
06:42	and if all the non-key columns are independent of each other.
06:48	For example, let us assume the BooksIssued table with columns as
06:54	BookIssueId (serving as the primary key), BookTitle, Member, IssueDate and ReturnDate.
07:03	And assume that the Library’s policy of return date is one month after the Book Issue Date.
07:11	Now, Base can calculate the Return Date by using the 'IssueDate' column which is a non-key column.
07:19	Meaning, 'ReturnDate' is really dependent only on 'IssueDate' column and no other column.
07:26	Also, if we enter a different date in the 'Return Date' field then it will violate our Library’s policy.
07:37	So, to keep the table in the 'Third Normal Form', we will remove the 'ReturnDate' column from the table.
07:44	So, now we know how to apply the first three Normal forms.
07:49	Usually, our database design can stop at 3NF.
07:55	For more information on Normal forms and database design, visit the websites shown on the screen.
08:05	Finally, let us test our database design, as we have gone over the database design process.
08:12	We can create the database structure.
08:16	Here we will create the Tables, Relationships, Rules or Constraints, Forms, Queries and Reports.
08:24	And we can test the database with real data and users.
08:29	Use the forms for performing data additions, updates or deletes to the database.
08:36	Run reports to see if the report results are correct and accurate.
08:42	As the database gets ready for use, we can check the performance in terms of speed.
08:50	We can add Indexes to the tables for faster retrieval of data.
08:55	And we should perform periodical database maintenance to keep our database application running successfully.
09:03	Now that we are done, here is an assignment for you:
09:08	Add a new entity called Media to the Library database design.
09:14	'Media' consists of DVDs and CDs and they can be either audio or video.
09:21	Just like books, DVDs and CDs can also be issued to the Library members.
09:28	Follow the database design process.
09:31	And apply the first three Normal forms to your design.
09:37	This brings us to the end of this tutorial on the third part of Database Desin in LibreOffice Base.
09:45	To summarize, we learned the following topics on database design:
09:50	7. Refine the database design.
09:52	8. Apply the normalization rules.
09:55	9. Test the database design.
09:58	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:10	This project is coordinated by http://spoken-tutorial.org.
10:15	More information on the same is available at the following link.
10:20	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, Sandhya.np14