![Exercises Normalisasi [PDF]](https://pdfs.asia/img/200x200/exercises-normalisasi.jpg)
17 0 735 KB
DATABASE SYSTEMS SI13013
PROGRAM STUDI SISTEM INFORMASI UIVERRSITAS TARUMANAGARA
Exercises Normalization
2
Table transaction custNo
custName
postNo Regency
supNo
supName
address
salNo
salPerson comment
C105 C105 C102 C103 C103 C103 C106
George Mile George Mile Perry Smith Conn Wide Conn Wide Conn Wide Lucas
17114 17114 17114 26742 26742 26742 54444
Nevada Nevada Nevada Norvegia Norvegia Norvegia Saint Mo
S103 S101 S102 S101 S103 S103 S104
Digital Image Casual Design Puma Casual Design Digital Image Digital Image Zest
04 Hossier 23 Oak St 8 Hoosier 23 Oak St 04 Hossier 04 Hossier Five Street
L502 L503 L201 L503 L404 L502 L202
Anderson Tucker Arnold S. Tucker Smith Loo Anderson Michael
3
Good Good Bad Good Bad
Berdasarkan pada table transactiontersebut, kerjakan soal-soal berikut ini ! 1. Tentukan ketergantungan fungsionalnya (parisal dan transitive) ! 2. Normalisasikan sampai dengan tahap ke-3 ! 3. Tentukan primary key dan foreign key dari masing-masing table yang sudah 3NF ! 4. Berdasakan jawaban 2 (table yang sudah 3NF) gambarkan dalam model ER (entity relationship diagram) tentukan multiplicity constrainsnya !
1
Menentukan Ketergantungan Fungsional terhadap PK
custNo
custName
postNo Regency
supNo
supName
address
salNo
salPerson comment
C105 C105 C102 C103 C103 C103 C106
George Mile George Mile Perry Smith Conn Wide Conn Wide Conn Wide Lucas
17114 17114 17114 26742 26742 26742 54444
S103 S101 S102 S101 S103 S103 S104
Digital Image Casual Design Puma Casual Design Digital Image Digital Image Zest
04 Hossier 23 Oak St 8 Hoosier 23 Oak St 04 Hossier 04 Hossier Five Street
L502 L503 L201 L503 L404 L502 L202
Anderson Tucker Arnold S. Tucker Smith Loo Anderson Michael
5
Nevada Nevada Nevada Norvegia Norvegia Norvegia Saint Mo
Good Good Bad Good Bad PK
1
Identifikasi ketergantungan parsial (PD) dan lakukukan normalisasi bentuk ke-2
custNo
custName
postNo Regency
supNo
supName
address
salNo
salPerson comment
C105 C105 C102 C103 C103 C103 C106
George Mile George Mile Perry Smith Conn Wide Conn Wide Conn Wide Lucas
17114 17114 17114 26742 26742 26742 54444
S103 S101 S102 S101 S103 S103 S104
Digital Image Casual Design Puma Casual Design Digital Image Digital Image Zest
04 Hossier 23 Oak St 8 Hoosier 23 Oak St 04 Hossier 04 Hossier Five Street
L502 L503 L201 L503 L404 L502 L202
Anderson Tucker Arnold S. Tucker Smith Loo Anderson Michael
Nevada Nevada Nevada Norvegia Norvegia Norvegia Saint Mo
Good Good Bad Good Bad PK
PD
6
1
Identifikasi ketergantungan transitif (TD) dan lakuka normalisasi bentuk ke-3
custNo
custName
postNo Regency
supNo
supName
address
salNo
salPerson comment
C105 C105 C102 C103 C103 C103 C106
George Mile George Mile Perry Smith Conn Wide Conn Wide Conn Wide Lucas
17114 17114 17114 26742 26742 26742 54444
S103 S101 S102 S101 S103 S103 S104
Digital Image Casual Design Puma Casual Design Digital Image Digital Image Zest
04 Hossier 23 Oak St 8 Hoosier 23 Oak St 04 Hossier 04 Hossier Five Street
L502 L503 L201 L503 L404 L502 L202
Anderson Tucker Arnold S. Tucker Smith Loo Anderson Michael
Nevada Nevada Nevada Norvegia Norvegia Norvegia Saint Mo
Good Good Bad Good Bad PK
PD TD 7
2
Tabel/Relasi Normal Bentuk Ke-2, menghilankan PD
CustSal
custNo C105 C105 C102 C103 C103 C103 C106
SupSal
salNo L502 L503 L201 L503 L404 L502 L202 salNo L502 L503 L201 L503 L404 L502 L202
comment Good Good Bad Good Bad salPerson Anderson Tucker Arnold S. Tucker Smith Loo Anderson Michael
CustPost
supNo S103 S101 S102 S101 S103 S103 S104
custNo C105 C105 C102 C103 C103 C103 C106
custName George Mile George Mile Perry Smith Conn Wide Conn Wide Conn Wide Lucas
supName Digital Image Casual Design Puma Casual Design Digital Image Digital Image Zest
postNo 17114 17114 17114 26742 26742 26742 54444
address 04 Hossier 23 Oak St 8 Hoosier 23 Oak St 04 Hossier 04 Hossier Five Street
Regency Nevada Nevada Nevada Norvegia Norvegia Norvegia Saint Mo
2
Identifikasi ketergantungan transitif (TD) dan membuat Normal Ke-3
Cust_Post
custNo C105 C102 C103 C106
custName George Mile Perry Smith Conn Wide Lucas
postNo 17114 17114 26742 54444
Regency Nevada Nevada Norvegia Saint Mo
TD
2 Identifikasi ketergantungan transitif (TD) dan membuat Normal Ke-3
Sup_Sal
supNo S103 S101 S102 S103 S104
supName Digital Image Casual Design Puma Digital Image Zest
address 04 Hossier 23 Oak St 8 Hoosier 04 Hossier Five Street
salNo L502 L503 L201 L404 L202
TD
salPerson Anderson Tucker Arnold S. Smith Loo Michael
2
Tabel/Relasi Normal Bentuk Ke-3, menghilankan TD CustSal bentuk Normal Ke-2 sudah dalam bentuk Normal Ke-3
Sales
Supplier Customer
Post
custNo C105 C102 C103 C106
postNo 17114 26742 54444
custName George Mile Perry Smith Conn Wide Lucas
Regency Nevada Norvegia Saint Mo
postNo 17114 17114 26742 54444
salNo L502 L503 L201 L404 L202 supNo S103 S101 S102 S104
salPerson Anderson Tucker Arnold S. Smith Loo Michael supName Digital Image Casual Design Puma Zest
supNo S103 S101 S102 S103 S104 address 04 Hossier 23 Oak St 8 Hoosier Five Street
3
Menentukan PK dan FK relasi normal bentuk ke-3 CustSal
: custNo, salNo (PK) custNo (FK) reference to Cutomer salNo (FK) reference to Sales Customer : custNo (PK) pstNo (FK) reference to Post Post : postNo (PK) Sales : salesNo (PK) supNo (FK) reference to Supplier Supplier : supNo (PK)
4
Model ER atau Entity Relationship Diagram) Customer
1..1
1..*
CustSal
1..*
1..1
Sales
1..*
1..*
1..1
1..1
Post
Supplier
4
Model ER atau Entity Relationship Diagram)
Jika diasumsikan bahwa satu Supplier pasti memiliki lebih dari satu Sales, kemudian Sales bisa saja tidak memiliki atau hanya memiliki satu Supplier, maka model ER nya seperti berikut
Customer
1..1
1..*
CustSal
1..*
1..1
Sales
1..*
1..*
1..1
0..1
Post
Supplier
Thank You Reference: Database Systems A Practical Approach to Design, Implementation, and Management Fourth Edition. Thomas M. Connolly and Carolyn E. Begg
