PoojaMoolya at 09:17, 23 March 2017

2017-03-23T09:17:12Z

Sandhya.np14 at 14:35, 4 October 2015

2015-10-04T14:35:19Z

Gaurav at 09:42, 9 July 2014

2014-07-09T09:42:26Z

Minal: 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…'

2012-12-28T07:15:02Z

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…'

New page

{| 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 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 the 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 co-ordinated 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

|-

@@ Line 13: / Line 13: @@
 |-
 |00:09
-|* Determining the purpose of our database
+|Determining the purpose of our database
 |-
 |00:12
-|* Finding and organizing the information required
+|Finding and organizing the information required
 |-
 |00:15
-|* Dividing the information into tables.
+|Dividing the information into tables.
 |-
@@ Line 57: / Line 57: @@
 |-
 |00:57
-|* Determine the purpose of our database
+|Determine the purpose of our database
 |-
 |01:00
-|* Find and organize the information required
+|Find and organize the information required
 |-
 |01:04
-|* Divide the information into '''tables'''
+|Divide the information into '''tables'''
 |-
 |01:07
-|* Turn information items into '''columns'''
+|Turn information items into '''columns'''
 |-
 |01:11
-|* Specify '''primary keys'''
+|Specify '''primary keys'''
 |-
 |01:14
-|* Set up the '''table relationship'''s
+|Set up the '''table relationship'''s
 |-
 |01:17
-|* Refine our design
+|Refine our design
 |-
 |01:20
-|* Apply the '''normalization''' rules
+|Apply the '''normalization''' rules
 |-
 |01:23
-|* And finally, test, '''run''' the database and maintain.
+|And finally, test, '''run''' the database and maintain.
 |-
@@ Line 401: / Line 401: @@
 |-
 |09:25
-|* Determining the purpose of our database
+|Determining the purpose of our database
 |-
 |09:28
-|*Finding and organizing the information required
+|Finding and organizing the information required
 |-
 |09:32
-|*Dividing the information into tables.
+|Dividing the information into tables.
 |-

@@ Line 1: / Line 1: @@
 {| border=1
-!Visual Cue
+|'''Time'''
-!Narration
+|'''Narration'''
 |-

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

PoojaMoolya at 09:17, 23 March 2017

Sandhya.np14 at 14:35, 4 October 2015

Gaurav at 09:42, 9 July 2014

Minal: 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…'