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

|Welcome to the Spoken tutorial on LibreOffice Base.

|In this tutorial,  

we will learn the following topics on database design:

|Determining the purpose of our database     

|Finding and organizing the information required     

|Dividing the information into tables.

|What is Database Design?

|Database design is the process of producing a detailed data model of a database.

|Provide up-to-date, accurate and complete information

|Which means we can ensure integrity of our information at various levels

|Meet our needs of data processing and reporting

|And easily accommodate change.

|The process of database design consists of the following steps

|Determine the purpose of our database     

|Find and organize the information required     

|Divide the information into tables     

|Turn information items into columns     

|Specify primary keys     

|Set up the table relationships    

|Refine our design     

|Apply the normalization rules     

|And finally, test, run the database, and maintain.

|Okay, let us go to the first step which is  

|Let us consider a simple Library Application.  

|So we need a Library application to maintain a list of books, and members  

|Now that we know the purpose of the Library application, let us identify the items here.  

|A Book has a title, an author, a publisher, and a price.  

|Also,  there are the Library members who have names, phone numbers and addresses.  

|Now when a book is issued to a member, there is a  

|book issue date, return date, actual return date and the checked in status.

|These individual items are also called attributes.  

|Each of these attributes represents a potential column in a table.  

|How do we update the information, when a book is returned by a member?  

|What kind of reports would we want to produce?  

|So the preliminary list of tables looks like the image shown on the screen.

|The major subjects or entities shown here are the books and the members.  

|Let us now look at the Books table in detail.  

|It has the 10 attributes or columns which we defined earlier:

|Title, Author, Publisher, PublisherAddress, PublisherCity, PublisherPhone, PublishYear, Price, AuthorBirthDate and AuthorCountry.  

|Now let us see how data is displayed in this table.  

|Notice that, each row or record contains information about the book, its author and its publisher.  

|So we notice that the author’s details and the publisher’s details have been repeated many times,

|It runs the risk of introducing anomalies in the database.  

|Now what is an anomaly?

|The first is called an insertion anomaly,

|which can occur when a new record is inserted,

|Or when certain attributes cannot be inserted into the database without the presence of other attributes.

|For example, we will suppose there is a new Publisher called Penguin.

|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.

|The second is called the deletion anomaly,

|Here, the deletion of a row or record in the database deletes more information than we wished to delete.

|For example, we see that the Orient Publishers have only one book titled ‘Paradise Lost’, in our library.  

|Now if we delete this entire record, then we lose all information on Orient Publishers.  

|And finally let us see what Update Anomaly is.  

|Now, updating the Address column for this Publisher requires us to make a change in more than one place.

|And if Cambridge supplied a thousand books, then it means, we need to change the address in those thousand records.  

|So this can lead to information not being accurate and thereby data integrity is lost.

|If the same information is repeated in more than one place, then we should place that information in a separate table.  

|Now we have split the Books table into Books, Authors and Publisher.  

|Notice that the columns in each table store facts about that entity or subject only.

|This way, we can record the publisher information only once inside the Publisher table.

|Similarly, having a separate Authors table allows recording the author information only once.  

|And we will see how we can link these tables back to the Books table in the next tutorial.

|This brings us to the end of this tutorial on the First part of Database Design in LibreOffice.  

|Determining the purpose of our database     

|Finding and organizing the information required     

|Dividing the information into tables

|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.  

|This project is co-ordinated by http://spoken-tutorial.org.  

|This script has been contributed by Priya Suresh, DesiCrew Solutions.

 

And this is Soundharya, DesiCrew Solutions, signing off.  

 

 

Thanks for joining