Difference between revisions of "LibreOffice-Suite-Base/C4/Database-Maintenance/English-timed"

|Welcome to the '''Spoken tutorial''' on '''LibreOffice Base'''.

|In this tutorial , we will learn how to:

 

Maintain a Database, Modify Database Structure, Defragment a database and Take Backups.

||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 '''form'''s 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 '''table'''s on books, members and books issued.

|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 this new '''Media''' table.

|This way, our database became more usable and up-to-date as we made changes whenever necessary.

|Along with '''table''' changes, we will also need to modify the forms to make them easier to use.

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

|For example, let us consider the '''Members''' table that lists all the members in the '''Library'''.

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

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

|Because we add data to the tables at different times, their actual storage is not in a particular order.

|We can use '''index'''es for table data, like we use a catalogue for a Library of books.

|And sometimes, deleting table data does not purge the data completely.

|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 may not be that big.

|'''Base''' offers a nice way of reorganizing called Defragmenting.

|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'''

|Now, we can also use another command in the SQL window-

|06:33

|'''SHUTDOWN COMPACT'''.

|For more information on defragmenting, visit 'hsqldb.org' Chapter 11.

|Finally, let us talk about '''Backups''' which help to keep a database secure.

|07:06

|computer crashes, Hard disk drive breakdowns or Viral infections.

|and store it in secondary media of storage such as external hard disks or CDs or DVDs or flash drives.

|So, to take a backup of the '''Library database''', locate where 'Library.odb' file is saved.

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

|Meaning- how often we add, update or delete data.

|So, we can schedule daily or weekly backups, depending on the frequency of the database usage.

|Here is an assignment-

|Alter the '''Members''' table to add two new columns -  '''Address''' and '''City'''.  

|09:08

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

|09:23

|Next, Defragment the 'Library database'.

|This brings us to the end of this tutorial on '''Database Maintenance in LibreOffice Base'''.

| Maintain a Database  

| Modify Database Structure

| Defragment a database

|'''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.

|This script has been contributed by Priya Suresh, DesiCrew Solutions. And this is Soundharya, DesiCrew Solutions, signing off.  

Thanks for joining.