Difference between revisions of "LibreOffice-Suite-Base/C4/Design-Refine-Database-Design-and-Normalization-Rules/English-timed"

|Welcome to the Spoken tutorial on LibreOffice Base.

|This tutorial is a continuation of the previous tutorial on Database Design.

|7. Refine the database design     

|8. Apply the normalization rules

|9. Test the database design

|In the last tutorial, we learnt how to set up primary keys and foreign keys to establish table relationships.

|Let us now continue with the process of database design.

|We can create sample queries, forms and reports and see if all our initial questions are answered.  

|We can add columns that we may have forgotten.  

|Also we can include Business rules to the Library database to enforce Database Integrity.

|For example, the Price column in the Books table has to be numeric.  

|Another business rule can be: the Return Date should be one month after the date of issue of a book.

|Or when a specific action occurs, further actions should be triggered.  

|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.

|So as we redesign, we may introduce new tables, columns, rules or constraints,

|And we have to go all  over the previous steps to ensure that the Data Integrity is not lost.

|Next, we can apply the normalization rules.

|These are used to see if our tables are  

|b) free of any modification anomalies that we saw earlier.

|The process of applying the rules or normal forms to a database design is called normalization.

|Let us see the first three Normal forms in our tutorial.  

|Let us first see the First Normal Form.  

First Normal Form or 1NF says that all column values must be atomic.  

|For example, each cell in the Price column in the Books table should have only one value.

|Meaning the column should hold only the price of that book and nothing else.  

|Similarly, each First Name cell in the Authors table should have only one author’s first name.

|First Normal form also says that there are no repeating groups of columns.  

|As an example, we will suppose that a Publisher has published 3 books  

|And the Publishers table structure has the following columns:

|Publisher Id, Publisher, Book1, Author 1, Book 2, Author 2, Book 3, Author 3

|Notice the repeating groups: Book and Author three times.  

|So if we see such repeating groups, we should revisit our design.  

|Now if the publisher publishes ten more books, we are forced to change the table structure by adding 20 columns.  

|So we see that the table design is not stable as data changes.

|Also searching and sorting the table by book or author will become cumbersome.

|So we solve this flaw by splitting the table into two or three tables.

|In our example, we will split the above table into Publishers, Books and Authors as shown in the image on the screen.

|This design brings the table to the First Normal Form

|Let us see the Second Normal Form now.

|A table is said to be in Second Normal Form, or 2NF, if it is in 1NF.

|and every non-key column is fully dependent on the entire primary key.

|This rule applies when we have a primary key that consists of more than one column.  

|For example, let us consider the BooksIssued table, with the following columns.

|BookId,  

MemberId,  

BookTitle,

And IssueDate,

with BookId and MemberId forming the primary key of the table.

|Now, notice the BookTitle column.  

|We can get the BookTitle by looking up the BookId in the Books table.  

|In other words, BookTitle is dependent on Book ID only, and not on Member ID.  

|So it is not dependent on the entire primary key.  

|To bring this table into Second Normal Form, we must remove BookTitle from this table.

|And retain only those columns that are fully dependent on both the primary key and columns.

|IssueDate column will be retained here, as it is fully dependent on both the primary key fields.

|Now let us see what the Third Normal Form is.

|A table is said to be in Third Normal Form (3NF), if it is in 2NF.

|For example, let us assume the BooksIssued table with columns as

|BookIssueId (serving as the primary key),

BookTitle,

Member,

IssueDate,

And ReturnDate.

|And assume that the Library’s policy of return date is one month after the Book Issue Date.  

|Now, Base can calculate the Return Date by using the IssueDate column which is a non-key column.  

|Meaning, ReturnDate is really dependent only on IssueDate column and no other column.

|Also, if we enter a different date in the Return Date field, then it will violate our Library’s policy.  

|So to keep the table in the Third Normal Form, we will remove the ReturnDate column from the table.

|So now we know how to apply the first three Normal forms.  

|Usually, our database design can stop at 3NF.  

|For more information on Normal forms and database design, visit the websites shown on the screen.  

|Finally let us test our database design, as we have gone over the database design process,

|We can create the database structure;

|Here we will create the Tables, Relationships, Rules or Constraints, Forms, Queries and Reports.

|Use the forms for performing data additions, updates or deletes to the database.  

|Run reports to see if the report results are correct and accurate.

|We can add Indexes to the tables for faster retrieval of data.

|And we should perform periodical database maintenance, to keep our database application running successfully.

|Add a new entity called Media to the Library database design.

|Media consists of DVDs and CDs. And they can be either audio or video.

|Just like books, DVDs and CDs can also be issued to the Library members.

|Follow the database design process.

|And apply the first three Normal forms to your design.

|This brings us to the end of this tutorial on the Third Part of Database Desin in LibreOffice Base.  

|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.

|This project is co-ordinated by http://spoken-tutorial.org.  

|This script has been contributed by Priya Suresh, DesiCrew Solutions.

 

And this is Soundharya, DesiCrew Solutions, signing off.  

 

 

Thanks for joining