Difference between revisions of "LibreOffice-Suite-Base/C2/Tables-and-Relationships/English"

Spoken tutorial on LibreOffice Base: Tables and Relationships Learning Objectives 1.Adding data to a Table 2.Define and create relationships

In this tutorial, we will cover Tables and Relationships in LibreOffice Base. Here, we will learn about 1.Adding data to a Table 2.Define and create relationships

Adding data to a Table

During the course of the tutorial we also created an example database called Library and created a Books table as well.

In this tutorial, we will resume with the Library database and learn how to add data to a table.

Click on the 'open an existing database file' option. In the 'Recently Used' drop down box, Library should be visible,

Click on Library.odb file

Click on the Finish button.

In LibreOffice Base, click on File >> Open .

Browse to directory where Library.odb file is saved

Click on it, and click on open.

For this, we will click on the Start button at the bottom left of the screen, click on All programs, then click on LibreOffice Suite and LibreOffice Base.

Since we already created the Library database in the last tutorial, this time we will just need to open it.

To do so, let us click on the 'open an existing database file' option.

In the 'Recently Used' drop down box, our Library database should be visible,

So now, click on the Finish button.

If you don't see it, we can click on the Open button in the centre to browse to the Windows directory where Library database is saved.

Once found, click on the filename

And click on the Open button.

Now, if LibreOffice Base program is already open, we can open the Library database from here,

By clicking on the File menu on the top and then clicking on Open.

We will browse the Windows directory where the Library database file is saved.

Lets click on the file Library.odb and click on the Open button at the bottom.

Right click on the Books table

Click on 'open'

Keep the new window 'Books – Library – LibreOffice Base: Table Data View' in focus.

Let us click on the Tables icon in the Database list on the left panel.

Notice that the Books table appears in the Tables list on the right panel.

Let us now right click on the Books table.

Notice the various options you can choose from here.

Let us now click on 'open', to add data to this table.

Alternately, we can also double click on the table name to open it.

A new window opens with title 'Books – Library – LibreOffice Base: Table Data View'.

Now we can start entering data into the Books table, by typing in values directly into each cell.

Student can pause here to copy the data into his/her Base window.

BookId field will autopopulate. Start from title field. Use tabs to go to the next field.

An Autobiography, Jawaharlal Nehru, 1975, Cambridge, 200

Conquest of Self, Mahatma Gandhi, 1975, Orient, 150

Merchant of Venice, William Shakespeare, 1980, Cambridge, 200

Macbeth, William Shakespeare, 1980, Cambridge, 200

Seven Summers, Mulk Raj Anand, 1985, Orient, 150

This means Base will assign ascending numbers automatically to each row of data that we insert.

Now, let us input data into the cells, row by row, as shown on the screen.

Let us close the window, by clicking the File menu on the top and then choosing Close.

Assignment: Create Members Table

Fields and Field type 1. MemberId with Fieldtype Integer. Primary key 2. Name with Fieldtype Text 3. Phone with Fieldtype Text

Create a Members table that will store information about each member, for example, member name, and phone number.

Include the following three fields. 1. MemberId with Fieldtype Integer , and make this the primary key

2. Name with Fieldtype Text

3. Phone with Fieldtype Text

Type in Field name and Field type as follows. Use tab key.

MemberId, Integer[INTEGER] Change the Field Properties in the bottom section. Change AutoValue from No to Yes.

Name, Text[VARCHAR]

Phone, Text[VARCHAR]

Click on Save icon below the File menu. Enter 'Members' in the table name text box. Click ok button.

Let us close this window.

Type in values as below:

Ravi Kumar, 29488737 Nisha Sharma, 82383848 Jacob Robin, 88737433 Salma Aziz, 72383483

just the way we did for the Books table.<pause for 10 seconds>

Type in Field name and Field type as follows. Use tab key.

IssueId, Integer[INTEGER] Change the Field Properties in the bottom section. Change AutoValue from No to Yes.

BookId, Integer[INTEGER]

MemberId, Integer[INTEGER]

IssueDate, Date[DATE]

ReturnDate, Date[DATE]

ActualReturnDate, Date[DATE]

CheckedIn Yes/No [Boolean]

Click on Save icon below the File menu. Enter 'BooksIssued' in the table name text box and click ok button.

In the last frame, show cursor movement as narration goes forward.

Close the window.

And let us create the third table: BooksIssued.

When done, the BooksIssued table will have the following fields:

IssueId, Fieldtype Integer. which will be the primary key BookId, Fieldtype Integer MemberId Fieldtype Integer IssueDate Fieldtype Date ReturnDate Fieldtype Date ActualReturnDate Fieldtype Date And CheckedIn Fieldtype Yes/No Boolean

Type in the following values. The IssueId Field will autopopulate itself. Start typing from the BookId field:

0, 1, 1-6-11, 1-7-11, tab key, No 3, 0, 2-6-11, 2-7-11, tab key, No 4, 2, 1-5-11, 2-6-11, 3-6-11, Yes

And now let us add the following sample data into it as you see on the screen.

While this may not make absolute sense right now, we will soon understand what’s happening.

Let us now learn about defining relationships in the database.

Define and Create relationships Show the 3 tables books, members and booksissued as tables and cells.

Move the cursor among each table and field to follow narration.

We can also circle a particular region or word in the slide to bring attention to the end-user.

Point cursor at the BooksIssued table

Books, Members and Issue of Books to Members.

Now we also set up columns to uniquely identify each book, each member and each book issue in these three tables.

They are the Primary Keys.

One of the various advantages of a primary key is that it helps to establish relationships between tables.

But why do we need relationships?

Let us look at the BooksIssued table. Here, we see the BookId and MemberId fields.

They can have any value in the BooksIssued table.

But, they will need to correspond to the same values as we have in the Books and Members tables respectively.

So if the book, Macbeth, has its BookId as 3 in the Books table,

Then by using 3 in the BookId of the BooksIssued table, we will still be referring to the same book.

So to explicitly connect these two tables, we will still need to link them someway.

Also, for example, How will you establish that 'Macbeth' was issued to Ravi Kumar on 2nd June 2011?

Or how will you ensure that a book is issued to only members of the library and not anyone else?

All of these can be achieved by setting up relationships, which helps interlink the data.

We will need to force Base to use values from the Books table and the Members table only, by linking the appropriate fields.

Let us see how.

This opens a small pop up window, select top most table and click on add button, repeat for the other two tables also.

Close the pop up window.

By click, drag and drop, space out the three tables well.

Click on the BookId in the Books table and drag and drop it on the BookId in the BooksIssued table. Point cursor on the line.

Click on the MemberId in the Members table and drag and drop it in the BooksIssued table.

This opens a small pop up window,

Here we will select the top most table and click on the add button, and repeat for the other two tables also.

Close the pop up window.

Now we see the three tables Books, BooksIssued and Members in a line.

By clicking, dragging and dropping, let us introduce more space among the tables.

Now, let us click on the BookId in the Books table and drag and drop it on the BookId in the BooksIssued table.

Notice a line connecting these two field names. So there, we have set up one relationship!

Let us repeat the same for MemberId.

Click on the MemberId in the Members table and drag and drop it in the BooksIssued table.

You can see that we just created two relationships.

And, this is how we can establish relationships

And therefore interlink meaningful data stored in various tables in the relational database.

1. Adding data to a table 2. Define and create relationships

To summarize, we learned how to: 1. Add data to a table 2. Define and create relationships