Opracuj na piśmie schemat bazy danych zamówień na towary (OBOWIĄZKOWE)

klienci, zamówienia, produkty;

musi być możliwość obliczenia, ile każdy klient jest winien za zamówione towary i zaznaczenia, czy klient już zapłacił za dane zamówienie;

określ atrybuty istotne dla poszczególnych obiektów;

określ powiązania, pamiętając, że zamówienie musi mieć co najmniej 1 pozycję, klient może być zarejestrowany mimo, że jeszcze nic nie zamówił, produkt ma stałą cenę jednostkową, zamawia się ściśle określone ilości ściśle określonych produktów, nie wszystkie produkty firmy stały się przedmiotem zamówienia;

nazwij powiązania i określ liczebności po stronie wiele;

Zadania
Zaproponuj relacyjną bazę danych Imieniny. Jaką wadę ma tabela (ddmm, rok, godz_wschodu_słońca, kurs_dolara)? Zaproponuj przekształcenie na dwie tabele. Jaką wadę ma tabela (nazwa_kursu, semestr, prowadzący, auto_prowadzącego) ? Zaproponuj przekształcenie na dwie tabele.

Pokaż, dlaczego relacja:

{miasto, dzielnica, symbol_rejestracyjny_samochodów}

F = { miasto, dzielnica --> symbol_rejestracyjny_samochodów,

miasto, symbol --> dzielnica,

symbol_rejestracyjny_samochodów --> miasto }

nie jest w postaci normalnej Boyce’a-Codda?

Czy jest w trzeciej postaci normalnej?

zadania na ćwiczeniach

  1. podaj na 2 sposoby pary nazwisko pracownika, nazwisko jego szefa - uwzględnij także samego szefa firmy
  2. podaj rodzaje stanowisk, na których firma zatrudniała w okresie '01-JAN-83' - '30-JUN-83' o ile zatrudniała na nich także w okresie '01-JAN-84' - '30-JUN-84'
  3. podaj pracowników zatrudnionych w tym samym departamecie, co SMITH
  4. znajdź na 2 sposoby departament, w którym brak pracowników
  5. podaj ostatnio zatrudnionego pracownika(ów) w każdym z departamentów
  6. wybierz nazwiska i daty zatrudnienia, przy dacie ostatnio zatrudnionego wstaw '*'

odpowiedzi

  1. sposób I: SELECT p.ename, s.enameFROM emp p, emp s
    WHERE p.mgr = s.empno(+)
    sposób II: SELECT p.ename, s.enameFROM emp p, emp s
    WHERE p.mgr = s.empno UNION SELECT ename, ‘ ‘ FROM emp WHERE mgr IS Null
  2. SELECT job FROM emp WHERE hiredate between '01-JAN-83' and '30-JUN-83'
    intersect
    SELECT job FROM emp WHERE hiredate between '01-JAN-84' AND '30-JUN-84'
  3. SELECT ename FROM emp WHERE deptno = (SELECT deptno FROM emp WHERE ename=‘SMITH’)
  4. sposób I: SELECT d.deptno, d.dname FROM emp e, dept d WHERE e.deptno(+)=d.deptno and e.empno IS Null
    sposób II: SELECT deptno FROM dept MINUS SELECT deptno from emp
  5. SELECT deptno, ename, hiredate FROM emp
    WHERE (hiredate, deptno) in
    (SELECT max(hiredate), deptno FROM emp GROUP BY deptno)
  6. SELECT ename, hiredate, '*'
    FROM emp
    WHERE hiredate = (SELECT max(hiredate) FROM emp)
    UNION
    SELECT ename, hiredate, ' '
    FROM emp
    WHERE hiredate < (SELECT max(hiredate) FROM emp)

zadania domowe (OBOWIĄZKOWE)

  1. wybierz dane o szefie firmy, podając także nazwę jego departamentu oraz klasę zaszeregowania jego zarobków /IS NULL/
  2. podaj rodzaje stanowisk, na których pracownicy zarabiają od 2000 do 3000; pamiętaj o usunięciu duplikatów
  3. dla każdej klasy zaszeregowania wyznacz osoby zarabiające najmniej w tej klasie /IN, podzapytanie/
  4. podaj nazwisko(a) osób najdawniej zatrudnionej(ych) /podzapytanie/
  5. obok nazwiska każdego pracownika umiesc skrót nazwy jego stanowiska z 3 jego pierwszych znaków, dzien tygodnia, w którym został zatrudniony, zarobki miesieczne w postaci <kwota> zl (np. 1000 zł) oraz prowizję roczną w takim samym formacie; w razie braku prowizji wygeneruj napis “BRAK”

 

zadania na ćwiczenia i do domu (OBOWIĄZKOWE)
  1. Uporządkuj pracowników wg daty ich zatrudnienia
  2. Oblicz ilu pracowników jest zatrudnionych na każdym stanowisku.
  3. Oblicz ilu pracowników zarabia miesięcznie powyżej średniej.
  4. Dla każdego pracownika oblicz ile dni pracuje w firmie.
  5. Jaki dzień tygodnia przypadnie za 1000 dni?
  6. Od dzisiaj zamiast stanowiska “CLERK” obowiązuje “OFFICER”. Wygeneruj aktualną listę pracowników: nazwisko, stanowisko, data_zatrudnienia.
  7. Podaj dla każdego szefa ilu ma podwładnych (numer_szefa, liczba_podwładnych).
  8. Wypełnij w tabeli EMP puste miejsca w kolumnie COMM napisem BRAK.
  9. Znajdź osoby o najdłuższym nazwisku w firmie.
  10. Czy istnieje choć jeden pracownik o zarobkach równych średniej?
  11. Podaj liczbę osób w każdej klasie zaszeregowania.
  12. Podaj trzech najlepiej zarabiających pracowników.
  13. Stwórz listę pracowników, wyróżnij osobę/osoby najwcześniej zatrudnione.
  14. Czy jest departament, w którym każdy pracownik zarabia powyżej średniej?
  15. Wygeneruj listę pracowników - nazwisko, data_zatrudnienia, nazwa_departamentu, klasa_zaszeregowania.
  16. Na którym stanowisku/stanowiskach najlepsze są średnie zarobki?
  17. W którym departamencie zatrudniono najpóźniej pracownika?

 

zadania na kolokwium grupa I

MODELOWANIE

1. W stolicy zostały wyznaczone trasy linii autobusowych - są to zestawy kolejnych przystanków. Każdą trasą porusza się jeden autobus (np. 159). Autobusy przemieszczają się zgodnie z jednolitym rozkładem jazdy, na którym wyróżnione są godziny jazdy autobusów niskopodłogowych.

Stwórz model komunikacji autobusowej miasta stosując notację ERWina. Pamiętaj o wyróżnieniu atrybutów kluczowych, zaznaczeniu rodzaju związków, zaznaczeniu liczebności oraz reguł spójności referencyjnej.

Zakładając stworzenie na bazie modelu odpowiednich tabel napisz zapytanie wybierające wszystkie linie autobusowe, które z przystanku ‘ŁAŻIENKI’ zabierają pasażerów na ‘PLAC ZAMKOWY’.

2. Przedstaw graficznie związek między encją EMP a encją DEPT.

SQL
  1. Znajdź departament/departamenty bez obsady.
  2. Podaj numery departamentów, w których jest mniej niż czterech pracowników.
  3. Jak liczna jest grupa pracowników w klasie zaszeregowania 3 (GRADE)?
  4. Wygeneruj za pomocą polecenia SELECT napis "klasówka z SQL".
  5. Wyznacz stanowiska (JOB), na których nie przysługuje prowizja (COMM). Pamiętaj o wyeliminowaniu duplikatów.
  6. Ilu pracowników posiada zarobki (SAL) pomiędzy 1000 a 2000?
  7. Ile miesięcznie w sumie zarabiają pracownicy zatrudnieni na stanowisku ANALYST?
  8. Podaj pary nazwisk bezpośredni szef, pracownik, uszereguj je w kolejności alfabetycznej nazwisk szefów.

 

zadania na ćwiczeniach

uwaga: jeśli pracujemy na koncie demo (SCOTT/TIGER) wówczas trzeba pamiętać o konieczności zakładania tabel o różnych nazwach
  1. * znajdź trzech najlepiej zarabiających pracowników
  2. utwórz tabelę emp1 na wzór emp (osoby pracujące na koncie SCOTT tworzą tabele o nazwie s<nr_indeksu>)
  3. w tabeli emp1 wyrównaj wszystkim płace do średniej pensji według stanowisk
  4. udostępnij tabelę emp1 wszystkim do oglądania
  5. zmień w emp1 datę zatrudnienia SCOTT’a na 3 kwietnia br. godzina 13:15:00
  6. zajrzyj do tabeli emp1 sąsiada
  7. utwórz jednokolumnową tabelę losowe, (nazwa kolumny liczba, typ kolumny - liczba trzycyfrowa, PRIMARY KEY)


  8. wstaw do tabeli losowe co najmniej 6 liczb (100, 12, 34, 23, 0, 1). Zastosuj parametryzację zapytania w SQL*Plus.

  9. * wybierz z tabeli losowe wartości kolumny liczba w porządku rosnącym nie stosując ORDER BY, numerując je po kolei:1 0

2 1

3 12

4 23

5 34

6 100
  1. podłącz się jako SCOTT/TIGER; wybierz z tabeli EMP informację o swoim (czyli SCOTT-a) rekordzie danych.

odpowiedzi

  1. SELECT prac.ename FROM emp prac WHERE
    3 > (SELECT count(*) FROM emp WHERE prac.sal < sal);
  2. CREATE TABLE emp1 AS SELECT * FROM emp
  3. UPDATE emp1 e1
    SET e1.sal = (SELECT max(sal) FROM emp WHERE job = e1.job)
  4. GRANT SELECT ON emp1 TO PUBLIC
  5. UPDATE emp1 SET hiredate=to_date('03-APR-99/13:15:00', 'dd-mon-yy/hh24:mi:ss')
    WHERE ename = 'SCOTT'
  6. SELECT * FROM <user_name>.emp1
  7. CREATE TABLE losowe (liczba NUMBER(3) PRIMARY KEY);
  8. INSERT INTO losowe (liczba) VALUES &podaj_3_cyfrowa_liczbe;
  9. SELECT count(*), M.liczba
    FROM losowe L, losowe M
    WHERE L.liczba <= M.liczba
    GROUP BY M.liczba;
  10. CONNECT scott/tiger
    SELECT * FROM emp WHERE ename = user;

Zadania na kolokwium z modelowania

grupa I

W warszawskich pubach podają piwo jednego lub kilku gatunków. To samo piwo może mieć różne ceny w różnych pubach. Bywalcy pubów odwiedzają różne puby i kosztują różnych piw. Każdy pije co najmniej jeden gatunek piwa i odwiedza co najmniej jeden pub.

Stwórz w notacji ErWina diagram związków encji oraz zaprojektuj znormalizowaną bazę danych (III postać normalna) umożliwiającą realizację różnych zapytań w zakresie powiązanych między sobą informacji o pubach, bywalcach, piwach i ich cenach.

Przedstaw dwie kolejne fazy tworzenia diagramu. Zaznacz rodzaje związków i ich liczebność.

grupa II

W warszawskich biurach podróży można wykupić przejazdy na różne trasy. Ta sama trasa może różnie kosztować w różnych biurach. Każdy zarejestrowany klient wykupił co najmniej jedną trasę w co najmniej jednym biurze.

Stwórz w notacji ErWina diagram związków encji oraz zaprojektuj znormalizowaną bazę danych (III postać normalna) umożliwiającą realizację różnych zapytań w zakresie powiązanych między sobą informacji o biurach, klientach, trasach i ich cenach.

Przedstaw dwie kolejne fazy tworzenia diagramu. Zaznacz rodzaje związków i ich liczebność

 

Projekt zaliczeniowy (OBOWIĄZKOWY)

Biuro Nieruchomości BAJKA

Biuro Nieruchomości BAJKA zajmuje się przyjmowaniem ofert sprzedaży mieszkań. Udziela klientom zainteresowanym kupnem mieszkania informacji o dostępnych ofertach sprzedaży, np. o metrażu, liczbie pokoi, które piętro, cena, data ważności oferty, lokalizacja. Biuro umawia swojego pośrednika z klientami na oględziny mieszkań. Jedną z reguł jest to, że pośrednik nie może być umówiony w tym samym czasie na oględziny dwóch różnych mieszkań. Biuro zajmuje się rejestrowaniem zawartych przy swoim pośrednictwie transakcji kupna-sprzedaży.

Stwórz model związków encji w notacji ErWina dla Biura Nieruchomości BAJKA.

Utwórz odpowiednie tabele w Oracle’u. Pamiętaj o zdefiniowaniu kluczy, dla których Oracle generuje automatyczne indeksy. Do generowania jednoznacznych identyfikatorów zastosuj sekwencje. Wprowadź przykładowe dane.

Napisz pakiet złożony z co najmniej dwóch procedur służących do obsługi Biura, w tym jednej procedury działającej na kursorze. Przykładowe procedury:

ZAREJESTROWANIE KLIENTA ZAINTERESOWANEGO KUPNEM MIESZKANIA

ZAREJESTROWANIE KLIENTA ZAINTERESOWANEGO SPRZEDAŻĄ MIESZKANIA

ZGŁOSZENIE MIESZKANIA DO SPRZEDAŻY

WYBÓR OFERTY WEDŁUG ZADANYCH KRYTERIÓW

WYŚWIETLENIE TEKSTÓW AKTUALNYCH OFERT (kursor)

Napisz triger (wyzwalacz) dla operacji UMÓWIENIE Z KLIENTEM NA OGLĘDZINY MIESZKANIA, który przy rejestrowaniu umówienia (operacja INSERT) zapobiega umówieniu pośrednika już umówionego o tej samej porze na inne oględziny.

Praca powinna być opracowana na piśmie oraz w Oracle’u. Może być wykonywana w grupach dwuosobowych. Procedury i triger powinny działać. Warunkiem zaliczenia pracy jest jej prezentacja na zajęciach.

ZADANIA TESTOWE: patrz lech.pjwstk.waw.pl/bd2