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

Visual Cues	Narration
Show Slide Number 1 Spoken tutorial on LibreOffice Base : Database Maintenance Learning Objectives Maintain a Database Modify Database Structure Defragment a database Take Backups	Welcome to the Spoken tutorial on LibreOffice Base. In this tutorial on Database Maintenance, we will learn how to Maintain a Database Modify Database Structure Defragment a database And take Backups
Show Slide Number 2 Database Maintenance Keep the database up-to-date, reliable and safe Modify data structures, update forms Example: Library database had books as the only media available initially Later, we expanded the Library to include more media like DVDs and CDs We added a new table Media to store DVD and CD info Such changes keep the database up-to-date and hence more usable	Database Maintenance Throughout the life of a Base database, we will need to take steps to keep the data up-to-date, reliable and safe. This includes modifying the data structure, and updating forms as is necessary to keep the data current. Let us consider the Library example database that we built in our previous tutorials. This database initially had tables on books, members and books issued. And we built our example forms, queries and reports based on this database structure. Later, the library expanded to have other media such as DVDs and CDs. So, we modified the Library database to make the structure up-to-date. For this, we added another table called Media and we stored the DVD and CD information in the new Media table. This way, our database became more usable and up-to-date, as we made changes whenever necessary.
Show Slide Number 3 Modify Existing Forms Add new forms to accommodate new table structures Example: Modify Books Data Entry Form to allow entry for DVDs and CDs also. Or add a new form to allow data entry for just the DVD and CD media. Modify/Add new queries, reports appropriately Modify existing table structures Example: Members table has Name and Phone info only Example: New changes to the Members table: Add Address and City fields SQL Syntax: ALTER TABLE Members ADD Address TEXT, ADD City TEXT Visit http://hsqldb.org/doc/2.0/guide/databaseobjects-chapt.html#N10E48 for more information	Along with table changes, we will also need to modify the forms to make them easier to use. Or we can build new forms to accommodate new table structures. For example, if we had a form to enter books data, we can modify it to allow data entry for DVDs and CDs also. Here we could add option buttons to choose the type of media, that is: books, or DVDs or CDs. Or, we can add a brand new form to allow data entry for just the DVD and CD media. Similarly, we will need to modify or add new queries and reports which are based on the data structure that was changed. And sometimes we will need to modify existing table structures. For example, let us consider the Members table that lists all the members in the Library. It currently stores their names and phone numbers only. Now if we have to store their address and city information also, we will need to modify the Members table structure. For this we can use SQL syntax such as: ALTER TABLE Members ADD Address TEXT, ADD City TEXT So the ALTER TABLE statement changes the table structure and adds two new columns: Address and City which will hold TEXT data. Visit hsqldb.org/ website for more information on creating and altering table structures. Use the url address shown on the screen.
Show Slide Number 4 Defragmenting a database Base requires huge memory for a database Base allocates a certain memory for the database Data not stored in orderly fashion as in the data tables Because data gets added or deleted at different times And Indexes also take up lot of memory. An Index is similar to a catalogue. Indexes store the physical location of data for efficient data retrieval Deleting data only disconnects the data from the indexes. Base’s solution to reorganize data: Defragmenting database Open the Base database Open the Tools menu and SQL sub menu Use following Command in SQL Window: CHECKPOINT DEFRAG Above command removes unneeded information from the database Above command closes, reorganizes data and re-opens database Alternate command: SHUTDOWN COMPACT. This command is similar, but does not reopen database. Visit http://hsqldb.org/doc/2.0/guide/deployment-chapt.html Chapter 11 for more information.	Next, let us see how we can keep the Base database reliable for use. Sometimes, Base requires a huge memory to hold comparatively small number of records. This is because, Base anticipates a certain amount of memory that the database may need. And, the data that we see in tables are not stored exactly in the same orderly manner. Because, as we add data to the tables at different times, their actual storage is not in a particular order. We can use indexes for table data, like we use a catalogue for a Library of books. A catalogue not only lists the books, but also stores their physical location. Similarly, we can build table indexes to locate the data efficiently. But indexes also can take up a lot of memory. And, sometimes, deleting table data does not purge the data permanently. They are just disconnected from table indexes but still occupy the space, until new data is added which takes up the space. So this is why the database grows bigger in size, although the actual data stored maybe not that big. Base offers a nice way of reorganizing called Defragmenting. For this, we will open the database that needs to be defragmented. Once inside the LibreOffice Base window, we will click on the Tools menu and then click on SQL sub menu And type the following command in the SQL window CHECKPOINT DEFRAG This SQL command removes the unneeded information in the Base database file. This will first close the database, reorganize the data and then re-open the database. Now, we can also use another command in the SQL window SHUTDOWN COMPACT. The only difference here is that this command won’t re-open the database. For more information on defragmenting, visit hsqldb.org Chapter 11
Show Slide Number 4 Backups Helps to keep database secure Potential loss of database due to Computer crashes Hard disk drive breakdowns Viral infections LibreOffice has a good Recovery Wizard that minimizes data loss Keeping Backups is very useful To take a backup, simply make copies of the Base database file Save it in secondary media of storage, such as secondary hard disks, or CDs or DVDs, or flash drives	Finally let us talk about Backups which help to keep a database secure. We could lose our database due to Computer crashes, Hard disk drive breakdowns or Viral infections LibreOffice has a good recovery wizard that minimizes the data loss. But a wise thing to do is to keep periodic backups of the database. And taking a backup is very simple. We will just need to make a copy of the Base database file And store it in secondary media of storage, such as external hard disks, or CDs or DVDs, or flash drives
Show Slide Number 5 Example: Backups Locate Library Database in Windows Explorer Copy and paste into a different hard disk drive or into a flash drive. A single copy and paste action takes care of backing up all the data structures, data, forms, queries and reports Frequency of Backups depends on frequency of data changes or structure changes to the database Accordingly schedule daily or weekly backups	So to take a backup of the Library database, locate where Library.odb file is saved And then, copy and paste the file in a different hard disk drive or into a flash drive. Now this single copy and paste action takes care of backing up the entire database: With all the data structures, data, forms, queries and reports in it. How often do we need to take backups? This depends on how often the database gets changed in terms of data or its structure. Meaning how often we add, update or delete data. And how often we modify the table structures, forms, queries or reports. So we can schedule daily or weekly backups, depending on the frequency of the database usage.
Show Slide Number 6 Assignment: 1. Alter the Members table to add two new columns Address and City. Let both columns be of data type TEXT. Open the Members table in Data entry mode and insert some sample address and city data 2. Defragment the Library database 3. Take a backup of the Library database and save it in a flash drive, or another hard disk drive, if available.	Here is an assignment 1. Alter the Members table to add two new columns - Address and City. Let both columns be of data type TEXT. Also open the Members table in Data Entry mode and insert some sample address and city data. 2. Next Defragment the Library database. 3. Finally, take a backup of the Library database, save it in a flash drive or another hard disk drive, if available.
Show Slide Number 7 Summary: Maintain a Database Modify Database Structure Defragment a database Take Backups	This brings us to the end of this tutorial on Database Maintenance in LibreOffice Base To summarize, we learned how to: Maintain a Database Modify Database Structure Defragment a database And take Backups.
Acknowledgement Slide	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. More information on the same is available at the following link http://spoken-tutorial.org/NMEICT-Intro. This script has been contributed by Priya Suresh, DesiCrew Solutions Pvt Ltd, signing off. Thanks for joining.

Contributors and Content Editors

Chandrika