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