4. Порядок выполнения работы
1. Используйте ODL модель данных о футбольных клубах, которая включает интерфейсы: футбольные клубы, тренеры, игроки, болельщики и отражает связи между ними. Выполните переход от ODL-модели к реляционному проекту.
2. Используйте E/R модель данных об инженерных фирмах, которая включает такие множества сущностей как фирмы, проекты, исполнители (инженеры), заказчики, оборудование и отражает связи между ними. Выполните переход от E/R модели к реляционному проекту.
3. Отношение «Жители поселка» содержит имя взрослого жителя поселка, его адрес, паспорт и дату рождения, имя каждого из его детей, метрику, дату рождения, номер автомобиля, марку автомобиля и пробег.
Определите функциональные зависимости и ключи отношения.
Определите, содержит ли отношение избыточность, и имеет ли аномалии.
Выполните декомпозицию отношения в нормальную форму BCNF.
Восстановите отношение из декомпозиции.
4. Сделайте выводы о методах перехода от моделей к реляционным проектам, о наличии избыточности и аномалий и о способах устранения избыточности.
Приложение 1
База данных спектаклей
Performances
title |
year |
runningTime |
type |
theatre |
composer |
producerC# |
Aida |
1998 |
260 |
perform |
Mariinsky |
Diuseppe Verdy |
123456 |
Don Carlos |
1999 |
225 |
perform |
La Scala |
Diuseppe Verdy |
234567 |
Don Pascuale |
1997 |
125 |
concert |
Mariinsky |
Gaetano Donizetti |
null |
Macbeth |
2001 |
180 |
perform |
Mariinsky |
Pyotr Tchaikovsky |
456789 |
Swan lake |
1950 |
175 |
perform |
Mariinsky |
Pyotr Tchaikovsky |
567890 |
Giselle |
1884 |
125 |
perform |
Mariinsky |
Adolphe Adam |
789012 |
Salome |
2000 |
90 |
perform |
Mariinsky |
Richard Strauss |
890123 |
Carmen |
1998 |
22 |
perform |
Sarah Bernhart |
George Bizet |
901234 |
Apollo |
1928 |
33 |
perform |
Mariinsky |
Igor Stravinsky |
112345 |
Otello |
1976 |
180 |
perform |
La Scala |
Diuseppe Verdy |
223456 |
Romeo and Juliet |
2001 |
180 |
perform |
Covent Garden |
Sergei Prokofiev |
334567 |
Arabella |
1997 |
180 |
perform |
Covent Garden |
Peter Mussbach |
445678 |
ActsIn
perfTitle |
theatreName |
perfYear |
actorName |
Don Carlos |
La Scala |
1999 |
Olga Borodina |
Carmen |
La Canerenlola |
1998 |
Olga Borodina |
Aida |
Mariinsky |
1998 |
Olga Borodina |
Aida |
Mariinsky |
1998 |
Irina Bogacheva |
Rigoletto |
Bolshoy Theatre |
1980 |
Irina Bogacheva |
Samson and Dalila |
Mariinsky |
2003 |
Irina Bogacheva |
Carmen |
Mariinsky |
1998 |
Yury Marusin |
La Traviata |
Bolshoy Theatre |
1980 |
Yury Marusin |
Swan Lake |
Mariinsky |
1950 |
Ulyana Lopatkina |
Giselle |
Mariinsky |
1884 |
Ulyana Lopatkina |
Giselle |
Mariinsky |
1884 |
Farukh Ruzimatov |
Apollo |
Mariinsky |
1928 |
Farukh Ruzimatov |
Otello |
La Scala |
1976 |
Plassito Domingo |
Otello |
La Scala |
1976 |
Frivotti Nuggi |
Actors
name |
address |
ampoule |
gender |
bithdate |
Olga Borodina |
Saint Petersburg Garden st.10/10 |
Singer |
F |
1972-02-02 |
Irina Bogacheva |
Saint Petersburg Nevsky st.15/15 |
Singer |
F |
1950-01-01 |
Yury Marusin |
Saint Petersburg Grate Sea st.15/15 |
Singer |
M |
1945-05-05 |
Ulyana Lopatkina |
Saint Petersburg Small Sea st.25/25 |
Principal dancer |
F |
1975-05-05 |
Farukh Ruzimatov |
Saint Petersburg Nevsky st.150/15 |
Principal dancer |
M |
1973-03-03 |
Plassito Domingo |
Milan |
Singer |
M |
1966-06-06 |
Producers
name |
address |
cert# |
networth |
Alexei Stepaniuk |
Saint Petersburg Garden st.10/10 |
123456 |
110000 |
Yury Alexandrov |
Saint Petersburg Lime av.15/15 |
234567 |
210000 |
David McVicar |
London Old st. 12/2 |
456789 |
1200000 |
Lev Ivanov |
Saint Petersburg Palace quay 21 |
567890 |
400000 |
Jules Perrot |
Paris Capuchin av.112 |
789012 |
550000 |
David Freeman |
New York Fifth av.133 |
890123 |
630000 |
Rollan Petit |
Paris Capuchin av.150 |
901234 |
920000 |
George Balanchine |
Paris |
012345 |
100000 |
Valery Gergiev |
Saint Petersburg Theatre sq.1 |
001122 |
1000000 |
Karlo Fontana |
Milan |
223456 |
10000000 |
Kennet Macmillan |
London Olg St 33 |
334567 |
20000000 |
Peter Mussbach |
London Liverpool st 17 |
445678 |
22000000 |
Theatre
teatreName |
artDirector |
city |
cert# |
Mariinsky |
Valery Gergiev |
Saint Petersburg |
001122 |
La Scala |
Karlo Fontana |
Milan |
223456 |
Covent Garden |
Russel Roberts |
London |
333444 |
Bolshoy Theatre |
Valery Borisov |
Moscow |
444555 |
Grand Opera |
Rudolf Nuriev |
Paris |
555666 |
База данных кораблей второй мировой войны
Classes Battles
сlass |
type |
country |
num Guns |
bore |
displacement |
|
name |
date |
Bismark |
Bs |
Germany |
8 |
15 |
42000 |
|
North Atlantic |
24.2.41 |
Iowa |
Bs |
USA |
9 |
16 |
46000 |
|
Guadalcanal |
15.11.42 |
Kondo |
Bc |
Japan |
8 |
14 |
32000 |
|
North Cope |
26.12.43 |
North California |
Bs |
USA |
9 |
16 |
37000 |
|
Surigao Straiy |
25.10.44 |
Renown |
Bc |
G Britain |
6 |
15 |
32000 |
|
|
|
Revenge |
Bs |
G Britain |
8 |
15 |
29000 |
|
|
|
Tennessee |
Bs |
USA |
12 |
14 |
32000 |
|
|
|
Yamato |
Bs |
Japan |
9 |
18 |
65000 |
|
|
|
Outcomes Ships
ship |
battle |
result |
|
name |
class |
launched |
Bismarck |
North Atlantic |
sunk |
|
California |
Tennessee |
1921 |
California |
Surigao Strait |
ok |
|
Haruna |
Kongo |
1915 |
Duke of York |
North Cape |
ok |
|
Hiei |
Kongo |
1914 |
Fuso |
Surigao Strait |
sunk |
|
Iowa |
Iowa |
1943 |
Hood |
North Atlantic |
sunk |
|
Kirishima |
Kongo |
1915 |
King George |
North Atlantic |
ok |
|
Kongo |
Kongo |
1913 |
Kirishima |
Guadalcanal |
sunk |
|
Missouri |
Iowa |
1944 |
Prince of Wales |
North Atlantic |
damaged |
|
Musachi |
Yamato |
1942 |
Rodney |
North Atlantic |
ok |
|
New Jersey |
Iowa |
1943 |
Schamhost |
North Cape |
sunk |
|
North Carolina |
North Carolina |
1941 |
South Dakota |
Guadalcanal |
damaged |
|
Ramillies |
Ravenge |
1917 |
Tennessee |
Surigao Strait |
ok |
|
Renown |
Renown |
1916 |
Washington |
Guadalcanal |
ok |
|
Repulse |
Renown |
1916 |
West Virginia |
Surigao Strait |
ok |
|
Resolution |
Revenge |
1916 |
Yamashiro |
Surigao Strait |
sunk |
|
Revenge |
Revenge |
1916 |
|
|
|
|
Royal Oak |
Revenge |
1916 |
|
|
|
|
Royal Sovereign |
Revenge |
1916 |
|
|
|
|
Tennessee |
Tennessee |
1920 |
|
|
|
|
Washington |
North Carolina |
1941 |
|
|
|
|
Wisconsin |
Iowa |
1944 |
|
|
|
|
Yamato |
Yamato |
1941 |
База данных компьютеров
Product |
|
PC |
|||||||
Maker |
Model |
Type |
|
Model |
Speed |
RAM |
HD |
CD |
Price |
A |
1001 |
PC |
|
1001 |
1330 |
160 |
160 |
6x |
595 |
A |
1002 |
PC |
|
1002 |
2200 |
320 |
160 |
6x |
399 |
A |
1003 |
PC |
|
1003 |
1660 |
1280 |
250 |
6x |
299 |
B |
1004 |
PC |
|
1004 |
5000 |
320 |
250 |
8x |
999 |
B |
1006 |
PC |
|
1005 |
6000 |
2560 |
200 |
8x |
899 |
B |
3002 |
Printer |
|
1006 |
5000 |
2560 |
310 |
8x |
1099 |
B |
3004 |
Printer |
|
1007 |
2000 |
1280 |
320 |
8x |
350 |
C |
1005 |
PC |
|
1008 |
2000 |
320 |
200 |
8x |
340 |
C |
1007 |
PC |
|
1009 |
7000 |
2560 |
250 |
8x |
790 |
D |
1008 |
PC |
|
1010 |
1660 |
160 |
120 |
8x |
218 |
D |
1009 |
PC |
|
|
|
|
|
|
|
D |
1010 |
PC |
|
Laptop |
|||||
D |
2001 |
Laptop |
|
Model |
Speed |
RAM |
HD |
Screen |
Price |
D |
2002 |
Laptop |
|
2001 |
5000 |
1280 |
100 |
9.5 |
1002 |
D |
2003 |
Laptop |
|
2002 |
5500 |
1280 |
70 |
11.3 |
800 |
D |
3001 |
Printer |
|
2003 |
5500 |
640 |
100 |
10.4 |
840 |
D |
3003 |
Printer |
|
2004 |
6000 |
640 |
120 |
11.2 |
1050 |
E |
2004 |
Laptop |
|
2005 |
6000 |
640 |
80 |
11.2 |
750 |
E |
2008 |
Laptop |
|
2006 |
5500 |
2560 |
160 |
12.1 |
1080 |
F |
2005 |
Laptop |
|
2007 |
7000 |
2560 |
160 |
12.1 |
1200 |
G |
2006 |
Laptop |
|
2008 |
5500 |
1280 |
120 |
12.1 |
880 |
G |
2007 |
Laptop |
|
|
|
|
|
|
|
H |
3005 |
Printer |
|
Printer |
|
||||
I |
3006 |
Printer |
|
Model |
Color |
Type |
Price |
|
|
|
|
|
|
3001 |
True |
Ink-jet |
275 |
|
|
|
|
|
|
3002 |
True |
Ink-jet |
255 |
|
|
|
|
|
|
3003 |
False |
Laser |
900 |
|
|
|
|
|
|
3004 |
False |
Laser |
888 |
|
|
|
|
|
|
3005 |
False |
Ink-jet |
240 |
|
|
|
|
|
|
3006 |
true |
Laser |
870 |
|
|