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

From Script | Spoken-Tutorial
Jump to: navigation, search
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.

Contributors and Content Editors

Gaurav, Minal, PoojaMoolya, Sandhya.np14