Pravin1389: moved LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships /English to LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships/English

2012-12-01T14:27:47Z

moved LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships /English to LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships/English

Chandrika: Created page with '{| border=1 !Visual Cue !Narration |- |Show Slide Number 1 Spoken tutorial on LibreOffice Base Database Design : 2 Learning Objectives Database Design: 2 (continuation of Datab…'

2012-11-27T15:26:37Z

Created page with '{| border=1 !Visual Cue !Narration |- |Show Slide Number 1 Spoken tutorial on LibreOffice Base Database Design : 2 Learning Objectives Database Design: 2 (continuation of Datab…'

New page

{| border=1
!Visual Cue
!Narration
|-
|Show Slide Number 1
Spoken tutorial on LibreOffice Base Database Design : 2

Learning Objectives

Database Design: 2 (continuation of Database Design: Part 1)

4. Turn information items into columns

5. Specify primary keys

6. Set up the table relationships
|Welcome to the Spoken tutorial on LibreOffice Base.

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

And we will learn the following topics here:

4. Turn information items into columns

5. Specify primary keys

6. Set up the table relationships
|-
|Show Slide Number 2
Database Design Process

Step 1. Determine the purpose of our database

Step 2. Find and organize the information required

Step 3. Divide the information into tables

Step 4. Turn information items into columns
|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.

Then we continued the process by finding and organizing the information about the library and

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.
|-
|Show Slide Number 3

Show BooksAuthorsPublishersTableDataNoId.ppt

Show cursor movements over the slide as narration goes forward.
|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.
|-
|Show Slide Number 4

Step 4. Turn information items into columns

Refine columns based on requirements

Splitting the Author name into First Name and Last Name, helps to search or sort by these columns.

No need to store results of calculations as separate columns in tables, as Base can perform the calculations whenever we want to see the results.
|Now, we can further refine the columns based on our requirements.

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.
|-
|Show Slide Number 5

Step 5. Specify primary keys

Primary key of a table is a column or set of columns that uniquely identifies each row stored in the table.

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

With primary key fields we can quickly associate logically related data from multiple tables and bring the data together

* Primary key does not allow duplicate values.

* Do not use names for primary keys, as two people can share the same name.

* A primary key should always have a value.

* If it is empty or Null, then we can’t consider it a primary key.

* We can force a primary key column to have a value always.

* Base supports the ‘AutoNumber’ data type
|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 is to quickly associate logically related data from multiple tables and bring the data together for us.

And we cannot have duplicate values in a primary key.

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

There can be two people with the same name in the same table.

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.
|-
|Show Slide Number 6

Show BooksAuthorsPublishersTableDataWithId.ppt

Show cursor movements over the slide as narration goes forward.
|As shown in the image on the screen, we can set up the primary keys for our tables as follows:

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.
|-
|Go back to Slide Number 5

Retain slide 5 content. And make the following appear:

Base forbids use of null values in primary key columns and that enforces Entity Integrity.
|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 null.
|-
|Show Slide Number 7

Step 6. Set up the table relationships

* Base is called a Relational Database Management System (RDBMS), as it supports setting up of table relations

* One-to-Many Relationship
|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.
|-
|Show Slide Number 6 again

Show BooksAuthorsPublishersTableDataWithId.ppt

Show cursor movements over the slide as narration goes forward.
|Let us consider the Books and the Authors tables in the image shown now.

A book is written by exactly one author.

Now, there are cases where two or several people co-author a single book.

But we will limit our example to just one person authoring one book.

Continuing on with our example, an author can write several books.

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.
|-
|Show Slide Number 8

Show BooksAuthorsPublishersTableDataWithIdForeignKeys.ppt

Show cursor movements over the slide as narration goes forward.
|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 a primary key in the Publishers table becomes the foreign key in the Books table by adding it there.
|-
|Show Slide Number 7 again.

Retain slide 7 content. And make the following appear:

* One-to-Many Relationship can be represented by sharing a column or a set of columns between two tables

* Table relationships are set up using Foreign keys

* A Primary Key in a table can be represented as a Foreign Key in a second table to establish the relationship.

* Referential Integrity: every foreign key value in a table will have a matching primary key value in the related tables.
|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.

Meaning, every foreign key value in a table will have a matching primary key value in the related tables.
|-
|Show Slide Number 9

Show BooksMembersDesignWithId.ppt

Show cursor movements over the slide as narration goes forward.
|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).
|-
|Show Slide Number 10

Show Many-Manyrelationship.ppt

Show cursor movements over the slide as narration goes forward.
|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.
|-
|Show Slide Number 7 again.

Retain slide 7 content. And make the following appear:

* Many-to-Many relationships can be represented in the database, by creating a third junction table

* The Junction table will have the primary key fields from each of the related tables.
|So by creating a third junction table, we can represent many-to-many relationships.
|-
|Show Slide Number 7 again.

Retain slide 7 content. And make the following appear:

* One-to-one relationship

* It is represented when there are rarely used attributes/columns in a table. These can be moved to a new supplemental table with the primary key being the same as in the main table.

* Each record in the supplemental table will correspond to exactly one record in the main table.
|And finally there is the One-to-one relationship.

Sometimes, some attributes or columns are specific to certain data only and so are rarely filled up with data.

Let us consider the case where only one author has a website address and the rest of them do not.

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

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

Each record in the supplemental table will correspond to exactly one record in the main table

which represents a One-to-one relationship.

So there, we have learnt to set up relationships in our database.
|-
|Show Slide Number 11

Summary:

Database Design

4. Turn information items into columns

5. Specify primary keys

6. Set up the table relationships
|This brings us to the end of this tutorial on the Second part of Database Design in LibreOffice Base

To summarize, we learned the following topics on database design:
4. Turn information items into columns

5. Specify primary keys

6. Set up the table relationships
|-
|Acknowledgement Slide
|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. More information on the same is available at the following link http://spoken-tutorial.org/NMEICT-Intro. This script has been contributed by Priya Suresh, DesiCrew Solutions. signing off. Thanks for joining.
|-

← Older revision	Revision as of 14:27, 1 December 2012
(No difference)

LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships/English - Revision history

Pravin1389: moved LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships /English to LibreOffice-Suite-Base/C4/Database-Design-Primary-Key-and-Relationships/English

Chandrika: Created page with '{| border=1 !Visual Cue !Narration |- |Show Slide Number 1 Spoken tutorial on LibreOffice Base Database Design : 2 Learning Objectives Database Design: 2 (continuation of Datab…'