BI系列（二）: Relational Data Model a

Learning Objectives

• Recognize relational database terminology
• Understand the meaning of the integrity rules for relational databases
• Understand the impact of referenced rows on maintaining relational databases

Overview

To effectively use a relational database, you need two kinds of knowledge.

• Understand the structure and contents of database tables
• Understand the operators of relational algebra as they are the building blocks of most commercial query languages (Next Chap.)

Basic Elements

Tables

Table is a two dimensional arrangement of data. A table consists of a heading defining the table name and column names and a body containing rows of data.

Data Type defines a set of values and permissible operations on the values. Each column of a table is associated with a data type.In SQL, data type includes CHAR(L), VARCHAR(L), FLOAT(P), DATA/TIME, DECIMAL(W,R), INTEGER, BOOLEAN).

Connections among Tables

Relationship is the connection between rows in two tables. Relationships are shown by column values in one table that match column values in another table.

Alternative Terminology

| Table-Oriented | Set-Oriented | Record-Oriented|
| :---- -------: | :----------:| :-------------: |
| Table | Relation | Record type; file |
| Row | Tuple | Record |
| Column | Attribute | Field |

Integrity Rules

Definition of the Integrity Rules

Entity integrity means that each table must have a column or combination of columns with unique values. For example, StdNo in Student is unique and the combination of StdNo and OfferNo is unique in Enrollment

Referential integrity means that the column values in one table must match column values in a related table

• Superkey is a column or combination of columns containing unique values for each row. The combination of every column in a table is always a superkey because rows in a table must be unique.
• Candidate key is a minimal superkey. A superkey is minimal if removing any column makes it no longer unique. A single column superkey is minimal because no columns can be removed.
• Null value is a special value that represents the absence of an actual value. A null value can mean that the actual value is unknown or does not apply to the given row.
• Primary key is a specially designated candidate key. The primary key for a table cannot contain null values.
• Foreign key is a column or combination of columns in which the values must match those of a candidate key. A foreign key must have the same data type as its associated candidate key.
• Entity integrity: No two rows of a table can contain the same value for the primary key. In addition, no row can contain a null value for any column of a primary key.
• Referential integrity: Only two kinds of values can be stored in a foreign key:
  • a value matching a candidate key value in some row of the table containing the associated candidate key
  • or a null value.

Applications of the Integrity Rules

Self-Referencing Relationship is a relationship in which a foreign key refers to the same table. Self-referencing relationships represent associations among members of the same set.

1-M Relationship is a connection between two tables in which one row of a parent table can be referenced by many rows of a child table. 1-M relationships are the most common kind of relationship.

M-N Relationship is a connection between tow tables in which rows of each table can be related to many rows of the other table. M-N relationships cannot be directly represented in the relational model. Two 1-M relationships and a linking or associative table represent an M-N relationship.

Delete and Update Actions for Referenced Rows

• Restrict: Do not allow the action on the referenced row
• Cascade:Perform the same action the related rows
• Nullify: Set the foreign key of related rows to null.
• Default: Set the foreign key of related rows to its default value

CREATE TABLE Statement in SQL

CREATE TABLE Syntax.png The first example.png Second Example.png Third example.png Fourth example.png Fifth Example.png Sixth Example.png