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

Kod materiałów bd_2_02

2

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;

Kod materiałów bd_2_02

3

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).

Kod materiałów bd_2_02

4

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

[e-mail]

. 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)