Chapter 2. The Relational Model
The Relational Model
Dr. Soper gives a lecture on the relational model and its role in the database world. Topics include relations, primary keys, composite keys, foreign keys, normal forms, and the normalization process.
- Learn the conceptual foundation of the relational model
- Understand how relations differ from non-relational tables
- Learn basic relational terminology
- Learn the meaning and importance of key, foreighn key, and related terminology
- Understand how foreign keys represent relationships
- Learn the purpose and use of surrogate keys
- Learn the meaning of functional dependencies
- Learn to apply a process for normalizing relations
- An entity is something of importance to a user or organization that needs to be represented in a database
- An entity represents one theme, topic, or business concept
- In the entity-relationship model (Topic 04), entities are restricted to things that can be represented using a single table
- A relation is two-dimension table that has specific characteristics
- The table dimensions, like a matrix, consist of rows and columns
Characeristics of a Relation
- Rows contain data about instances of an entity
- Columns contain data about attributes of the entity
- Cell of the table hold a single value
- All value in column are of the same kind (data type)
- Each column has a unique name
- The order of the columns is unimportant
- The order of the rows is unimportant
- No two rows can be identical
A Sample Relation
A Non-relational Example
- A key is one (or more) columns of relation whose values are used to identify a row.
Uniqueness of Keys
- Unique Key. Data value is unique for each row. Consequently, the key will uniquely identify a row.
- Nonunique Key. Data value may be shared among several rows. Consequently, the key will identify a set of rows.
Dan's Typology of Database Keys
A Composite Key
- A composite key is a key that is composed of two or more attributes.
- For a key to be unique, it must often become a composite key.
Composite Key Example
Flight Number + Date = Unique!
|UA 36||03 Sep 2016|
|AA 704 ||31 Oct 2016|
|UA 36||04 Sep 2016|
|BA 9||31 Oct 2016|
A Candidate Key
- A candidate key is called "candidate" becouse it has the potential to become the primary key.
- A candidate key is unique key.
A Primary Key
- A primary key is a candidate key chosen to be the main key for the relation.
- If you know the value of the primary key, you will be able to uniquely identify a single row witin the table.
Primary Key Example
EmployeeNumber is Primary Key
A Surrogate Key
- A surrogate key is unique, numeric value that is added to a relation to serve as the primary key.
- Surrogate key values have no meaning to users and are usually hidden on forms, queries, and reports.
- A surrogate key is often used in place of a composite primary key.
Primary Key Example
EmployeeNumber is Primary Key, but this type of primary key is called a Surrogate Key.
Surrogate Key Example
|1||UA 36||03 Sep 2016|
|2||AA 704||31 Oct 2016|
|3||UA 36||04 Sep 2016|
|4||BA 9||31 Oct 2016|
Ralations between Tables
- A table may be related to other tables
- For example.
- An Employee works in a Department
- A Manager controls a Project.
A Foreign Key
- To establish relationship, we need to implement a foreign key.
- A foreign key is a primary key from one table that is placed into another table.
- The key is called a foreign key in the table that receives the key.
- Referential integrity states that every value of a foreign key must match a value of an existing primary key.
- A Null values means that no data exists.
- You can think of a null value as an empty cell in the table.
- This is different from zero, space character, empty stryng, or tab character.
The problem of Null values
- A Null is often ambiguous. It could mean...
- The column value is not appropriate for the specific row.
- The column value has not been decided.
- The column value is unknown.
- Each may have entirely different implications.
- A relationship between attributes in which one attribute (or group of attributes) determines the value of another attribute in the same table
- The price of one delicious Girl Scout cookie can determine the price of a box of 12 cookies
(CookiePrice, Qtv)-> BoxPrice
- The attribute ( or attributes ) that we use as the starting point ( the variable on the left side of the equation) is called a determinant
Candidate/Primary Keys and Functional Dependency
- By definition...
A candidate key of relation will functionally determine all other non-key attributes in the row
- Likewise, by definition...
A primary key of relation will functionally determine all other no-key attributes in the row
- A process of analyzing a relation to ensure that it is well formed
- Normalization involves decomposing relations with anomalies to produce smaller, well-structured relations
- More specifically, if a relation is normalized (well-formed), rows can be inserted, deleted, or modified without anomalies
- Relational design principles for normalized relations:
- To be a well-formed relation, every determinant must be a candidate key
- Any relation that is not well-formed should be broken into two or more well-formed relations!
- TIP: As a general rule, a well-formed relation will not encompass more than one business concept!
First Normal Form
- Table contains no multivalued attributes
- Every attribute value is atomic
- The figure on the next slide is not 1-st Normal Form (multivalued attributes)-> it is not relation
- All relations are in 1-st Normal Form
Second Normal Form
- 1NF PLUS every non-key attribute is fully functionally dependent on the ENTIRE primary key
- Every non-key attribute must be defined by the entire key, not by only part of key
- No partial dependencies
Third Normal Form
- 2NF PLUS no transitive dependencies (functional dependencies) on non-primary-key attributes
- Such dependencies are called transitive, becouse the primary key is a determinant for another attribute, which in turn is a determinant for a third
- Solution: Non-key determinant with transitive dependencies goes into new table; non-key determinant becomes primary key in the new table and remains as a foreign key in the old table
Copyright by Dr.Daniel Soper