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