Database Day 07: Relational Model

Major players in developing database software

1. Oracle
2. IBM
3. Microsoft
4. SAP
5. Teradata

Data redundancy -> motivation behind the development of the relational model

The degree of relationship (also known as cardinality) is the number of occurrences in one entity which are associated (or linked) to the number of occurrences in another entity. There are three degrees of relationship, known as one-to-one (1:1), one-to-many (1:M) and many-to-many (M:M).

The degree of a relation: number of attributes in a relation.

Terminologies of a relational model: attribute, tuple.

A key is used to uniquely identify a tuple or a row in the relational model.

Superkey: an attribute or a set of attributes that uniquely identifies a tuple or a row.

Candidate key: a superkey such that no proper subset is a superkey within the relation. A candidate key K in relation R will have the following properties: uniqueness (i.e. for each tuple in R, the values of K uniquely identify that tuple) and irreducibility (i.e. no proper subset of K has a uniqueness property).

A Primary Key is defined as “A candidate key that is selected to identify tuples uniquely within a relation”.

Selecting Primary Key (PK):
– Choose the shortest candidate key.
– A number as it provides a more compact PK.

Foreign Key (FK): an attribute or a combination of attributes in one table whose values must either match the primary key in another table or be NULL.

Entity integrity
• Primary Key must not be NULL.

Referential integrity
• The values of FK must match the value of the PK in another relation or be NULL.

Column/Domain integrity
• All values in a given column must come from the same domain (the same data type and range).

EQUIJOIN: A join based on equivalence of a common attribute, with the join column appearing twice

NATURAL join: A join based on equivalence of a common attribute, with the join column appearing once

OUTER join: A join based on equivalence of a common attribute in which matched pairs are retained and any unmatched values in the other table are assigned nulls (may be left, right or full)

INNER join: A join that selects all rows from both tables as long as there is a match between the columns in both tables.

Data redundancy exists when unnecessarily duplicated data are found in the database.

Data independence is a condition in which the programs that access data are not dependent on the data storage characteristics of the data.

A DBMS is best described as a collection of programs that manage the database structure and that control shared access to the data in the database.

Structural independence exists when data access programs are not subject to change when the file's structural characteristics, such as the number or order of the columns in a table, change.

References
FIT5132 Introduction to databases lecture notes, Semester 2, 2014, Monash University.