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

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