ADMINISTRARE BLOCURI LOCUINTE

ACADEMIA DE STUDII ECONOMICE, FACULTATEA DE CIBERNETICA, STATISTICA SI INFORMATICA ECONOMICA, MASTER BAZE DE DATE SUPORT PENTRU AFACERI, BUCURESTI, 2007

ADMINISTRARE    BLOCURI LOCUINTE

DESCRIERE

Se doreste o aplicatie care sa gestioneze toate datele referitoare la administratia blocurilor de locuinte. Aplicatia va gestiona date cu privire la:

Administratori : date personale referitoare la administratorii asociatiilor de locatari.

Detalii legate de structura administrativa a asociatiei cum ar fi :blocurile ce intra in componenta, adresa, numarul de apartamente existente in fiecare bloc, numarul de scari, si administratorul asociatiei.

Proprietari : date personale referitoare la proprietarii unor apartamente.

Apartamente : blocul caruia ii apartine, scara, etajul, numarul de camere, numarul de locatari, proprietarul apartamentului, si asociatia din care face parte.

Intretinere : luna si anul in care se percepe intretinerea, numarul blocului, respectiv ala apartamentului, suma de plata, si proprietarul

Chitante eliberate : data intocmirii chitantei, apartamentul pentru care se inregistreaza plata, delegatul care face plata, suma achitata, luna pentru care se achita, si respectiv anul.

SCOP

Aplicatia vine in ajutorul personalului insarcinat cu supravegherea atat din punct de vedere administrativ, cat si financiar, a administratiilor de locatari a a complexelor de locuinte.

1. DEFINIREA SCHEMEI BAZEI DE DATE

ADMINISTRATOR

ASOCIATIE_LOCATARI

PROPRIETAR

APARTAMENT

INTRETINERE

CHITANTA

A. DEFINIREA BAZEI DE DATE

create table ADMINISTRATOR

cnp_admin char constraint PKey_admin primary key

nume varchar2 not null

adresa varchar2

telefon char

data_n date

salariu number

create table ASOCIATIE_LOCATARI

cod_asoc number constraint PKey_asoc primary key

nr_bloc char not null

adresa varchar2

nrapartamente number

nretaje number

nrscari char

cnp_admin char

CONSTRAINT FKAdministrator FOREIGN KEY cnp_admin REFERENCES ADMINISTRATOR cnp_admin

create table PROPRIETAR

cnp_proprietar char constraint PKey_prop primary key

nume varchar2 not null

adresa varchar2

telefon char

data_n date

create table APARTAMENT

cod_ap char constraint PKey_ap primary key

nr_bloc char not null

nr_ap char

scara char

etaj number

nr_camere number

nr_locatari number

cnp_proprietar char

cod_asoc number

CONSTRAINT FKProprietar FOREIGN KEY cnp_proprietar REFERENCES proprietar cnp_proprietar

CONSTRAINT FKAsociatie FOREIGN KEY cod_asoc REFERENCES ASOCIATIE_LOCATARI cod_asoc

);

create table INTRETINERE

cod_intretinere char constraint PKey_intr primary key

luna varchar CONSTRAINT FLUNA_CK check luna in 'ianuarie' 'februarie' 'martie' 'aprilie' 'mai' 'iunie' 'iulie' 'august' 'septembrie' 'octombrie' 'noiembrie' 'decembrie'

an number

nr_bloc char

nr_ap number

suma_plata number

cod_ap char

CONSTRAINT FKcodapart FOREIGN KEY cod_ap REFERENCES APARTAMENT cod_ap

CREATE table CHITANTIER

nr_chit number CONSTRAINT PKey_chitantier primary key

data_chit date

cod_ap char

cnp_delegat char

suma number

luna_platita varchar CONSTRAINT FLUNAP_CK check luna_platita in 'ianuarie' 'februarie' 'martie' 'aprilie' 'mai' 'iunie' 'iulie' 'august' 'septembrie' 'octombrie' 'noiembrie' 'decembrie'

an_platit number

CONSTRAINT FKcod_ap FOREIGN KEY cod_ap REFERENCES apartament cod_ap

--INSERARE:

insert into ADMINISTRATOR

values 'Matei Eliza' 'Iuliu Maniu nr. 69, sector 6' to_date 'oct 12,83' 'mon dd,yy'

insert into ADMINISTRATOR

values 'Mihai Marina' 'rosia Montana nr. 2, sector 6' to_date 'aug 15,84' 'mon dd,yy'

insert into ASOCIATIE_LOCATARI

values 'A1' 'Rosia Montana nr 2, sector 6'

insert into ASOCIATIE_LOCATARI

values 'B1' 'Rosia Montana nr 5, sector 6'

insert into ASOCIATIE_LOCATARI

values 'B2' 'Rosia Montana nr 8, sector 6'

insert into ASOCIATIE_LOCATARI

values 'C1' 'Iuliu Maniu nr 13'

insert into ASOCIATIE_LOCATARI

values 'C2' 'Iuliu Maniu nr 13, sector 6'

insert into PROPRIETAR

values 'Matei Eliza' 'Iuliu Maniu nr. 69, sector 6' to_date 'oct 12,83' 'mon dd,yy'

insert into PROPRIETAR

values 'Ionescu Calin' 'rosia Montana nr. 2, sector 6' to_date 'aug 15,84' 'mon dd,yy'

insert into PROPRIETAR

values 'Nitu Stefan' 'Iuliu Maniu nr. 69, sector 6' to_date 'oct 22,77' 'mon dd,yy'

insert into PROPRIETAR

values 'Mihai Marina' 'rosia Montana nr. 2, sector 6' to_date 'jun 10,56' 'mon dd,yy'

insert into PROPRIETAR

values 'Dumitrescu Camelia' 'Iuliu Maniu nr. 69, sector 6' to_date 'jan 07,83' 'mon dd,yy'

insert into PROPRIETAR

values 'Dumitru Mihnea' 'rosia Montana nr. 2, sector 6' to_date 'aug 15,43' 'mon dd,yy'

insert into PROPRIETAR

values 'Ionescu Marius' 'Iuliu Maniu nr. 69, sector 6' to_date 'oct 09,55' 'mon dd,yy'

insert into PROPRIETAR

values 'Mirea Ana' 'rosia Montana nr. 2, sector 6' to_date 'aug 30,21' 'mon dd,yy'

insert into APARTAMENT

values '002A' 'A1' 'A'

insert into APARTAMENT

values '003A' 'B1' 'B'

insert into APARTAMENT

values '001B' 'B1' 'F'

insert into APARTAMENT

values '101A' 'A1' 'A'

insert into APARTAMENT

values '111A' 'A1' 'B'

insert into APARTAMENT

values '231C' 'C1' 'A'

insert into APARTAMENT

values '023A' 'C2' 'B'

insert into APARTAMENT

values '034D' 'B1' 'A'

insert into APARTAMENT

values '001A' 'A1' 'A'

alter table intretinere modify cnp_proprietar char

insert into INTRETINERE

VALUES '123AB' 'august' 'A1' '002A'

insert into INTRETINERE

VALUES '123AC' 'septembrie' 'B1' '002A'

insert into INTRETINERE

VALUES '234MN' 'septembrie' 'B1' '001B'

insert into INTRETINERE

VALUES '245TR' 'septembrie' 'A1' '101A'

insert into INTRETINERE

VALUES '897ML' 'septembrie' 'A1' '111A'

insert into INTRETINERE

VALUES '113MB' 'septembrie' 'C2' '231C'

insert into INTRETINERE

VALUES '678LP' 'septembrie' 'B1' '034D'

insert into INTRETINERE

VALUES '546JJ' 'septembrie' 'A1' '001A'

insert into CHITANTIER

values('1235',to_date('sep 12,07','mon dd,yy'), '002A','1234567894329','100','august','2007');

insert into CHITANTIER

values('1236',to_date('oct 15,07','mon dd,yy'), '002A','1234567894329','120','septembrie','2007');

insert into CHITANTIER

values('1237',to_date('oct 12,07','mon dd,yy'), '001B','1234567891234','300','septembrie','2007');

insert into CHITANTIER

values('1238',to_date('oct 21,07','mon dd,yy'), '101A','1234567894329','100','septembrie','2007');

insert into CHITANTIER

values('1210',to_date('oct 07,07','mon dd,yy'), '111A','1234567891232','150','septembrie','2007');

B. MODIFICARI IN STRUCTURA TABELELOR

Dorim sa schimbam numele tabelei Chitantier in Chitanta

alter table chitantier

rename to chitanta

desc chitanta

Dorim sa modificam proprietatile campului cnp_proprietar

alter table intretinere modify cnp_proprietar char

Adaugam o noua restrictie pe tabela apartament: nr de camere sa fie mai mic decat 4

alter table apartament

add CONSTRAINT check_Nr_camere check nr_camere> and nr_camere<

Dorim sa stergem din structura tabelei administrator coloana care nu este utilizata

alter table administrator

drop unused columns

C. ACTUALIZAREA DATELOR

Vrem sa modificam numarul de telefon al unui proprietar.

update proprietar

set telefon

where lower cnp_proprietar

Deoarece s-a spart o conducta de apa in bloc la etajul 2 al blocului xxx, cheltuielile de intretinere pentru apartamentele de la etajele superioare au crescut cu 50 RON pentru fiecare apartament in parte.

update INTRETINERE

set suma_plata suma_plata

where cod_ap in select cod_ap from APARTAMENT

where scara 'A' and nr_bloc 'A1' and etaj>

Asociatia de locatari pentru blocul A1 a inchiriat unei agentii imobiliare un loc amenajat la parter(care nu este apartament). Din cauza acestei investitii cheltuielile lunare vor fi mai mici iar salariul administratorului va creste cu 15%.

update ADMINISTRATOR

set salariu salariu salariu

where cnp_admin in select cnp_admin from ASOCIATIE_LOCATARI

where nr_bloc 'A1'

In urma vanzarii de catre un proprietar a apartamentului, se face modificarea datelor proprietarului;

update proprietar

set nume 'Popescu Anca'

where cnp_proprietar

Cream un nou camp pentru proprietar si il completam in functie de lista de cereri;

alter table proprietar

add categorie varchar

update proprietar

set categorie 'social 1'

where nume in 'Dumitrescu Camelia' 'Ionescu Calin'

update proprietar

set categorie 'social 2'

where nume 'Dumitru Mihnea'

update proprietar

set categorie 'social 3'

where nume 'Mirea Ana'

update proprietar

set categorie 'social 4'

where nume 'Matei Eliza'

Dorim sa stergem din evidenta proprietarul anterior

delete from proprietar

where nume 'Nitu Stefan'

--D. INTEROGAREA BAZEI DE DATE

--inner join pe tabelele proprietar si apartament in functie de codul proprietarului;

select p nume a

from proprietar p apartament a

where p cnp_proprietar a cnp_proprietar

-- sa se afiseze numele proprietarilor si datele despre apartamentele proprietarilor din blocurile

--care au prima litera a codului 'C';

select p nume a

from proprietar p apartament a

where p cnp_proprietar a cnp_proprietar and upper a nr_bloc like 'C%'

--sa se listeze pentru fiecare administrator cate blocuri are in administratie

select b nume count a nr_bloc Numar

from asociatie_locatari a administrator b

where b cnp_admin a cnp_admin

group by b nume

--sa se afiseze numele proprietarilor,datele apartamentelor pe care le au in posesie si sumele de plata la intretinere;

select p nume i suma_plata a

from proprietar p intretinere i apartament a

where p cnp_proprietar i cnp_proprietar and a cod_ap i cod_ap

--sa se scoata o evidenta cu toti proprietarii, in care sa se evidentieze cei care au platit

select p nume c nr_chit c suma

from proprietar p chitanta c apartament a

where p cnp_proprietar a cnp_proprietar

and a cod_ap c cod_ap

--sa se calculeze sumele finale de plata pentru cei care au depus cereri pentru ajutor social

select p nume i suma_plata

case

when lower p categorie 'social 1' then

when lower p categorie 'social 2' then

when lower p categorie 'social 3' then

when lower p categorie 'social 4' then

else

end i suma_plata i suma_plata Suma_calculata

from proprietar p intretinere i

where p cnp_proprietar i cnp_proprietar

--sa se calculeze reducerile care se fac pentru proprietarii care au depus cereri pentru

-- ajutor social

select p nume i suma_plata

decode lower p categorie 'social 1'

'social 2' 'social 3' 'social 4' Reducere

from proprietar p intretinere i

where p cnp_proprietar i cnp_proprietar

--sa se calculeze varstele proprietarilor pe baza datei de nastere

select nume data_n round MONTHS_BETWEEN data_n sysdate Varsta

from proprietar

--sa se scoata o evidenta cu privire la administratorii care au in administartie 2 sau 3

--blocuri

select a nume count c cod_asoc

from administrator a asociatie_locatari c

where a cnp_admin c cnp_admin

group by a nume

having count c cod_asoc

union

select a nume count c cod_asoc

from administrator a asociatie_locatari c

where a cnp_admin c cnp_admin

group by a nume

having count c cod_asoc

--sa se scoata o evidenta despre proprietarii si chitantele eliberate pentru acestia in urma ---platilor efectuate in

--pe luna octombrie , mai putin cele din perioada 06-09 octombrie 2007;

select p nume c data_chit

from proprietar p chitanta c apartament a

where a cnp_proprietar p cnp_proprietar

and a cod_ap c cod_ap

and data_chit>to_date 'oct 01,2007' 'month dd,YYYY'

minus

select p nume c data_chit

from proprietar p chitanta c apartament a

where a cnp_proprietar p cnp_proprietar

and a cod_ap c cod_ap and data_chit>to_date 'oct 06,2007' 'month dd,YYYY' and data_chit<to_date 'oct 09,2007' 'month dd,YYYY'

--sa se scoata o evidenta cu proprietarii care au efectuat mai putin de 4 plati

select p nume sum c suma as 'valoare' count c nr_chit Nr

from proprietar p chitanta c apartament a

where a cnp_proprietar p cnp_proprietar

and a cod_ap c cod_ap

group by p nume

having count c nr_chit)<=

select from chitanta

--sa se scoata o situatie cu privire la cei care au efectuat mai putin de 4 plati la intretinere,

--dar cu sume cuprinse intre 250 si 600 RON

select p nume sum c suma as 'valoare' count c nr_chit Nr

from proprietar p chitanta c apartament a

where a cnp_proprietar p cnp_proprietar

and a cod_ap c cod_ap

group by p nume

having count c nr_chit)<=

intersect

select p nume sum c suma as 'valoare' count c nr_chit Nr

from proprietar p chitanta c apartament a

where a cnp_proprietar p cnp_proprietar

and a cod_ap c cod_ap

group by p nume

having sum c suma between and

--sa se afle numele administratorului al asociatiei de locatari din blocul care are apartamentul cu codul 231C

select nume

from administrator

where cnp_admin select cnp_admin from asociatie_locatari where nr_bloc

select nr_bloc from apartament where cod_ap '231C'

--sa se scoata o situatie cu proprietarii de apartamente din blocurile caer incep cu litera C

select p nume a cod_ap

from proprietar p apartament a

where p cnp_proprietar a cnp_proprietar and substr nr_bloc 'C'

--sa se afle cate apartamente are in administratie fiecare administrator

select a nume sum b nrapartamente as 'NrAp'

from administrator a asociatie_locatari b

where a cnp_admin b cnp_admin

group by a nume

order by sum b nrapartamente desc

--sa se afle media incasarilor pe fiecare luna pt blocurile A1, B1

select luna round avg suma_plata as 'S'

from intretinere

where nr_bloc in 'A1' 'B1'

group by luna

--sa se scoata o situatie cu proprietarii care au platit, sumele totale si numarul de chitante eliberate pentru fiecare

select p nume sum c suma as 'valoare' count c nr_chit Nr

from proprietar p chitanta c apartament a

where a cnp_proprietar p cnp_proprietar

and a cod_ap c cod_ap

group by p nume

having count c nr_chit)<=

select from chitanta

--sa se scoata o situatie cu privire la restantieri

select i cod_intretinere i cnp_proprietar i suma_plata v Total i suma_plata v Total as Restanta

from intretinere i situatie_v v

where i cnp_proprietar v cnp_proprietar

--END SELECT

E GESTIUNEA ALTOR OBIECTE ALE BAZEI DE DATE

--Se creeaza un view cu date din cele doua tabele proprietar si apartament pe baza codului proprietarului;

create view propr_apart_v

as

select p nume p cnp_proprietar p telefon a nr_bloc a nr_ap a scara a etaj a nr_camere

from proprietar p apartament a

where p cnp_proprietar a cnp_proprietar

-- Se creeaza un view cu datele din proprietar si intretinere ;

create view plati_apart_v

as

select p nume p cnp_proprietar i nr_ap i cod_ap i suma_plata i luna

from proprietar p intretinere i

where p cnp_proprietar i cnp_proprietar

select from plati_apart_v

se creeaza un view cu datele dorite doar pe luna septembrie a anului curent;

create view rest_v

as select nr_chit data_chit cod_ap cnp_delegat suma luna_platita an_platit

from chitanta where luna_platita like 'sep%' and an_platit

--Se creeaza un view cu date din cele doua tabele proprietar si apartament pe baza codului proprietarului;

create view propr_apart_v

as

select p nume p cnp_proprietar p telefon a nr_bloc a nr_ap a scara a etaj a nr_camere

from proprietar p apartament a

where p cnp_proprietar a cnp_proprietar

-- Se creeaza un view cu datele din proprietar si intretinere ;

create view plati_apart_v

as

select p nume p cnp_proprietar i nr_ap i cod_ap i suma_plata i luna

from proprietar p intretinere i

where p cnp_proprietar i cnp_proprietar

select from plati_apart_v

select from rest_v

se creeaza un view cu datele dorite doar pe luna septembrie a anului curent;

create view rest_v

as select nr_chit data_chit cod_ap cnp_delegat suma luna_platita an_platit

from chitanta where luna_platita like 'sep%' and an_platit

select from rest_v

-- se creeaza un view cu datele din tabela intretinere si view-ul rest_v calculand si sumele platite de fiecare proprietar pe luna septembrie;

create view situatie_v as

select rest_v cnp_delegat sum rest_v suma Total

from rest_v

group by rest_v cnp_delegat

----calculam sumele restante pentru fiecare proprietar pe luna septembrie;

select i cod_intretinere i cnp_proprietar i suma_plata v Total i suma_plata v Total as Restanta

from intretinere i situatie_v v apartament a

where i cnp_proprietar v cnp_delegat

----calculam sumele restante pentru fiecare proprietar pe luna septembrie;

select i cod_intretinere i cnp_proprietar i suma_plata v Total i suma_plata v Total as Restanta

from intretinere i situatie_v v

where i cnp_proprietar v cnp_delegat

--cream INDEX pe baza campului nume din tabela proprietar;

create index numeprop_IDX on proprietar nume

--cream sinonim pt tabela chitanta

create synonym gestiune for chitanta

--secventa

create sequence seq_chitanta

start with increment by

maxvalue nocycle