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

1. Почетна релација (UNF)

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)

2. Функционални зависности (FDs)

Track

track_id → name, album_id, media_type_id, genre_id, composer, milliseconds, bytes

Price

price_id → value, date, track_id

Album

album_id → artist_id

InvoiceLine

invoice_line_id → invoice_id, track_id, quantity

Invoice

invoice_id → customer_id

Customer

customer_id → first_name, last_name, company, support_rep_id, personal_info_id, contact_id

Employee

employee_id → title, reports_to, birth_date, hire_date

AddressInfo

address_info_id → address, city, state, country, postal_code, phone, fax, email

Playlist

playlist_id → playlist_name

PlaylistTrack

playlist_track_id → playlist_id, track_id

3. Прва нормална форма (1NF)

Сите атрибути се атомарни → релацијата е во 1NF.

Примарен клуч (на Full): (invoice_line_id, playlist_track_id) (секој ред е уникатен по комбинацијата на фактурна линија и песна во плејлиста).

4. Втора нормална форма (2NF)

Ги одвојуваме зависностите од делови на составениот клуч:

4.1 Track

Track(track_id, name, album_id, media_type_id, genre_id, composer, milliseconds, bytes)
PK: track_id

4.2 Price

Price(price_id, value, date, track_id)
PK: price_id
FK: track_id → Track(track_id)

4.3 Album

Album(album_id, artist_id)
PK: album_id
FK: artist_id → Artist(artist_id)

4.4 Artist

Artist(artist_id, artist_name)
PK: artist_id

4.5 InvoiceLine

InvoiceLine(invoice_line_id, invoice_id, track_id, quantity)
PK: invoice_line_id
FK: invoice_id → Invoice(invoice_id)
FK: track_id → Track(track_id)

4.6 Invoice

Invoice(invoice_id, customer_id)
PK: invoice_id
FK: customer_id → Customer(customer_id)

4.7 Customer

Customer(customer_id, first_name, last_name, company, support_rep_id, personal_info_id, contact_id)
PK: customer_id
FK: support_rep_id → Employee(employee_id)

4.8 Employee

Employee(employee_id, title, reports_to, birth_date, hire_date)
PK: employee_id

4.9 AddressInfo

AddressInfo(address_info_id, address, city, state, country, postal_code, phone, fax, email)
PK: address_info_id

4.10 Playlist

Playlist(playlist_id, playlist_name)
PK: playlist_id

4.11 PlaylistTrack

PlaylistTrack(playlist_track_id, playlist_id, track_id)
PK: playlist_track_id
FK: playlist_id → Playlist(playlist_id)
FK: track_id → Track(track_id)

5. Трета нормална форма (3NF)

album_id → artist_id → Artist е одвоен.

support_rep_id → Employee е FK, нема транзитивна зависност во Customer.

Адресата е изнесена во AddressInfo.

Сите не-клучни атрибути зависат директно од клучевите.

✅ Сега сите релации се во 3NF.

Финален нормализиран модел (3NF)

Track(track_id, name, album_id, media_type_id, genre_id, composer, milliseconds, bytes)

Price(price_id, value, date, track_id)

Album(album_id, artist_id)

Artist(artist_id, artist_name)

InvoiceLine(invoice_line_id, invoice_id, track_id, quantity)

Invoice(invoice_id, customer_id)

Customer(customer_id, first_name, last_name, company, support_rep_id, personal_info_id, contact_id)

Employee(employee_id, title, reports_to, birth_date, hire_date)

AddressInfo(address_info_id, address, city, state, country, postal_code, phone, fax, email)

Playlist(playlist_id, playlist_name)

PlaylistTrack(playlist_track_id, playlist_id, track_id)

← Назад на почетна