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
最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市,隨后出現(xiàn)的幾起案子跷乐,更是在濱河造成了極大的恐慌吏祸,老刑警劉巖绎速,帶你破解...
    沈念sama閱讀 212,718評(píng)論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件颈将,死亡現(xiàn)場(chǎng)離奇詭異挤茄,居然都是意外死亡逞刷,警方通過查閱死者的電腦和手機(jī)村生,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,683評(píng)論 3 385
  • 文/潘曉璐 我一進(jìn)店門惊暴,熙熙樓的掌柜王于貴愁眉苦臉地迎上來,“玉大人梆造,你說我怎么就攤上這事缴守。” “怎么了镇辉?”我有些...
    開封第一講書人閱讀 158,207評(píng)論 0 348
  • 文/不壞的土叔 我叫張陵屡穗,是天一觀的道長(zhǎng)。 經(jīng)常有香客問我忽肛,道長(zhǎng)村砂,這世上最難降的妖魔是什么? 我笑而不...
    開封第一講書人閱讀 56,755評(píng)論 1 284
  • 正文 為了忘掉前任屹逛,我火速辦了婚禮础废,結(jié)果婚禮上,老公的妹妹穿的比我還像新娘罕模。我一直安慰自己评腺,他們只是感情好,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,862評(píng)論 6 386
  • 文/花漫 我一把揭開白布淑掌。 她就那樣靜靜地躺著蒿讥,像睡著了一般。 火紅的嫁衣襯著肌膚如雪抛腕。 梳的紋絲不亂的頭發(fā)上芋绸,一...
    開封第一講書人閱讀 50,050評(píng)論 1 291
  • 那天,我揣著相機(jī)與錄音担敌,去河邊找鬼摔敛。 笑死,一個(gè)胖子當(dāng)著我的面吹牛全封,可吹牛的內(nèi)容都是我干的马昙。 我是一名探鬼主播,決...
    沈念sama閱讀 39,136評(píng)論 3 410
  • 文/蒼蘭香墨 我猛地睜開眼刹悴,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼给猾!你這毒婦竟也來了?” 一聲冷哼從身側(cè)響起颂跨,我...
    開封第一講書人閱讀 37,882評(píng)論 0 268
  • 序言:老撾萬榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎扯饶,沒想到半個(gè)月后恒削,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體池颈,經(jīng)...
    沈念sama閱讀 44,330評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,651評(píng)論 2 327
  • 正文 我和宋清朗相戀三年钓丰,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了躯砰。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片。...
    茶點(diǎn)故事閱讀 38,789評(píng)論 1 341
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡携丁,死狀恐怖琢歇,靈堂內(nèi)的尸體忽然破棺而出,到底是詐尸還是另有隱情梦鉴,我是刑警寧澤李茫,帶...
    沈念sama閱讀 34,477評(píng)論 4 333
  • 正文 年R本政府宣布,位于F島的核電站肥橙,受9級(jí)特大地震影響魄宏,放射性物質(zhì)發(fā)生泄漏。R本人自食惡果不足惜存筏,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 40,135評(píng)論 3 317
  • 文/蒙蒙 一宠互、第九天 我趴在偏房一處隱蔽的房頂上張望。 院中可真熱鬧椭坚,春花似錦予跌、人聲如沸。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,864評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽。三九已至巾表,卻和暖如春汁掠,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背集币。 一陣腳步聲響...
    開封第一講書人閱讀 32,099評(píng)論 1 267
  • 我被黑心中介騙來泰國(guó)打工考阱, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人鞠苟。 一個(gè)月前我還...
    沈念sama閱讀 46,598評(píng)論 2 362
  • 正文 我出身青樓乞榨,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親当娱。 傳聞我的和親對(duì)象是個(gè)殘疾皇子吃既,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,697評(píng)論 2 351

推薦閱讀更多精彩內(nèi)容

  • **2014真題Directions:Read the following text. Choose the be...
    又是夜半驚坐起閱讀 9,442評(píng)論 0 23
  • 夕陽染紅天邊的云彩 秋蟲吟唱?jiǎng)e離的笙簫 列車載動(dòng)游子的哀愁 透過車窗玻璃 遙望茫茫人海 母親的身影 忽而矮小 忽而...
    月舞傾城閱讀 1,002評(píng)論 74 81
  • 高中的時(shí)候,看見一男一女抱在一起跨细,我驚訝鹦倚,咦,真膽大冀惭!大學(xué)的時(shí)候震叙,看見兩男抱在一起掀鹅,我驚訝,咦媒楼,我要起雞皮疙瘩乐尊!現(xiàn)...
    江顏閱讀 179評(píng)論 0 2
  • 1.指令含義: directive(指令)是特殊的帶有前綴 v- 的命令,指令的值限定為綁定表達(dá)式划址,指令的職責(zé)就是...
    addin_gao閱讀 438評(píng)論 0 1