LibreOffice-Suite-Base/C4/Database-Maintenance/English-timed
From Script | Spoken-Tutorial
| 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. |
