Ma’ruza: T. Xo’jaqulov

Maydonlarida skalyar qiymatlar mavjud bo`lgan jadvallarga 1 NF deb qaraladi.

• Maydonlarida skalyar qiymatlar mavjud bo`lgan jadvallarga 1 NF deb qaraladi.
• Misol (1NF emas)

• Birinchi normal forma (1NF)

• AuName va AuPhone atributlari qiymatlari scalyar emas.

• 0-321-32132-1

• Balloon

• Sleepy, Snoopy, Grumpy

• 321-321-1111, 232-234-1234, 665-235-6532

• Small House

• 714-000-0000

• $34.00

• 0-55-123456-9

• Main Street

• Jones, Smith

• 123-333-3333, 654-223-3455

• Small House

• 714-000-0000

• $22.95

• 0-123-45678-0

• Ulysses

• Joyce

• 666-666-6666

• Alpha Press

• 999-999-9999

• $34.00

• 1-22-233700-0

• Visual Basic

• Roman

• 444-444-4444

• Big House

• 123-456-7890

• $25.00

• ISBN

• Title

• AuName

• AuPhone

• PubName

• PubPhone

• Price

Yangi jadval yaratilib, qaytariladigan guruhlarni alohida holda joylashtiriladi.

• Yangi jadval yaratilib, qaytariladigan guruhlarni alohida holda joylashtiriladi.
• Har bir yangi jadvalga birlamchi kalit hosil qilinadi.
• Misol (1NF )

• Birinchi normal forma (1NF)ga keltirish

• 0-321-32132-1

• Balloon

• Small House

• 714-000-0000

• $34.00

• 0-55-123456-9

• Main Street

• Small House

• 714-000-0000

• $22.95

• 0-123-45678-0

• Ulysses

• Alpha Press

• 999-999-9999

• $34.00

• 1-22-233700-0

• Visual Basic

• Big House

• 123-456-7890

• $25.00

• ISBN

• Title

• PubName

• PubPhone

• Price

• ISBN

• AuName

• AuPhone

• 0-123-45678-0

• Joyce

• 666-666-6666

• 1-22-233700-0

• Roman

• 444-444-4444

• 0-55-123456-9

• Smith

• 654-223-3455

• 0-55-123456-9

• Jones

• 123-333-3333

• 0-321-32132-1

• Grumpy

• 665-235-6532

• 0-321-32132-1

• Snoopy

• 232-234-1234

• 0-321-32132-1

• Sleepy

• 321-321-1111

• Funksional bog`lanish

• 0-321-32132-1

• Balloon

• $34.00

• 0-55-123456-9

• Main Street

• $22.95

• 0-123-45678-0

• Ulysses

• $34.00

• 1-22-233700-0

• Visual Basic

• $25.00

• ISBN

• Title

• Price

• Ob`yekt atributlari: {ISBN, Title, Price}
• Funksional bog`lanishlar: {ISBN}  {Title}
• {ISBN}  {Price}

2 misol

• 2 misol

• Funksional bog`lanish

• 1

• Big House

• 999-999-9999

• 2

• Small House

• 123-456-7890

• 3

• Alpha Press

• 111-111-1111

• PubID

• PubName

• PubPhone

• Ob`yekt atributlari:{PubID, PubName, PubPhone}
• Funksional bog`lanishlar:{PubId}  {PubPhone}
• {PubId}  {PubName}
• {PubName, PubPhone}  {PubID}

• AuID

• AuName

• AuPhone

• 6

• Joyce

• 666-666-6666

• 7

• Roman

• 444-444-4444

• 5

• Smith

• 654-223-3455

• 4

• Jones

• 123-333-3333

• 3

• Grumpy

• 665-235-6532

• 2

• Snoopy

• 232-234-1234

• 1

• Sleepy

• 321-321-1111

• 3 misol

• Ob`yekt atributlari: {AuID, AuName, AuPhone}
• Funksional bog`lanishlar: {AuId}  {AuPhone}
• {AuId}  {AuName}
• {AuName, AuPhone}  {AuID}

2NF uchun ikkita talab mavjud

• 2NF uchun ikkita talab mavjud
• Ma`lumotlar bazasi birinchi normal formada bo`lishi shart.
• Barcha kalit bo`lmagan atributlar kalit atributga funksional bog`langan bo`lishi shart.
• Misol 1 ( 2NF emas)
• Atributlar:  {Title, PubId, AuId, Price, AuAddress}
• Kalitlar:  {Title, PubId, AuId}
• {Title, PubId, AuID}  {Price}
• {AuID}  {AuAddress}
• AuAddress kalitga tegishli emas
• AuAddress va AuID funksional bog`lanishda. AuID kalit.

• Ikkinchi normal forma (2NF)

Ikkinchi normal forma (2NF)

• 0-321-32132-1

• Balloon

• Small House

• 714-000-0000

• $34.00

• 0-55-123456-9

• Main Street

• Small House

• 714-000-0000

• $22.95

• 0-123-45678-0

• Ulysses

• Alpha Press

• 999-999-9999

• $34.00

• 1-22-233700-0

• Visual Basic

• Big House

• 123-456-7890

• $25.00

• ISBN

• Title

• PubName

• PubPhone

• Price

• ISBN

• AuName

• AuPhone

• 0-123-45678-0

• Joyce

• 666-666-6666

• 1-22-233700-0

• Roman

• 444-444-4444

• 0-55-123456-9

• Smith

• 654-223-3455

• 0-55-123456-9

• Jones

• 123-333-3333

• 0-321-32132-1

• Grumpy

• 665-235-6532

• 0-321-32132-1

• Snoopy

• 232-234-1234

• 0-321-32132-1

• Sleepy

• 321-321-1111

• SELECT tab1.*, tab2.* from tab1
• INNER JOIN tab2 ON tab1.ISBN=tab2.ISBN;

ISBN	Title	PubName	PubPhone	Price	AuName	AuPhone
0-321-32132-1	Balloon	Small House	714-000-0000	$34.00	Sleepy	321-321-1111
0-321-32132-1	Balloon	Small House	714-000-0000	$34.00	Snoopy	232-234-1234
0-321-32132-1	Balloon	Small House	714-000-0000	$34.00	Grumpy	665-235-6532

Uchinchi normal forma (3NF).

• Ma’lumotlar munosabatlarda 2nf ga kеltirilganda ham bir qancha noqulayliklar bo‘ladi. Jumladan, ma’lumotlarda axborotlarni ortiqchaligi, amallarni bajarish qiyinligi va boshqalar. Bunday munosabatlarni 3NFga kеltiriladi. 3NFda tranzitiv bog‘lanish paydo bo`ladi.
• Agar A,B,S R munosabatini 3 ta atributi yoki atributlar to‘plami bo‘lsin. Agar B atribut A atributga, S atribut esa B atributga bog‘langan bo‘lsa, ya’ni, A → B va B → S Bunda tеskari bog‘linishlar bo‘lmasa, unda S atribut A atributga tranzitiv bog‘langan dеyiladi. Uni ko‘pincha diagramma ko‘rinishida quyidagicha bеlgilaymiz:

Uchinchi normal forma (3NF).

• A

• B

• C

• A

• B

• B

• C

• 3 NF bu sxemadan o`tishi uni 2 ta munosabatga ajratish bilan bajariladi.

Uchinchi normal forma (3NF)

• 0-321-32132-1

• Balloon

• Small House

• 714-000-0000

• $34.00

• 0-55-123456-9

• Main Street

• Small House

• 714-000-0000

• $22.95

• 0-123-45678-0

• Ulysses

• Alpha Press

• 999-999-9999

• $34.00

• 1-22-233700-0

• Visual Basic

• Big House

• 123-456-7890

• $25.00

• ISBN

• Title

• PubName

• PubPhone

• Price

• ISBN

• AuName

• AuPhone

• 0-123-45678-0

• Joyce

• 666-666-6666

• 1-22-233700-0

• Roman

• 444-444-4444

• 0-55-123456-9

• Smith

• 654-223-3455

• 0-55-123456-9

• Jones

• 123-333-3333

• 0-321-32132-1

• Grumpy

• 665-235-6532

• 0-321-32132-1

• Snoopy

• 232-234-1234

• 0-321-32132-1

• Sleepy

• 321-321-1111

• SELECT tab1.*, tab2.* from tab1 INNER JOIN
• (tab3 INNER JOIN tab2 ON tab3.ID=tab2.ISBN)
• ON tab3.ID=tab1.ISBN;

ID	Tab1_ISBN	Tab2_ISBN
1	0-321-32132-1	0-321-32132-1
2	0-321-32132-1	0-55-123456-9
3	0-321-32132-1	1-22-233700-0