PoojaMoolya at 09:05, 23 March 2017

2017-03-23T09:05:15Z

PoojaMoolya at 05:07, 10 March 2017

2017-03-10T05:07:04Z

Sandhya.np14 at 17:08, 30 September 2015

2015-09-30T17:08:59Z

Show changes

Gaurav at 09:41, 9 July 2014

2014-07-09T09:41:19Z

Minal: 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 …'

2012-12-28T07:12:53Z

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

New page

{| 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 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 co-ordinated 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

|-

@@ Line 9: / Line 9: @@
 |00:04
 |In this tutorial , we will learn how to:
- Maintain a Database
- Modify Database Structure
+Maintain a Database, Modify Database Structure, Defragment a database and Take Backups.
- Defragment a database and
- Take Backups.
 |-
@@ Line 203: / Line 201: @@
 |-
-||06:33
+|06:33
-||'''SHUTDOWN COMPACT'''.
+|'''SHUTDOWN COMPACT'''.
 |-
@@ Line 223: / Line 221: @@
 |-
-|07;06
+|07:06
 |computer crashes, Hard disk drive breakdowns or Viral infections.
@@ Line 291: / Line 289: @@
 |-
-||09:08
+|09:08
-||Let both columns be of data type TEXT.
+|Let both columns be of data type TEXT.
 |-
@@ Line 299: / Line 297: @@
 |-
-|09;23
+|09:23
 |Next, Defragment the 'Library database'.
@@ Line 335: / Line 333: @@
 |-
-||10:03
+|10:03
-||supported by the National Mission on Education through ICT, MHRD, Government of India.
+|supported by the National Mission on Education through ICT, MHRD, Government of India.
 |-
-||10:10
+|10:10
-||This project is coordinated by http://spoken-tutorial.org.
+|This project is coordinated by http://spoken-tutorial.org.
 |-
-||10:15
+|10:15
-||More information on the same is available at the following link.
+|More information on the same is available at the following link.
 |-

@@ Line 9: / Line 9: @@
 |00:04
 |In this tutorial , we will learn how to:
-* Maintain a Database
+ Maintain a Database
-* Modify Database Structure
+ Modify Database Structure
-* Defragment a database and
+ Defragment a database and
-* Take Backups.
+ Take Backups.
 |-
@@ Line 316: / Line 316: @@
 |-
 |09:48
-|* Maintain a Database
+| Maintain a Database
 |-
 |09:50
-|* Modify Database Structure
+| Modify Database Structure
 |-
 |09:54
-|* Defragment a database
+| Defragment a database
 |-
 |09:56
-|* And take Backups.
+|And take Backups.
 |-

@@ Line 1: / Line 1: @@
 {| border=1
-!Visual Cues
+|'''Time'''
-!Narration
+|'''Narration'''
 |-
 |00:00

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

PoojaMoolya at 09:05, 23 March 2017

PoojaMoolya at 05:07, 10 March 2017

Sandhya.np14 at 17:08, 30 September 2015

Gaurav at 09:41, 9 July 2014

Minal: 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 …'