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