資料庫設計與教學

*2015/10/15

John

Enterprise Logisticsand E-Business Center

SQL

*

*


Data RowData Table

*


(primary key), *

A1234567TinaFemaleNTHUF1234567TomMaleNCTUA1122334JaneFemaleNTU


-

(Cardinality)3 1 2 3

*


Example-(,,)

(,,)

*!


(,,,)

(,,)

*


(,,)

(,,,,)

*


(,,)

(,,,,) *

*


(Normalization)

*


*

0001A01P01150001A01P02120002B01P01150002B01P03150003C01P04100003C01P05200004B02P02120005A02P03150005A02P0410


(null)

*

0001A01P01150001A01P02120002B01P01150002B01P03150003C01P04100003C01P05200004B02P02120005A02P03150005A02P04100006B02nullnullnull


*



*



(Normalization)

:

*


:

*

11234FirstTomJohn25678SecondMaryJane


*

11234FirstJohn

11234FirstTom25678SecondMary

25678SecondJane


:key(primary key) key(primary key)table22(primary key)table(primary key)1

*


*(){} {}(){} {}(){} {}key{} a non-key column is dependent on a component of the primary key


*{} {}{} {}{} {}


:(primary key)

(primary key)

(,,)

(,,)

*


(,,)

(,)

(,)

*

*SQL


SQLSQL: Structured Query LanguageSQL SQL SELECT SQL(Microsoft Visual Basic / Microsoft Access Jet database engine)

*


SQL

(DDLData Definition Language) (DMLData Manipulation Language) (DQLData Query Language) (DCLData Control Language)

*


SQL Server with ToolsWindows XPSQL Server 2008 R2 1Microsoft .Net Framework 3.5 SP1 2Windows Installer 4.5 3Windows PowerShell 1.0Express

*


*


*


*SQL Express


*SQLNT_AUTHORITY\SYSTEM


*


ClientSQL Server 2008 R2SQL Server

*


SQL ServerSQLEXPRESSTCP/IPTCP/IP

*


IPIPAllTCP1433 *


SQL ServerSQL Server (SQLEXPRESS)SQL Server *


SQL



SQL


* ()Nullnvarchar(MAX)keynvarchar(50)


(Numeric Data) SQL integer, float, moneySUM()() (Character & Strings Data) / (Date Data) / date, time, timestamp

*


(Boolean Data) true/false, Yes/No, 1/0 (NULL Data) *


*


*


*


*


*


*


*


*


*


*


*


*


*


ER Model *

Q&A Time

**Data Table

Data RowDataTable***

***

*** [] *tablekey=?? key=key=table2keykey=()

() ()

() keykey*non-key column is dependent on a component of the primary keykeykey*(Transitivity) AB BC AC

(Partial Functional Dependence): XY H X ( H X ) HY Y X Y X**True False1 01 True0 False*

資料庫設計與教學

Documents

Transcript of 資料庫設計與教學