Difference between revisions of "LibreOffice-Suite-Base/C2/Tables-and-Relationships/English-timed"
From Script | Spoken-Tutorial
(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 cover Tables and Relationships in Li…') |
|||
| Line 1: | Line 1: | ||
{| border=1 | {| border=1 | ||
| − | + | |'''Time''' | |
| − | + | |'''Narration''' | |
|- | |- | ||
Revision as of 14:50, 9 July 2014
| Time | Narration |
| 00:00 | Welcome to the Spoken tutorial on LibreOffice Base. |
| 00:04 | In this tutorial, we will cover Tables and Relationships in LibreOffice Base. |
| 00:10 | Here, we will learn about Adding data to a Table |
| 00:16 | Define and create relationships |
| 00:19 | In the previous LibreOffice Base tutorial, we introduced Base, database basics and learnt how to create a database and a table. |
| 00:31 | During the course of the tutorial we also created an example database called Library and created a Books table as well. |
| 00:42 | In this tutorial, we will resume with the Library database and learn how to add data to a table. |
| 00:51 | For this, let us invoke the LibreOffice Base program. |
| 00:57 | For this, we will click on the Start button at the bottom left of the screen, |
| 01:03 | click on All programs, then click on LibreOffice Suite and LibreOffice Base. |
| 01:12 | Since we already created the Library database in the last tutorial, this time we will just need to open it. |
| 01:21 | To do so, let us click on the 'open an existing database file' option. |
| 01:28 | In the 'Recently Used' drop down box, our Library database should be visible, |
| 01:35 | So now, click on the Finish button. |
| 01:38 | 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. |
| 01:50 | Once found, click on the filename And click on the Open button. |
| 01:57 | Now, if LibreOffice Base program is already open, we can open the Library database from here, |
| 02:07 | By clicking on the File menu on the top and then clicking on Open. |
| 02:14 | We will browse the Windows directory where the Library database file is saved. |
| 02:21 | Lets click on the file Library.odb and click on the Open button at the bottom. |
| 02:31 | Now we are in the Library Database. |
| 02:35 | Let us click on the Tables icon in the Database list on the left panel. |
| 02:42 | Notice that the Books table appears in the Tables list on the right panel. |
| 02:48 | Let us now right click on the Books table. |
| 02:53 | Notice the various options you can choose from here. |
| 02:58 | Let us now click on 'open', to add data to this table. |
| 03:04 | Alternately, we can also double click on the table name to open it. |
| 03:10 | A new window opens with the title 'Books – Library – LibreOffice Base: Table Data View'. |
| 03:20 | Now we can start entering data into the Books table, by typing in values directly into each cell. |
| 03:31 | Notice the Bookid column has 'AutoField', |
| 03:37 | This means Base will assign ascending numbers automatically to each row of data that we insert. |
| 03:48 | Now, let us input data into the cells, row by row, as shown on the screen. |
| 04:22 | So, here we have 5 rows of sample data in our table Books. |
| 04:29 | Let us close the window, by clicking the File menu on the top and then choosing Close. |
| 04:39 | Here is an assignment for you. |
| 04:42 | Create a Members table that will store information about each member, for example, member name, and phone number. |
| 04:53 | Include the following three fields. |
| 04:57 | Member Id with Field type Integer ,and make this the primary key |
| 05:06 | Name with Fieldtype Text |
| 05:10 | Phone with Fieldtype Text |
| 05:15 | Okay, when you are done, this is how the Members table will look like. |
| 05:22 | Let us close this window. |
| 05:25 | Now let us add 4 sample members into the Members table now as shown on the screen , |
| 05:35 | just the way we did for the Books table.<pause for 10 seconds> |
| 05:46 | Once done, let us close this window. |
| 05:50 | Now, let us go back to the main window and click on the Tables Icon again. |
| 05:57 | And let us create the third table: Books Issued. |
| 06:04 | When done, the Books Issued table will have the following fields: |
| 06:09 | Issue Id, Field type Integer. which will be the primary key |
| 06:16 | Book Id,Field type,Integer |
| 06:20 | Member Id ,Field type,Integer |
| 06:24 | Issue Date,Field type,Date |
| 06:28 | Return Date,Field type,Date |
| 06:31 | Actual Return Date,Field type,Date |
| 06:35 | And Checked In,Field type Yes/No Boolean |
| 06:42 | Okay, we have created the Books Issued table, |
| 06:47 | And now let us add the following sample data into it as you can see on the screen. |
| 06:56 | While this may not make absolute sense right now, we will soon understand what’s happening. |
| 07:17 | Now, we have the three tables in our Library database, with sample data also. |
| 07:25 | Let us now learn about defining relationships in the database. |
| 07:31 | So we created three tables to store three different sets of information. |
| 07:38 | Books, Members and Issue of Books to Members. |
| 07:44 | Now we also set up columns to uniquely identify each book, each member and each book issue in these three tables. |
| 07:57 | They are the Primary Keys. |
| 08:00 | One of the various advantages of a primary key is that it helps to establish relationships between tables. |
| 08:10 | But why do we need relationships? |
| 08:13 | Let us look at the Books Issued table. Here, we see the Book Id and Member Id fields. |
| 08:23 | They can have any value in the Books Issued table. |
| 08:28 | But, they will need to correspond to the same values as we have in the Books and Members tables respectively. |
| 08:38 | So if the book, Macbeth, has its Book Id as 3 in the Books table, |
| 08:45 | Then by using 3 in the Book Id of the Books Issued table, we will still be referring to the same book. |
| 08:56 | So to explicitly connect these two tables, we will still need to link them someway. |
| 09:05 | Also, for example,How will you establish that 'Macbeth' was issued to Ravi Kumar on 2nd June 2011? |
| 09:16 | Or how will you ensure that a book is issued to only members of the library and not anyone else? |
| 09:25 | All of these can be achieved by setting up relationships, which helps interlink the data. |
| 09:34 | We will need to force Base to use values from the Books table and the Members table only, by linking the appropriate fields. |
| 09:46 | Let us see how. |
| 09:48 | In the Libre Office Base main window, let us click on Tools and then click on Relationships. |
| 09:58 | This opens a small pop up window, |
| 10:03 | Here we will select the top most table and click on the add button, and repeat for the other two tables also. |
| 10:15 | Close the pop up window. |
| 10:18 | Now we see the three tables Books, Books Issued and Members in a line. |
| 10:26 | By clicking, dragging and dropping, let us introduce more space among the tables. |
| 10:35 | Now, let us click on the Book Id in the Books table and drag and drop it on the Book Id in the Books Issued table. |
| 10:48 | Notice a line connecting these two field names. So there, we have set up one relationship! |
| 10:57 | Let us repeat the same for MemberId. |
| 11:02 | Click on the Member Id in the Members table and drag and drop it in the Books Issued table. |
| 11:11 | You can see that we just created two relationships. |
| 11:16 | And, this is how we establish relationships |
| 11:20 | And therefore interlink meaningful data stored in various tables in the relational database. |
| 11:30 | This brings us to the end of this tutorial on Tables and Relationships in LibreOffice |
| 11:36 | To summarize, we learned how to: Add data to a table Define and create relationships |
| 11:45 | 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. |
| 11:57 | This project is co-ordinated by http://spoken-tutorial.org. |
| 12:03 | More information on the same is available at the following link. |
| 12:08 | This tutorial has been contributed by ...............................(Name of the translator and narrator)
And this is -----------------------(name of the recorder) from --------------------------(name of the place)signing off. Thanks for watching. Thanks for joining |
