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

From Script | Spoken-Tutorial
Revision as of 12:46, 28 December 2012 by Minal (Talk | contribs)

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


Thanks for joining

Contributors and Content Editors

Gaurav, Minal, Sandhya.np14