Baza danych

K. Matouk – ćwiczenia z Accessa
1
Bazy danych
A. PROJEKT BAZY DANYCH
Zaprojektować bazę danych zawierającą informacje o pewnej FIRMIE.
Potrzebne są informacje o
PRACOWNIKACH,
GRUPACH (grupach pracowników) i
ZADANIACH.
TABELE
(Koniecznie, ale być może niewystarczające).
TABELA
ATRYBUTY (KOLUMNY)
Grupa
ID_Pracownika, Imię, Nazwisko, DataUr, Zwierzchnik (identyfikator
pracownika w tej samej tabeli)
ID_Grupy, Nazwa, ID_Szefa (identyfikator pracownika)
Zadania
ID_Zadania, ID_Grupy, Nazwa, DataRozp, DataZak, MinLiczbaOsób
Pracownik
????
Klucze, jeśli są pojedynczymi polami, powinny być typu Autonumer.
POWIĄZANIA (RELACJE)
(Występują powiązania 1-1 i 1-Wiele.)
 Grupy są tworzone tylko na potrzeby zadań (jednej grupie odpowiada jedno zadanie i
odwrotnie). Zadanie może być jednak utworzone przed utworzeniem grupy. Sprzężenie
powinno być takie, by można było oglądać wszystkie zadania, nie tylko te, którym
przydzielono grupy.
(uwaga: należy połączyć pole IDGrupy (od tabeli Zadania) z IDgrupy (do tabeli Grupa).
Na pierwszy rzut oka może to wyglądać jako relacja 1-∞, ale w momencie kiedy
zmieniamy właściwości pola IDGrupy (w tabeli Zadania) w miejscu indeksowane na Tak
(Bez duplikatów), relacja staje się 1-1).
 Jeden pracownik może być szefem wielu grup. Grupa nie musi mieć określonego szefa.
Więzy integralności mają być sprawdzane, ale niewymuszane (bez operacji
kaskadowych). Sprzężenie między tablicami należy określić tak, by w zapytaniach można
było oglądać grupy i nazwiska ich szefów oraz grupy nieposiadające szefów.
 Pracownik może należeć do wielu grup. Grupa może składać się z wielu pracowników.
Należy wymusić przestrzeganie więzów integralności i kaskadowe usuwanie i
modyfikowanie rekordów.
Relacje
WARUNKI POPRAWNOŚCI (REGUŁY)
Jeśli znana jest data urodzenia pracownika (może być pusta), to na pewno spełniony jest
warunek, że pracownik ma nie więcej niż 65 lat (nie jest jeszcze na emeryturze).
B. KWERENDY
W wyznaczonej bazie danych (zawierającej informacje o pracownikach pewnej firmy) należy
utworzyć poniższe zapytania:
K. Matouk – ćwiczenia z Accessa
2
1. pracownicy (imię, nazwisko, data urodzenia) urodzeni w roku 1970 lub później,
uszeregowani malejąco wg wieku,
zad1
2. jak wyżej, a ponadto należy podać wiek pracowników (liczbę lat)
zad2
3. pracownicy i dane (imiona i nazwiska) ich stałych zwierzchników
zad3
4. pracownicy pracujący w grupie pod nazwą „Marketing”; nazwisko i imię pracownika
mają się znaleźć w jednym polu (w tej kolejności) o nazwie „Pracownik”; dane mają być
uporządkowane rosnąco wg tego pola
zad4
5. pracownicy pracujący w grupie o zadanej nazwie (parametr), uporządkowani rosnąco wg
nazwisk i imion)
6. pracownicy i liczba grup, w których pracują
zad6
7. pracownicy, którzy pracują w więcej niż dwóch grupach i liczba grup, w których pracują
zad7
8. pracownicy urodzeni w roku 1970 lub później i liczba grup, w których pracują
zad8
9. wszyscy podwładni zwierzchnika o zadanym nazwisku (parametr)
10. 5 pracowników z największą sumą zarobków (suma po wszystkich grupach)
zad10
C. KWERENDY CD
W wyznaczonej bazie danych (zawierającej informacje o pracownikach pewnej firmy) należy
utworzyć poniższe zapytania:
1. pracownicy i grupy, w których pracują
zad1cd
(pola: IDPracownika, Pracownik zawierający nazwisko i imię pracownika, IDGrupy,
Nazwa grupy).
Uporządkowanie: rosnąco wg pola Pracownik
2. daty urodzenia pracowników, których nazwiska rozpoczynają się podanym ciągiem
znaków (parametr)
(pola: Nazwisko, Imię, DataUr).
Uporządkowanie: rosnąco wg nazwisk
UWAGA
Podanie pustego ciągu znaków powinno spowodować wyświetlenie wszystkich
pracowników
3. suma zarobków pracowników:
zad3cd
Sumowane mają być zarobki >= 400 zł, a wyświetlane tylko sumy zarobków >=1000 zł;
(pola: Pracownik, Suma zarobków).
Uporządkowanie: malejąco wg sumy zarobków
K. Matouk – ćwiczenia z Accessa
3
4. długości trwania zadań
zad4cd
(pola: Szef grupy (nazwisko i imię szefa), Nazwa grupy, Nazwa zadania, Data
Rozpoczęcia, Długość zadania (liczba dni, jeśli znane są data rozpoczęcia i data
zakończenia zadania, w przeciwnym wypadku - wartość „?”).
Uporządkowanie: malejąco wg długości zadań
UWAGA
Zadania nie muszą mieć przydzielonych grup, a grupy nie muszą mieć wyznaczonych
swoich szefów
5. pracownicy i grupy, w których pracują, oraz nazwiska szefów tych grup
zad5cd
6. nazwy grup i dane ich szefów
(pola: Szef zawierające Nazwisko i imię, Rok urodzenia).
Uporządkowanie: wg pola Szef
UWAGA
Należy także wyświetlić grupy niemające szefów.
zad6cd
7. zadania, dla których liczba przydzielonych pracowników jest przynajmniej taka, jak
podano w polu MinLiczbaPrac)
zad7cd
UWAGA
Należy zwrócić uwagę na pola wg, których ma przebiegać grupowanie i pola, które mają
być zliczane.
8. zestawienie zawierające grupy i wypłaty w grupach;
zapytanie krzyżowe:
nagłówki wierszy - nazwa grupy, suma wypłat w danej grupie,
nagłówek kolumny - dane pracownika (nazwisko i imię) (rosnąco),
wartości
- wypłaty pracownika w danej grupie.
zad8cd
9. to samo, co w zadaniu 8, a oprócz tego nagłówkiem wiersza ma być nazwisko szefa grupy
zad9cd
K. Matouk – ćwiczenia z Accessa
Relacje
4
K. Matouk – ćwiczenia z Accessa
Zadanie 1
5
K. Matouk – ćwiczenia z Accessa
Zadanie 2
6
K. Matouk – ćwiczenia z Accessa
Zadanie 3
7
K. Matouk – ćwiczenia z Accessa
Zadanie 4
Zadanie 5
8
K. Matouk – ćwiczenia z Accessa
Zadanie 6
9
K. Matouk – ćwiczenia z Accessa
Zadanie 7
10
K. Matouk – ćwiczenia z Accessa
Zadanie 8
11
K. Matouk – ćwiczenia z Accessa
Zadanie 10
w widoku SQL modyfikujemy instrukcję dodając TOP 5
SELECT TOP 5 Pracownik.Imię, Pracownik.Nazwisko, Sum(PracWGrupie.[Miesięczna Wypłata]) AS
[SumazMiesięczna Wypłata]
FROM Pracownik INNER JOIN PracWGrupie ON Pracownik.IDPracownika = PracWGrupie.IDPracownika
GROUP BY Pracownik.Imię, Pracownik.Nazwisko;
12
K. Matouk – ćwiczenia z Accessa
Zadanie 1 cd
13
K. Matouk – ćwiczenia z Accessa
Zadanie 3 cd
14
K. Matouk – ćwiczenia z Accessa
Zadanie 4 cd
15
K. Matouk – ćwiczenia z Accessa
Zadanie 5 cd
16
K. Matouk – ćwiczenia z Accessa
Zadanie 6 cd
17
K. Matouk – ćwiczenia z Accessa
Zadanie 7 cd
18
K. Matouk – ćwiczenia z Accessa
Zadanie 8 cd
Kwerenda dla potrzeb tego zadania
Rozwiązanie
19
K. Matouk – ćwiczenia z Accessa
Zadanie 9 cd
Kwerenda dla potrzeb tego zadania
Rozwiązanie 9 cd
20