LibreOffice-Suite-Base/C4/Database-Design-Purpose-OrganizeTables/English

Visual Cue	Narration
Show Slide Number 1 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	Welcome to the Spoken tutorial on LibreOffice Base. 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
Show Slide Number 2 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	What is Database Design? 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
Show Slide Number 3 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.	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. And finally, test, run the database, and maintain.
Show Slide Number 4 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.	Okay, let us go to the first step which is 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.
Show Slide Number 5 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?	Our first step is to find and organize the required information. 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?
Show Slide Number 6 Step 3. Divide the information into tables	Now that we have some details, let us see how we can 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 Slide Number 7 Show BooksMembersDesign.ppt Slide Show cursor movements over the slide as narration goes forward.	So the preliminary list of tables looks like the image shown on the screen. 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 Slide Number 8 Show BooksDataTableNoIdColumn.ppt Slide Show cursor movements over the slide as narration goes forward.	Now let us see how data is displayed in this table. 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.
Show Slide Number 9 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	Now what is an anomaly? 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.
Go back to Slide Number 8 again Show BooksDataTableNoIdColumn.ppt Slide Show cursor movements over the slide as narration goes forward	For example, we will suppose there is a new Publisher called Penguin. 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.
Show Slide Number 9 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.	The second is called the deletion anomaly, 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.
Go back to Slide Number 8 again Show BooksDataTableNoIdColumn.ppt Slide Show cursor movements over the slide as narration goes forward.	For example, we see that the Orient Publishers have only one book titled ‘Paradise Lost’, in our library. 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.
Show Slide Number 9 Retain slide 9 content. And make the following appear: Update Anomaly occurs during the updating of a record.	And finally let us see what Update Anomaly is. It occurs during the updating of a record.
Go back to Slide Number 8 again Show BooksDataTableNoIdColumn.ppt Slide Show cursor movements over the slide as narration goes forward.	For example, let us suppose that Cambridge Publishers have a new address. 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.
Show Slide Number 9 Retain slide 9 content. And make the following appear: All the anomalies can cause data inaccuracy and hence data integrity can be lost	So this can lead to information not being accurate and thereby data integrity is lost. How do we solve these problems?
Go back to Slide Number 8 again Show BooksDataTableNoIdColumn.ppt Slide Show cursor movements over the slide as narration goes forward.	We should redesign so that we record each fact just once. 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 Slide Number 10 Show BooksMembersAuthorsPublishersDesign.ppt Slide	Now we have split the Books table into Books, Authors and Publications. 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.
Show Slide Number 11 Summary: Database Design 1. Determine the purpose of our database 2. Find and organize the information required 3. Divide the information into tables	This brings us to the end of this tutorial on the First part of Database Design in LibreOffice Base 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
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, Pravin1389