Difference between revisions of "LibreOffice-Suite-Base/C2/Tables-and-Relationships/English-timed"
From Script | Spoken-Tutorial
Sandhya.np14 (Talk | contribs) |
|||
Line 5: | Line 5: | ||
|- | |- | ||
||00:00 | ||00:00 | ||
− | ||Welcome to the Spoken tutorial on LibreOffice Base. | + | ||Welcome to the '''Spoken tutorial''' on '''LibreOffice Base'''. |
|- | |- | ||
||00:04 | ||00:04 | ||
− | ||In this tutorial, we will cover Tables and Relationships in LibreOffice Base. | + | ||In this tutorial, we will cover '''Tables''' and '''Relationships''' in '''LibreOffice Base'''. |
|- | |- | ||
||00:10 | ||00:10 | ||
− | ||Here, we will learn about | + | ||Here, we will learn about: * adding data to a '''Table'''. |
|- | |- | ||
||00:16 | ||00:16 | ||
− | ||Define and create relationships | + | ||* Define and create relationships. |
|- | |- | ||
||00:19 | ||00:19 | ||
− | ||In the previous LibreOffice Base tutorial, we introduced Base, database basics and learnt how to create a database and a table. | + | ||In the previous '''LibreOffice Base''' tutorial, we introduced Base, database basics and learnt how to create a database and a table. |
|- | |- | ||
||00:31 | ||00:31 | ||
− | ||During the course of the tutorial we also created an example database called Library and created a Books table as well. | + | ||During the course of the tutorial, we also created an example database called '''Library''' and created a ''''Books' table''' as well. |
|- | |- | ||
||00:42 | ||00:42 | ||
− | ||In this tutorial, we will resume with the Library database and learn how to add data to a table. | + | ||In this tutorial, we will resume with the '''Library''' database and learn how to add data to a table. |
|- | |- | ||
||00:51 | ||00:51 | ||
− | ||For this, let us invoke the LibreOffice Base program. | + | ||For this, let us invoke the '''LibreOffice Base''' program. |
|- | |- | ||
||00:57 | ||00:57 | ||
− | ||For this, we will click on the Start button at the bottom left of the screen, | + | ||For this, we will click on the '''Start''' button at the bottom left of the screen, |
|- | |- | ||
||01:03 | ||01:03 | ||
− | ||click on All programs, then click on LibreOffice Suite and LibreOffice Base. | + | ||click on '''All programs''', then click on '''LibreOffice Suite''' and '''LibreOffice Base'''. |
|- | |- | ||
||01:12 | ||01:12 | ||
− | ||Since we already created the Library database in the last tutorial, this time we will just need to open it. | + | ||Since we already created the 'Library' database in the last tutorial, this time we will just need to open it. |
|- | |- | ||
||01:21 | ||01:21 | ||
− | ||To do so, let us click on the 'open an existing database file' option. | + | ||To do so, let us click on the '''open an existing database file''' option. |
|- | |- | ||
||01:28 | ||01:28 | ||
− | ||In the 'Recently Used' drop down box, our Library database should be visible | + | ||In the '''Recently Used''' drop-down box, our 'Library' database should be visible. |
|- | |- | ||
||01:35 | ||01:35 | ||
− | ||So now, click on the Finish button. | + | ||So now, click on the '''Finish''' button. |
|- | |- | ||
||01:38 | ||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. | + | ||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 | ||01:50 | ||
− | ||Once found, click on the filename | + | ||Once found, click on the filename and click on the '''Open''' button. |
|- | |- | ||
||01:57 | ||01:57 | ||
− | ||Now, if LibreOffice Base program is already open, we can open the Library database from here | + | ||Now, if 'LibreOffice Base' program is already open, we can open the 'Library database' from here. |
|- | |- | ||
||02:07 | ||02:07 | ||
− | ||By clicking on the File menu on the top and then clicking on Open. | + | ||By clicking on the '''File''' menu on the top and then clicking on '''Open'''. |
|- | |- | ||
||02:14 | ||02:14 | ||
− | ||We will browse the Windows directory where the Library database file is saved. | + | ||We will browse the Windows directory where the 'Library' database file is saved. |
|- | |- | ||
||02:21 | ||02:21 | ||
− | || | + | ||Let's click on the file 'Library.odb' and click on the '''Open''' button at the bottom. |
|- | |- | ||
||02:31 | ||02:31 | ||
− | ||Now we are in the Library Database. | + | ||Now we are in the '''Library Database'''. |
|- | |- | ||
||02:35 | ||02:35 | ||
− | ||Let us click on the Tables icon in the Database list on the left panel. | + | ||Let us click on the '''Tables''' icon in the '''Database''' list on the left panel. |
|- | |- | ||
||02:42 | ||02:42 | ||
− | ||Notice that the Books table appears in the Tables list on the right panel. | + | ||Notice that the '''Books''' table appears in the '''Tables''' list on the right panel. |
|- | |- | ||
||02:48 | ||02:48 | ||
− | ||Let us now right click on the Books table. | + | ||Let us now right-click on the '''Books''' table. |
|- | |- | ||
Line 105: | Line 105: | ||
|- | |- | ||
||02:58 | ||02:58 | ||
− | ||Let us now click on ' | + | ||Let us now click on '''Open''' to add data to this table. |
|- | |- | ||
||03:04 | ||03:04 | ||
− | ||Alternately, we can also double click on the table name to open it. | + | ||Alternately, we can also double-click on the table name to open it. |
|- | |- | ||
||03:10 | ||03:10 | ||
− | ||A new window opens with the title 'Books – Library – LibreOffice Base: Table Data View'. | + | ||A new '''window''' opens with the title ''''Books – Library – LibreOffice Base: Table Data View''''. |
|- | |- | ||
||03:20 | ||03:20 | ||
− | ||Now we can start entering data into the Books table | + | ||Now we can start entering data into the '''Books''' table by typing in values directly into each '''cell'''. |
|- | |- | ||
||03:31 | ||03:31 | ||
− | ||Notice the Bookid column has 'AutoField' | + | ||Notice the '''Bookid''' column has 'AutoField'. |
|- | |- | ||
||03:37 | ||03:37 | ||
− | ||This means Base will assign ascending numbers automatically to each row of data that we insert. | + | ||This means '''Base''' will assign ascending numbers automatically to each row of data that we insert. |
|- | |- | ||
||03:48 | ||03:48 | ||
− | ||Now, let us input data into the | + | ||Now, let us input data into the '''cell'''s, row by row, as shown on the screen. |
|- | |- | ||
||04:22 | ||04:22 | ||
− | ||So, here we have 5 rows of sample data in our table Books. | + | ||So, here we have 5 rows of sample data in our '''table''' 'Books'. |
|- | |- | ||
||04:29 | ||04:29 | ||
− | ||Let us close the window | + | ||Let us close the window by clicking the '''File''' menu on the top and then choosing '''Close'''. |
|- | |- | ||
Line 145: | Line 145: | ||
|- | |- | ||
||04:42 | ||04:42 | ||
− | ||Create a Members table that will store information about each member, for example | + | ||Create a '''Members''' table that will store information about each member, for example- member name', and phone number. |
|- | |- | ||
Line 153: | Line 153: | ||
|- | |- | ||
||04:57 | ||04:57 | ||
− | ||Member Id with Field type Integer | + | ||'''Member Id''' with '''Field type- Integer''' and make this the '''primary key'''. |
|- | |- | ||
||05:06 | ||05:06 | ||
− | ||Name with Fieldtype Text | + | ||'''Name''' with '''Fieldtype- Text''', |
|- | |- | ||
||05:10 | ||05:10 | ||
− | ||Phone with Fieldtype Text | + | ||'''Phone''' with '''Fieldtype- Text'''. |
|- | |- | ||
||05:15 | ||05:15 | ||
− | ||Okay, when you are done, this is how the Members table will look like. | + | ||Okay, when you are done, this is how the '''Members''' table will look like. |
|- | |- | ||
Line 173: | Line 173: | ||
|- | |- | ||
||05:25 | ||05:25 | ||
− | ||Now let us add 4 sample members into the Members table now as shown on the screen , | + | ||Now let us add 4 sample members into the '''Members''' table now as shown on the screen , |
|- | |- | ||
||05:35 | ||05:35 | ||
− | ||just the way we did for the Books table.<pause for 10 seconds> | + | ||just the way we did for the Books table. <pause for 10 seconds> |
|- | |- | ||
Line 185: | Line 185: | ||
|- | |- | ||
||05:50 | ||05:50 | ||
− | ||Now, let us go back to the main window and click on the Tables Icon again | + | ||Now, let us go back to the main window and click on the '''Tables''' Icon again |
|- | |- | ||
||05:57 | ||05:57 | ||
− | || | + | ||and let us create the third table: '''Books Issued'''. |
|- | |- | ||
||06:04 | ||06:04 | ||
− | ||When done, the Books Issued table will have the following fields: | + | ||When done, the '''Books Issued''' table will have the following fields: |
|- | |- | ||
||06:09 | ||06:09 | ||
− | ||Issue Id, Field type Integer | + | ||'''Issue Id, Field type Integer''' which will be the '''primary key''', |
|- | |- | ||
||06:16 | ||06:16 | ||
− | ||Book Id,Field type | + | ||'''Book Id, Field type-Integer''', |
|- | |- | ||
||06:20 | ||06:20 | ||
− | ||Member Id ,Field type | + | ||'''Member Id, Field type-Integer''', |
|- | |- | ||
||06:24 | ||06:24 | ||
− | |Issue Date,Field type | + | |'''Issue Date, Field type- Date''', |
|- | |- | ||
||06:28 | ||06:28 | ||
− | ||Return Date,Field type | + | ||'''Return Date, Field type-Date''', |
|- | |- | ||
||06:31 | ||06:31 | ||
− | ||Actual Return Date,Field type | + | ||'''Actual Return Date, Field type- Date''', |
|- | |- | ||
||06:35 | ||06:35 | ||
− | ||And Checked In,Field type Yes/No Boolean | + | ||And '''Checked In, Field type- Yes/No Boolean'''. |
|- | |- | ||
||06:42 | ||06:42 | ||
− | ||Okay, we have created the Books Issued table | + | ||Okay, we have created the '''Books Issued''' table. |
|- | |- | ||
||06:47 | ||06:47 | ||
− | ||And now let us add the following sample data into it as you can see on the screen. | + | ||And now, let us add the following sample data into it as you can see on the screen. |
|- | |- | ||
Line 237: | Line 237: | ||
|- | |- | ||
||07:17 | ||07:17 | ||
− | ||Now, we have the three tables in our Library database | + | ||Now, we have the three tables in our Library database with sample data also. |
|- | |- | ||
||07:25 | ||07:25 | ||
− | ||Let us now learn about defining relationships in the database. | + | ||Let us now learn about defining '''relationships''' in the database. |
|- | |- | ||
||07:31 | ||07:31 | ||
− | ||So we created three tables to store three different sets of information. | + | ||So, we created three tables to store three different sets of information. |
|- | |- | ||
||07:38 | ||07:38 | ||
− | ||Books, Members and Issue of Books to Members. | + | ||'''Books, Members''' and Issue of Books to Members. |
|- | |- | ||
||07:44 | ||07:44 | ||
− | ||Now we also set up columns to uniquely identify each book, each member and each book | + | ||Now we also set up columns to uniquely identify each book, each member and each book issued in these three tables. |
|- | |- | ||
||07:57 | ||07:57 | ||
− | ||They are the Primary | + | ||They are the '''Primary Key'''s. |
|- | |- | ||
|08:00 | |08:00 | ||
− | |One of the various advantages of a primary key is that it helps to establish relationships between tables. | + | |One of the various advantages of a '''primary key''' is that it helps to establish relationships between tables. |
|- | |- | ||
Line 269: | Line 269: | ||
|- | |- | ||
||08:13 | ||08:13 | ||
− | ||Let us look at the Books Issued table. Here, we see the Book Id and Member Id fields. | + | ||Let us look at the '''Books Issued''' table. Here, we see the '''Book-Id''' and '''Member-Id''' fields. |
|- | |- | ||
||08:23 | ||08:23 | ||
− | |They can have any value in the Books Issued table. | + | |They can have any value in the '''Books Issued''' table. |
|- | |- | ||
||08:28 | ||08:28 | ||
− | ||But, they will need to correspond to the same values as we have in the Books and Members tables respectively. | + | ||But, they will need to correspond to the same values as we have in the '''Books''' and '''Members''' tables respectively. |
|- | |- | ||
||08:38 | ||08:38 | ||
− | ||So if the book, Macbeth, has its Book Id as 3 in the Books table | + | ||So if the book, Macbeth, has its 'Book Id' as 3 in the 'Books' table |
|- | |- | ||
||08:45 | ||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 | ||08:56 | ||
− | ||So to explicitly connect these two tables, we will still need to link them someway. | + | ||So, to explicitly connect these two tables, we will still need to link them someway. |
|- | |- | ||
||09:05 | ||09:05 | ||
− | ||Also, for example | + | ||Also, for example- How will you establish that 'Macbeth' was issued to Ravi Kumar on 2nd June 2011? |
|- | |- | ||
Line 301: | Line 301: | ||
|- | |- | ||
||09:25 | ||09:25 | ||
− | ||All of these can be achieved by setting up relationships | + | ||All of these can be achieved by setting up relationships which help interlink the data. |
|- | |- | ||
|09:34 | |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. | + | |We will need to force '''Base''' to use values from the 'Books' table and the 'Members' table only, by linking the appropriate fields. |
|- | |- | ||
Line 313: | Line 313: | ||
|- | |- | ||
||09:48 | ||09:48 | ||
− | ||In the | + | ||In the 'LibreOffice Base' main window, let us click on '''Tools''' and then click on '''Relationships'''. |
|- | |- | ||
||09:58 | ||09:58 | ||
− | ||This opens a small pop up window | + | ||This opens a small pop-up window. |
|- | |- | ||
||10:03 | ||10:03 | ||
− | ||Here we will select the top most table and click on the | + | ||Here, we will select the top most '''table''' and click on the '''Add''' button and repeat for the other two tables also. |
|- | |- | ||
||10:15 | ||10:15 | ||
− | ||Close the pop up window. | + | ||Close the pop-up window. |
|- | |- | ||
||10:18 | ||10:18 | ||
− | ||Now we see the three tables Books, Books Issued and Members in a line. | + | ||Now we see the three tables '''Books, Books Issued''' and '''Members''' in a line. |
|- | |- | ||
Line 337: | Line 337: | ||
|- | |- | ||
||10:35 | ||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. | + | ||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. |
|- | |- | ||
Line 345: | Line 345: | ||
|- | |- | ||
||10:57 | ||10:57 | ||
− | ||Let us repeat the same for | + | ||Let us repeat the same for '''Member-Id'''. |
|- | |- | ||
||11:02 | ||11:02 | ||
− | ||Click on the Member Id in the Members table and drag and drop it in the Books Issued table. | + | ||Click on the '''Member-Id''' in the '''Members''' table and drag and drop it in the '''Books Issued''' table. |
|- | |- | ||
Line 361: | Line 361: | ||
|- | |- | ||
||11:20 | ||11:20 | ||
− | ||And therefore interlink meaningful data stored in various tables in the relational database. | + | ||And therefore interlink meaningful data stored in various tables in the '''relational database'''. |
|- | |- | ||
||11:30 | ||11:30 | ||
− | ||This brings us to the end of this tutorial on Tables and Relationships in LibreOffice | + | ||This brings us to the end of this tutorial on '''Tables and Relationships in LibreOffice'''. |
|- | |- | ||
||11:36 | ||11:36 | ||
− | ||To summarize, we learned how to: Add data to a table Define and create relationships | + | ||To summarize, we learned how to: Add data to a table, Define and create relationships. |
|- | |- | ||
||11:45 | ||11:45 | ||
− | ||Spoken Tutorial | + | ||'''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 | ||11:57 | ||
− | ||This project is | + | ||This project is coordinated by http://spoken-tutorial.org. |
|- | |- | ||
Line 384: | Line 384: | ||
|- | |- | ||
||12:08 | ||12:08 | ||
− | ||This | + | ||This script has been contributed by Priya Suresh, DesiCrew Solutions. And this is Soundharya, DesiCrew Solutions, signing off. |
− | + | Thanks for joining. | |
− | And this is | + | |
− | + | ||
− | + |
Revision as of 09:10, 27 September 2015
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 | Let's 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 issued 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 help 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 'LibreOffice 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 Member-Id. |
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 coordinated by http://spoken-tutorial.org. |
12:03 | More information on the same is available at the following link. |
12:08 | This script has been contributed by Priya Suresh, DesiCrew Solutions. And this is Soundharya, DesiCrew Solutions, signing off.
Thanks for joining. |