POLITECHNIKA CZĘSTOCHOWSKA

BAZY DANYCH II

PROJEKT: Sieć kablowa

WYKONAŁ:

Marcin Karpeta

Gr V

1. Cel projektu

Celem projektu jest stworzenie bazy klientów telewizji kablowej świadczącej również usługi dostępu do Internetu. Baza będzie zawierać informacje o klientach oraz o pakietach programów, pakietach internetowych dostępnych w ofercie Baza zawiera również tabele opłat poszczególnych klientów.

Baza posiada funkcję dająca informacje o ilości abonentów na danej ulicy lub w danym budynku, informacje o ilości użytkowników poszczególnych pakietów Informacje o tym, kto zalega z opłatami. Dzięki tym informacja można w łatwy i prosty sposób kirowa siecią kablowa oraz rozwijać ofertę o nowe pozycje handlowe

2. Tabele

oplaty
ID_KLIENTA	NUMBER(5)
ABONAMENT	NUMBER(5,2)
ZALEGLOŚCI	NUMBER(6,2)
STAN_OPLAT	NUMBER(6,2)

klient
ID_KLIENTA	NUMBER(5)
IMIE	VARCHAR2(20)
NAZWISKO	VARCHAR2(30)
ULICA	VARCHAR2(30)
NR_BUDYNKU	NUMBER(3)
NR_MIESZKANIA	NUMBER(3)
KOD_POCZTOWY	VARCHAR2(6)
MIASTO	VARCHAR2(25)
DATA_PODPISANIA_UMOWY	DATE
PAKIETY_PROGRAMOW	NUMBER(3)
PAKIETY_INTERNETOWE	NUMBER(3)

pakiety_programow
ID_PAKIETU	NUMBER(3)
NAZWA_PAKIETU	VARCHAR2(15)
ILOSC_PROGRAMOW	NUMBER(2)
CENA_PAKIETU	NUMBER(5,2)

Nazwa
ID_PAKIETU	NUMBER(3)
NAZWA_PAKIETU	VARCHAR2(15)
PREDKOSC_TRANSMISJI	NUMBER(4)
MAX_TRANSFER	NUMBER(3)
LICZBA_KONT_EMAIL	NUMBER(1)
STRONA_WWW	VARCHAR2(3)
ZEWNETRZNE_IP	VARCHAR2(3)
CENA_PAKIET	NUMBER(5,2)

programy
ID_PROGRAMU	NUMBER(3)
NAZWA_PROGRAMU	VARCHAR2(10)
JEZYK	VARCHAR2(10)
CZESTOTLIWOSC	VARCHAR2(3)
DZWIEK	VARCHAR2(6)

Tabela programy - przechowuje informacje o programach nazwie języku częstotliwości, dźwięku

programy
ID_PROGRAMU	NUMBER(3)
NAZWA_PROGRAMU	VARCHAR2(10)
JEZYK	VARCHAR2(10)
CZESTOTLIWOSC	VARCHAR2(3)
DZWIEK	VARCHAR2(6)

create table programy

(

id_programu number(3) primary key,

nazwa_programu varchar2(10) not null ,

jezyk varchar2(10) not null constraint jezyk check(jezyk like initcap(jezyk)),

czestotliwosc varchar2(3) unique constraint czestotliwosc check (czestotliwosc like upper(czestotliwosc)),

dzwiek varchar2(6) constraint dzwiek check (dzwiek in('STEREO','MONO'))

);

Wstawianie danych do tabeli

insert into programy values(nr_programu.nextval,'TVP1','Polski',upper('s01'),upper('STEREO'));

insert into programy values(nr_programu.nextval,'TVP2','Polski',upper('s02'),upper('STEREO'));

insert into programy values(nr_programu.nextval,'TVP3','Polski',upper('s03'),upper('MONO'));

Dane z tabeli programy

ID_PROGRAMU	NAZWA_PROG	JEZYK	CZE	DZWIEK
0	TVP1	Polski	S01	STEREO
1	TVP2	Polski	S02	STEREO
2	TVP3	Polski	S03	MONO

Tabela pakiety_programow - zawiera informację o pakietach programów dostępnych w ofercie telewizji kablowej. Tabela zawiera informacje o nazwie pakietu, ilości programów w nim dostępnych oraz o cenie danego pakietu.

pakiety_programow
ID_PAKIETU	NUMBER(3)
NAZWA_PAKIETU	VARCHAR2(15)
ILOSC_PROGRAMOW	NUMBER(2)
CENA_PAKIETU	NUMBER(5,2)

create table pakiety_programow

(

id_pakietu number(3) primary key,

nazwa_pakietu varchar2(15)not null,

ilosc_programow number(2) not null,

cena_pakietu number(5,2) constraint cena_pakietu check(cena_pakietu>=0.0)

);

Wstawianie danych do tabeli

insert into pakiety_programow values(0,'BRAK',0,0.00);

insert into pakiety_programow values(1,'Standard',5,25.50);

insert into pakiety_programow values(2,'Srebrny',10,34.99);

insert into pakiety_programow values(3,'Zloty',15,39.99);

insert into pakiety_programow values(4,'Platynowy',25,59.99);

Dane z tabeli

ID_PAKIETU	NAZWA_PAKIETU	ILOSC_PROGRAMOW	CENA_PAKIETU
0	BRAK	0	0
1	Standard	5	25,5
2	Srebrny	10	34,99
3	Zloty	15	39,99
4	Platynowy	25	59,99

Tabela pakiety_internetowe - zawiera informacje o pakietach usług internetowych dostępnych w ofercie TV kablowej. Tabela zawiera informacje o nazwie pakietu,prędkości transmisji w Kb, maksymalnym transferze po przekroczeniu, którego naliczana jest dodatkowa oplata, liczba kont email, które otrzymuje użytkownik, informacje o koncie, na którym może utworzyć stronę www, informacje czy przyznano mu zewnętrzne IP, cena pakietu.

Nazwa	Typ
ID_PAKIETU	NUMBER(3)
NAZWA_PAKIETU	VARCHAR2(15)
PREDKOSC_TRANSMISJI	NUMBER(4)
MAX_TRANSFER	NUMBER(3)
LICZBA_KONT_EMAIL	NUMBER(1)
STRONA_WWW	VARCHAR2(3)
ZEWNETRZNE_IP	VARCHAR2(3)
CENA_PAKIET	NUMBER(5,2)

create table pakiety_internetowe

(

id_pakietu number(3) primary key,

nazwa_pakietu varchar2(15) not null unique,

predkosc_transmisji number(4) not null,

max_transfer number(3) not null,

liczba_kont_email number(1),

strona_www varchar2(3) constraint strona_www check

(strona_www in (upper('tak'),upper('nie'))),

zewnetrzne_ip varchar2(3)constraint zewnetrzne_ip check (zewnetrzne_ip in (upper('tak'),upper('nie'))),

cena_pakiet number(5,2) constraint cena_pakiet check (cena_pakiet>=0.00)

);

Wstawianie danych do tabeli

insert into pakiety_internetowe values(0,'BRAK',0,0,0,upper('nie'),upper('nie'),0.00);

insert into pakiety_internetowe values(1,'Net',256,5,1,upper('nie'),upper('nie'),37.50);

insert into pakiety_internetowe values(2,'Net+',512,10,2,upper('tak'),upper('nie'),59.99);

insert into pakiety_internetowe values(3,'SuperNet',1024,25,5,upper('tak'),upper('nie'),89.99);

insert into pakiety_internetowe values(4,'NetVip',2048,100,5,upper('tak'),upper('tak'),119.99);

Dane z tabeli

ID_PAKIETU	NAZWA_PAKIETU	PREDKOSC_TRANSMISJI	MAX_TRANSFER	LICZBA_KONT_EMAIL	STR	ZEW	CENA_PAKIET
1	Net	256	5	1	NIE	NIE	37,5
2	Net+	512	10	2	TAK	NIE	59,99
3	SuperNet	1024	25	5	TAK	NIE	89,99

Tabela klient - zawiera informacje o klientach TV kablowej. Tabela zawiera informacje imieniu, nazwisku, adresie klienta, dacie podpisania umowy, wykupionych pakietach telewizyjnych i internetowych.

klient
ID_KLIENTA	NUMBER(5)
IMIE	VARCHAR2(20)
NAZWISKO	VARCHAR2(30)
ULICA	VARCHAR2(30)
NR_BUDYNKU	NUMBER(3)
NR_MIESZKANIA	NUMBER(3)
KOD_POCZTOWY	VARCHAR2(6)
MIASTO	VARCHAR2(25)
DATA_PODPISANIA_UMOWY	DATE
PAKIETY_PROGRAMOW	NUMBER(3)
PAKIETY_INTERNETOWE	NUMBER(3)

create table klient

(

id_klienta number(5) primary key,

imie varchar2(20) not null constraint imie check (imie in initcap(imie)) ,

nazwisko varchar2(30) not null constraint nazwisko check (nazwisko in initcap(nazwisko)),

ulica varchar2(30) not null constraint ulica check (ulica in initcap(ulica)),

nr_budynku number(3) not null,

nr_mieszkania number(3) not null,

kod_pocztowy varchar2(6) not null,

miasto varchar2(25) constraint miasto check (miasto in initcap(miasto)),

data_podpisania_umowy date default sysdate,

pakiety_programow number(3),

FOREIGN KEY (pakiety_programow) REFERENCES pakiety_programow,

pakiety_internetowe number(3),

FOREIGN KEY (pakiety_internetowe) REFERENCES pakiety_internetowe

);

Wstawianie danych do tabeli

insert into klient values(nr_klienta.nextval,initcap('Marcin'),initcap('Karpeta'),initcap('spoldzielcza'),26,12,'26-110',initcap('Skarzysko'),sysdate-60,1,1);

insert into klient values(nr_klienta.nextval,initcap('Zofia'),initcap('abramowicz'),initcap('lotnicza'),6,13,'27-120',initcap('Skarzysko'),sysdate-90,1,2);

insert into klient values(nr_klienta.nextval,initcap('Marian'),initcap('pazdzioch'),initcap('gornicza'),13,16,'26-110',initcap('Skarzysko'),sysdate-76,2,1);

Dane z tabeli

ID_KLIENTA	IMIE	NAZWISKO	ULICA	NR_BUDYNKU	NR_MIESZKANIA	KOD_PO	MIASTO	DATA_POD	PAKIETY_PROGRAMOW	PAKIETY_INTERNETOWE
0	Marcin	Karpeta	Spoldzielcza	26	12	26-110	Skarzysko	05/03/20	1	1
1	Zofia	Abramowicz	Lotnicza	6	13	27-120	Skarzysko	05/02/18	1	2
2	Marian	Pazdzioch	Gornicza	13	16	26-110	Skarzysko	05/03/04	2	1

Tabela opłaty - zawiera informacje o abonamencie klienta, zaległościach, stanie opłat

oplaty
ID_KLIENTA	NUMBER(5)
ABONAMENT	NUMBER(5,2)
ZALEGLOŚCI	NUMBER(6,2)
STAN_OPLAT	NUMBER(6,2)

create table oplaty

(

id_klienta number(5) not null primary key,

FOREIGN KEY (id_klienta) REFERENCES klient,

abonament number(5,2) not null constraint abonament check (abonament>=0),

zaleglości number(6,2) default 0,

stan_oplat number(6,2) default 0

);

Wstawianie danych do tabeli - odbywa się poprzez wyzwalacz w trakcie dopisywania klientów. Można jedynie aktualizować tabele. Ustawienie zaległości i stanu opłat odbywa się poprzez oprogramowanie, które regularnie sprawdza te dane i aktualizuje tabele

Przykładowe dane z tabeli

ID_KLIENTA	ABONAMENT	ZALEGLOŚCI	STAN_OPLAT
0	63	-250	0
1	85,49	0	85,49
2	72,49	0	72,49
3	94,98	0	0

3. Dodatkowe elementy bazy

1. wyzwalacze

Wyzwalacz wywoływany w po dopisaniu nowego klienta do bazy Oblicza abonament dla nowego klienta i zapisuje go do tabeli opłaty dla tego klienta

CREATE or replace TRIGGER dodaj_klienta

after INSERT ON klient

FOR EACH ROW

declare

tv number;

net number;

BEGIN

select cena_pakietu into tv from pakiety_programow where id_pakietu=:new.pakiety_programow;

select cena_pakiet into net from pakiety_internetowe where id_pakietu=:new.pakiety_internetowe;

insert into oplaty values(:new.id_klienta,tv+net,0,0);

END;

Wyzwalacz wywoływany po dokonaniu modyfikacji wykupionych usług dla klienta. Wyzwalacz ustawia nowa wartość abonamentu

CREATE or replace TRIGGER akt_dane_klienta

after update ON klient

FOR EACH ROW

declare

tv number;

net number;

BEGIN

select cena_pakietu into tv from pakiety_programow where id_pakietu=:new.pakiety_programow;

select cena_pakiet into net from pakiety_internetowe where id_pakietu=:new.pakiety_internetowe;

update oplaty set abonament=(tv+net) where ID_KLIENTA=:old.ID_KLIENTA;

END;

Wyzwalacz wywoływany przed usunięciem klienta z bazy. Kasuje rekord z tabeli opłaty powiązany z kasowanym klientem

CREATE or replace TRIGGER usun_klienta

before DELETE ON klient

FOR EACH ROW

BEGIN

delete oplaty where id_klienta=:old.id_klienta;

END;

2. sekwencje

Sekwencja tworząca kolejne nr klienta w trakcie dopisywania klientów do tabeli

create sequence nr_klienta

INCREMENT by 1

START with 0

MINVALUE 0

MAXVALUE 99999

Sekwencja tworząca id dla dopisywanych programów

create sequence nr_programu

INCREMENT by 1

START with 0

MINVALUE 0

MAXVALUE 99

3. perspektywy

Perspektywy pokazujące jakie programy znajdują się w poszczególnych pakietach

Pakiet standardowy

create view pakiet_standard(program,jezyk,czestotliwosc,dzwiek) as

select nazwa_programu,jezyk,czestotliwosc,dzwiek from programy p,pakiety_programow pp where p.id_programu <= pp.ilosc_programow and pp.id_pakietu like 1 ;

Pakiet srebrny

create view pakiet_srebrny(program,jezyk,czestotliwosc,dzwiek) as

select nazwa_programu,jezyk,czestotliwosc,dzwiek from programy p,pakiety_programow pp where p.id_programu <= pp.ilosc_programow and pp.id_pakietu like 2 ;

Pakiet złoty

create view pakiet_zloty(program,jezyk,czestotliwosc,dzwiek) as

select nazwa_programu,jezyk,czestotliwosc,dzwiek from programy p,pakiety_programow pp where p.id_programu <= pp.ilosc_programow and pp.id_pakietu like 3 ;

Pakiet platynowy

create view pakiet_platynowy(program,jezyk,czestotliwosc,dzwiek) as

select nazwa_programu,jezyk,czestotliwosc,dzwiek from programy p,pakiety_programow pp where p.id_programu <= pp.ilosc_programow and pp.id_pakietu like 4 ;

Perspektywa pokazująca pełna informacje o kliencie

create view info_klient (id_klienta,imie,nazwisko,ulica,nr_budynku,nr_mieszkania, kod_pocztowy,miasto,data_umowy,pakiety_tv, pakiety_net,abonament,zaleglosci,stan_oplat)as

select * from klient natural join oplaty;

Perspektywa pokazująca informacje o klientach posiadających zaległości

create view zaleglosci_klient (Id_klienta,imie,nazwisko,zaleglosc)as

select k.id_klienta,imie, nazwisko,zaleglości from klient k, oplaty o where k.id_klienta=o.id_klienta and o.ZALEGLOŚCI>0;

4. funkcje

Funkcja wyświetlająca liczbę klientów

create or replace function liczba_klientow

return number

as

ilosc number;

begin

select count(*) into ilosc from klient;

return ilosc;

end;

Funkcja wyświetlająca liczbę klientów mieszkających na danej ulicy

create or replace function kl_ulica (ul varchar2)

return number

as

ilosc number;

begin

select count(*) into ilosc from klient where ulica=initcap(ul);

return ilosc;

end;

Funkcja zwracająca ilość klientów zamieszkałych w tym samym budynku

create or replace function kl_budynek (bd number, ul varchar2)

return number

as

ilosc number;

begin

select count(*) into ilosc from klient where NR_BUDYNKU=bd and ulica like 'initcap(ulica)';

return ilosc;

end;

Funkcja zwracająca liczbę użytkowników danego pakietu TV

create or replace function kl_tv (tv number)

return number

as

ilosc number;

begin

select count(*) into ilosc from klient where pakiety_programow=tv;

return ilosc;

end;

Funkcja zwracająca liczbę użytkowników danego pakietu internetowego

create or replace function kl_net (net number)

return number

as

ilosc number;

begin

select count(*) into ilosc from klient where pakiety_internetowe=net;

return ilosc;

end;

5. procedury

Procedura dodająca pakiet TV

create or replace procedure dodaj_pakiet_tv(id_pakietu number,nazwa_pakietu varchar2,ilosc_programow number,cena_pakietu number)

as

begin

insert into pakiety_programow values(id_pakietu,nazwa_pakietu,ilosc_programow,cena_pakietu);

end;

Procedura usuwająca pakiet TV

create or replace procedure usun_pakiet_tv (id_pak number)

as

begin

delete from pakiety_programow where ID_PAKIETU=id_pak;

end;

Procedura dodająca pakiet internetowy

create or replace procedure dodaj_pakiet_net(id_pakietu number,nazwa_pakietu varchar2,predkosc_transmisji number,max_transfer number,liczba_kont_email number,strona_www varchar2,zewnetrzne_ip varchar2,cena_pakietu number)

as

begin

insert into pakiety_internetowe values(id_pakietu,nazwa_pakietu,predkosc_transmisji,max_transfer,liczba_kont_email, upper('zewnetrzne_ip'),upper('strona_www'),cena_pakietu);

end;

Procedura usuwająca pakiet internetowy

create or replace procedure usun_pakiet_net (id_pak number)

as

begin

delete from pakiety_programow where ID_PAKIETU=id_pak;

end;

4. Zapytania

Pokaz liczbę klientów, sumę ich abonamentów, średni abonament, maksymalny abonament oraz minimalny abonament wśród wszystkich klientów TV kablowej

select count(*) as liczba_klientow,sum(abonament) as suma_abonamentow, avg(abonament)as sredni_aboanement, max(abonament)as maksymaly_abonament,min(abonament)as minimalny_abonament from oplaty;

Pokaż dane klientów którzy są abonentami TV kablowej dłużej niż 3 miesiące i pokaż ich od najdłuższego stażu do najkrótszego

select id_klienta,imie,nazwisko, trunc(MONTHS_BETWEEN(sysdate, DATA_PODPISANIA_UMOWY))as czas_bycia_klientem from klient where MONTHS_BETWEEN(sysdate, DATA_PODPISANIA_UMOWY)>3 order by 4 desc;

Pokaż programy które można oglądać jedynie w pakiecie najdroższym

select * from programy where id_programu>=(select ILOSC_PROGRAMOW from pakiety_programow where ID_PAKIETU=3);

Pokaz osoby które mają wykupiony najwyższy pakiet programów i najwyższy pakiet internetowy

select * from klient where pakiety_internetowe=4 and pakiety_programow=4;

Pokaż osoby które maja wykupione jedynie pakiety programów

select * from klient where pakiety_programow >0 and pakiety_internetowe =0;

11

---