1.3 ????-????????(IPO??)

1
??? ???????????
2
????

• ??? ??????
• 1.1 ??????-?????????
• 1.2 ??????-?????????
• 1.3 ????-????????(IPO??)
• 1.4 ????-???????
• 1.5 ????-??????????
• ??? ?????
• 2.1 ???????
• 2.2 ?????
• 2.3 ?????????
• 2.4 ?????
• 2.5 ???????????

3
??????
??????
4
??????
5
????????

• 1.?????
• ?????,??????????????
• ??????,????.?
• 2.?????
• ?????,?????.?
• 3.?????
• ?????,??????,???.?
• 4.?????
• ?????,???(I/S),?????(B/S).?

6
????????
1.?????
4.?????
2.?????
3.?????
Transaction
Standard
?????
???????????????????????????
7
????IPO Model
8
?? VS. ?? VS. ??

• ??(Data) vs. ??(Information)
• Data?????????????
• Information???????????
• Information???????????????????,??
• Information????????????????
• ??(Data) vs. ??(Information) vs. ??(Knowledge)
• Data???????????????
• Information?????????????
• Knowledge???????????????????????

9
?????????

• ????????????
• ??? User
• ?? Data
• ?? Hardware
• ?? SoftWare

DBS
DBMS
AP
DB
User
PC
User
AP ??????????
DBS DB DBMS User Hardware
10
?????User A

• ??? User
• ??????? / ????(Application Programmer /
  Application Programs)
• ??(???)???(End Users)
• ??????(Database Administrator,??DBA)
• ????
• A.??????? / ????
• (Application Programmer / Application
  Programs)
• ????COBOL?PL/I?C?Java?VB?Delphi?Power
• Builder?4GL???,?????????(??????
• Application Systems),??????????????..??
• ???,???????SQL???DBMS???????
• ??,??select?insert?update?delete???

11
?????User B

• B.??(???)???(End Users)
• (Application Programmer / Application
  Programs)
• ???????????????????,??????
• ???Application Systems?????DBMS??????,
• ???????????,????????
• ?SQL????
• ????(Menu-Driven)
• ????(Form-Driven)
• ??MS Access?????????Query???????
• ???
• ???????????

12
?????User C
C.??????(Database Administrator,??DBA) 1.
?????(Information Technology,??IT)???? 2.
??????????DBMS??????? 3. ????,????
(1)????????? (2)??????????????
(3)???????????,???????? (4)?????(Security
Control),???Firework (5)?????(Integrity
Checking) ,?????,14?38? (6)????(Backup)???(Re
covery),?Dump Backup Utility (7)??????(Bulk
Copy) (8)??????(Disk Mirroring)???????,??????
(9)????(Audit Trail)????????????????
(10)??????(System Catalog??Data
Dictionary)?Database Schema ????
(11)??
13
?????Data??

• ?? Data
• ???????Data??????????
• 1. ????(Operational Data)
• 2. ????(Transaction Log)
• 1. ????(Operational Data)
• (A) DB??????????Data ? ??????
• (B) DB???????Data?Data,???????
• ????? Metadata ??????
• 2. ????(Transaction Log)
• ???????????,????????????
• ,???????? DBA ????????,???
• ? Backup ? Recovery ??????

14
?????Data??

• ?? Data ????
• ? ????(Operational Data) ? OD
• ????(Transaction Log) ? TL
• ? OD VS. TL ????????

TL
TL
OD
OD
(a)
(b)
15
?????Hardware Software

• ?? Hardware
• 1. RAM Floppy Driver Hard Disk I / O
  channels
• 2. Processor Main Memory
• ?? Software
• 1. ?DBMS????
• 2. Utility????
• 3. ??????
• 4. ??????
• 5. ??????
• 6. ??

16
???????(DBLC)
DB???????
?????? Conceptual Design
1
? DB ? ? ? ? ? ? ?
2a
?????????
DB?????
2
E-R Modeling
2b
DB???????
3
??????
2c
DB????
2d
?????
4
Access ? MSSQL ? Informix ? Sybase ? Oracle ?
??DB??
2e
??????
5
??? ??
?????
2f
???????
6
?????
???? ????
2g
17
??????????
Peter P.S. Chen E-R Model
ANSI/X3/SPARC DataBase 3 Layer Architecture
E.F. Codd Normalization Theory
E.F. Codd Relational Data Model
1976 ?
1970 ?
1972 ?
1975 ?
Time
18
ER Model??

• ??Entity-Relationship Model
• ????? E-R Model
• ???? ???????
• ??????(Peter P.S. Chen)?1976????
• ?????????(Semantic Data Model)
• ??-????DB??????
• ??-??SA???????????
• ??-???????
• Ouput-??????(Entity-Relationship Diagram)
• ??ER Diagram?ERD

19
ER Model??

• ??(Entity)
• ?? ????????????
• ?? ???
• ????
• ????
• ????(??)??,??? ?? ?
• Ex DB????3??
• 3?????
• ??????????????

PS. ??(Identify)??????
???Primary Key???
20
ER Model??

• ????(Entity Set)
• ?? ?????????Entity??????
• ????Entity????Entity Set
• ?? ??????? Entity Set
• ?????????????
• ???????????? Entity Set
• ???? Entity

21
ER Model??

• ????(Entity Set)
• Ex?????????????????Entity Set
• ??????????.
• ?????????????
• ???????????
• ????????????DBMS?SA???
• PS.???(?) ????(?)
• ??(?) ???(?) ???(?) ???(?)
• ????????

22
ER Model??

• ???(Relationship)
• ?? Entity Set ? Entity Set ??????????
• Relationship, ?? Relationship ??????
• ?? ??Entity Set ? ?? Entity Set ??????
• ???? ?? Relationship
• ?? ??Entity Set ? ?? Entity Set ??????
• ???? ?? Relationship
• ???????????,??????,?????
• ???????Entity Set,???Relationship??

23
ER Diagram (ERD) ??

• Entity Set ??????(??)?????
• ? Entity Set ?? ?????
• ps.?????????

??
??
??
??
??
24
ER Diagram (ERD) ??

• Relationship ??????(??)????
• ?? Relationship ?????
• ??
• ps.?????????

??
??
??
??
??
25
ER Diagram (ERD) ??

• ???ERD
• ?? ??Entity Set ? ?? Entity Set ??????
• ???? ?? Relationship

Relationship
Entity Set
Entity Set
??
??
??
26
ER Diagram (ERD) ??

• ???(Connectivity)
• ??ERD?????
• ????????
• 1 To 1 (?? ???)
• 1 To Many (?? ???????)
• Many To Many (?? ???)
• ?Connectivity???????

Relationship
Connectivity
Connectivity
Entity Set
Entity Set
27
ER Diagram (ERD) ??

• ??Connectivity??ERD

??
n
m
??
??
??
n
1
??
??
??
1
1
??
??
28
ER Diagram (ERD) ??

• Connectivity???
• ?1??? From Left To Right
• ??1??? From Right To Left
• ????????????

Relationship
Entity Set
Entity Set
1 To Many
? ? ? ? ? ?
1
n
Many To 1
n
1
max
max
Many To Many
m
n
29
ER Diagram (ERD) ??

• ???ERD
• ?? ??Entity Set ? ?? Entity Set ??????
• ???? ?? Relationship

Relationship
C1
C2
Entity Set
Entity Set
??
m
n
??
??
30
ER Diagram (ERD) ??

• ???????ERD ??

??
m
n
??
??

• ??
• ???? ????????????
• ???? ?????????????
• ??????
• ????????
• ?????????

Exam.
31
??????ERD??
n
??
??
m
??
1
n
? ?
? ?
m
m
n
n
??
??
??
m
??
1
?????? ??????
n
1
??
??
32
ERD??????
n
??
m
??
m
n
??
??
???
n
m
????????? ??????????
33
ER Diagram (ERD) ??

• ??(Attribute)
• ???? Entity Set ? Relationship ??????
• ???--??????????

P.K.
P.K.
P.K.
C1
A1
A2
B1
B2
Relationship
m
n
Entity Set
Entity Set
An
Bn
Cn
34
ER Diagram (ERD) ??
?

• ?? Attribute ??ERD

P.K.
??
??
??
??
??
??
??
m
n
??
??
??
??
??
??
??
35
ER Diagram (ERD) ??

• ??DataBase???????????
• ???--- E ---- Entity Set
• ???--- R ---- Relationship

??E ??R
36
ER Diagram (ERD) ??

• ??E??R
• ????? Entity Set ???????(Master File)
• ????? Relationship ????????(Transaction File)

Transaction File
Master File
37
ER Diagram (ERD) ??

• ??E??R

??Master File ?
n
??
??
??Transaction File ?
m
??
1
n
? ?
? ?
m
m
n
n
??
??
??
m
??
1
n
1
??
??
38
??????????
1975?? ANSI/X3/SPARC ?? DBS ?? ??????????(Data
Independence)
???
User1
User2
UserN
User3
Database
External Schema
???
ERM ? ? ? ?
Conceptual Schema
Internal Schema
???
39
ER Diagram (ERD) ??

• ???????Entity Set ??
• ?(Person)
• ?(Place)
• ?(Thing)
• ??(Concept)
• ??(Event)

Exam
40
ER Diagram (ERD) ??

• ????????Entity Set

?(Person) ?? ?? ??? ??? ??
??(Event) ?? ????
?(Thing) ?? ??
?(Place) ?? ?? ????
??(Concept) ?? ?? ??
41
ER Diagram (ERD) ??

• ERD ?????

? ? ? ? ?
STEP1??Problem
STEP2??Entity Set
STEP3??Relationship
STEP4??Connectivity
STEP5??E?R?Attribute
STEP6??E?R?Primary Key
STEP7??.
42
?????(Normalization)??
Review ???(Relation) ???

1. ???????(Tuple)
2. ??????????
3. ??????????
4. ????????(?)??(Atomic Value)

• Note
• ?????????,????
• ???( Table )????( Relation )
• Relation ???? Table

43
?????(Normalization)??

• Atomic Value ??
• Every row-column position in every table, there
  is always exectly one data value, never a set of
  values.
• ???????????-??????,???????,????????

SP
SP
S P
S1 P2 P3 P5
S2 P3
S3 P1 P4
S4 P2 P3 P10
S P
S1 P2 P3 P5
S2 P3
S3 P1 P4
S4 P2 P3 P10
Atomic check
X
ok
X
ok
44
?????(Normalization)??

• ?? Atomic Value ???

SP
SP
SP
S P
S1 P2
S1 P3
S1 P5
S2 P3
S3 P1
S3 P4
S4 P2
S4 P3
S4 P10
S P
S1 P2 P3 P5
S2 P3
S3 P1 P4
S4 P2 P3 P10
S P
S1 P2
P3
P5
S2 P3
S3 P1
P4
S4 P2
P3
P10
??
PK???Null
??
?????
???Flat File (????)
45
?????(Normalization)??

• ?? Atomic Value ???

SP
SP
S P
S1 P2
S1 P3
S1 P5
S2 P3
S3 P1
S3 P4
S4 P2
S4 P3
S4 P10
S P
S1 P2 P3 P5
S2 P3
S3 P1 P4
S4 P2 P3 P10
??
??
?
?
?
Data Redundancy
46
?????(Normalization)??

• ????????? Relation (i.e. Table ) ?? Data Items /
  Attributes ????????????,?????????? (
  Normalization ) ?,??????????? ( Normal Form) ??

• Note
• ??? Normalization ??????
• ? Table ????????????
• ???? DB ???????( Single File )???
• ??????? DB ?????????????
• (???? ??)?? ?

47
?????(Normalization)??

• Normal Form ???
• Normal Form ?????????? ?????,??????,?????

First Normal Form Second
Normal Form Third
Normal Form BOYCE/CODD Normal Form Fourth
Normal Form Fifth Normal
Form
1NF 2NF 3NF BCNF 4NF 5NF
????? ????? ????? BC ??? ????? ?????
48
?????(Normalization)??

• Normal Form ???
• ???? Performance ???,???? DataBase ?? Relation
  ??? 3NF ????
• 3NF ????1NF?2NF ? 3NF?????,??? 1NF ? Relation ??
  2NF ? Relation ?? 3NF ? Relation ?
• ????? 4NF ? 5NF ,??? 1NF?2NF ? 3NF?

49
?????(Normalization)??

• ?????(Normal Form)????

Set Conception
1NF
2NF
3NF
BCNF
4NF
PJ/NF (5NF)
50
?????(Normalization)??

• Table ? ? 1NF Relation

Table
COURSE-NO COURSE-TITLE INSTRUCTOR-NAME INSTRUCTOR-LOCATION STUDENT-NO GRADE
101 BCC WU 302 10,15,18 75,83,66
201 SA CHUNG 206 20,30,31 92,80,77
. . . . . .
PK
1NF
COURSE-NO COURSE-TITLE INSTRUCTOR-NAME INSTRUCTOR-LOCATION STUDENT-NO GRADE
101 BCC WU 302 10 75
101 BCC WU 302 15 83
101 BCC WU 302 18 66
201 SA CHUNG 206 20 92
201 SA CHUNG 206 30 80
201 SA CHUNG 206 31 77
. . . . . .
51
?????(Normalization)??

• FDD ?? ? ? 3NF

STUDENT-NO COURSE-NO
COURSE-TITLE
GRADE
INSTRUCTOR-NAME
INSTRUCTOR-LOCATION
52
?????(Normalization)??

• ??????

?????????
?????
???????
????????
???????
????????
???????
??????? ????????
Boyce-Codd?????
??????
???????
?????????
???????