LibreOffice-Suite-Base/C4/Database-Design-Purpose-OrganizeTables/English-timed
From Script | Spoken-Tutorial
| Time | Narration |
| 00:00 | Welcome to the Spoken tutorial on LibreOffice Base. |
| 00:04 | In this tutorial, we will learn the following topics on database design: |
| 00:09 | Determining the purpose of our database |
| 00:12 | Finding and organizing the information required |
| 00:15 | Dividing the information into tables. |
| 00:19 | What is Database Design? |
| 00:21 | 'Database design' is the process of producing a detailed data model of a database. |
| 00:28 | With a good design, a database can |
| 00:32 | provide up-to-date, accurate and complete information. |
| 00:37 | Which means- we can ensure integrity of our information at various levels, |
| 00:43 | meet our needs of data processing and reporting |
| 00:48 | and easily accommodate change. |
| 00:51 | The process of database design consists of the following steps: |
| 00:57 | Determine the purpose of our database |
| 01:00 | Find and organize the information required |
| 01:04 | Divide the information into tables |
| 01:07 | Turn information items into columns |
| 01:11 | Specify primary keys |
| 01:14 | Set up the table relationships |
| 01:17 | Refine our design |
| 01:20 | Apply the normalization rules |
| 01:23 | And finally, test, run the database and maintain. |
| 01:28 | Okay, let us go to the first step which is- |
| 01:32 | Determine the purpose of our database. |
| 01:35 | Let us consider a simple Library Application. |
| 01:38 | A library usually has books. |
| 01:41 | And these books are issued to its registered members. |
| 01:45 | So, we need a Library application to maintain a list of books and members. |
| 01:51 | And track the issuing of this books to its members. |
| 01:56 | Our first step is to find and organize the required information. |
| 02:01 | Here is where we gather all of the types of information we want to record in the database. |
| 02:09 | Now that we know the purpose of the 'Library application', let us identify the items here. |
| 02:17 | There are books. |
| 02:19 | A Book has a title, an author, a publisher and a price. |
| 02:24 | And we can also store the author’s information such as, date of birth and country he or she belongs to. |
| 02:33 | We can also store the publisher’s name, address and phone. |
| 02:38 | Also, there are the 'Library' members who have names, phone numbers and addresses. |
| 02:45 | Now when a book is issued to a member, there is a- |
| 02:49 | book issue date, return date, actual return date and the checked-in status. |
| 02:56 | These individual items are also called attributes. |
| 03:01 | Each of these attributes represents a potential column in a table. |
| 03:08 | At this point, we can formulate questions such as: |
| 03:12 | How do we add information about a set of fresh books supplied to the Library by a publisher? |
| 03:20 | How do we maintain a list of its members? |
| 03:25 | What if a member leaves or a member wants to change his/her address? |
| 03:32 | How do we update the information when a book is returned by a member? |
| 03:38 | What kind of reports would we want to produce? |
| 03:42 | Which books are most read among the readers? |
| 03:46 | And how do we generate a list of books which are over due to be returned by members who have borrowed them? |
| 03:55 | Now that we have some details, let us see how we can divide the information into tables. |
| 04:02 | We will divide our information items or attributes into major entities or subjects. |
| 04:11 | Each subject then becomes a table. |
| 04:14 | So, the preliminary list of tables looks like the image shown on the screen. |
| 04:21 | The major subjects or entities shown here are the books and the members. |
| 04:26 | Therefore, it makes sense to start out with two tables, one for books and one for members. |
| 04:33 | Let us now look at the Books table in detail. |
| 04:37 | It has 10 attributes or columns which we defined earlier: |
| 04:43 | Title, Author, Publisher, PublisherAddress, PublisherCity, PublisherPhone, PublishYear, Price, AuthorBirthDate and AuthorCountry. |
| 04:58 | Now, let us see how data is displayed in this table. |
| 05:03 | Notice that each row or record contains information about the book, its author and its publisher. |
| 05:13 | Now, there are two flaws in this design. |
| 05:17 | There can be many books from the same author or the publisher. |
| 05:23 | So, we notice that the author’s details and the publisher’s details have been repeated many times |
| 05:31 | which wastes computer disk space. |
| 05:34 | And a second problem with this design is: |
| 05:38 | it runs the risk of introducing anomalies in the database. |
| 05:44 | Now, what is an anomaly? |
| 05:47 | It is simply an error or inconsistency in the database. |
| 05:53 | There are three types of anomalies: |
| 05:57 | The first is called an insertion anomaly |
| 06:01 | which can occur when a new record is inserted. |
| 06:06 | Or, when certain attributes cannot be inserted into the database without the presence of other attributes. |
| 06:14 | For example, we will suppose there is a new Publisher called 'Penguin'. |
| 06:21 | Now, our design will not let us insert details about Penguin publishers until our library can get a hold of at least one book by them. |
| 06:34 | The second is called the deletion anomaly |
| 06:39 | which occurs during the deletion of a record. |
| 06:43 | Here, the deletion of a row or record in the database deletes more information than we wished to delete. |
| 06:51 | For example, we see that the 'Orient Publishers' have only one book titled ‘Paradise Lost’, in our library. |
| 07:01 | Now, if we delete this entire record then we lose all information on Orient Publishers. |
| 07:10 | And we lose information about the author John Milton as well. |
| 07:16 | And finally, let us see what Update Anomaly is. |
| 07:21 | It occurs during the updating of a record. |
| 07:26 | For example, let us suppose that Cambridge Publishers have a new address. |
| 07:32 | Now, updating the Address column for this Publisher requires us to make a change in more than one place. |
| 07:40 | In our case, two places. |
| 07:43 | And, if Cambridge supplied a thousand books then it means, we need to change the address in those thousand records. |
| 07:54 | And we might accidentally change the address in one place but forget to change it in the other places. |
| 08:02 | So, this can lead to information not being accurate and thereby data integrity is lost. |
| 08:11 | How do we solve these problems? |
| 08:14 | We should redesign so that we record each fact just once. |
| 08:20 | If the same information is repeated in more than one place then we should place that information in a separate table. |
| 08:29 | Let us see how. |
| 08:31 | Now we have split the Books table into Books, Authors and Publisher. |
| 08:38 | Notice that the columns in each table store facts about that entity or subject only. |
| 08:47 | This way, we can record the publisher information only once inside the Publisher table. |
| 08:55 | Similarly, having a separate Authors table allows recording the author information only once. |
| 09:04 | And we will see how we can link these tables back to the Books table in the next tutorial. |
| 09:12 | This brings us to the end of this tutorial on the first part of Database Design in LibreOffice. |
| 09:19 | To summarize, we learned the following topics on database design: |
| 09:25 | Determining the purpose of our database |
| 09:28 | Finding and organizing the information required |
| 09:32 | Dividing the information into tables. |
| 09:36 | 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. |
| 09:48 | This project is coordinated by http://spoken-tutorial.org. |
| 09:54 | More information on the same is available at the following link. |
| 09:58 | This script has been contributed by Priya Suresh, DesiCrew Solutions. And this is Soundharya, DesiCrew Solutions, signing off.
Thanks for joining. |
