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

From Script | Spoken-Tutorial
Jump to: navigation, search
Visual Cue Narration
Show Slide Number 1

Spoken tutorial on LibreOffice Base : Database Design : 3 Learning Objectives

Database Design: 3 (continuation of Database Design: Part 2)

7. Refine the database design

8. Apply the normalization rules

9. Test the database design

Welcome to the Spoken tutorial on LibreOffice Base.

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

And we will learn the following topics here:

7. Refine the database design

8. Apply the normalization rules

9. Test the database design

Show Slide Number 2

Step 7. Refine our database design

  • Create and populate the tables with sample data.
  • Create sample queries, forms and reports and see if all our initial questions are answered.
  • Check for unnecessary duplications and eliminate them by altering the design.
  • Add columns that we may have forgotten.
  • Include Business rules to the Library database to enforce Database Integrity.
  • Example, the Price column in the Books table has to be numeric.
  • Example: Return Date should be one month after the date of book issue.
  • Example is 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.
  • As we redesign, we may introduce new tables, columns, rules or constraints,
  • Go over all the previous steps to ensure that the Data Integrity is not lost.
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.

First, we will refine our database design.

Now that we have a preliminary design, we can create and populate the tables with sample data.

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

We can check for unnecessary duplications and eliminate them by altering the design.

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 over all the previous steps to ensure that the Data Integrity is not lost.

Show Slide Number 3

Step 8. Apply the normalization rules

  • Check if the tables structured correctly and
  • Check if tables are 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.
Next, we can apply the normalization rules.

These are used to see if our tables are

a) structured correctly and

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.

Show Slide Number 4

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

Let us first see the First Normal Form.

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

Show Slide Number 5

Show BooksAuthorsTableDataWithId.ppt

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.

Show Slide Number 4

Retain slide 4 content. And make the following appear:

First Normal form also says that there are no repeating groups of columns. Example: Publisher publishes 3 books. Consider Publishers table with columns: Publisher Id, Publisher, Book1, Author 1, Book 2, Author 2, Book 3, Author 3

  • Book and Author are represented as repeating groups
  • Table structure not stable as data changes
  • Searching and sorting become cumbersome.
  • Solution: split the table into Publishers, Books and Authors
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.

Show Slide Number 5

Show BooksAuthorsPublishersTableDataWithId.ppt

Show cursor movements over the slide as narration goes forward.

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

and keeps the table structures stable as data on publishers and books keep changing.

Show Slide Number 6

Second Normal Form

  • 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 there is a primary key that consists of more than one column.
  • Example: BooksIssued table with the following columns.

BookId, (primary key) MemberId, (primary key) BookTitle, IssueDate

  • We can get the BookTitle by looking up the BookId in the Books table.
  • Meaning, BookTitle is dependent on Book ID only, and not on Member ID
  • So BookId is not dependent on the entire primary key.
  • Solution: Remove BookTitle from BooksIssued table.
  • And we have to retain only those columns that are fully dependent on both the primary key columns; IssueDate column will be retained here, as it is fully dependent on both the primary key fields
Let us see the Second Normal Form now.

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

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 columns

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

Show Slide Number 7

Third Normal Form

  • A table is said to be in Third Normal Form (3NF), if it is in 2NF and if all non-key columns are independent of each other.

Example: BooksIssued table with columns: BookIssueId (Primary key) BookTitle Member IssueDate ReturnDate

  • Library’s policy of return date is one month after the Book Issue Date.
  • 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.
  • If we enter a different date in the Return Date field, then it will violate the Library’s policy.
  • Solution: Remove the ReturnDate from the table to keep the table in 3NF.
  • For more information on Normal forms, visit

http://en.wikipedia.org/wiki/Database_normalization

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

http://databases.about.com/od/specificproducts/Database_Design.htm

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 2nd Normal Form.

and if all the non-key columns are independent of each other.

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.

Show Slide Number 8

Step 9. Test the database

Create the database structure: Tables, Relationships, Rules or Constraints, Forms, Queries and Reports.

  • Test the database with real data and users.
  • 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.
  • As the database gets ready for use, Check the performance in terms of speed.
  • Add Indexes to the tables for faster retrieval of data.
  • Perform periodical database maintenance, to keep our database application running successfully.
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.

And we can test the database with real data and users.

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.

As the database gets ready for use, we can check the performance in terms of speed.

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.

Show Slide Number 9

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

1. Media consists of audio and video DVDs and CDs.

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

3. Follow the database design process

4. Apply the first three Normal forms to your design.

Now that we are done, here is an assignment for you:

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.

Show Slide Number 10

Summary:

Database Design

7. Refine the database design

8. Apply the normalization rules

9. Test the database design

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

To summarize, we learned the following topics on database design:

7. Refine the database design

8. Apply the normalization rules

9. Test the database design

Acknowledgement Slide 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. More information on the same is available at the following link http://spoken-tutorial.org/NMEICT-Intro. This script has been contributed by Priya Suresh, DesiCrew Solutions.signing off. Thanks for joining.

Contributors and Content Editors

Chandrika

Retrieved from "https://script.spoken-tutorial.org/index.php?title=LibreOffice-Suite-Base/C4/Design-Refine-Database-Design-and-Normalization-Rules/English&oldid=332"