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

From Script | Spoken-Tutorial
Revision as of 13:11, 5 October 2015 by Sandhya.np14 (Talk | contribs)

(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search
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