BI系列(二): Relational Data Model and the Create Table Statement

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
最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容

  • **2014真题Directions:Read the following text. Choose the be...
    又是夜半惊坐起阅读 9,984评论 0 23
  • 夕阳染红天边的云彩 秋虫吟唱别离的笙箫 列车载动游子的哀愁 透过车窗玻璃 遥望茫茫人海 母亲的身影 忽而矮小 忽而...
    月舞倾城阅读 1,069评论 74 81
  • 高中的时候,看见一男一女抱在一起,我惊讶,咦,真胆大!大学的时候,看见两男抱在一起,我惊讶,咦,我要起鸡皮疙瘩!现...
    江颜阅读 186评论 0 2
  • 1.指令含义: directive(指令)是特殊的带有前缀 v- 的命令,指令的值限定为绑定表达式,指令的职责就是...
    addin_gao阅读 454评论 0 1