LibreOffice-Suite-Base/C2/Tables-and-Relationships/English
| Visual Cues | Narration |
|---|---|
| Show Slide Number 1
Spoken tutorial on LibreOffice Base: Tables and Relationships Learning Objectives 1.Adding data to a Table 2.Define and create relationships |
Welcome to the Spoken tutorial on LibreOffice Base.
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 |
| Show Slide Number 2
Adding data to a Table |
In the previous LibreOffice Base tutorial, we introduced Base, database basics and learnt how to create a database and 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. |
| Open LibreOffice Base program from Start-->All Programs-->LibreOffice Suite--> LibreOffice Base
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, let us invoke the LibreOffice Base program.
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. |
| Click on the Tables icon in the Database list on the left panel.
Right click on the Books table Click on 'open' Keep the new window 'Books – Library – LibreOffice Base: Table Data View' in focus. |
Now we are in the Library Database.
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. |
| Enter the following rows of data into the Books table.
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 |
Notice the Bookid column has 'AutoField',
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. |
| Close the window. | So, here we have 5 rows of sample data in our table Books.
Let us close the window, by clicking the File menu on the top and then choosing Close. |
| Show Slide Number 3
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 |
Here is an assignment for you.
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 |
| Go back to the main window, Click on Tables Icon on the Database List in the left and click on 'Create table in design view'.
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. |
|
| Close the window. | Okay, when you are done, this is how the Members table will look like.
Let us close this window. |
| In the main window, right click on Members table and click on open.
Type in values as below: Ravi Kumar, 29488737 Nisha Sharma, 82383848 Jacob Robin, 88737433 Salma Aziz, 72383483 |
Now let us add 4 sample members into the Members table now as shown on the screen ,
just the way we did for the Books table.<pause for 10 seconds> |
| Close the window. | Once done, let us close this window. |
| Go to the main window, Click on Tables Icon on the Database List in the left and click on 'Create table in design view'.
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. |
Now, let us go back to the main window and click on the Tables Icon again.
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 |
| In the main window, rightclick on BooksIssued table and click on open.
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 |
Okay, we have created the BooksIssued table,
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. |
| Go to the LibreOffice Base main window and point to the three tables. | Now, we have the three tables in our Library database, with sample data also.
Let us now learn about defining relationships in the database. |
| Show Slide Number 4
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 |
So we created three tables to store three different sets of information.
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. |
| Go back to the LibreOffice Base main window. Click on Tools and click on Relationships
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. |
In the LibreOffice Base main window, let us click on Tools and then click on Relationships.
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. |
| Show Slide Number 4
1. Adding data to a table 2. Define and create relationships |
This brings us to the end of this tutorial on Tables and Relationships in LibreOffice
To summarize, we learned how to: 1. Add data to a table 2. Define and create relationships |
| 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. signing off. Thanks for joining. |
