Difference between revisions of "LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships/English-timed"

!Narration

|Welcome to the Spoken tutorial on LibreOffice Base.

|This tutorial is a continuation of the previous tutorial on Database Design.

|4. Turn information items into columns

|5. Specify primary keys

|6. Set up the table relationships

|In the last tutorial, we started the process of database design for a simple Library application.

|We first determined the purpose of building the Library database.  

|we divided the information into Tables.  

|And so, we had identified four tables in our Library database: Books, Authors, Publications and Members.

|Let us now go to the next step, which is turning the information items into columns.     

|Here, we will decide which information item we want to store in each table.  

|Each information item which we identified earlier, becomes a field, and is displayed as a column in the table.

|As shown in the image on the screen, the Books table has 5 columns which are also called fields.

|So each row or record here contains information about exactly one book in its columns.

|Similarly, each record in the Authors table contains information about just one author.  

|And each record in the Publishers table contains information about just one publisher.  

|For example, we can split the Author name into First Name and Last Name, so that we can search or sort by these columns.  

|And we don’t have to store results of calculations as separate columns in tables  

|because Base can perform the calculations whenever we want to see the results.

|Now that we are clear about tables and columns, let us see how we can specify primary keys next.

|What is a primary key?  

|Each table should include a column or a set of columns that uniquely identifies each row stored in the table.  

|This column or set of columns is the primary key of the table.  

|This is often a unique identification number, such as a Book Id or an Author Id.

|We can use the primary key fields to quickly associate logical related data from multiple tables and bring the data together for us.

|For example, we cannot use people’s names as a primary key, because names are not unique.  

|Next, a primary key should always have a value.  

|If it is empty or Null, then we cannot consider it a primary key.  

|And we can force a primary key column to have a value always by setting the column’s data type as an ‘AutoNumber’ which Base will generate automatically.

|BookId for Books table,  

|AuthorId for Authors table,  

|PublishersId for Publishers table  

|Similarly, though not shown here, MemberId will be the primary key for the Members table.

|Finally, by setting up the primary keys in the tables, we are enforcing Entity Integrity.

|Entity Integrity ensures that there are no duplicate records within the table.  

|It also ensures that the field that identifies each record within the table is unique and never dull.  

| Now that we have the primary keys in the three tables, we can bring them all together by setting up the relationships.  

|Since Base supports this concept, Base is called a Relational Database Management System, RDBMS in short.

|There are a few types of relationships and we’ll see them now.

|First we will see what a One-to-Many relationship is.  

|Let us consider the Books and the Authors tables in the image shown now.

|So for one author represented in the Authors table, there can be many books represented in the Books table written by the author.  

|This is therefore, a one-to-many relationship.

|And we can represent this in our Library database

|By taking the Author Id which is the primary key in the Authors table and adding it to the Books table. Simple.

|So the Author Id in the Books table is called the Foreign key.  

|Similarly the Publisher Id which is the primary key in the Publishers table becomes the foreign key in the Books table by adding it there.  

|So by sharing a column or a set of columns, we can represent one-to-many relationships in the database.

|And table relationships can be set up using the Foreign keys.

|So a primary key in a table can be represented as a foreign key in a second table to establish the relationship.

|Thereby we enforce Referential integrity.  

|Next, let us see what a Many-to-Many relationship is.

|Let us go back to the table design now.

|A book can be issued to any number of library members, (considering there are many copies available).  

|Similarly a member can borrow any number of books (of course, considering, books are available).

|So here we have an example of multiple books being issued to multiple members  

|which represents a Many-to-many relationship.

|So we can represent this many-to-many relationship in our database

|by creating a third table, BooksIssued table, which is also called a Junction table.  

|And here, we will insert the primary keys from each of the two tables - Books and Members, into the BooksIssued table.  

|As a result, the BooksIssued table records each book issued to a member.

|So by creating a third junction table, we can represent many-to-many relationships.

|And finally there is the One-to-one relationship.  

|And leaving the new website column in the Authors table mostly empty, we will consume disk space.

|So we can move this column into a new supplemental table, whose primary key will be the same Author Id.

|which represents a One-to-one relationship.

|So there, we have learnt to set up relationships in our database.

|This brings us to the end of this tutorial on the Second part of Database Design in LibreOffice Base  

|4. Turn information items into columns

|5. Specify primary keys

|6. Set up the table relationships

|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.  

|This project is co-ordinated by http://spoken-tutorial.org.  

 

|This script has been contributed by Priya Suresh, DesiCrew Solutions.

 

And this is Soundharya, DesiCrew Solutions, signing off.  

 

 

Thanks for joining