LibreOffice-Suite-Base/C4/Design-Refine-Database-Design-and-Normalization-Rules/English-timed
From Script | Spoken-Tutorial
| Visual Cue | 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 co-ordinated 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.
|
