FAKULTA INFORMATIKY, MASARYKOVA UNIVERSITA V BRNĚ Príklady k přednášce PV003 - Architektura relačních databází Milan Drášil, únor 2003 ZADÁNÍ PŘÍKLADU PRO PV003.....................................................................................................................1 PŘÍKLAD 1..............................................................................................................................................2 PŘÍKLAD 2..............................................................................................................................................3 PŘÍKLAD 3..............................................................................................................................................5 PŘÍKLAD 4...........................................................................................................................................................7 PŘÍKLAD 5.........................................................................................................................................................10 PŘÍKLAD 6.........................................................................................................................................................11 PŘÍKLAD 7.........................................................................................................................................................12 PŘÍKLAD 8............................................................................................................................................12 PŘÍKLAD 10...........................................................................................................................................12 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady Zadání příkladu pro PV003 1 1) Katastr nemovitostí vede údaje o vlastnictví pozemků (parcel) a budov. 2) Nemovitost může vlastnit "oprávněný subjekt" a to i částečně. 3) Oprávněný subjekt může vlastnit více nemovitostí. 4) Oprávněný subjekt je buď fyzická osoba, právnická osoba nebo společné jmění manželů. 5) Právnická osoba je identifikována IČOem, fyzická osoba je identifikována rodným číslem. 6) Rodné číslo má strukturu buď RRMMDDccc nebo RRMMDDcccc. Pokud je rodné číslo desetimístné, potom je dělitelné 11. 7) Parcela je identifikována kódem katastrálního území (státní číselník), typem parcely (stavební, nestavební), parcelním číslem, a podlomením. 8) Budova je identifikována kódem části obce (státní číselník) a číslem popisným, resp. evidenčním. 9) Obec se skládá z 1, nebo více částí obce. 10)Obec se skládá z 1, nebo více katastrálních území. 11)Části obce a katastrální území nejsou skladebné. 12) Vlastnické vztahy jsou vedeny na listech vlastnictví a to tak, že v části A jsou uvedeny oprávněné subjekty se podílem vlastnictví ke všem nemovitostem, které jsou uvedeny v části B. např: Okres: 3209 Praha - východ Obec: 078 Říčany Kat. území 01 Říčany u Prahy Číslo LV 365 A Vlastník (pořadí jméno ,název a adresa) Identifikátor Podíl 1 Josef Novák Podlesná 11, Brno 581205/3256 1/2 2 Pavel Novák Sady 15, Olomouc 351231/454 1/2 B Parcelní Výměra Část obce Druh číslo m2 číslo. Budovy 1452/2 4584 1452/3 592 13)Součet podílů na listu vlastnictví je 1. 14) Na listu vlastnictví je uvedena alespoň jedna nemovitost. 15) Nemovitost je uvedena právě na jednom listu vlastnictví. 1 Příklady jsou řešeny v rámci RDBMS ORACLE - pro jiné SQL databáze tato řešení nemusí platit. louka orná půda 1 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady PŘÍKLAD 1. Vytvořte konceptuálni datový model reprezentovaný E-R diagramem.D Řešení (jedno z možných): Okres 1,n Obce za okres 1,1 Část obce Obec >f- 1,1 1,n Obec má části 1,n 1,1 Obec se skládá z KU 0,n Budovy v obci Oprávněný subjekt Katastrální území Parcely v KU 0,n 1,1 Vlastní 0,n Budova 1,1 o,n List vlastnictví N^----------------H Budova je na listu vlastnictví 0,n 1,1 A1'1 Parcela 1,n Parcela je na listu vlastnictví -o<- 2 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady PŘÍKLAD 2. Navrhněte logický datový model reprezentovaný PDM diagramem (tj. tabulky a referenční integrita). Řešení (jedno z možných): P01_OKRES ID NUMBERffl NÁZEV VARCHAR2(64) P01 KAT UZ k> ID NUMBER(5) ____ NÁZEV VARCHAR2(64) ID_OBEC NUMBER(5) ID = ID OBEC ID = ADRESA OKRES ID OKRES P01 OBEC JD NUMBER(5) NÁZEV VARCHAR2(64) ID_OKRES NUMBER(5) ID = SJM PARTNER2 ID = SJM PARTNER! ID = ID KU ICO RC SJM_PARTNER1 -SJM_PARTNER2 PŘÍJMENÍ JMÉNO TITUL_PRED TITUL_ZA .ADRESA_OKRES ADRESA_OBEC ADRESA_COBCE ADRESA CPOP P01_OPSUB NUMBER NUMBER(8) NUMBER(10) NUMBER NUMBER VARCHAR2(128) VARCHAR2(64) VARCHAR2(16) VARCHAR2(16) NUMBER(4) NUMBER(5) NUMBER(5) NUMBER(5) 3k> :ADRESA OBEC ID: ID = ID OBEC ADRESA_COBCE P01_CAST_OBCE ]D NUMBER(5) NÁZEV VARCHAR2(64) ID_OBEC NUMBER(5) 3k> cfk> ID = ID OPSUB P01 VLASTNI ID KU ID LV ID OPSUB PODIL_CITATEL PODÍL JMENOVATEL NUMBER(6) NUMBER(5) NUMBER NUMBER NUMBER ID = ID CAST OBCE P01 PARCELA ID KU PARC TYP PARC CIS PAR POD DRUH_POZ NEM_VYUZ VÝMERA CIS LV NUMBER(6) NUMBERd) NUMBER(4) NUMBERO) NUMBER(2) NUMBER(3) NUMBER(9) NUMBER(5) ID_KU : ID LV : ID_KU ID LV ID_KU : ID LV = = ID_KU CIS LV ID_KU = ID LV = : LV_KU| CIS LV P01_LV ID KU NUMBER(6) ID LV NUMBER(5) P01 BUDOVA TYP CISLA CISLO DOMOVNÍ ID CAST OBCE LV_KU CIS LV VARCHAR2(1) NUMBER(5) NUMBER(5) NUMBER(6) NUMBER(5) 3 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady Návrh fyzického datového modelu bude kopíruje entity z Příklad 1, s jedinou výjimkou. Vazba "Vlastní" (kardinality M:N) je realizována tabulkou. Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady PŘÍKLAD 3 Sestavte zakládací skript pro tabulky po i_okres, poi_obec, po i_cast_obce, poi_kat_uz,poi_budova,poi_parcela. Dbejte na to, aby kažká tabulka měla primární klíč a byly realizovány všechny vazby z diagramu v Příklad 2. D Řešení: DROP TABLE P01_OKRES cascade constraints; create table P01_OKRES ( ID number(4), NÁZEV varchar2(64), constraint P01_OKRES_PK primary key (id) DROP TABLE P01_OBEC cascade constraints; create table P01_OBEC ( ID number(5), NAZEV varchar2(64), ID_OKRES number(5) NOT NULL, constraint P01_OBEC_PK primary key (ID) , constraint P01_OBEC_FK01 foreign key (ID_OKRES) references P01_OKRES(ID) ); DROP TABLE P01_CAST_OBCE cascade constraints; create table P01_CAST_OBCE ( ID number(5), NAZEV varchar2(64), ID_OBEC number(5) NOT NULL, constraint P01_CAST_OBCE_PK primary key (ID), constraint P01_CAST_OBCE_FK01 foreign key (ID_OBEC) references P01_OBEC(ID) ); DROP TABLE P01_KAT_UZ cascade constraints; 5 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady create table P01_KAT_UZ ( ID number(6), NÁZEV varchar2(64), ID_OBEC number(5) NOT NULL, constraint P01_KAT_UZ_PK primary key (ID) , constraint P01_KAT_UZ_FK01 foreign key (ID_OBEC) references P01_OBEC(ID; DROP TABLE P01_BUDOVA cascade constraints; create table P01 BUDOVA VARCHAR2(1), /* P - popisne, E -number(5), /* cislo popisne nebo TYP_CISLA evidencni */ CISLO_DOMOVNI evidencni */ ID_CAST_OBCE number(5) NOT NULL, CIS_LV number(5), constraint P01_BUDOVA_PK primary key (ID_CAST_OBCE,TYP_CISLA,CISLO_DOMOVNI), constraint P01_BUDOVA_FK01 foreign key (ID_CAST_OBCE) references P01_CAST_OBCE(ID), constraint P01_BUDOVA_CH01 check (TYP_CISLA IN ('E','P')), constraint P01_BUDOVA_CH02 check (CISLO DOMOVNI>0) drop table P01_PARCELA cascade constraints; create table P01 PARCELA ID_KU number(6) PARC_TYP number(1) PARC_CIS number(4) PAR_POD number(3) DRUH_POZ number(2) NEM_VYUZ number(3) VÝMERA number(9) CIS_LV number(5) constraint P01_PARCELA_PK primary key (ID_KU,PARC_TYP,PARC_CIS,PAR_P0D; constraint P01_PARCELA_FK01 foreign key (ID_KU) references P01_KAT_UZ(ID) constraint P01_PARCELA_CH01 check (PARC_TYP IN ('1','2'))); 6 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady PŘÍKLAD 4 Do zakládacího SQL skriptu doplň tabulky: Oprávněných subjektů P01_OPSUB se sloupci: - IČO - RČ - RČ - RČ u SJM Jméno Příjmení Tituly před jménem Tituly za jménem Adresa_okres Adresa obec Adresa část obce Adresa číslo popisné / orientační + nutné vazební sloupce Listů vlastnictví P01_LV se sloupci: ID (identifikace) + nutné vazební sloupce Vazba mezi Oprávněnými subjekty a LV P01_VLASTNÍ se sloupci Podíl - čitatel Podíl - jmenovatel + nutné vazební sloupce Uprav v SQL skriptu chybějící vazby.D 7 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady Řešení: create table P01 OPSUB int, number(8) , number (10] varchar2(12Ě varchar2(64) varchar2 (16) varchar2 (16) not not not not ID ICO RC SJM_PARTNER1 int, SJM_PARTNER2 int, PRIJMENI JMENO TITUL_PRED TITUL_ZA ADRESA_OKRES number(4 ADRESA_OBEC number(5 ADRESA_COBCE number(5 ADRESA_CPOP number(5 constraint P01_OPSUB_PK pr constraint P01_OPSUB_FKl f (ADRESA_OKRES) references constraint P01_OPSUB_FK2 f (ADRESA_OBEC) references constraint P01_OPSUB_FK3 f references P01_OBEC(ID) , constraint P01_OPSUB_FK4 f references P01_CAST_OBCE( constraint P01_OPSUB_FK5 f references P01_OPSUB(ID), constraint P01_OPSUB_FK6 f references P01 OPSUB(ID) null, null, null, null, imary key (ID), oreign key P01_OKRES(ID), oreign key P01_OBEC(ID), oreign key (ADRESA_OBEC) oreign key (ADRESA_COBCE) ID) , oreign key (SJM_PARTNER1) oreign key (SJM_PARTNER2) create table P01_LV ( ID_KU number(6), ID_LV number(5), constraint P01_LV_PK primary key (ID_KU,ID_LV), constraint P01_LV_FKl foreign key (ID_KU) references P01 KAT UZ(ID) alter table P01_PARCELA add constraint P01_PARCELA_FK3 foreign key (ID_KU,CIS_LV) references P01_LV(ID_KU,ID_LV); alter table P01_PARCELA modify CIS_LV not null; alter table P01 BUDOVA add LV KU number(6) 8 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady alter table P01_BUDOVA add constraint P01_BUDOVA_FK3 foreign key (LV_KU,CIS_LV) references P01_LV(ID_KU,ID_LV); alter table P01_BUDOVA modify CIS_LV not null; alter table P01_BUDOVA modify LV_KU not null; create table P01_VLASTNI ( ID_KU number(6), ID_LV number(5), ID_OPSUB int, PODIL_CITATEL int, PODIL_JMENOVATEL int, constraint P01_VLASTNI_PK primary key (ID_KU,ID_LV,ID_OPSUB), constraint P01_VLASTNI_FKl foreign key (ID_KU,ID_LV) references P01_LV(ID_KU,ID_LV), constraint P01_VLASTNI_FK2 foreign key (ID_OPSUB) references P01_OPSUB(ID) ); 9 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady PŘÍKLAD 5 Sestrojte dotazy, které vrátí: a) Jméno, příjmení a adresu oprávněného subjektu (ID=%d) b) Parcely a podíly ve vlastnictví oprávněného subjektu (ID=%d) c) Sestrojte dotaz, který vrátí všechny vlastníky parcely, podíly a jejich adresy, je-li vstupem její úplná identifikace (tj. její primární klíč).D Řešení: select OS.JMÉNO, OS.PŘÍJMENÍ, OK.NÁZEV, OB.NÁZEV, CO.NÁZEV, OS.ADRESA_CPOP from P01_OKRES OK, P01_OBEC OB, P01_CAST_OBCE CO, P01_OPSUB OS where OS.ID =%d AND OS.ADRESA_OKRES=OK.ID AND OS.ADRESA_OBEC =OB.ID AND OS.ADRESA_COBCE=CO.ID; select KU.NÁZEV, PA.PARC_TYP, PA.PARC_CIS, PA.PAR_POD, VL.PODIL_CITATEL| | VI IVL.PODIL_JMENOVATEL from P01_VLASTNI VL, P01_PARCELA PA, P01_KAT_UZ KU where VL.ID_OPSUB=%d AND VL.ID_KU =PA.ID_KU AND VL.ID_LV =PA.CIS_LV AND PA.ID_KU =KU.ID; 10 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady PŘÍKLAD 6 Zrušte v tabulce (resp. zakážte používání viz. alter table .. modify .. disable) integritni omezení primárního klíče P01_OPSUB_PK. Pokusně vložte do P01_OPSUB_PK několik řádků, z nichž některé mají stejná ID. a) Odstraňte duplicitní řádky poi_opsub_pk b) Znovu povolte integritni omezení P01_OPSUB_PK.D Metoda #1: a) create sequence p; b) alter table P01_OPSUB add POM int; c) update P01_OPSUB set pom=p.nextval; d) delete from P01_OPSUB A where porno (select max(pom) from P01_OPSUB B where a.id=b.id); e) alter table P01_OPSUB drop column POM; f) drop sequence p; Metoda #2: Každý SQL select, update obsahuje fiktivní sloupec rownum, který obsahuje zpracovávaný řádek a) alter table P01_OPSUB add POM int; b) update P01_OPSUB set pom=rownum; d) delete from P01_OPSUB A where porno(select max(pom) from P01_OPSUB B where a.id=b.id); Metoda #3: Fiktivní sloupec rowid v každé tabulce obsahuje "adresu řádku tabulky", je unikátní pro celou instanci databáze ORACLE. delete from P01_OPSUB A where ROWIDO ( select max(ROWID) from P01_OPSUB b where a.id=b.id ); 11 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady PŘÍKLAD 7 Realizujte trigger, který na tabulce poi_opsub automaticky naplní primárni klíč (id) hodnotou se sekvence poi_opsub_seq a zkontroluje korektnost sloupce rc (rodné číslo).D PŘÍKLAD 8 V tabulce poi_opsub jsou redundantní cizí klíče, ADRESA_OKRES (->P01_OKRES) ADRESA_OBEC (->P01_OBEC) jejich hodnota lze odvodit z tabulek poi_cast_obce a poi_obec.2 Mohou však být nekonzistentní, tj. v jednom řádku tabulky poi_opsub je uvedena obec, která není nadřízena části obce atd. a) Zformulujte SQL skript, který tyto klíče uvede do souladu. b) Zajistěte (triggerem), aby k takové situaci nemohlo dojít. D PŘÍKLAD 10 Datový model doplňte tabulkou: CREATE TABLE P01_REPORT ( ID INT, LINE_NO INT, LINE VARCHAR2(1024), CONSTRAINT POl_REPORT_PK PRIMARY KEY (ID,LINE_NO) ) Sestavte pl/sql proceduru generate_lv (v package p01) package POl is procedure GENERATE_LV ( KU in INT, /* katastrálni uzemí */ LV in INT, /* číslo listu vlastnictví */ ID in int /* report id */ ); end POl; po jejímž provedení: begin POl. GENERATE_LVUu,Iv, xx) ; end; / 2 Nemusí se nutně jednat o analytickou chybu, důvodem může být zrychlování dotazů. 12 Fakulta Informatiky, Masarykova Universita v Brně PV003 - příklady příkaz: SELECT LINE FROM P01_REPORT WHERE ID=xx ORDER BY LINE_NO vrátí výpis - list vlastnictví v této formě: Okres: 3209 Praha - východ Obec: 078 Říčany Kat. území 01 Říčany u Prahy Číslo LV 365 A Vlastník (pořadí jméno/název a adresa) Identifikátor Podíl 1 Josef Novák Podlesná 11, Brno 581205/3256 1/2 2 Pavel Novák Sady 15, Olomouc 351231/454 1/2 B Parcelní Výměra Část Číslo Druh číslo m2 obce budovy 1452/2 4584 louka 1452/3 592 orná půda 13