LibreOffice-Suite-Base/C3/Create-tables/English

From Script | Spoken-Tutorial
Jump to: navigation, search
Visual Cues Narration
Show Slide Number 1

Spoken tutorial on LibreOffice Base: Create Tables

Learning Objectives

Create a Table by a) Creating Views b) Using the Copy method

Welcome to the Spoken tutorial on LibreOffice Base.

In this tutorial, we will learn how to

Create a Table by a) Creating Views and b) Using the Copy method

In the Base main window,

Click on Tables on the left panel,

Just point on the Create View option. Don’t click.

Lets go to our Library database.

Let us click on the Tables icon on the left panel.

On the right panel, we see three ways of creating a table.

We will go through the ‘Create View’ option now.

Show slide number 2

What is a View? Similar to a table, but does not hold data

Defined as a Query Expression (needs to appear as narration goes forward)

When viewed, it has columns and rows of data just like a table. (needs to appear as narration goes forward) Use views to allow limited access or hide the structure and names of the underlying table columns and table data. (needs to appear as narration goes forward) Example: View that lists members only and without their phone numbers

Before that, let us learn about Views. What is a View?

A view is similar to a table, but it does not hold the data.

It is defined as a Query Expression, which is simply retrieval of data from tables or other views from the database.

So, when viewed, it has columns and rows of data just like a table.

Views can be used to allow limited access

Or hide the structure and names of the underlying table columns and table data.

For example, we can create a simple view which will list all the members of the library,

And we can leave out their phone numbers to maintain confidentiality.

Here the underlying table would be Members.

Other users of the Library database can be allowed to access this view but not the Members table.

In this way, they can only see the names of the members and not their phone numbers.

Go back to the Base main window,

click on the ‘Create View’ on the right.

Okay, let us go back to the main Base window, and create this view.

Let us click on ‘Create View’ on the right panel.

Click on Members

Click on close.

We see a new window called the View Design and a popup window that says Add tables.

Let us click on Members

And close this popup window.

Double click on the MemberId and Name fields.

Click on the empty cell in the Sort row under the Name column in the bottom section. Click on ‘ascending’

Now, we are in the View design window.

And we will double click on the MemberId and Name fields.

Adding the Id field is always useful

Because this helps us to join this view with any other related table, for example the BooksIssued Table.

We can add functions, criteria and sort any way we want.

But for now, we will sort the member names in ascending order.

For this, let us click on the empty cell in the Sort row under the Name column in the bottom section.

And then click on ‘ascending’.

Click on the Save icon.

Type in: ‘View: Members Name Only’

Click on Ok.

Click on the Edit menu at the top

and then click on Run Query

Let us save our first view.

Here, let us type a descriptive name for this view: View: Members Name Only.

And let us click on the Ok button.

To see the underlying data, let us click on the Edit menu at the top

And then click on Run Query at the bottom.

Point mouse over the top section over the data.

Close the window.

And we see a new section at the top that lists all the members of the Library in ascending order.

Note that we don’t see any phone numbers.

And there is our simple view.

We can create and design views according to our requirements.

Show Slide number 3

Assignment: 1. Create a View of Books Issued to Members and only those books that are not checked in. 2. Include in the view, the following fields: Book Titles, Member Names, Issue Date, and Return Date. 3. Name the view as ‘View: List of Books not checked in’.

Before moving on to the next topic, here is an assignment.

Create a View of Books Issued to Members and only those books that are not checked in.

Include in the view, the following fields: Book Titles, Member Names, Issue Date, and Return Date.

Name the view as ‘View: List of Books not checked in’.

Show slide number 4:

Create Tables by Copy method

Table name: Media Field names: MediaId, MediaType, Title, Publishyear

Show cursor movement on slide as narration goes forward.

Okay, next let us learn to create tables by using the copy method.

This is an easy way of creating tables, provided we know that the table structures are going to be very similar.

For this, let us assume that our Library has DVDs and CDs.

And we can store this data in a new table called Media.

A CD or a DVD can have a title and a publish-year for example.

In order to distinguish between an audio and a video, we will introduce a MediaType field.

Now since the Books table has almost similar fields, we can copy-paste the Books table,

And then we can rename the fields and the table name in the process.

We’ll see how.

Go to Base main window.

Right click on Books table. Click on copy and then right click on it

Click on Paste.

Let us go back to the main Base window

Here let us right click on the Books table,

And we will see the copy option. Let us click on it;

And then right click on it.

Notice the various options here. There is paste and there is also a Paste Special.

We can use it copy and paste in a specific format.

Possible formats are Formatted text, HTML or a database table.

So we can choose the database table here,

Or we can choose Paste from the right click menu.

Type in Media in the table name text box

Click on the Definition and Data.

Click on the Next button.

This opens a wizard and in this window,

We will first rename our table by typing in ‘Media’ against the table name.

In the options, we will click on Definition and Data.

Let us click on the Next button.

Move bookId, title and publishyear to the right.

Click on the Next button.

In the next window, we will add the columns.

Let us choose BookId, title and publish-year for this demonstration.

Now we will select these fields on the left and use the single arrow button to move them to the right side

and click on the Next button.

Rename Bookid to MediaId

Click on the Create button.

In the next window, we see our columns.

Here we can rename the fields and change their data types.

We will rename the BookId to MediaId.

And we will click on the Create button.

In the Base main window,

Point to the Media table, then right click on Media table, click on edit.

In the main Base window, there is our new Media table.

Let us now edit the table to add a new field MediaType which will hold the audio or the video type information.

Click on cell below Publishyear

Type ‘MediaType’ as Field Name and then choose Text as Field Type.

Click on Save icon.

Now we are in the table design window.

Here let us introduce MediaType as the last column.

Let us click on the cell below Publishyear

And enter ‘MediaType’ as Field Name and then choose Text as Field Type.

Now let us save the table design and we are done.

There, we just created our Media table by using the Copy method.

Show Slide number 5

Assignment: 1. Create a table. Use the ‘Use Wizard to Create table’ method. 2. Use the ‘Assets’ sample table and rename it to ‘AssetsCopy’. 3. Explore the various options in this method.

Okay, here is another assignment.

Create a table using the ‘Use Wizard to Create table’ method.

Here, use the ‘Assets’ sample table and rename it to ‘AssetsCopy’.

And explore the various options in this method.

Show Slide Number 6

Summary

Create a Table by a) Creating Views b) Using the Copy method

This brings us to the end of this tutorial on Creating Tables in LibreOffice Base

To summarize, we learned how to Create a Table by a) Creating Views and b) Using the Copy method

Acknowledgement Slide Spoken Tutorial Project is a part of the Talk to a Teacher project, supported by the National Mission on Education through ICT, MHRD, Government of India. This project is co-ordinated by http://spoken-tutorial.org. More information on the same is available at the following link http://spoken-tutorial.org/NMEICT-Intro. This script has been contributed by Priya Suresh, DesiCrew Solutions Pvt Ltd. signing off. Thanks for joining.

Contributors and Content Editors

Chandrika