august 2015

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.

Topic Objectives

  • 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

Entity

  • 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

Relation

  • 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

Sample Relational Table

A Non-relational Example

Sample Non Relational Table

Terninology

Synonyms...

Relational terminology

Keys

  • 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.
Non Unique Key

Dan's Typology of Database Keys

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!

Flight NumberDate
UA 3603 Sep 2016
AA 704 31 Oct 2016
UA 3604 Sep 2016
BA 931 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

EmployeeNumberFirstNameLastName
100YousefHassan
101JerryCadley
102YiChang
107ShirAnavi

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.

EmployeeNumberFirstNameLastName
100YousefHassan
101JerryCadley
102YiChang
107ShirAnavi

Surrogate Key Example

FlightIdFlightNumberDate
1UA 3603 Sep 2016
2AA 70431 Oct 2016
3UA 3604 Sep 2016
4BA 931 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.
Foreign Key Foreign Key

Referential Integrity

  • Referential integrity states that every value of a foreign key must match a value of an existing primary key.
Foreign Key

Null values

  • 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.

Functional Dependency

  • A relationship between attributes in which one attribute (or group of attributes) determines the value of another attribute in the same table
  • Illustration...
    • The price of one delicious Girl Scout cookie can determine the price of a box of 12 cookies
      (CookiePrice, Qtv)-> BoxPrice

Determinants

  • 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

Data Normalization

  • 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

Normalization Principles

  • 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