[Database]Chapter2. Relational Model
Introduction
The structure of relational model, and the relational algebra.
The structure of relational model
Relational database is formed of Relations that has unique names. All entity-relation model can be represented on relations.
Relations is the unit for saving data in database. Let me explain some important terms :
Record : A row line. In other words, a line of data.
Fields : A column line.
Schema : A set of relation names and attributes.
Instance : A set of records.
Attribute : A specific information of relation. All attributes must be different each other.
Degree : A number of attributes.
Tuple : An other words of the record.
Cardinality : A number of records.
Domain : A set of values in attribute. For example, Client ID is 4 digit numbers, Client First Name is 20 maximum size of string,… etc.
Plus, there are some characteristics of the relations.
- All records in relation must be different each other.
- The order of records is not important.
- The order of attribute is also not important.
- Relations can be changed by inserting, deleting on real-time.
- All attribute must be different each other, but its value can be same. However, its values must be single value, cannot be multiple value. (e.g. {Stewart, Jameson})
We can use query language to handle relations, and SQL is the most world-wide use for this action. We will see how to use SQL in chatper 3. But before we go to see at SQL, let’s see the concept of relational model.
The relational algebra
The relational algebra is the best method to explain the relational model.
The relational algebra can do that get an single relation and create a new relation.
- The operators about input single relation : Select, Project
- The operators about input two relations : Union, Set Difference, Cartesian Product
- Other operators : Set Intersection, Join, Division
OK, let’s see these operator one by one with this example Entity-Relation Diagram.
Relations created from entities :
Relations created from relationship :
(To learn how to convert ER diagram to relational schema, see this tutorial.)
Select : Create new relation with records from relation r where condition P is true.
Example : Select “Brighton” branch information from Account table.
Example : Select account that has over $800 balance from “Brighton” branch.
This is the concept of select algebra. You can use select algebra to get the row line of data.
Project : Create new relation with attribute A1, A2, …, Ar from relation r.
Example : Account Number and Balance from Account table?
Example : Account Number and Balance that has over $600 on balance from Account table?
Below is the concept of project algebra. You can use project algebra to get the non-duplicated column data.
Union : Create new relation where the records contained both relation r and relation s.
Example : All customer’s name who use banks for borrowing and depositing.
Below is the concept of union algebra. Note that to use union algebra, you must set same numbers of attributes and attribute type for both relations.
Set Difference : Create new relation that relation r’s records minus relation s’s records.
Example : All customer’s name who do deposit but not do borrow?
Below is the concept of Set difference algebra. Note that to use Set difference algebra, you must set same numbers of attributes and attribute type for both relations.
Cartesian Product : Create new relation that all possible combinations with relation r and s. This is used for connect two relations that both has same attribute.
Example : All customer’s name who borrowed from “Perryridge” branch?
Below is the concept of Cartesian Product.
Set Intersection : Create new relation with all records that included both relation r and relation s.
Example : All customer’s name that do both borrowing and depositing.
Join : Create new relation that combined by two relations with common attributes. There are some types of join algebra.
Natural Join : The algebra that combine two relation with common attributes.
Example : The bank which customers are depositing living in Harrison?
Example : Find all customers who have both a loan and an account.
Below is the concept of Natural Join. You can find that Natural Join is similar on Cartesian Product with no duplicate values.
Theta Join : The natural join with conditional operator.
Equi Join : The theta join which conditional operator is “=”.
Outer Join : The extended natural join. If there is null value exists when natural join do, the outer join set that value with NULL. Let’s see this with an example.
There are two relations : loan and borrower. I will combine these two relations with an one.
Inner Join(Natural Join) results :
Left Outer Join results :
Right Outer Join results :
Full Outer Join results :
Division : Used for “For all” query. Get all records which relation s exists on relation r.
Example : All customer’s name who have accounts from all “Brooklyn” Banks.
Below is the concept of division algebra. You can easily understand with these picture.
