Нормализација на базата

Нормални форми по релации

Релација	Клуч(еви)	Функциски зависности (ФЗ)	Нормална форма
Full(track_id, name, album_id, artist_id, media_type_id, genre_id, composer, milliseconds, bytes, price_id, value, date, invoice_line_id, invoice_id, quantity, customer_id, first_name, last_name, company, support_rep_id, personal_info_id, contact_id, employee_id, title, reports_to, birth_date, hire_date, address_info_id, address, city, state, country, postal_code, phone, fax, email, playlist_id, playlist_name, playlist_track_id)	track_id, album_id, artist_id, media_type_id, genre_id, price_id, invoice_line_id, invoice_id, customer_id, employee_id, address_info_id, contact_id, playlist_id, playlist_track_id	Многу функционални зависности (track_id → {...}, album_id → {...}, artist_id → name, ...)	1NF

Сите атрибути се атомични

Заклучок

Релација	Клуч(еви)	Функциски зависности (ФЗ)	Нормална форма
Track(track_id, name, album_id, media_type_id, genre_id, composer, milliseconds, bytes)	track_id	track_id → {name, album_id, media_type_id, genre_id, composer, milliseconds, bytes}	BCNF
Album(album_id, title, artist_id)	album_id	album_id → {title, artist_id}	BCNF
Artist(artist_id, name)	artist_id	artist_id → name	BCNF
Genre(genre_id, name)	genre_id	genre_id → name	BCNF
MediaType(media_type_id, name)	media_type_id	media_type_id → name	BCNF

Основни ентитети

Заклучок

Релација	Клуч(еви)	Функциски зависности (ФЗ)	Нормална форма
Price(price_id, value, date, track_id)	price_id (и можен (date, track_id))	price_id → {value, date, track_id}	BCNF

Табела за цени

Заклучок

Релација	Клуч(еви)	Функциски зависности (ФЗ)	Нормална форма
InvoiceLine(invoice_line_id, invoice_id, track_id, quantity)	invoice_line_id (и можен (invoice_id, track_id))	(invoice_line_id → {invoice_id, track_id, quantity})	BCNF
Invoice(invoice_id, customer_id, invoice_date, billing_address, billing_city, billing_state, billing_country, billing_postal_code, total)	invoice_id	invoice_id → {customer_id, invoice_date, billing_address, billing_city, billing_state, billing_country, billing_postal_code, total}	BCNF

Заклучок

Фактури

Релација	Клуч(еви)	Функциски зависности (ФЗ)	Нормална форма
Customer(customer_id, first_name, last_name, company, support_rep_id, personal_info_id, contact_id)	customer_id	customer_id → {first_name, last_name, company, support_rep_id, personal_info_id, contact_id}	BCNF
Employee(employee_id, first_name, last_name, title, reports_to, birth_date, hire_date, personal_info_id, contact_id)	employee_id	employee_id → {first_name, last_name, title, reports_to, birth_date, hire_date, personal_info_id, contact_id}	BCNF
AddressInfo(address_info_id, address, city, state, country, postal_code)	address_info_id	address_info_id → {address, city, state, country, postal_code}	BCNF
Contact(contact_id, phone, fax, email)	contact_id	contact_id → {phone, fax, email}	BCNF

отстрануваат повторувања на Track во Playlist

Заклучок

Релација	Клуч(еви)	Функциски зависности (ФЗ)	Нормална форма
Playlist(playlist_id, name)	playlist_id	playlist_id → name	BCNF
PlaylistTrack(playlist_track_id, playlist_id, track_id)	playlist_track_id (и можен (playlist_id, track_id))	playlist_track_id → {playlist_id, track_id}	BCNF

Контакт инфо

Заклучок

Релација	Клуч(еви)	Функциски зависности (ФЗ)	Нормална форма
Track(track_id, name, album_id, media_type_id, genre_id, composer, milliseconds, bytes)	track_id	track_id → {name, album_id, media_type_id, genre_id, composer, milliseconds, bytes}	BCNF
Album(album_id, title, artist_id)	album_id	album_id → {title, artist_id}	BCNF
Artist(artist_id, name)	artist_id	artist_id → name	BCNF
Genre(genre_id, name)	genre_id	genre_id → name	BCNF
MediaType(media_type_id, name)	media_type_id	media_type_id → name	BCNF
Price(price_id, value, date, track_id)	price_id (и можен (date, track_id))	price_id → {value, date, track_id}	BCNF
InvoiceLine(invoice_line_id, invoice_id, track_id, quantity)	invoice_line_id (и можен (invoice_id, track_id))	(invoice_line_id → {invoice_id, track_id, quantity})	BCNF
Invoice(invoice_id, customer_id, invoice_date, billing_address, billing_city, billing_state, billing_country, billing_postal_code, total)	invoice_id	invoice_id → {customer_id, invoice_date, billing_address, billing_city, billing_state, billing_country, billing_postal_code, total}	BCNF
Customer(customer_id, first_name, last_name, company, support_rep_id, personal_info_id, contact_id)	customer_id	customer_id → {first_name, last_name, company, support_rep_id, personal_info_id, contact_id}	BCNF
Employee(employee_id, first_name, last_name, title, reports_to, birth_date, hire_date, personal_info_id, contact_id)	employee_id	employee_id → {first_name, last_name, title, reports_to, birth_date, hire_date, personal_info_id, contact_id}	BCNF
AddressInfo(address_info_id, address, city, state, country, postal_code)	address_info_id	address_info_id → {address, city, state, country, postal_code}	BCNF
Contact(contact_id, phone, fax, email)	contact_id	contact_id → {phone, fax, email}	BCNF
Playlist(playlist_id, name)	playlist_id	playlist_id → name	BCNF
PlaylistTrack(playlist_track_id, playlist_id, track_id)	playlist_track_id (и можен (playlist_id, track_id))	playlist_track_id → {playlist_id, track_id}	BCNF

Заклучок

• Сите релации се во BCNF.
• Релациите Price и InvoiceLine може да сметаме дека се во 3NF, бидејќи date, track_id се уникатни кај Price, invoice_id, track_id кај InvoiceLine, во база не е сетирано дека мора да се уникатни, но за ова се осигураме деке е такa со соодветни тригери (upsert_price и merge_invoice_line).
• Price – нова теабела која ни овозможува чување на историј на цени.
• Новите табели AddressInfo и Contact овозможуваат отстранување на повторувања на адреси и контакти → уште почиста нормализација.