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

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

Kod materiałów bd_2_02

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

Kod materiałów bd_2_02

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