Difference between revisions of "LibreOffice-Suite-Base/C4/Database-Design-Purpose-OrganizeTables/English"

Spoken tutorial on LibreOffice Base : Database Design Learning Objectives

Database Design

1. Determine the purpose of our database

2. Find and organize the information required

3. Divide the information into tables

In this tutorial, we will learn the following topics on database design:

1. Determining the purpose of our database

2. Finding and organizing the information required

3. Dividing the information into tables

Database design is the process of producing a detailed data model of a database.

With a good design, a database can

4. Provide up-to-date, accurate and complete information

5. Which means we can ensure integrity of our information at various levels

6. Meet our needs of data processing and reporting

7. Easily accommodate change

• Database design is the process of producing a detailed data model of a database.

• With a good design, a database can

• Provide up-to-date, accurate and complete information

• Which means we can ensure integrity of our information at various levels

• Meet our needs of data processing and reporting

• And easily accommodate change

The process of database design consists of the following steps

1. Determine the purpose of our database

2. Find and organize the information required

3. Divide the information into tables

4. Turn information items into columns

5. Specify primary keys

6. Set up the table relationships

7. Refine our design

8. Apply the normalization rules

9. Finally: test, run the database, and maintain.

1. Determine the purpose of our database

2. Find and organize the information required

3. Divide the information into tables

4. Turn information items into columns

5. Specify primary keys

6. Set up the table relationships

7. Refine our design

8. Apply the normalization rules

9. And finally, test, run the database, and maintain.

Step 1. Determine the purpose of our database

• Build a simple Library Application

• That maintains a list of books, and members

• And tracks the issuing of the books to its members.

Determine the purpose of our database.

Let us consider a simple Library Application.

A library usually has books.

And these books are issued to its registered members.

So we need a Library application to maintain a list of books, and members

And track the issuing of the books to its members.

Step 2. Find and organize the information required

• Book has a title, an author, a publisher, and a price.

• Author’s info: date of birth and his/her country

• Publisher’s info: name, address and phone.

• Library members have names, phone numbers and addresses.

• Books Issued to Members: book issue date, return date, the actual return date and the checked in status.

• Each of the above attribute represents a potential column in a table.

• Formulate Questions:

• How do we add information about a set of fresh books supplied to the Library by a publisher?

• How do we maintain a list of its members?

• What if a member leaves or a member wants to change his/her address?

• How do we update the information, when a book is returned by a member?

• What kind of reports would we want to produce?

• Which books are most read among the members?

• How do we generate a list of books which are over due to be returned by members who borrowed them?

Here is where we gather all of the types of information we want to record in the database.

Now that we know the purpose of the Library application, let us identify the items here.

There are books.

A Book has a title, an author, a publisher, and a price.

And we can also store the author’s information such as, date of birth and country he or she belongs to.

We can also store the publisher’s name, address and phone.

Also, there are the Library members who have names, phone numbers and addresses.

Now when a book is issued to a member, there is a

book issue date, return date, the actual return date and the checked in status.

These individual items are also called attributes.

Each of these attributes represents a potential column in a table.

At this point, we can formulate questions such as:

How do we add information about a set of fresh books supplied to the Library by a publisher?

How do we maintain a list of its members?

What if a member leaves or a member wants to change his/her address?

How do we update the information, when a book is returned by a member?

What kind of reports would we want to produce?

Which books are most read among the members?

And how do we generate a list of books which are over due to be returned by members who borrowed them?

Step 3. Divide the information into tables

We will divide our information items or attributes into major entities or subjects.

Each subject then becomes a table.

Show BooksMembersDesign.ppt Slide

Show cursor movements over the slide as narration goes forward.

The major subjects or entities shown here are the books and the members.

Therefore, it makes sense to start out with two tables, one for books and one for members.

Let us now look at the Books table in detail.

It has the 10 attributes or columns which we identified earlier:

Title, Author, Publisher, PublisherAddress, PublisherCity, PublisherPhone, PublishYear, Price, AuthorBirthDate and AuthorCountry.

Show BooksDataTableNoIdColumn.ppt Slide

Show cursor movements over the slide as narration goes forward.

Notice that, each row or record contains information about the book, its author and its publisher.

Now, there are two flaws in this design.

There can be many books from the same author or the publisher.

So we notice that the author’s details and the publisher’s details have been repeated many times,

which wastes computer disk space.

And a second problem with this design is:

It runs the risk of introducing anomalies in the database.

• Anomaly is simply an error or inconsistency in the database.

• Three types of anomalies

• Insertion Anomaly occurs when a new record is inserted Or when certain attributes cannot be inserted into the database without the presence of other attributes

It is simply an error or inconsistency in the database.

There are three types of anomalies:

The first in called an insertion anomaly,

which can occur when a new record is inserted,

Or when certain attributes cannot be inserted into the database without the presence of other attributes.

Show BooksDataTableNoIdColumn.ppt Slide

Show cursor movements over the slide as narration goes forward

Now our design will not let us insert details about Penguin publishers, until our library can get hold of at least one book by them.

Retain slide 9 content. And make the following appear:

Deletion anomaly occurs during the deletion of a record. And the deletion of a row or record in the database deletes more information than we wished to delete.

which occurs during the deletion of a record.

Here, the deletion of a row or record in the database deletes more information than we wished to delete.

Show BooksDataTableNoIdColumn.ppt Slide

Show cursor movements over the slide as narration goes forward.

Now if we delete this entire record, then we lose all information on Orient Publishers.

And we lose information about the author John Milton as well.

Retain slide 9 content. And make the following appear:

• Update Anomaly occurs during the updating of a record.

It occurs during the updating of a record.

Show BooksDataTableNoIdColumn.ppt Slide

Show cursor movements over the slide as narration goes forward.

Now, updating the Address column for this Publisher requires us to make a change in more than one place.

In our case, two places.

And if Cambridge supplied a thousand books, then it means, we need to change the address in those thousand records.

And we might accidentally change the address in one place but forget to change it in the other places.

Retain slide 9 content. And make the following appear:

All the anomalies can cause data inaccuracy and hence data integrity can be lost

How do we solve these problems?

Show BooksDataTableNoIdColumn.ppt Slide

Show cursor movements over the slide as narration goes forward.

If the same information is repeated in more than one place, then we should place that information in a separate table.

Let us see how.

Show BooksMembersAuthorsPublishersDesign.ppt Slide

Notice that the columns in each table store facts about that entity or subject only.

This way, we can record the publisher information only once inside the Publications table.

Similarly, having a separate Authors table allows recording the author information only once.

And we will see how we can link these tables back to the Books table in the next tutorial.

Summary:

Database Design

1. Determine the purpose of our database

2. Find and organize the information required

3. Divide the information into tables

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

1. Determining the purpose of our database

2. Finding and organizing the information required

3. Dividing the information into tables