Difference between revisions of "LibreOffice-Suite-Base/C4/Database-Maintenance/English-timed"
From Script | Spoken-Tutorial
Sandhya.np14 (Talk | contribs) |
PoojaMoolya (Talk | contribs) |
||
(One intermediate revision by the same user not shown) | |||
Line 9: | Line 9: | ||
|00:04 | |00:04 | ||
|In this tutorial , we will learn how to: | |In this tutorial , we will learn how to: | ||
− | + | ||
− | + | Maintain a Database, Modify Database Structure, Defragment a database and Take Backups. | |
− | + | ||
− | + | ||
|- | |- | ||
Line 203: | Line 201: | ||
|- | |- | ||
− | + | |06:33 | |
− | + | |'''SHUTDOWN COMPACT'''. | |
|- | |- | ||
Line 223: | Line 221: | ||
|- | |- | ||
− | |07 | + | |07:06 |
|computer crashes, Hard disk drive breakdowns or Viral infections. | |computer crashes, Hard disk drive breakdowns or Viral infections. | ||
Line 291: | Line 289: | ||
|- | |- | ||
− | + | |09:08 | |
− | + | |Let both columns be of data type TEXT. | |
|- | |- | ||
Line 299: | Line 297: | ||
|- | |- | ||
− | |09 | + | |09:23 |
|Next, Defragment the 'Library database'. | |Next, Defragment the 'Library database'. | ||
Line 316: | Line 314: | ||
|- | |- | ||
|09:48 | |09:48 | ||
− | | | + | | Maintain a Database |
|- | |- | ||
|09:50 | |09:50 | ||
− | | | + | | Modify Database Structure |
|- | |- | ||
|09:54 | |09:54 | ||
− | | | + | | Defragment a database |
|- | |- | ||
|09:56 | |09:56 | ||
− | | | + | |And take Backups. |
|- | |- | ||
Line 335: | Line 333: | ||
|- | |- | ||
− | + | |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. | |
|- | |- |
Latest revision as of 14:35, 23 March 2017
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. |