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

From Script | Spoken-Tutorial
Jump to: navigation, search
Line 4: Line 4:
 
|-
 
|-
 
|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 And take Backups  
+
|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, and updating forms as is necessary to keep the data current.
+
|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 tables on books, members and books issued.
+
|This database initially had '''table'''s on books, members and books issued.
  
 
|-
 
|-
Line 36: Line 40:
 
|-
 
|-
 
|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, as we made changes whenever necessary.
+
|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 68:
 
|-
 
|-
 
|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, or DVDs or CDs.
+
|Here we could add option buttons to choose the type of media, that is: books or DVDs or CDs.
  
 
|-
 
|-
Line 84: Line 88:
 
|-
 
|-
 
|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 96:
 
|-
 
|-
 
|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 140:
 
|-
 
|-
 
|04:26
 
|04:26
|Because, we add data to the tables at different times, their actual storage is not in a particular order.
+
|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 indexes for table data, like we use a catalogue for a Library of books.
+
|We can use '''index'''es for table data, like we use a catalogue for a Library of books.
  
 
|-
 
|-
Line 156: Line 160:
 
|-
 
|-
 
|05:04
 
|05:04
|And, sometimes, deleting table data does not purge the data completely.
+
|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, until new data is added which takes up 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 180:
 
|-
 
|-
 
|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
+
|and type the following command in the SQL window.
  
 
|-
 
|-
 
|06:07
 
|06:07
|CHECKPOINT DEFRAG
+
|'''CHECKPOINT DEFRAG'''
  
 
|-
 
|-
Line 196: Line 200:
 
|-
 
|-
 
|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
 
||06:33
||SHUTDOWN COMPACT.
+
||'''SHUTDOWN COMPACT'''.
  
 
|-
 
|-
Line 208: Line 212:
 
|-
 
|-
 
|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 220: Line 224:
 
|-
 
|-
 
|07;06
 
|07;06
|Computer crashes, Hard disk drive breakdowns or Viral infections
+
|computer crashes, Hard disk drive breakdowns or Viral infections.
  
 
|-
 
|-
Line 240: Line 244:
 
|-
 
|-
 
|07:34
 
|07:34
|And store it in secondary media of storage, such as external hard disks, or CDs or DVDs, or flash drives
+
|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 256:
 
|-
 
|-
 
|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.
+
|with all the data structures, data, forms, queries and reports in it.
  
 
|-
 
|-
Line 268: Line 272:
 
|-
 
|-
 
|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 280:
 
|-
 
|-
 
|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'''.  
  
 
|-
 
|-
Line 292: Line 296:
 
|-
 
|-
 
|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;23
 
|09;23
|Next Defragment the Library database.
+
|Next, Defragment the 'Library database'.
  
 
|-
 
|-
Line 304: Line 308:
 
|-
 
|-
 
|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 316:
 
|-
 
|-
 
|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
  
 
|-
 
|-
 
|09:56
 
|09:56
|And take Backups.
+
|* And take Backups.
  
 
|-
 
|-
 
|09:58
 
|09:58
|Spoken Tutorial Project is a part of the Talk to a Teacher project,  
+
|'''Spoken Tutorial''' project is a part of the '''Talk to a Teacher''' project,  
  
 
|-
 
|-
Line 336: Line 340:
 
|-
 
|-
 
||10:10
 
||10:10
||This project is co-ordinated by http://spoken-tutorial.org.  
+
||This project is coordinated by http://spoken-tutorial.org.  
  
 
|-
 
|-
Line 344: Line 348:
 
|-
 
|-
 
|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
+
  
 
|-
 
|-

Revision as of 22:38, 30 September 2015

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