Difference between revisions of "LibreOffice-Suite-Base/C2/Tables-and-Relationships/English-timed"

From Script | Spoken-Tutorial
Jump to: navigation, search
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 Adding data to a Table
+
||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 And click on the Open button.  
+
||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
||Lets click on the file Library.odb and click on the Open button at the bottom.
+
||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 'open', to add data to this table.
+
||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, by typing in values directly into each cell.  
+
||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 cells, row by row, as shown on the screen.
+
||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, by clicking the File menu on the top and then choosing Close.
+
||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, member name, and phone number.  
+
||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 ,and make this the primary key
+
||'''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.
+
||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. which will be the primary key
+
||'''Issue Id, Field type Integer''' which will be the '''primary key''',
  
 
|-
 
|-
 
||06:16
 
||06:16
||Book Id,Field type,Integer
+
||'''Book Id, Field type-Integer''',
  
 
|-
 
|-
 
||06:20
 
||06:20
||Member Id ,Field type,Integer   
+
||'''Member Id, Field type-Integer''',  
  
 
|-
 
|-
 
||06:24
 
||06:24
|Issue Date,Field type,Date   
+
|'''Issue Date, Field type- Date''',  
  
 
|-
 
|-
 
||06:28
 
||06:28
||Return Date,Field type,Date   
+
||'''Return Date, Field type-Date''',  
  
 
|-
 
|-
 
||06:31
 
||06:31
||Actual Return Date,Field type,Date   
+
||'''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, with sample data also.  
+
||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 issue in these three tables.  
+
||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 Keys.  
+
||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.  
+
||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,How will you establish that 'Macbeth' was issued to Ravi Kumar on 2nd June 2011?  
+
||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, which helps interlink the data.  
+
||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 Libre Office Base main window, let us click on Tools and then click on Relationships.  
+
||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 add button, and repeat for the other two tables also.  
+
||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 MemberId.  
+
||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 Project is a part of the Talk to a Teacher project, supported by the National Mission on Education through ICT, MHRD, Government of India.  
+
||'''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 co-ordinated by http://spoken-tutorial.org.  
+
||This project is coordinated by http://spoken-tutorial.org.  
  
 
|-
 
|-
Line 384: Line 384:
 
|-
 
|-
 
||12:08
 
||12:08
||This tutorial has been contributed by ...............................(Name of the translator and narrator)
+
||This script has been contributed by Priya Suresh, DesiCrew Solutions. And this is Soundharya, DesiCrew Solutions, signing off.  
 
+
Thanks for joining.
And this is -----------------------(name of the recorder) from --------------------------(name of the place)signing off. Thanks for watching. 
+
 
+
  Thanks for joining
+

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.

Contributors and Content Editors

Gaurav, Minal, PoojaMoolya, Sandhya.np14