Relational Database
- Tags
- soft-eng
Is a database system [see page 4, model] designed to eliminate redundancy of stored information and minimise the dependency between data items.
Logically a relational database presents a set of tables indexed as row \( \times \) column. Each row is a record, or unique entity, in the table. Each column is a field tied to the record.
Table 1:
Example of a database table with 3 records and 4 fields. The first field custId is the primary key of this table.
| custId | forename | surname | addId |
|---|---|---|---|
| 235 | Inderpal | Singh | S104DP12 |
| 673 | Sarah | Wilson | S57AA297 |
| 589 | Tariq | Al Harq | S116SQ40 |
Relationship Types
In Relational Databases relationships are [see page 8, classified] into 3 types:
- One-to-One (1:1)
- Every student has a unique UCard (1:1)
- Each bank branch has a single address (1:1)
- One-to-Many (1:M)
- An address contains many householders (1:1..*)
- A person optionally has a driving license (1:0..1)
- A woman optionally has many children (1:0..*)
- A personal may optionally be a student (1:0..1)
- Many-to-Many (M:N)
- Many modules are offered on many degrees (1..*:1..*)
- Many books are loaned optionally to a borrower (0..*:0..1)
- Many borrowers optionally reserve many books (0..*:0..*)
Relational Algebra
Defines the operations of a relational database. This [see page 3, includes]:
- Set operations: Union, Intersection, and Difference.
- [see page 5, Product]: Create a longer relation combining two others.
- [see page 4, Select]: Filter the tuples of a relation using a predicate.
- Project: Create a shorter relation with fewer places.
- [see page 5, Join]: Similar to product but merging one or more places.
Joins
Relational joins can come in several varieties depending on the sort of set combination.
Table 2:
Listing of all the various see page 6, [joins]. In the case of left and right outer joins if a record does not match any record in the other table the columns from that other table is filled in with nulls.
| Type | Description |
|---|---|
| Natural Join | Joins tables on the same named columns (one copy in result). |
| Inner Join | Joins on explicitly named columns (all are in the result). |
| Left Outer Join | An inner join but also with left hand rows with no matches in the right hand table. |
| Right Outer Join | An inner join but also with right hand rows with no matches in the left hand table. |
| Full Outer Join | The union of the left and router outer joins. |
