Difference between revisions of "LibreOffice-Suite-Base/C4/Database-Design-Purpose-OrganizeTables/English-timed"
From Script | Spoken-Tutorial
(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…') |
PoojaMoolya (Talk | contribs) |
||
(2 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
{| border=1 | {| border=1 | ||
− | + | |'''Time''' | |
− | + | |'''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. |
|- | |- | ||
|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 |
|- | |- | ||
|00:48 | |00:48 | ||
− | | | + | |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 | + | |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 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 | + | |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 | + | |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 | + | |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 | + | |How do we update the information when a book is returned by a member? |
|- | |- | ||
|03:38 | |03:38 | ||
− | |What kind of | + | |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 | + | |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 | + | |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. |
|- | |- | ||
|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 | + | |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 | + | |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 | + | |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 | + | |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 | + | |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 | + | |'''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 | + | |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. |