A Relational Database
A “Database” is a collection of data, such as a directory or a subdirectory.
When creating a “Database”, or rather a card catalog, we don’t have to use “Access”. This can also be done in “Excel” using a table .
When we create a “Database” in “Access”, we create a “Database” that consists of several tables that are linked together. This is called a relational “Database”.
The example of a relational “Database” below, is a mail order company. In this example, the company can offer multiple products to a customer and can supply a product to multiple customers. If we enter these orders in a card catalog, then the data that is repeated. As seen in the picture below, the name and address of the customer are repeated. The relational “Database” eliminates all of this redundant data.
In a relational “Database”, we can create several tables.
All you have to do is add a field with unique data to different tables. Through this unique feature, we can identify each record in our table.
To enter unique data for a field in a table, we make it a “Primary key”. A “Primary key” is a field in the table with unique data.
We see that each table has a “Primary key”. The illustration below is for the “Customers” table with the “Customer ID” field as the “Primary Key”. For the “Orders” table, it is the “Number” field and for the “Items” table, it is the “Item ID”. The “Primary keys” for each table in the image below are shown in BOLD.
This prevents duplication of customers in the “Customers” table, or having the same order number multiple times in the “Orders” table, or the same article multiple times in the “Articles” table.
In the “Orders” table, I added the fields with “Primary keys” from the other two tables. This helps connect these three tables together. So the “Customer ID” field and “Article ID” are added to the “Orders” table.
Each relationship consists of a primary table and a related table. The primary table usually contains the “Primary key”. In the example below the “Customers” table is the primary table and the “Orders” table is the related table.
In the “Customers” table, “Customer ID” is the “Primary key” and in the “Orders” table, the “Customer ID” field is added, but this is not a “Primary key”. This means that the “Customer ID” field in the ” Customers” table may occur only once, but in the ” Orders” table, it can occur multiple times. This allows you to have a customer with multiple orders.
When we talk about relational “Databases”, we have three possible “Relationships”:
- “one-to-one” relation
- “one-to-many” relation
- “many-to-many” relation
A “one-to-one” “Relationship” is when you have the primary field from the primary table, with just one related field in the related table. Both fields are primary fields. In the example below, the customer has one private address, and one billing address.
The most commonly used is the “one-to-many” relationship, where the primary field in the primary table may have multiple related fields in the related table. One example is when a customer places multiple orders.
The third is the “many-to-many” “Relationship”. For a “many-to-many” “Relationship” to happen, you need to have a third table (you need to add it). We call this an intermediate table or a connection table. This table splits the “many-to-many” “Relationship” into two “one-to-many”
“Relationships”. In other words, “many-to-many” “Relationships” are actually two or more “one-to-many” “Relationships”. You add the “Primary keys” from both tables to the intermediate table.
You've completed Lesson 6 START NEXT LESSON