LibreOffice-Suite-Base/C4/Database-Maintenance/English-timed

From Script | Spoken-Tutorial
Revision as of 22:38, 30 September 2015 by Sandhya.np14 (Talk | contribs)

Jump to: navigation, search
Time Narration
00:00 Welcome to the Spoken tutorial on LibreOffice Base.
00:04 In this tutorial , we will learn how to:
  • Maintain a Database
  • Modify Database Structure
  • Defragment a database and
  • Take Backups.
00:19 Database Maintenance-
00:21 Throughout the life of a Base database, we will need to take steps to keep the data up-to-date, reliable and safe.
00:31 This includes modifying the data structure and updating forms as is necessary to keep the data current.
00:41 Let us consider the Library example database that we built in our previous tutorials.
00:48 This database initially had tables on books, members and books issued.
00:55 And we built our example forms, queries and reports based on this database structure.
01:03 Later, the library expanded to have other media such as DVDs and CDs.
01:11 So, we modified the Library database to make the structure up-to-date.
01:16 For this, we added another table called Media
01:21 and we stored the DVD and CD information in this new Media table.
01:28 This way, our database became more usable and up-to-date as we made changes whenever necessary.
01:39 Along with table changes, we will also need to modify the forms to make them easier to use.
01:47 Or we can build new forms to accommodate new table structures.
01:54 For example, if we had a form to enter books data, we can modify it to allow data entry for DVDs and CDs also.
02:08 Here we could add option buttons to choose the type of media, that is: books or DVDs or CDs.
02:19 Or, we can add a brand new form to allow data entry for just the DVD and CD media.
02:28 Similarly, we will need to modify or add new queries and reports which are based on the data structure that was changed.
02:39 And sometimes we will need to modify existing table structures.
02:45 For example, let us consider the Members table that lists all the members in the Library.
02:53 It currently stores their names and phone numbers only.
02:58 Now, if we have to store their address and city information also, we will need to modify the Members table structure.
03:09 For this, we can use SQL syntax such as:
03:15 ALTER TABLE Members ADD Address TEXT, ADD City TEXT
03:22 So, the ALTER TABLE statement changes the table structure and adds two new columns:
03:30 Address and City which will hold TEXT data.
03:36 Visit 'hsqldb.org/' website for more information on creating and altering table structures.
03:47 Use the 'url' address shown on the screen.
03:52 Next, let us see how we can keep the Base database reliable for use.
03:59 Sometimes, Base requires a huge memory to hold comparatively small number of records.
04:08 This is because, Base anticipates a certain amount of memory that the database may need.
04:17 And, the data that we see in tables are not stored exactly in the same orderly manner.
04:26 Because we add data to the tables at different times, their actual storage is not in a particular order.
04:36 We can use indexes for table data, like we use a catalogue for a Library of books.
04:45 A catalogue not only lists the books, but also stores their physical location.
04:53 Similarly, we can build table indexes to locate the data efficiently.
05:00 But indexes also can take up a lot of memory.
05:04 And sometimes, deleting table data does not purge the data completely.
05:11 They are just disconnected from table indexes but still occupy the space until new data is added which takes up the space.
05:24 So, this is why the database grows bigger in size, although the actual data stored may not be that big.
05:35 Base offers a nice way of reorganizing called Defragmenting.
05:42 For this, we will open the database that needs to be defragmented.
05:49 Once inside the 'LibreOffice Base' window, we will click on the Tools menu and then click on SQL sub menu
06:01 and type the following command in the SQL window.
06:07 CHECKPOINT DEFRAG
06:10 This SQL command removes the unneeded information in the Base database file.
06:19 This will first close the database, reorganize the data and then re-open the database.
06:27 Now, we can also use another command in the SQL window-
06:33 SHUTDOWN COMPACT.
06:36 The only difference here is that this command won’t re-open the database.
06:43 For more information on defragmenting, visit 'hsqldb.org' Chapter 11.
06:54 Finally, let us talk about Backups which help to keep a database secure.
07:02 We could lose our database due to
07;06 computer crashes, Hard disk drive breakdowns or Viral infections.
07:14 LibreOffice has a good recovery wizard that minimizes the data loss.
07:20 But a wise thing to do is to keep periodic backups of the database.
07:26 And taking a backup is very simple.
07:30 We will just need to make a copy of the database file
07:34 and store it in secondary media of storage such as external hard disks or CDs or DVDs or flash drives.
07:47 So, to take a backup of the Library database, locate where 'Library.odb' file is saved.
07:57 And then, copy and paste the file in a different hard disk drive or into a flash drive.
08:08 Now, this single copy and paste action takes care of backing up the entire database
08:17 with all the data structures, data, forms, queries and reports in it.
08:24 How often do we need to take backups?
08:28 This depends on how often the database gets changed in terms of data or its structure.
08:37 Meaning- how often we add, update or delete data.
08:42 And how often we modify the table structures, forms, queries or reports.
08:49 So, we can schedule daily or weekly backups, depending on the frequency of the database usage.
08:58 Here is an assignment-
09:00 Alter the Members table to add two new columns - Address and City.
09:08 Let both columns be of data type TEXT.
09:13 Also, open the Members table in Data Entry mode and insert some sample address and city data.
09;23 Next, Defragment the 'Library database'.
09:27 Finally, take a backup of the Library database, save it in a flash drive or another hard disk drive, if available.
09:38 This brings us to the end of this tutorial on Database Maintenance in LibreOffice Base.
09:45 To summarize, we learned how to:
09:48 * Maintain a Database
09:50 * Modify Database Structure
09:54 * Defragment a database
09:56 * And take Backups.
09:58 Spoken Tutorial project is a part of the Talk to a Teacher project,
10:03 supported by the National Mission on Education through ICT, MHRD, Government of India.
10:10 This project is coordinated by http://spoken-tutorial.org.
10:15 More information on the same is available at the following link.
10:20 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