LibreOffice-Suite-Base/C2/Tables-and-Relationships/English-timed

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.

Contributors and Content Editors

Gaurav, Minal, PoojaMoolya, Sandhya.np14