Difference between revisions of "LibreOffice-Suite-Base/C4/Database-Maintenance/English-timed"
From Script | Spoken-Tutorial
(Created page with '{| border=1 !Visual Cues !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 …') |
PoojaMoolya (Talk | contribs) |
||
(3 intermediate revisions by 3 users not shown) | |||
Line 1: | Line 1: | ||
{| border=1 | {| border=1 | ||
− | + | |'''Time''' | |
− | + | |'''Narration''' | |
|- | |- | ||
|00:00 | |00:00 | ||
− | |Welcome to the Spoken tutorial on LibreOffice Base. | + | |Welcome to the '''Spoken tutorial''' on '''LibreOffice Base'''. |
|- | |- | ||
|00:04 | |00:04 | ||
− | |In this tutorial , we will learn how to Maintain a Database Modify Database Structure Defragment a database | + | |In this tutorial , we will learn how to: |
+ | |||
+ | Maintain a Database, Modify Database Structure, Defragment a database and Take Backups. | ||
|- | |- | ||
||00:19 | ||00:19 | ||
− | ||Database Maintenance | + | ||Database Maintenance- |
|- | |- | ||
|00:21 | |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. | + | |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 | |00:31 | ||
− | |This includes modifying the data structure | + | |This includes modifying the '''data structure''' and updating '''form'''s as is necessary to keep the data current. |
|- | |- | ||
|00:41 | |00:41 | ||
− | |Let us consider the Library example database that we built in our previous tutorials. | + | |Let us consider the '''Library''' example database that we built in our previous tutorials. |
|- | |- | ||
|00:48 | |00:48 | ||
− | |This database initially had | + | |This database initially had '''table'''s on books, members and books issued. |
|- | |- | ||
Line 36: | Line 38: | ||
|- | |- | ||
|01:03 | |01:03 | ||
− | |Later, the library expanded to have other media such as DVDs and CDs. | + | |Later, the '''library''' expanded to have other media such as DVDs and CDs. |
|- | |- | ||
|01:11 | |01:11 | ||
− | |So, we modified the Library database to make the structure up-to-date. | + | |So, we modified the '''Library database''' to make the structure up-to-date. |
|- | |- | ||
|01:16 | |01:16 | ||
− | |For this, we added another table called Media | + | |For this, we added another table called '''Media''' |
|- | |- | ||
|01:21 | |01:21 | ||
− | |and we stored the DVD and CD information in this new Media table. | + | |and we stored the DVD and CD information in this new '''Media''' table. |
|- | |- | ||
|01:28 | |01:28 | ||
− | |This way, our database became more usable and up-to-date | + | |This way, our database became more usable and up-to-date as we made changes whenever necessary. |
|- | |- | ||
|01:39 | |01:39 | ||
− | |Along with table changes, we will also need to modify the forms to make them easier to use. | + | |Along with '''table''' changes, we will also need to modify the forms to make them easier to use. |
|- | |- | ||
Line 64: | Line 66: | ||
|- | |- | ||
|01:54 | |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. | + | |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 | |02:08 | ||
− | |Here we could add option buttons to choose the type of media, that is: books | + | |Here we could add option buttons to choose the type of media, that is: books or DVDs or CDs. |
|- | |- | ||
Line 84: | Line 86: | ||
|- | |- | ||
|02:45 | |02:45 | ||
− | |For example, let us consider the Members table that lists all the members in the Library. | + | |For example, let us consider the '''Members''' table that lists all the members in the '''Library'''. |
|- | |- | ||
Line 92: | Line 94: | ||
|- | |- | ||
|02:58 | |02:58 | ||
− | |Now if we have to store their address and city information also, we will need to modify the Members table structure. | + | |Now, if we have to store their address and city information also, we will need to modify the '''Members''' table structure. |
|- | |- | ||
|03:09 | |03:09 | ||
− | |For this we can use SQL syntax such as: | + | |For this, we can use SQL syntax such as: |
|- | |- | ||
|03:15 | |03:15 | ||
− | |ALTER TABLE Members ADD Address TEXT, ADD City TEXT | + | |'''ALTER TABLE Members ADD Address TEXT, ADD City TEXT''' |
|- | |- | ||
|03:22 | |03:22 | ||
− | |So the ALTER TABLE statement changes the table structure and adds two new columns: | + | |So, the '''ALTER TABLE''' statement changes the table structure and adds two new columns: |
|- | |- | ||
|03:30 | |03:30 | ||
− | |Address and City which will hold TEXT data. | + | |'''Address''' and '''City''' which will hold '''TEXT''' data. |
|- | |- | ||
|03:36 | |03:36 | ||
− | |Visit hsqldb.org/ website for more information on creating and altering table structures. | + | |Visit 'hsqldb.org/' website for more information on creating and altering table structures. |
|- | |- | ||
|03:47 | |03:47 | ||
− | |Use the url address shown on the screen. | + | |Use the 'url' address shown on the screen. |
|- | |- | ||
|03:52 | |03:52 | ||
− | |Next, let us see how we can keep the Base database reliable for use. | + | |Next, let us see how we can keep the '''Base''' database reliable for use. |
|- | |- | ||
|03:59 | |03:59 | ||
− | |Sometimes, Base requires a huge memory to hold comparatively small number of records. | + | |Sometimes, '''Base''' requires a huge memory to hold comparatively small number of records. |
|- | |- | ||
Line 136: | Line 138: | ||
|- | |- | ||
|04:26 | |04:26 | ||
− | |Because | + | |Because we add data to the tables at different times, their actual storage is not in a particular order. |
|- | |- | ||
|04:36 | |04:36 | ||
− | |We can use | + | |We can use '''index'''es for table data, like we use a catalogue for a Library of books. |
|- | |- | ||
Line 156: | Line 158: | ||
|- | |- | ||
|05:04 | |05:04 | ||
− | |And | + | |And sometimes, deleting table data does not purge the data completely. |
|- | |- | ||
|05:11 | |05:11 | ||
− | |They are just disconnected from table indexes but still occupy the space | + | |They are just disconnected from table indexes but still occupy the space until new data is added which takes up the space. |
|- | |- | ||
|05:24 | |05:24 | ||
− | |So this is why the database grows bigger in size, although the actual data stored may not be that big. | + | |So, this is why the database grows bigger in size, although the actual data stored may not be that big. |
|- | |- | ||
|05:35 | |05:35 | ||
− | |Base offers a nice way of reorganizing called Defragmenting. | + | |'''Base''' offers a nice way of reorganizing called Defragmenting. |
|- | |- | ||
Line 176: | Line 178: | ||
|- | |- | ||
|05:49 | |05:49 | ||
− | |Once inside the LibreOffice Base window, we will click on the Tools menu and then click on SQL sub menu | + | |Once inside the 'LibreOffice Base' window, we will click on the '''Tools''' menu and then click on '''SQL sub menu''' |
|- | |- | ||
|06:01 | |06:01 | ||
− | | | + | |and type the following command in the SQL window. |
|- | |- | ||
|06:07 | |06:07 | ||
− | |CHECKPOINT DEFRAG | + | |'''CHECKPOINT DEFRAG''' |
|- | |- | ||
Line 196: | Line 198: | ||
|- | |- | ||
|06:27 | |06:27 | ||
− | |Now, we can also use another command in the SQL window | + | |Now, we can also use another command in the SQL window- |
|- | |- | ||
− | + | |06:33 | |
− | | | + | |'''SHUTDOWN COMPACT'''. |
|- | |- | ||
Line 208: | Line 210: | ||
|- | |- | ||
|06:43 | |06:43 | ||
− | |For more information on defragmenting, visit hsqldb.org Chapter 11 | + | |For more information on defragmenting, visit 'hsqldb.org' Chapter 11. |
|- | |- | ||
|06:54 | |06:54 | ||
− | |Finally let us talk about Backups which help to keep a database secure. | + | |Finally, let us talk about '''Backups''' which help to keep a database secure. |
|- | |- | ||
Line 219: | Line 221: | ||
|- | |- | ||
− | |07 | + | |07:06 |
− | | | + | |computer crashes, Hard disk drive breakdowns or Viral infections. |
|- | |- | ||
Line 240: | Line 242: | ||
|- | |- | ||
|07:34 | |07:34 | ||
− | | | + | |and store it in secondary media of storage such as external hard disks or CDs or DVDs or flash drives. |
|- | |- | ||
|07:47 | |07:47 | ||
− | |So to take a backup of the Library database, locate where Library.odb file is saved | + | |So, to take a backup of the '''Library database''', locate where 'Library.odb' file is saved. |
|- | |- | ||
Line 252: | Line 254: | ||
|- | |- | ||
|08:08 | |08:08 | ||
− | |Now this single copy and paste action takes care of backing up the entire database | + | |Now, this single copy and paste action takes care of backing up the entire database |
|- | |- | ||
|08:17 | |08:17 | ||
− | | | + | |with all the data structures, data, forms, queries and reports in it. |
|- | |- | ||
Line 268: | Line 270: | ||
|- | |- | ||
|08:37 | |08:37 | ||
− | |Meaning how often we add, update or delete data. | + | |Meaning- how often we add, update or delete data. |
|- | |- | ||
Line 276: | Line 278: | ||
|- | |- | ||
|08:49 | |08:49 | ||
− | |So we can schedule daily or weekly backups, depending on the frequency of the database usage. | + | |So, we can schedule daily or weekly backups, depending on the frequency of the database usage. |
|- | |- | ||
|08:58 | |08:58 | ||
− | |Here is an assignment | + | |Here is an assignment- |
|- | |- | ||
|09:00 | |09:00 | ||
− | |Alter the Members table to add two new columns - Address and City. | + | |Alter the '''Members''' table to add two new columns - '''Address''' and '''City'''. |
|- | |- | ||
− | + | |09:08 | |
− | + | |Let both columns be of data type TEXT. | |
|- | |- | ||
|09:13 | |09:13 | ||
− | |Also open the Members table in Data Entry mode and insert some sample address and city data. | + | |Also, open the '''Members''' table in '''Data Entry''' mode and insert some sample address and city data. |
|- | |- | ||
− | |09 | + | |09:23 |
− | |Next Defragment the Library database. | + | |Next, Defragment the 'Library database'. |
|- | |- | ||
Line 304: | Line 306: | ||
|- | |- | ||
|09:38 | |09:38 | ||
− | |This brings us to the end of this tutorial on Database Maintenance in LibreOffice Base | + | |This brings us to the end of this tutorial on '''Database Maintenance in LibreOffice Base'''. |
|- | |- | ||
Line 312: | Line 314: | ||
|- | |- | ||
|09:48 | |09:48 | ||
− | |Maintain a Database | + | | Maintain a Database |
|- | |- | ||
|09:50 | |09:50 | ||
− | |Modify Database Structure | + | | Modify Database Structure |
|- | |- | ||
|09:54 | |09:54 | ||
− | |Defragment a database | + | | Defragment a database |
|- | |- | ||
Line 328: | Line 330: | ||
|- | |- | ||
|09:58 | |09:58 | ||
− | |Spoken Tutorial | + | |'''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 | |10:20 | ||
− | |This script has been contributed by Priya Suresh, DesiCrew Solutions. | + | |This script has been contributed by Priya Suresh, DesiCrew Solutions. And this is Soundharya, DesiCrew Solutions, signing off. |
− | + | Thanks for joining. | |
− | And this is Soundharya, DesiCrew Solutions, signing off. | + | |
− | + | ||
− | + | ||
− | Thanks for joining | + | |
|- | |- |
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. |