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

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

BaseRelation(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.

Примарен клуч (на BaseRelation): (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)

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