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

From Script | Spoken-Tutorial
Jump to: navigation, search
(Created page with '{| border=1 !Visual Cue !Narration |- |00:00 |Welcome to the Spoken tutorial on LibreOffice Base. |- |00:04 |In this tutorial, we will learn the following topics on database d…')
 
 
(2 intermediate revisions by 2 users not shown)
Line 1: Line 1:
 
{| border=1
 
{| border=1
!Visual Cue
+
|'''Time'''
!Narration
+
|'''Narration'''
  
 
|-
 
|-
 
|00:00
 
|00:00
|Welcome to the Spoken tutorial on LibreOffice Base.
+
|Welcome to the '''Spoken tutorial''' on '''LibreOffice Base'''.
  
 
|-
 
|-
 
|00:04
 
|00:04
|In this tutorial,  
+
|In this tutorial, we will learn the following topics on database design:
we will learn the following topics on database design:
+
  
 
|-
 
|-
Line 26: Line 25:
 
|-
 
|-
 
|00:19
 
|00:19
|What is Database Design?
+
|What is '''Database Design'''?
  
 
|-
 
|-
 
|00:21
 
|00:21
|Database design is the process of producing a detailed data model of a database.
+
|'Database design' is the process of producing a detailed data model of a database.
  
 
|-
 
|-
Line 38: Line 37:
 
|-
 
|-
 
|00:32
 
|00:32
|Provide up-to-date, accurate and complete information
+
|provide up-to-date, accurate and complete information.
  
 
|-
 
|-
 
|00:37
 
|00:37
|Which means we can ensure integrity of our information at various levels
+
|Which means- we can ensure integrity of our information at various levels,
  
 
|-
 
|-
 
|00:43
 
|00:43
|Meet our needs of data processing and reporting
+
|meet our needs of data processing and reporting
  
 
|-
 
|-
 
|00:48
 
|00:48
|And easily accommodate change.
+
|and easily accommodate change.
  
 
|-
 
|-
 
|00:51
 
|00:51
|The process of database design consists of the following steps
+
|The process of database design consists of the following steps:
  
 
|-
 
|-
Line 66: Line 65:
 
|-
 
|-
 
|01:04
 
|01:04
|Divide the information into tables     
+
|Divide the information into '''tables'''    
  
 
|-
 
|-
 
|01:07
 
|01:07
|Turn information items into columns     
+
|Turn information items into '''columns'''    
  
 
|-
 
|-
 
|01:11
 
|01:11
|Specify primary keys     
+
|Specify '''primary keys'''    
  
 
|-
 
|-
 
|01:14
 
|01:14
|Set up the table relationships    
+
|Set up the '''table relationship'''s    
  
 
|-
 
|-
Line 86: Line 85:
 
|-
 
|-
 
|01:20
 
|01:20
|Apply the normalization rules     
+
|Apply the '''normalization''' rules     
  
 
|-
 
|-
 
|01:23
 
|01:23
|And finally, test, run the database, and maintain.
+
|And finally, test, '''run''' the database and maintain.
  
 
|-
 
|-
 
|01:28
 
|01:28
|Okay, let us go to the first step which is  
+
|Okay, let us go to the first step which is-
  
 
|-
 
|-
Line 102: Line 101:
 
|-
 
|-
 
|01:35
 
|01:35
|Let us consider a simple Library Application.  
+
|Let us consider a simple '''Library Application'''.  
  
 
|-
 
|-
Line 114: Line 113:
 
|-
 
|-
 
|01:45
 
|01:45
|So we need a Library application to maintain a list of books, and members  
+
|So, we need a '''Library application''' to maintain a list of books and members.
  
 
|-
 
|-
Line 130: Line 129:
 
|-
 
|-
 
|02:09
 
|02:09
|Now that we know the purpose of the Library application, let us identify the items here.  
+
|Now that we know the purpose of the 'Library application', let us identify the items here.  
  
 
|-
 
|-
Line 138: Line 137:
 
|-
 
|-
 
|02:19
 
|02:19
|A Book has a title, an author, a publisher, and a price.  
+
|A '''Book''' has a '''title''', an '''author''', a '''publisher''' and a '''price'''.  
  
 
|-
 
|-
Line 150: Line 149:
 
|-
 
|-
 
|02:38
 
|02:38
|Also,  there are the Library members who have names, phone numbers and addresses.  
+
|Also,  there are the 'Library' members who have names, phone numbers and addresses.  
  
 
|-
 
|-
 
|02:45
 
|02:45
|Now when a book is issued to a member, there is a  
+
|Now when a book is issued to a member, there is a-
  
 
|-
 
|-
 
|02:49
 
|02:49
|book issue date, return date, actual return date and the checked in status.
+
|book issue date, return date, actual return date and the checked-in status.
  
 
|-
 
|-
 
|02:56
 
|02:56
|These individual items are also called attributes.  
+
|These individual items are also called '''attribute'''s.  
  
 
|-
 
|-
 
|03:01
 
|03:01
|Each of these attributes represents a potential column in a table.  
+
|Each of these attributes represents a potential '''column''' in a '''table'''.  
  
 
|-
 
|-
Line 186: Line 185:
 
|-
 
|-
 
|03:32
 
|03:32
|How do we update the information, when a book is returned by a member?  
+
|How do we update the information when a book is returned by a member?  
  
 
|-
 
|-
 
|03:38
 
|03:38
|What kind of reports would we want to produce?  
+
|What kind of '''report'''s would we want to produce?  
  
 
|-
 
|-
Line 214: Line 213:
 
|-
 
|-
 
|04:14
 
|04:14
|So the preliminary list of tables looks like the image shown on the screen.
+
|So, the preliminary list of tables looks like the image shown on the screen.
  
 
|-
 
|-
 
|04:21
 
|04:21
|The major subjects or entities shown here are the books and the members.  
+
|The major subjects or '''entities''' shown here are the books and the members.  
  
 
|-
 
|-
Line 226: Line 225:
 
|-
 
|-
 
|04:33
 
|04:33
|Let us now look at the Books table in detail.  
+
|Let us now look at the '''Books''' table in detail.  
  
 
|-
 
|-
 
|04:37
 
|04:37
|It has the 10 attributes or columns which we defined earlier:
+
|It has 10 attributes or columns which we defined earlier:
  
 
|-
 
|-
 
|04:43
 
|04:43
|Title, Author, Publisher, PublisherAddress, PublisherCity, PublisherPhone, PublishYear, Price, AuthorBirthDate and AuthorCountry.  
+
|'''Title, Author, Publisher, PublisherAddress, PublisherCity, PublisherPhone, PublishYear, Price, AuthorBirthDate''' and '''AuthorCountry'''.  
  
 
|-
 
|-
 
|04:58
 
|04:58
|Now let us see how data is displayed in this table.  
+
|Now, let us see how data is displayed in this table.  
  
 
|-
 
|-
 
|05:03
 
|05:03
|Notice that, each row or record contains information about the book, its author and its publisher.  
+
|Notice that each row or '''record''' contains information about the book, its author and its publisher.  
  
 
|-
 
|-
Line 254: Line 253:
 
|-
 
|-
 
|05:23
 
|05:23
|So we notice that the author’s details and the publisher’s details have been repeated many times,
+
|So, we notice that the author’s details and the publisher’s details have been repeated many times
  
 
|-
 
|-
Line 266: Line 265:
 
|-
 
|-
 
|05:38
 
|05:38
|It runs the risk of introducing anomalies in the database.  
+
|it runs the risk of introducing anomalies in the database.  
  
 
|-
 
|-
 
|05:44
 
|05:44
|Now what is an anomaly?
+
|Now, what is an '''anomaly'''?
  
 
|-
 
|-
Line 282: Line 281:
 
|-
 
|-
 
|05:57
 
|05:57
|The first is called an insertion anomaly,
+
|The first is called an '''insertion anomaly'''
  
 
|-
 
|-
 
|06:01
 
|06:01
|which can occur when a new record is inserted,
+
|which can occur when a new record is inserted.
  
 
|-
 
|-
 
|06:06
 
|06:06
|Or when certain attributes cannot be inserted into the database without the presence of other attributes.
+
|Or, when certain attributes cannot be inserted into the database without the presence of other attributes.
  
 
|-
 
|-
 
|06:14
 
|06:14
|For example, we will suppose there is a new Publisher called Penguin.
+
|For example, we will suppose there is a new '''Publisher''' called 'Penguin'.
  
 
|-
 
|-
 
|06:21
 
|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.
+
|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
 
|06:34
|The second is called the deletion anomaly,
+
|The second is called the '''deletion anomaly'''
  
 
|-
 
|-
Line 310: Line 309:
 
|-
 
|-
 
|06:43
 
|06:43
|Here, the deletion of a row or record in the database deletes more information than we wished to delete.
+
|Here, the deletion of a '''row''' or '''record''' in the database deletes more information than we wished to delete.
  
 
|-
 
|-
 
|06:51
 
|06:51
|For example, we see that the Orient Publishers have only one book titled ‘Paradise Lost’, in our library.  
+
|For example, we see that the 'Orient Publishers' have only one book titled ‘Paradise Lost’, in our library.  
  
 
|-
 
|-
 
|07:01
 
|07:01
|Now if we delete this entire record, then we lose all information on Orient Publishers.  
+
|Now, if we delete this entire record then we lose all information on Orient Publishers.  
  
 
|-
 
|-
Line 326: Line 325:
 
|-
 
|-
 
|07:16
 
|07:16
|And finally let us see what Update Anomaly is.  
+
|And finally, let us see what '''Update Anomaly''' is.  
  
 
|-
 
|-
Line 338: Line 337:
 
|-
 
|-
 
|07:32
 
|07:32
|Now, updating the Address column for this Publisher requires us to make a change in more than one place.
+
|Now, updating the '''Address''' column for this '''Publisher''' requires us to make a change in more than one place.
  
 
|-
 
|-
Line 346: Line 345:
 
|-
 
|-
 
|07:43
 
|07:43
|And if Cambridge supplied a thousand books, then it means, we need to change the address in those thousand records.  
+
|And, if Cambridge supplied a thousand books then it means, we need to change the address in those thousand records.  
  
 
|-
 
|-
Line 354: Line 353:
 
|-
 
|-
 
|08:02
 
|08:02
|So this can lead to information not being accurate and thereby data integrity is lost.
+
|So, this can lead to information not being accurate and thereby '''data integrity''' is lost.
  
 
|-
 
|-
Line 366: Line 365:
 
|-
 
|-
 
|08:20
 
|08:20
|If the same information is repeated in more than one place, then we should place that information in a separate table.  
+
|If the same information is repeated in more than one place then we should place that information in a separate '''table'''.  
  
 
|-
 
|-
Line 374: Line 373:
 
|-
 
|-
 
|08:31
 
|08:31
|Now we have split the Books table into Books, Authors and Publisher.  
+
|Now we have split the '''Books''' table into '''Books, Authors''' and '''Publisher'''.  
  
 
|-
 
|-
 
|08:38
 
|08:38
|Notice that the columns in each table store facts about that entity or subject only.
+
|Notice that the columns in each table store facts about that '''entity''' or subject only.
  
 
|-
 
|-
 
|08:47
 
|08:47
|This way, we can record the publisher information only once inside the Publisher table.
+
|This way, we can record the publisher information only once inside the '''Publisher''' table.
  
 
|-
 
|-
 
|08:55
 
|08:55
|Similarly, having a separate Authors table allows recording the author information only once.  
+
|Similarly, having a separate '''Authors''' table allows recording the author information only once.  
  
 
|-
 
|-
 
|09:04
 
|09:04
|And we will see how we can link these tables back to the Books table in the next tutorial.
+
|And we will see how we can link these tables back to the '''Books''' table in the next tutorial.
  
 
|-
 
|-
 
|09:12
 
|09:12
|This brings us to the end of this tutorial on the First part of Database Design in LibreOffice.  
+
|This brings us to the end of this tutorial on the first part of '''Database Design''' in '''LibreOffice'''.  
  
 
|-
 
|-
Line 410: Line 409:
 
|-
 
|-
 
|09:32
 
|09:32
|Dividing the information into tables
+
|Dividing the information into tables.
  
 
|-
 
|-
 
|09:36
 
|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.  
+
|'''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
 
|09:48
|This project is co-ordinated by http://spoken-tutorial.org.  
+
|This project is coordinated by http://spoken-tutorial.org.  
  
 
|-
 
|-
Line 426: Line 425:
 
|-
 
|-
 
|09:58
 
|09:58
|This script has been contributed by Priya Suresh, DesiCrew Solutions.
+
|This script has been contributed by Priya Suresh, DesiCrew Solutions. And this is Soundharya, DesiCrew Solutions, signing off.  
 
+
Thanks for joining.
And this is Soundharya, DesiCrew Solutions, signing off.  
+
 
+
 
+
Thanks for joining
+
  
 
|-
 
|-

Latest revision as of 14:47, 23 March 2017

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