POLITECHNIKA ŚWIĘTOKRZYSKA
KATEDRA SYSTEMÓW STEROWANIA
I ZARZĄDZANIA
BAZY DANYCH II
Laboratorium 2
(Czas trwania 2x45 minut)
PL/SQL
I WYMAGANIA
Student przystępując do zajęć powinien mieć opanowany materiał na temat:
• Języka SQL
• Podstaw języka PL/SQL
II PRZEBIEG ĆWICZENIA
1.
Opis problemu
Dana jest następująca baza danych
DROP TABLE Place;
DROP TABLE Etaty;
DROP TABLE Osoby;
DROP TABLE Stanowiska;
CREATE TABLE Osoby(
id_os NUMBER(5),
Imie VARCHAR2(15) CONSTRAINT Os_imie_nn NOT NULL,
Nazwisko VARCHAR2(20) CONSTRAINT Os_nazw_nn NOT NULL,
Data_ur DATE NOT null,
Plec VARCHAR2(1) NOT null,
id_Kierownika NUMBER(5),
CONSTRAINT Os_id_pk PRIMARY KEY (id_os) ,
CONSTRAINT Os_Plec CHECK(Plec IN ('K','M')),
CONSTRAINT Os_nazw_Up CHECK( Nazwisko=InitCap(nazwisko)), CONSTRAINT Os_Imie_Up CHECK( Imie=InitCap(imie)));
CREATE TABLE Stanowiska(
id_st NUMBER(2),
Nazwa_st VARCHAR2(20) CONSTRAINT ST_nazw_nn NOT NULL , Min_placa NUMBER(7,2) CONSTRAINT St_min_pl NOT NULL
CHECK(Min_placa>100),
CONSTRAINT St_id_pk PRIMARY KEY (id_st),
CONSTRAINT St_Naz UNIQUE (Nazwa_st));
CREATE TABLE Etaty(
id_e NUMBER(5),
id_os NUMBER(5) CONSTRAINT E_id_os_nn NOT NULL,
id_st NUMBER(2) CONSTRAINT E_id_st_nn NOT NULL,
data_zat DATE CONSTRAINT E_data_zat_nn NOT NULL,
data_zw DATE ,
CONSTRAINT E_id_pk PRIMARY KEY (id_e),
CONSTRAINT E_idos_fk FOREIGN KEY (id_os)
REFERENCES Osoby ON DELETE CASCADE,
CONSTRAINT E_idst_fk FOREIGN KEY (id_st)
REFERENCES Stanowiska ON DELETE CASCADE,
CONSTRAINT E_data CHECK(data_zat<data_zw));
CREATE TABLE Place(
id_os NUMBER(5) NOT NULL,
data DATE NOT null,
za_miesiac VARCHAR2(2) NOT null,
podstawa NUMBER(7,2) NOT NULL,
premia NUMBER(7,2),
CONSTRAINT pk_Place PRIMARY KEY (id_os,data),
CONSTRAINT fk_id_os FOREIGN KEY (id_os)
REFERENCES Osoby ON DELETE CASCADE,
CONSTRAINT c_pods CHECK (podstawa>0),
CONSTRAINT c_prem CHECK (podstawa>=0));
ALTER TABLE osoby ADD CONSTRAINT id_kie
FOREIGN KEY (id_kierownika) REFERENCES Osoby ON DELETE SET null; DROP SEQUENCE Seq_osoby;
DROP SEQUENCE Seq_Etaty;
DROP SEQUENCE Seq_Stanowiska;
CREATE SEQUENCE Seq_Osoby INCREMENT BY 1 START WITH 100 NOMAXVALUE; CREATE SEQUENCE Seq_Etaty INCREMENT BY 1
START WITH 100 NOMAXVALUE;
CREATE SEQUENCE Seq_Stanowiska INCREMENT BY 1 START WITH 1
NOMAXVALUE;
2
2.
Zadania do wykonania
Napisz pakiet który będzie zawierał poniŜsze funkcje i procedury.
1. PROCEDURE która będzie dodawać stanowisko jako parametry wywołania podajemy nazwę i min i max place
2. PROCEDURE dodająca osobę do tabeli osoby jako parametry wywołania podajemy Imię, Nazwisko, Data_ur (numer pracownika sami wyznaczamy) 3. Funkcje która zwróci imię i nazwisko kierownika podanej osoby. Jako parametr wywołania podajemy ID_Os. Sprawdzić czy jest kierownik czy nie ma i czy jest dana osoba czy tez nie. np "Nie ma osoby" lub "Nie ma kierownika" bądź tez Jan Kowalski;
4. Napisz Funkcje która zwróci podatek jaki dana osoba ma zapłacić za dany rok.
40%*rocz_zar gdy rocz_zar>85000
30%*rocz_zar gdy 50000>=rocz_zar>46000
19%*rocz_zar w pozostał przypadkach
kwota wolna od podatku 3500 (0% podatku)
5. PROCEDURE która wyświetli raport o wszystkich pracownikach którzy pracowali w danym roku, jako parametry wywołania podajemy rok.
6. PROCEDURE która wyświetlić kto pracował na podanym stanowisku w danym roku gdzie rok jeśli nie podano ustawić na wartość domyślna 2012.
7. PROCEDURE która wyświetli raport z zarobków danych osób z podzieleniem na lata i na końcu wstawi końcowe saldo zarobionych pieniędzy w firmie Raport z plac dnia 20-11-2006
Imię Nazwisko Data Urodzenia
Jan
Kowaslki 10-11-1970
Place za rok 2010
Data
Podstawa Premia
Razem
01-10-2005 1800
100
1900
...
Suma za rok 2005: 10556
...
Suma za wszystkie lata : 263563
Imię
Nazwisko Data
Urodzenia
8. PROCEDURĘ która wyświetli składy zespołów dla danego kierownika. Kierownika przekazujemy jako parametr wywołania podajemy imię i nazwisko.
9. PROCEDURĘ która będzie dodawała etat dla podanej osoby. W procedurze naleŜy sprawdzić czy dany pracownik ma ukończone 18 lat i czy nie przekracza pełnego etatu w naszej firmie.
10. PROCEDURĘ która wyświetli pełne informacje o pracownikach na podanym stanowisku (kto od kiedy pracuje ile zarabia itd. MoŜna wykorzystać funkcje z następnego zadania).
3
11. FUNKCJĘ która zwróci ile dana osoba zarobiła w danym roku.
Uwaga powyŜsze procedury i funkcje naleŜy zademonstrować w przykładowych programach
III SPRAWOZDANIE Z ĆWICZENIA
Sprawozdanie z ćwiczeń powinno zawierać:
• Dane zespołu wykonującego ćwiczenie (nazwiska, imiona, Grupa dziekańska, data i godzina wykonania ćwiczenia).
• Opisane procedury i funkcje.
• Wyniki zadziałania procedur.
Sprawozdanie powinno być wysłane w ciągu tygodnia na adres j.wikarek@tu.kielce.pl. Jako temat wiadomości wpisać: bd2_02_xxxx (gdzie xxxx to numer grupy dziekańskiej np. 211A).
Sprawozdanie powinno być przysłane jako spakowane archiwum zip. Plik powinien posiadać nazwę: bd2_02_xxxx.zip (gdzie xxxx to numer grupy dziekańskiej np. 211A) 4