database programming in
play

Database Programming in SQL/O RACLE SQL-3 Standard/ORACLE 8: - PowerPoint PPT Presentation

Mar 15, 2023 •119 likes •1.6k views

Database Programming in SQL/ORACLE Database Programming in SQL/O RACLE SQL-3 Standard/ORACLE 8: ER-Modeling Schema Generation Queries Views Complex attributes, nested tables Database Optimization Access

  1. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Entity types Mapping ERM to RM Let E ER an entity type and B ER a relationship type in the ERM. ( E ER , { A 1 , . . . , A n } ) − → E ( A 1 , . . . , A n ) 1. Entity types: ( E ER , { A 1 , . . . , A n } ) − → E ( A 1 , . . . , A n ) , 2. Relationship types: ( B ER , { RO 1 : E 1 , . . . , RO k : E k } , { A 1 , . . . , A m } ) − → B ( E 1 _ K 11 , . . . , E 1 _ K 1 p 1 , . . . , name continent area E k _ K k 1 , . . . , E k _ K kp k , A 1 , . . . , A m ) , Asia 4.5E7 ent_79110 where { K i 1 , . . . , K ip i } are the primary keys of E i , 1 ≤ i ≤ k . In case that for a relationship type B ER , the keys of involved entity types have coinciding names, the role Continent specifications may be used to guarantee the uniqueness of key attributes in the relationship type. Name Area In case that k = 2 and a (1,1) relationship complexity, the VARCHAR(20) NUMBER relation schema of the relationship type and that of the Europe 9562489.6 entity type may be merged. Africa 3.02547e+07 3. For a weak entity type, the key attributes of the identifying Asia 4.50953e+07 entity type must be added. America 3.9872e+07 4. Aggregate types can be ignored if the underlying Australia 8503474.56 relationship type is mapped. The Relational Model 18 The Relational Model 19

  2. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Relationship Types Relationship Types In case that k = 2 and a (1,1) relationship complexity, the ( B ER , { RO 1 : E 1 , . . . , RO k : E k } , { A 1 , . . . , A m } ) − → relation schema of the relationship type and that of the entity B ( E 1 _ K 11 , . . . , E 1 _ K 1 p 1 , . . . , type may be merged. E k _ K k 1 , . . . , E k _ K kp k , A 1 , . . . , A m ) , name Country code where { K i 1 , . . . , K ip i } are the primary keys of E i , 1 ≤ i ≤ k . (it Germany D is allowed to rename, e.g., to use Country for Country.Code ) < 1 , 1 > Europe R is_capital code name < 0 , 1 > encompasses continent Country name pop. City Berlin 3472009 ent_0815 percent Country 20 Name code Population Capital Province ... encompasses Germany D 83536115 Berlin Berlin Country Continent Percent Sweden S 8900954 Stockholm Stockholm VARCHAR(4) VARCHAR(20) NUMBER Canada CDN 28820671 Ottawa Quebec R Europe 20 Poland PL 38642565 Warsaw Warszwaskie R Asia 80 Bolivia BOL 7165257 La Paz Bolivia D Europe 100 .. .. .. .. .. . . . . . . . . . The Relational Model 20 The Relational Model 21

  3. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Weak Entity Types Relationship Types For a weak entity type, the key attributes of the identifying entity type In case that for a relationship type B , the keys of involved entity must be added. types have coinciding names, the role specifications may be area pop. used to guarantee the uniqueness of key attributes in the 248678 61170500 relationship type. name Country code BRD D ent_4711 area pop. code Country name 35751 10272069 in name Province < 0 , ∗ > < 0 , ∗ > Baden-W. ent_1997 C2 C1 < 1 , 1 > borders in Prov. < 1 , 1 > borders name pop. City Country1 Country2 Freiburg 198496 ent_0815 City D F Name Country Province Population ... D CH Freiburg D Baden-W. 198496 .. CH F Berlin D Berlin 3472009 .. .. .. .. .. .. .. .. The Relational Model 22 The Relational Model 23

  4. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE SQL = S tructured Q uery L anguage • common query language Data Dictionary: Contains meta data about the database • standardization: SQL-89, SQL-2 (1992), SQL-3 (1996) Database Language: • SQL-2 in 3 stages: entry, intermediate, and full level DDL: D ata D efinition L anguage for defining schema • tables • SQL-3: object-orientation • views • descriptive querying language • indexes • results are always sets of tuples (relations) • integrity constraints DML: D ata M anipulation L anguage for manipulating • implementation: ORACLE (and many others) database states • Search/Read • Insert • SQL is case-insensitive, i.e., CITY=city=City=cItY. • Modify • inside quotes, SQL is not case-insensitive, i.e., City=’Berlin’ • Delete � = City=’berlin’. • every command has to be ended with a semicolon “;” • comment lines are embraced in / ∗ . . . ∗ / , or introduced by -- or rem . SQL-2 24 SQL-2 25

  5. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Data Dictionary Data Dictionary Consists of tables and views that contain meta data about the database. ALL_OBJECTS : contains all objects that are accessible for a user. With SELECT * FROM DICTIONARY (abbrev. SELECT * FROM DICT ),the Data Dictionary explains itself. ALL_CATALOG : contains all tables, views, and synonyms that are accessible for a user. TABLE_NAME ALL_TABLES : contains all tables that are accessible for a user. COMMENTS Analogously for several other things. ( select * from ALL_ARGUMENTS ALL_CATALOG where TABLE_NAME LIKE ’ALL%’; ). Arguments in objects accessible to the user USER_OBJECTS : contains all objects that where the user is the ALL_CATALOG owner. All tables, views, synonyms, sequences accessible to the user Analogously for other database object types, in most case ALL_CLUSTERS there is also an abbreviation for USER_... , e.g. OBJ for Description of clusters accessible to the user USER_OBJECTS . ALL_CLUSTER_HASH_EXPRESSIONS ALL_USERS : contains informations about all users of the Hash functions for all accessible clusters database. . . . Data Dictionary 26 Data Dictionary 27

  6. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE SELECT table_name FROM tabs; Table_name Table_name BORDERS ISLAND The schema of individual tables and views can be displayed by CITY LAKE < table > or abbreviated DESC using DESCRIBE < table > : CONTINENT LANGUAGE DESC City; COUNTRY LOCATED DESERT IS_MEMBER Name NULL? Typ ECONOMY MERGES_WITH NAME NOT NULL VARCHAR2(25) ENCOMPASSES MOUNTAIN COUNTRY NOT NULL VARCHAR2(4) ETHNIC_GROUP ORGANIZATION PROVINCE NOT NULL VARCHAR2(35) GEO_DESERT POLITICS POPULATION NUMBER GEO_ISLAND POPULATION LONGITUDE NUMBER GEO_LAKE PROVINCE LATITUDE NUMBER GEO_MOUNTAIN RELIGION GEO_RIVER RIVER GEO_SEA SEA 28 rows selected. Data Dictionary 28 Data Dictionary 29

  7. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Queries: SELECT-FROM-WHERE Projection: Choose some columns Queries against the database are in SQL formulated by the SELECT command. Its basic structure is simple: SELECT < attr-list > Attributes SELECT FROM < table > ; FROM Relation(s) Condition WHERE Simplest form: all columns and rows of a relation For all cities, give its name and the country to which it belongs: SELECT * FROM City; SELECT Name, Country FROM City; Name C. Province Pop. Long. Lat. . . . . . . Name COUNTRY . . . . . . . . . . . . Tokyo J Vienna A Vienna 1583000 16,3667 48,25 Stockholm S Innsbruck A Tyrol 118000 11,22 47,17 Stuttgart D Baden-W. 588482 9.1 48.7 Warsaw PL Freiburg D Germany 198496 NULL NULL Cochabamba BOL . . . . . . . . . . . . Hamburg D . . . . . . Berlin D 3114 rows selected. .. .. SQL: Queries 30 SQL: Queries 31

  8. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE DISTINCT SELECT * FROM Island; Name Islands Area ... . . . . . . . . . . . . Duplicate Elimination Jersey Channel Islands NULL . . . Mull Inner Hebrides 910 . . . • Duplicates are not automatically eliminated: Montserrat Antilles 106 . . . – duplicate elimination is expensive (sorting and deleting) Grenada Antilles NULL . . . – user may be interested in duplicates . . . . . . . . . . . . – later: aggregate functions on relations with duplicates • Duplicate elimination: DISTINCT -clause SELECT Islands SELECT DISTINCT Islands FROM Island; FROM Island; • later: Duplicates are automatically eliminated when set operations UNION , INTERSECT , ... are used Islands Islands . . . . . . Channel Islands Channel Islands Inner Hebrides Inner Hebrides Antilles Antilles . . Antilles . . . . SQL: Queries 32 SQL: Queries 33

  9. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Example: SELECT Name, Country, Population Selections: Choose some rows FROM City WHERE Country = ’J’; SELECT < attr-list > FROM < table > Name Country Population WHERE < predicate > ; Tokyo J 7843000 < predicate > may be of the following forms: Kyoto J 1415000 • < attribute > < op > < value > with op ∈ { = , <, >, < = , > = } , Hiroshima J 1099000 • < attribute > [NOT] LIKE < string > , where each underscore Yokohama J 3256000 in the string stands for an arbitrary character, and “%” Sapporo J 1748000 stands for arbitrary many characters, . . . . . . . . . • < attribute > IN < value-list > , where < value-list > is either of the form (’val1’,. . . ,’valn’), or may be given as the result of a Example: subquery, SELECT Name, Country, Population • [NOT] EXISTS < subquery > FROM City • NOT ( < predicate > ), WHERE Country = ’J’ AND Population > 2000000 • < predicate > AND < predicate > , Name Country Population • < predicate > OR < predicate > . Tokyo J 7843000 Yokohama J 3256000 SQL: Queries 34 SQL: Queries 35

  10. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE ORDER BY Example: SELECT Name, Country, Population FROM City SELECT Name, Country, Population WHERE Country LIKE ’%J_%’; FROM City WHERE Population > 5000000 Name Country Population ORDER BY Population DESC; (descending) Kingston JA 101000 Amman JOR 777500 Suva FJI 69481 Name Country Population . . . . . . . . . Seoul ROK 10.229262 The requirement that the “J” is followed by at least one Mumbai IND 9.925891 character excludes japanese cities (“J”) from the result. Karachi PK 9.863000 Mexico MEX 9.815795 Sao Paulo BR 9.811776 Moscow R 8.717000 . . . . . . . . . SQL: Queries 36 SQL: Queries 37

  11. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Aggregate functions • COUNT (*| [DISTINCT] < attribute > ) • MAX ( < attribute > ) ORDER BY, Alias • MIN ( < attribute > ) • SUM ([DISTINCT] < attribute > ) • AVG ([DISTINCT] < attribute > ) Example: How many cities are stored in the database? SELECT Name, Population/Area AS Density FROM Country SELECT Count (*) ORDER BY 2 ; (Default: ascending) FROM City; Count(*) Name Density 3114 Western Sahara ,836958647 Example: How many countries are stored in the database for Mongolia 1,59528243 which at least one city with more than 1,000,000 inhabitants is stored? French Guiana 1,6613956 Namibia 2,03199228 SELECT Count (DISTINCT Country) FROM City Mauritania 2,26646745 WHERE Population > 1000000; Australia 2,37559768 Count(DISTINCT(Country)) 68 SQL: Queries 38 Aggregate functions 39

  12. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Grouping GROUP BY conputes one row for every group. This group Aggregate functions contains data that is obtained by using aggregate functions over all rows of the group. SELECT < expr-list > FROM < table > WHERE < predicate > Example: Compute the sum of the population of all Austrian GROUP BY < attr-list > ; cities, and the number of inhabitants of Austria’s largest city. returns for every value of < attr-list > a single row. Thus, in SELECT SUM(Population), MAX(Population) < expr-list > only the following expressions are allowed: FROM City WHERE Country = ’A’; • constants, • attribute from < attr-list > , SUM(Population) MAX(Population) • attribute, which have the same value for all rows in such a 2434525 1583000 group (e.g. Code , if < attr-list > contains Country ), • Aggregate functions , which are then applied to all tuples of the corresponding group. The WHERE clause < predicate > contains only attributes of the And what, if these values are needed for each of the relations mentioned in < table > (i.e., no aggregate functions). countries?? Aggregate functions 40 Grouping 41

  13. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Grouping Conditions over Groups Example: For every country, return the number of inhabitants The HAVING clause allows to state additional conditions on the that live in cities. groups: SELECT Country, Sum(Population) SELECT < expr-list > FROM City FROM < table > GROUP BY Country; WHERE < predicate1 > GROUP BY < attr-list > Country SUM(Population) HAVING < predicate2 > ; A 2434525 • WHERE clause: conditions on individual tuples before grouping, AFG 892000 AG 36000 • HAVING clause: conditions to select groups for the result. In the HAVING clause, in addition to aggregate function AL 475000 expressions over attributes, only those attributes are AND 15600 allowed that are mentioned explicitly in the GROUP BY . . . . . . clause. Grouping 42 Grouping 43

  14. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Set Operations SQL queries can be joined by set operations: Conditions on Groups < select-clause > < set-op > < select-clause > ; • UNION [ALL] • MINUS [ALL] Example: Compute for each country the total number of • INTERSECT [ALL] inhabitants that live in cities with more than 100,000 inhabitants. Output only those countries where this number is • automatical elemination of duplicates (can be prevented by more than 10 millions. ALL) SELECT Country, SUM(Population) Example: Give all names of cities that also occur as names of FROM City countries: WHERE Population > 10000 (SELECT Name GROUP BY Country FROM City) HAVING SUM(Population) > 10000000; INTERSECT (SELECT Name Country SUM(Population) FROM Country); AUS 12153500 Name BR 77092190 CDN 10791230 Armenia CO 18153631 Djibouti . . . . Guatemala . . . . . Grouping 44 Set Operations 45

  15. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Join Queries Example: All countries that have less inhabitants than Tokyo. Join queries provide a possibility to combine several relations SELECT Country.Name, Country.Population into a query. FROM City, Country SELECT < attr-list > WHERE City.Name = ’Tokyo’ AND Country.Population < City.Population; FROM < table-list > WHERE < predicate > ; Name Einwohner Basically, a join is based on the cartesian product of the Albania 3249136 contributing relations (Theory: see “Introduction to Andorra 72766 Databases”). Liechtenstein 31122 • resulting attributes: union of all attributes of contributing Slovakia 5374362 relations Slovenia 1951443 • attributes that occur in several relations must be qualified . . . . by < table > . < attr > . . . • join of a relation with itself – aliases. Join Queries 46 Join Queries 47

  16. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Equijoin Join of a relation with itself Example: For all organizations, give the continents where they Example: Compute all pairs of cities in different countries are seated. which have the same name. encompasses: Country, Continent, Percentage. SELECT A.Name, A.Country, B.Country Organization: Abbreviation, Name, City, Country, Province. FROM City A, City B WHERE A.Name = B.Name SELECT Continent, Abbreviation AND A.Country < B.Country; FROM encompasses, Organization WHERE encompasses.Country = Organization.Country; A.Name A.Country B.Country Name Organization Alexandria ET RO America UN Alexandria ET USA Europe UNESCO Alexandria RO USA Europe CCC Barcelona E YV Europe EU Valencia E YV America CACM Salamanca E MEX Australia/Oceania ANZUS . . . . . . . . . . . . . . . Join Queries 48 Join Queries 49

  17. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Uncorrelated Subquery • independent from the values of the tuple which is currently processed in the surrounding query, • evaluated once before the surrounding query, Subqueries • the result is then used for evaluating the WHERE clause of the surrounding query, • strictly sequential evaluation, thus, the qualification of The WHERE clause can contain results of subqueries: multiply occurring attributes is not necessary. SELECT < attr-list > FROM < table > < attribute > ( < op > [ANY | ALL]| IN) WHERE < subquery > ; Example: Give all countries where there exists a city with name “Victoria”: < subquery > is a SELECT query ( Subquery ), • SELECT Name • for < op > ∈ { = , <, >, < = , > = } , < subquery > must result in a FROM Country relation with a single column, WHERE Code IN (SELECT Country • for IN < subquery > , also multi-column results are allowed FROM City (since O RACLE 8), WHERE Name = ’Victoria’); • for < op > without ANY or ALL , the result of < subquery > must contain only a single row. Country.Name Canada Seychelles Subqueries 50 Subqueries 51

  18. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Subquery with ALL Uncorrelated Subquery with IN Example: ALL can e.g. be used for computing all countries that are smaller than all countries that have more than 10 million Example: Give all cities that are known to be situated at a inhabitants: river, lake, or a sea: SELECT Name,Area,Population SELECT * FROM Country FROM CITY WHERE Area < ALL WHERE (Name,Country,Province) (SELECT Area IN (SELECT City,Country,Province FROM located); FROM Country WHERE Population > 10000000); Name Country Province Population ... Name Area Population Ajaccio F Corse 53500 . . . Albania 28750 3249136 Karlstad S Värmland 74669 . . . Macedonia 25333 2104035 San Diego USA California 1171121 . . . Andorra 450 72766 . . . . . . . . . . . . . . . . . . Subqueries 52 Subqueries 53

  19. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE The EXISTS Operator Correlated Subquery EXISTS and NOT EXISTS simulate the existential quantifier. • Subquery depends on attribute values of the tuple which is SELECT < attr-list > currently processed in the outer query, FROM < table > • evaluated once for every tuple of the surrounding query , WHERE [NOT] EXISTS • imported attributes must be qualified. ( < select-clause > ); Example: Compute all cities where more than 1/4 of the Example: Compute all countries for which cities with more population of the corresponding country is living. than 1,000,000 inhabitants are stored. SELECT Name, Country SELECT Name FROM City FROM Country WHERE Population * 4 > WHERE EXISTS (SELECT Population ( SELECT * FROM Country FROM City WHERE Code = City.Country); WHERE Population > 1000000 AND City.Country = Country.Code) ; Name Country Copenhagen DK Name Tallinn EW Serbia and Montenegro Vatican City V France Reykjavik IS Spain Auckland NZ Austria . . . . . . . . . Subqueries 54 Subqueries 55

  20. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Example A country is strongly urbanized if more than 10 percent of its population live in cities with more than 500,000 inhabitants. Transformation EXISTS , Subquery, Join Which member countries of the EU are strongly urbanized? SELECT Country.Name FROM Country, City, is_member WHERE Organization = ’EU’ Equivalent to the previous one are the following queries: AND is_member.Country = Country.Code AND is_member.Type = ’member’ AND City.Population > 500000 AND City.Country = Country.Code SELECT Name GROUP BY Country.Name, Country.Population FROM Country HAVING (SUM(City.Population)/Country.Population) > 0.1; WHERE Code IN ( SELECT Country Name FROM City Austria WHERE City.Population > 1000000); Denmark Germany SELECT DISTINCT Country.Name Ireland FROM Country, City Italy WHERE City.Country = Country.Code Netherlands AND City.Population > 1000000; Spain United Kingdom Subqueries 56 Subqueries 57

  21. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Subqueries in the FROM Clause Subqueries in the FROM Clause SELECT < attr-list > FROM < table/subquery-list > WHERE < condition > ; Values which are obtained in different ways from different ... especially suitable for nested computations with aggregate tables can be related. functions Example: Compute the total number of people who live in the Example: Compute the total number of people who do not live largest city of their countries. in the stored cities. SELECT sum(pop_biggest) SELECT Population - Urban_Residents FROM (SELECT country, max(population) as pop_biggest FROM FROM City (SELECT SUM(Population) AS Population GROUP BY country); FROM Country), (SELECT SUM(Population) AS Urban_Residents sum(pop_biggest) FROM City); 273837106 Population-Urban_Residents 4620065771 Subqueries 58 Subqueries 59

  22. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Generation of Tables Schema Definition CREATE TABLE < table > ( < col > < datatype > , . . . < col > < datatype > ) • the database schema contains all information about the CHAR( n ) : string with fixed length n . structure of the database, VARCHAR2( n ) : string with variable length ≤ n . • tables, views, constraints, indexes, clusters, triggers ... || : string concatenation. • O RACLE 8: datatypes, methods NUMBER : numbers. for NUMBER , the usual operators + , − , ∗ , and / , and the comparisons = , > , > = , < = , and < are allowed. • is defined and modified using the DDL (Data Definition Additionally there is BETWEEN x AND y . Language), Inequality: ! = , ∧ = , ¬ = , or <> . • CREATE , ALTER , and DROP of schema objects, DATE : Dates and times: Century – Year – Month – Day – Hour • access rights: GRANT . – Minute – Second. There is also arithmetics and some more functions for these datatypes. additional Datatypes are described in the manual. Schema Definition 60 Schema Definition 61

  23. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Definition of Tables: Constraints With the definition of tables, properties and constraints on the attribute values can be specified. • Constraints on a single or on several attributes: • Constraints on the domain, • Specification of default values, Table Definition • NULL values allowed or not, • Specification of key constraints, The below SQL statement generates the City relation (still • Predicates over each individual tuple. without integrity constraints): Syntax: CREATE TABLE City CREATE TABLE < table > ( Name VARCHAR2(35), < datatype > [DEFAULT ( < col > < value > ] Country VARCHAR2(4), [ < colConstraint > ... < colConstraint > ], Province VARCHAR2(32), . . . Population NUMBER, < datatype > [DEFAULT < col > < value > ] Longitude NUMBER, [ < colConstraint > ... < colConstraint > ], Latitude NUMBER ); [ < tableConstraint > ,] . . . [ < tableConstraint > ]) < colConstraint > concerns only a single column, • < tableConstraint > can concern several columns. • Schema Definition 62 Schema Definition 63

  24. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Definition of Tables: Default Values DEFAULT < value > Definition of Tables: Constraints A member country of an organization is assumed to be a full member if nothing else is specified: CREATE TABLE is_member ( Country VARCHAR2(4), Two types of constraints: Organization VARCHAR2(12), • A column condition < colConstraint > is a condition that is Type VARCHAR2(30) concerned only with a single column (to which it is DEFAULT ’member’) associated) • A table condition < tableConstraint > may concern several INSERT INTO is_member VALUES (’CZ’, ’EU’, ’membership applicant’); columns. INSERT INTO is_member (Land, Organization) VALUES (’D’, ’EU’); Each < colConstraint > or < tableConstraint > is of the form Country Organization Type CZ EU membership applicant [CONSTRAINT < name > ] < condition > D EU member . . . . . . . . . Schema Definition 64 Schema Definition 65

  25. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Definition of Tables: Conditions (Overview) Definition of Tables: Syntax Syntax: [CONSTRAINT < name > ] < condition > [CONSTRAINT < name > ] < condition > Keywords in < condition > : < name > is optional (otherwise, an internal where CONSTRAINT name is assigned). 1. CHECK ( < condition > ) : no line is allowed to violate < condition > . NULL values result in an unknown that does not violate any check condition. < name > is needed for NULL -, UNIQUE -, CHECK -, and • 2. [NOT] NULL : indicates whether a column is allowed to REFERENCES -constraints, if the constraint should be contain null values (only as < colConstraint > ). changed or deleted eventually, 3. UNIQUE ( < column-list > ) : requires every value in a • PRIMARY KEY can be changed or deleted without having an column to be unique (wrt. all tuples in this table). explicit name. 4. PRIMARY KEY ( < column-list > ) : Declares the given columns as primary keys of this table. Since for a < colConstraint > , the column is implicitly known, 5. FOREIGN KEY ( < column-list > ) REFERENCES the ( < column-list > ) part is omitted. < table > ( < column-list2 > ) [ON DELETE CASCADE|ON DELETE SET NULL] : declares a set of attributes to be a foreign key. Schema Definition 66 Schema Definition 67

  26. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Definition of Tables: PRIMARY KEY , UNIQUE , and NULL • PRIMARY KEY ( < column-list > ) : declares these columns to be the primary key of a table. • PRIMARY KEY is equivalent to combining UNIQUE and NOT NULL . Definition of Tables: CHECK Constraints • UNIQUE is not necessarily violated by NULL values, whereas PRIMARY KEY forbids NULL values. • as column constraints: domain constraint One Two CREATE TABLE City a b ( Name VARCHAR2(35), satisfies UNIQUE (One,Two) . Population NUMBER CONSTRAINT CityPop a NULL CHECK (Population >= 0), NULL b ...); NULL NULL • as table constraints: arbitrary integrity constraints on the • Since for each table, only one PRIMARY KEY may be values of each individual tuple. defined, candidate keys must be specified by NOT NULL and UNIQUE . Relation Country : Code is the PRIMARY KEY , Name is a candidate key: CREATE TABLE Country ( Name VARCHAR2(32) NOT NULL UNIQUE, Code VARCHAR2(4) PRIMARY KEY); Schema Definition 68 Schema Definition 69

  27. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Definition of Tables: FOREIGN KEY ...REFERENCES Definition of Tables: Foreign Keys • FOREIGN KEY ( < column-list > ) REFERENCES < table > ( < column-list2 > ) [ON DELETE CASCADE|ON A mountain is located in a province of come country: DELETE SET NULL] : declares the attribute tuple Country Code < column-list > of the table to be a foreign key that references the attribute tuple < column-list2 > of the table < table > . belongs_to • The referenced attribute tuple < table > ( < column-list2 > ) Name must be declared as PRIMARY KEY of < table > . Mountain Province in Name • A REFERENCES condition is not violated by NULL values. • ON DELETE CASCADE|ON DELETE SET NULL: referential CREATE TABLE geo_Mountain action (later). ( Mountain VARCHAR2(20) REFERENCES Mountain(Name), CREATE TABLE is_member Country VARCHAR2(4) , (Country VARCHAR2(4) Province VARCHAR2(32) , REFERENCES Country(Code), CONSTRAINT GMountRefsProv Organization VARCHAR2(12) FOREIGN KEY (Country,Province) REFERENCES Organization(Abbreviation), REFERENCES Province (Country,Name)); Type VARCHAR2(30) DEFAULT ’member’); Schema Definition 70 Schema Definition 71

  28. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Views Definition of Tables • Virtual tables Complete definition of the table City , including conditions and keys: • are not computed at the time of their definition, but are • computed each time when they are accessed. CREATE TABLE City • mirror the current state of the database. ( Name VARCHAR2(35), • modifications (of the data) are restricted. Country VARCHAR2(4) REFERENCES Country(Code), < name > ( < column-list > ) AS CREATE [OR REPLACE] VIEW Province VARCHAR2(32) – + < tableConstraint > < select-clause > ; Population NUMBER CONSTRAINT CityPop CHECK (Population >= 0), Example: A user ofte needs the information in which country Longitude NUMBER CONSTRAINT CityLong some city is located, but is not interested in country codes and CHECK ((Longitude >= -180) AND (Longitude <= 180)), population: Latitude NUMBER CONSTRAINT CityLat CHECK ((Latitude >= -90) AND (Latitude <= 90)), CREATE VIEW CityCountry (City, Country) AS CONSTRAINT CityKey SELECT City.Name, Country.Name PRIMARY KEY (Name, Country, Province), FROM City, Country FOREIGN KEY (Country,Province) WHERE City.Country = Country.Code; REFERENCES Province (Country,Name)); If a user now searches for all cities in Cameroon, he can state the following query: • if a table is generated with a REFERENCES SELECT * < table > ( < column-list > ) clause, < table > must already FROM CityCountry be defined, and < column-list > must be declared as WHERE Country = ’Cameroon’; PRIMARY KEY . Schema Definition 72 Views 73

  29. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Inserting Information Deleting Tables and Views • INSERT statement. • insert individual tuples manually, INSERT INTO < table > [( < column-list > )] • tables and views are deleted with DROP TABLE or DROP VALUES ( < value-list > ); VIEW : or < table-name > [CASCADE CONSTRAINTS]; DROP TABLE DROP VIEW < view-name > ; • insert the result of a query: • tables need not to be empty when they are deleted. INSERT INTO < table > [( < column-list > )] < subquery > ; • it is not possible to delete a table that contains referenced tuples. • remaining columns are filled with null values. • a table which is still a target of a REFERENCES declaration E.g., insert the subsequent tuple: cannot be deleted by a simple DROP TABLE command. INSERT INTO Country (Name, Code, Population) < table > CASCADE CONSTRAINTS a table • with DROP TABLE VALUES (’Lummerland’, ’LU’, 4); is deleted together with all referential integrity constraints A table Metropolis (Name, Country, Population) can be that point to it. populated by the following statement: INSERT INTO Metropolis SELECT Name, Country, Population Modification of Tables and Views FROM City WHERE Population > 1000000; later. Deleting Tables and Views 74 Inserting Information 75

  30. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Modifying Tuples Deletion of Tuples UPDATE < table > < attribute > = < value > | ( < subquery > ), SET . . . < attribute > = < value > | ( < subquery > ), ( < attribute-list > ) = ( < subquery > ), Tuples can be deleted with the DELETE command: . . . DELETE FROM < table > ( < attribute-list > ) = ( < subquery > ) WHERE < predicate > ; WHERE < predicate > ; With an empty WHERE clause, all tuples of a table are deleted (the table itself remains, it can be removed with DROP TABLE ): Example: DELETE FROM City; UPDATE City SET Name = ’Leningrad’, Population = Population + 1000, WHERE Name = ’Sankt-Peterburg’; The below command deletes all cities that have less than Beispiel: Set the total population of each country to the sum of 50,000 inhabitants: the population of its administrative divisions: DELETE FROM City UPDATE Country WHERE Population < 50000; SET Population = (SELECT SUM(Population) FROM Province WHERE Province.Country=Country.Code); Inserting Information 76 Modifying Tuples 77

  31. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Date and Time The DATE datatype stores century, year, month, day, hour, minute, second. • Set input format by NLS_DATE_FORMAT , • Default: ’DD-MON-YY’ e.g., ’20-Oct-97’ . Date and Time CREATE TABLE Politics ( Country VARCHAR2(4), ORACLE provides some functions for working with DATE Independence DATE, information: Government VARCHAR2(120)); • SYSDATE returns the current date/time. ALTER SESSION SET NLS_DATE_FORMAT = ’DD MM YYYY’; • addition und subtraction of absolute values over DATE is INSERT INTO politics VALUES allowed. Numbers are interpreted as days: SYSDATE + 1 is (’B’,’04 10 1830’,’constitutional monarchy’); tomorrow, SYSDATE + (10 / 1440) is “in ten minutes”. All countries that have been founded between 1200 and 1600: • ADD_MONTHS ( d, n ) adds n months to a date d . SELECT Country, Independence • LAST_DAY ( d ) yields the last day of a the month to which d FROM Politics belongs. WHERE Independence BETWEEN • MONTHS_BETWEEN ( d 1 , d 2 ) returns the number of months ’01 01 1200’ AND ’31 12 1599’; between two dates. Land Datum MC 01 01 1419 NL 01 01 1579 E 01 01 1492 THA 01 01 1238 Date and Time 78 Date and Time 79

  32. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Object Orientation in O RACLE 8 • complex data types: name longitude Generation of Data Types geo coord Mountain New class of schema objects: CREATE TYPE height latitude • CREATE [OR REPLACE] TYPE < name > AS OBJECT • nested tables: ( < attr > < datatype > , . . . Nested_Languages < attr > < datatype > ); Country Languages For “full” objects, there is also a D German 100 CREATE TYPE BODY ... where the methods are defined in PL/SQL ... later. CH German 65 Without body/methods, simply complex datatypes are French 18 generated (similar to Records ). Italian 12 • CREATE [OR REPLACE] TYPE < name > Romansch 1 AS TABLE OF < datatype > FL NULL (“Collection”, tables as data types ) F French 100 . . . . . . • objects, methods, object tables, object references ... (later) Complex Data Types 80 Complex Data Types 81

  33. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Complex Data Types Geographical coordinates: CREATE TYPE GeoCoord AS OBJECT ( Longitude NUMBER, Complex Data Types Latitude NUMBER); / Access to individual components of complex attributes uses the CREATE TABLE Mountain common dot -Notation (similar to records). ( Name VARCHAR2(20), O RACLE 8.0: only with qualification : Height NUMBER, SELECT Name, B.Coordinates.Longitude, Coordinates GeoCoord); B.Coordinates.Latitude < type > AS OBJECT (...) automatically defines CREATE TYPE FROM Mountain B; a Constructor method < type > : INSERT INTO Mountain Name Coordinates.Longitude Coordinates.Latitude VALUES (’Feldberg’, 1493, GeoCoord(8,48)); Feldberg 8 48 SELECT * FROM Mountain; Name Height Coordinates(Longitude, Latitude) Feldberg 1493 GeoCoord(8,48) Complex Data Types 82 Complex Data Types 83

  34. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Nested Tables Nested Tables CREATE [OR REPLACE] TYPE < inner_type > AS OBJECT (...); CREATE TYPE Language_T AS OBJECT / ( Name VARCHAR2(50), < inner_table_type > AS CREATE [OR REPLACE] TYPE Percentage NUMBER ); TABLE OF < inner_type > ; / / CREATE TYPE Languages_list AS CREATE TABLE < table_name > TABLE OF Language_T; (... , / < inner_table_type > , < table-attr > CREATE TABLE NLanguage ... ) ( Country VARCHAR2(4), < table-attr > STORE AS NESTED TABLE < name > ; Languages Languages_list) NESTED TABLE Languages STORE AS Languages_nested; CREATE TYPE Language_T AS OBJECT ( Name VARCHAR2(50), Again: constructor methods Percentage NUMBER ); / CREATE TYPE Languages_list AS INSERT INTO NLanguage TABLE OF Language_T; VALUES( ’SK’, / Languages_list CREATE TABLE NLanguage ( Language_T(’Slovak’,95), ( Country VARCHAR2(4), Language_T(’Hungarian’,5))); Languages Languages_list) NESTED TABLE Languages STORE AS Languages_nested; Nested Tables 84 Nested Tables 85

  35. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Querying Contents of Nested Tables Nested Tables Contents of inner tables: SELECT * < table-attr > FROM ...) THE (SELECT FROM NLanguage WHERE Country=’CH’; SELECT ... FROM THE ( < select-statement > ) WHERE ... ; Country Languages(Name, Percentage) INSERT INTO THE ( < select-statement > ) CH Languages_List(Language_T(’French’, 18), VALUES ... / SELECT ... ; Language_T(’German’, 65), Language_T(’Italian’, 12), DELETE FROM THE ( < select-statement > ) WHERE ... ; Language_T(’Romansch’, 1)) SELECT Name, Percentage SELECT Languages FROM THE( SELECT Languages FROM NLanguage FROM NLanguage WHERE Country=’CH’; WHERE Country=’CH’); Languages(Name, Percentage) Name Percentage German 65 Languages_List(Language_T(’French’, 18), French 18 Language_T(’German’, 65), Italian 12 Language_T(’Italian’, 12), Romansch 1 Language_T(’Romansch’, 1)) Nested Tables 86 Nested Tables 87

  36. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Copying Nested Tables Nested tables can be inserted “as a whole” if the set of tuples is structured (casted) as a collection: Nested Tables CAST(MULTISET(SELECT ...) AS < nested-table-type > ) INSERT INTO NLanguage -- allowed, but wrong !!!! (SELECT Country, CAST(MULTISET(SELECT Name, Percentage If a query already results in a table, this can be inserted as a FROM Language whole: WHERE Country = A.Country) AS Languages_List) FROM Language A); INSERT INTO < table > each tuple (country, languageList) n -times VALUES (..., THE ( SELECT < attr > ( n = number of languages in this country) !! FROM < table’ > INSERT INTO NLanguage (Country) WHERE ...) ); (SELECT DISTINCT Country FROM Language); UPDATE NLanguage B SET Languages = INSERT INTO NLanguage VALUES CAST(MULTISET(SELECT Name, Percentage (’CHXX’, THE (SELECT Languages from NLanguage FROM Language A WHERE Country=’CH’)); WHERE B.Country = A.Country) AS Languages_List); Nested Tables 88 Nested Tables 89

  37. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Working with Nested Tables TABLE ([ < table > .] < attr > ) can be used in Subquery : Working with Nested Tables SELECT Country FROM NLanguage Not too simple ... (O RACLE 8.0) WHERE EXISTS (SELECT * • Subquery may only return a single nested table. ⇒ not FROM TABLE (Languages) -- to the current tuple possible to select an inner table, depending on the WHERE Name=’German’); surrounding tuple: All countries where german is spoken: Country SELECT Country -- NOT ALLOWED !!!! A FROM NLanguage A, B THE ( SELECT Languages CH FROM NLanguage B D WHERE B.Country=A.Country) WHERE Name=’German’); NAM But: Attributes of the inner table cannot be selected in the outer SELECT statement. ⇒ not possible to return the percentage of the languages in the corresponding countries. Nested Tables 90 Nested Tables 91

  38. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Working with Nested Tables SELECT Country, Name -- NOT ALLOWED !! Working with Nested Tables FROM NLanguage A, THE ( SELECT Languages CURSOR -Operator: FROM NLanguage B Example: WHERE B.Country=A.Country); SELECT Country, SELECT Country, Name CURSOR (SELECT * FROM NLanguage A, FROM TABLE (Languages)) THE ( SELECT Languages FROM NLanguage; FROM NLanguage B WHERE B.Country=A.Country) Country CURSOR(SELECT...) WHERE A.Country = ’CH’; -- now allowed. CH CURSOR STATEMENT : 2 NAME PERCENTAGE Using a table All_Languages that contains all languages: French 18 SELECT Country, Name German 65 FROM NLanguage, All_Languages WHERE Name IN Italian 12 (SELECT Name Romansch 1 FROM TABLE (Languages)); Conclusion: the domain of nested tables must be accessible in a single table. ⇒ Cursors etc. in PL/SQL. Nested Tables 92 Nested Tables 93

  39. Database Programming in SQL/ORACLE Complex Data Types SELECT * FROM USER_TYPES Type_name Type_oid Typecode Attributes Methods Pre Inc GeoCoord _ Object 2 0 NO NO Language_T _ Object 2 0 NO NO Languages_List _ Collection 0 0 NO NO Delete: DROP TYPE [FORCE] With FORCE , a datatype can be deleted whose definition is still needed by other types. Same scenario: DROP TYPE Language_T “Typ mit abh"angigen Typen oder tables kann nicht gel"oscht oder ersetzt werden” DROP TYPE Language_T FORCE deletes Language_T , but SQL> desc Languages_List; FEHLER: ORA-24372: Ung"ultiges Objekt f"ur Beschreibung Nested Tables 94 End of a Transaction For a long transaction, savepoints can be set: Safepoints Begin of a Transaction Transactions in ORACLE Transactions • process is killed. • User exits from O RACLE , • DDL statement (e.g. CREATE, DROP, RENAME, ALTER ), • ROLLBACK [TO • COMMIT statement: all changes become persistent, < savepoint > ] , SAVEPOINT SET TRANSACTION READ [ONLY | WRITE]; Database Programming in SQL/ORACLE < savepoint > ; < savepoint > ] undoes all changes [since 95

  40. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE PART II: This and That Referential Integrity – A First Look Part I: Basics • ER model and relational data model • if a table that contains columns that are defined as foreign • generation of a (relational) schema: CREATE TABLE keys by REFERENCES < table > ( < column-list > ) is • queries: SELECT – FROM – WHERE generated, < table > must be already defined, and < column-list > must already be declared as PRIMARY KEY . • working on the database: DELETE, UPDATE Part II: further topics on basic SQL • When tuples are inserted, the corresponding referenced tuples must already be present. • modifications of the database schema • When tuples are deleted, the referential integrity must be • referential integrity preserved. • view updates • tables and views are deleted with DROP TABLE or DROP • access control VIEW . • optimization • it is not possible to delete a table that still contains referenced tuples. Part III: prodecural concepts, OO, embedding • tables which are targets of a REFERENCES declaration can • PL/SQL: procedures, functions, triggers < table > CASCADE be deleted by DROP TABLE • object-orientation CONSTRAINTS . • Embedded SQL, JDBC • nested tables do not support referential integrity. Referential Integrity 96 Modifying the Database Schema 97

  41. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Modification of Table Schemata Modification of Schema Objects • ALTER TABLE • add columns and conditions, • CREATE statement • change conditions, • ALTER statement • delete, deactivate, and reactivate conditions. • DROP statement ALTER TABLE < table > ADD ( < add-clause > ) • TABLE MODIFY ( < modify-clause > ) • VIEW DROP < drop-clause > . . • TYPE . DROP < drop-clause > • INDEX DISABLE < disable-clause > • ROLE . . . • PROCEDURE DISABLE < disable-clause > ENABLE < enable-clause > • TRIGGER . . . . . . ENABLE < enable-clause > ; Modifying the Database Schema 98 Modification of Table Schemata 99

  42. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Adding Table Conditions Adding Columns to Tables ALTER TABLE < table > ADD ( < ... add some columns ... > , ALTER TABLE < table > < tableConstraint > , < datatype > [DEFAULT ADD ( < col > < value > ] . [ < colConstraint > ... . < colConstraint > ], . . . < tableConstraint > ) . MODIFY ( < modify-clause > ) < datatype > [DEFAULT < col > < value > ] DROP < drop-clause > [ < colConstraint > ... < colConstraint > ], ... ; < add table constraints > ...) MODIFY ( < modify-clause > ) Add an assertion that the sum of the percentages of industry, DROP < drop-clause > service and agriculture of the GDP is at most 100%: ... ; ALTER TABLE Economy New columns are filled with NULL values. ADD (Unemployment NUMBER CHECK (Unemployment > 0), CHECK (Industry + Service + Agriculture <= 100)); Beispiel: The relation economy is extended with a column unemployment : • if a condition is added that does not hold in the current database state, an error message is returned. ALTER TABLE Economy ADD (Unemployment NUMBER CHECK (Unemployment > 0)); ALTER TABLE City ADD (CONSTRAINT citypop CHECK (Population > 100000)); Modification of Table Schemata 100 Modification of Table Schemata 101

  43. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE ALTER TABLE ... DROP/DISABLE/ENABLE Modify Column Definitions of a Table • (Integrity)constraints on a table • column conditions can be added by ALTER TABLE ... ADD . - delete, ALTER TABLE < table > - deactivate for some time, ADD ( < add-clause > ) - reactivate. MODIFY ( < col > [ < datatype > ] [DEFAULT < value > ] ALTER TABLE < table > [ < colConstraint > ... < colConstraint > ], . ADD ( < add-clause > ) . . MODIFY ( < modify-clause > ) < col > [ < datatype > ] [DEFAULT < value > ] DROP PRIMARY KEY [CASCADE] | [ < colConstraint > ... < colConstraint > ]) UNIQUE ( < column-list > ) | DROP < drop-clause > CONSTRAINT < constraint > ... ; DISABLE PRIMARY KEY [CASCADE] | • for < colConstraint > , only NULL and NOT NULL are allowed UNIQUE ( < column-list > ) | here. < constraint > | ALL TRIGGERS CONSTRAINT All other conditions must be added by ALTER TABLE ... ENABLE PRIMARY KEY | UNIQUE ( < column-list > ) | ADD ( < tableConstraint > ) . < constraint > | ALL TRIGGERS; CONSTRAINT ALTER TABLE Country MODIFY (Capital NOT NULL); • PRIMARY KEY must not be deleted/disabled as long as there ALTER TABLE encompasses is a REFERENCES declaration to it. ADD (PRIMARY KEY (Country,Continent)); ALTER TABLE Desert • DROP PRIMARY KEY CASCADE deletes/disables ADD (CONSTRAINT DesertArea CHECK (Area > 10)); corresponding REFERENCES declarations. • Error message, if a condition is added that is not satisfied • ENABLE : if some constraints have been disabled in the current database state. cascadingly, they must be reactivated manually. Modification of Table Schemata 102 Modification of Table Schemata 103

  44. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Referential Integrity Referential Integrity Referential integrity occur when in the transformation from the ER model to the relational model, key attributes of entities are Country incorporated into the relationship tables (correspondence Name Code Capital Province between primary and foreign keys): Germany D Berlin Berlin Europe R United States USA Washington Distr. Columbia code name . . . . . . . . . . . . encompasses continent Country City percent Name Country Province CREATE TABLE Country 20 Berlin D Berlin (Name VARCHAR2(32), Washington USA Distr. Columbia Code VARCHAR2(4) PRIMARY KEY, . . . . . . . . . ...); CREATE TABLE Continent FOREIGN KEY ( < attr-list > ) (Name VARCHAR2(10) PRIMARY KEY, < table’ > ( < attr-list’ > ) REFERENCES Area NUMBER(2)); • ( < attr-list’ > ) must be a candidate key of the referenced CREATE TABLE encompasses table. (Continent VARCHAR2(10) REFERENCES Continent(Name), Country VARCHAR2(4) REFERENCES Country(Code), • in O RACLE : must be declared as primary key. Percentage NUMBER); Referential Integrity 104 Referential Integrity 105

  45. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Referential Actions Referential Integrity • if the contents of a table changes, actions are carried out for preserving referential integrity, • as column condition: • if this is not possible, the changes are not executed, or < attr > [CONSTRAINT < name > ] even undone. REFERENCES < table’ > ( < attr’ > ) 1. INSERT into a referenced table or DELETE from a referencing CREATE TABLE City table does not cause any problems: (... INSERT INTO Country Country VARCHAR2(4) VALUES (’Lummerland,’LU’,...); CONSTRAINT CityRefsCountry DELETE FROM is_member (’D’,’EU’); REFERENCES Country(Code) ); 2. INSERT or UPDATE in a referencing table must not generate • as table condition: foreign key values that do not exist in the referenced table: [CONSTRAINT < name > ] INSERT INTO City FOREIGN KEY ( < attr-list > ) VALUES (’Karl-Marx-Stadt’,’DDR’,...); REFERENCES < table’ > ( < attr-list’ > ) If the target key exists, there is no problem: CREATE TABLE Country UPDATE City SET Country=’A’ WHERE Name=’Munich’; (... 3. DELETE und UPDATE of the referenced table: it is useful to CONSTRAINT CapitalRefsCity adapt the referencing table by referential actions FOREIGN KEY (Capital,Code,Province) automatically: REFERENCES City(Name,Country,Province) ); UPDATE Country SET Code=’UK’ WHERE Code=’GB’; or DELETE FROM Country WHERE Code=’I’; Referential Integrity 106 Referential Integrity 107

  46. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Referential Actions in the SQL-2 Standard NO ACTION: The operation is executed; after execution, it is checked, whether “dangling references” occurred. If so, the Referential Actions in the SQL-2 Standard operation is undone: DELETE FROM River; distinguish between the reference River - River and located - River ! RESTRICT: SET DEFAULT: The operation is executed only if no “dangling references” the operation is executed and for all referenced tuples, the can occur: foreign key value is set to the specified DEFAULT values (for DELETE FROM Organization WHERE ...; which a corresponding tuple in the referenced relation must error message if an organization would be deleted that still exist). has some members. SET NULL: CASCADE: the operation is executed and for all referenced tuples, the The operation is executed. Referencing tuples are also foreign key value is set to the NULL value (for this, NULL deleted or modified. values must be allowed). UPDATE Country SET Code=’UK’ WHERE Code=’GB’; located : city is located as a river/sea/lake modifies also other tables: located(Bremerhaven,Nds.,D,Weser, NULL ,North Sea) Country: (United Kingdom,GB,. . . ) ❀ DELETE * FROM River WHERE Name=’Weser’; (United Kingdom,UK,. . . ) located(Bremerhaven,Nds.,D, NULL , NULL ,North Sea) Province:(Yorkshire,GB,. . . ) ❀ (Yorkshire,UK,. . . ) City: (London,GB,Greater London,. . . ) ❀ (London,UK,Greater London,. . . ) Referential Integrity 108 Referential Integrity 109

  47. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Referential Actions Referential Actions in the SQL-2-Standard Country Referential integrity constraints and referential actions are Name Code Capital Province specified with the CREATE TABLE or ALTER TABLE command as Germany D Berlin Berlin < columnConstraint > (for individual columns) United States USA Washington Distr. Columbia < col > < datatype > . . . . . . . . . . . . CONSTRAINT < name > < table’ > ( < attr’ > ) CASCADE REFERENCES [ ON DELETE {NO ACTION | RESTRICT | CASCADE | NO ACTION SET DEFAULT | SET NULL } ] [ ON UPDATE {NO ACTION | RESTRICT | CASCADE | SET DEFAULT | SET NULL } ] City or < tableConstraint > (for multiple columns) Name Country Province Berlin D Berlin CONSTRAINT < name > FOREIGN KEY ( < attr-list > ) Washington USA Distr. Columbia < table’ > ( < attr-list’ > ) REFERENCES . . . . . . . . . [ ON DELETE ...] [ ON UPDATE ...] 1. DELETE FROM City WHERE Name=’Berlin’; 2. DELETE FROM Country WHERE Name=’Germany’; Referential Integrity 110 Referential Integrity 111

  48. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Referential Actions in ORACLE Referential Actions: UPDATE without CASCADE • ORACLE 9: only ON DELETE/UPDATE NO ACTION , ON DELETE CASCADE , and ON DELETE SET NULL are Beispiel: Renaming of a country: implemented. CREATE TABLE Country • of no ON ... is specified, NO ACTION is used by default. ( Name VARCHAR2(32) NOT NULL UNIQUE, • ON UPDATE CASCADE is missing, which is cumbersome Code VARCHAR2(4) PRIMARY KEY); when applying updates. (’United Kingdom’,’GB’) • This has its reasons . . . CREATE TABLE Province ( Name VARCHAR2(32) Syntax as < columnConstraint > : Country VARCHAR2(4) CONSTRAINT ProvRefsCountry REFERENCES Country(Code)); CONSTRAINT < name > < table’ > ( < attr’ > ) REFERENCES (’Yorkshire’,’GB’) [ON DELETE CASCADE|ON DELETE SET NULL] Now, the country code should be changed from ’GB’ to ’UK’. Syntax as < tableConstraint > : • UPDATE Country SET Code=’UK’ WHERE Code=’GB’; CONSTRAINT < name > ❀ “dangling reference” of the old tuple (’Yorkshire’,’GB’). FOREIGN KEY [ ( < attr-list > )] • UPDATE Province SET Code=’UK’ WHERE Code=’GB’; < table’ > ( < attr-list’ > ) REFERENCES ❀ “dangling reference” of the new tuple (’Yorkshire’,’UK’). [ON DELETE CASCADE|ON DELETE SET NULL] Referential Integrity 112 Referential Integrity 113

  49. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Referential Integrity Constraints Referential Actions: UPDATE without CASCADE It is also possible to define a constraint with the table definition, and immediately disable it: • disable referential integrity constraint, CREATE TABLE < table > < datatype > [DEFAULT ( < col > < value > ] • apply updates, [ < colConstraint > ... < colConstraint > ], • reactivate referential integrity constraint: . . . < datatype > [DEFAULT < col > < value > ] [ < colConstraint > ... < colConstraint > ], ALTER TABLE Province [ < tableConstraint > ], . DISABLE CONSTRAINT ProvRefsCountry; . . UPDATE Country [ < tableConstraint > ]) SET Code=’UK’ WHERE Code=’GB’; DISABLE ... . . UPDATE Province . SET Country=’UK’ WHERE Country=’GB’; DISABLE ... ENABLE ... ALTER TABLE Province . . ENABLE CONSTRAINT ProvRefsCountry; . ENABLE ...; Referential Integrity 114 Referential Integrity 115

  50. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Referential Actions: Cyclic References Referential Actions: Problems with ON UPDATE Country Country Name Code Capital Province Name Code Capital Province Germany D Berlin Berlin Germany D Berlin Berlin United States US Washington Distr.Col. United States US Washington Distr.Col. . . . . . . . . . . . . . . . . . . . . . . . . CASCADE SET NULL Province Province Name Country Capital Name Country Capital Berlin D Berlin Berlin D Berlin Distr.Col. US Washington Distr.Col. US Washington . . . . . . . . . . . . . . . . . . CASCADE City City Name Country Province Name Country Province DELETE FROM Country Berlin D B Berlin D B WHERE Code=’D’ Washington USA Distr.Col. Washington USA Distr.Col. . . . . . . . . . . . . . . . . . . Referential Integrity 116 Referential Integrity 117

  51. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Referential Actions General case: • already a single update may be ambiguous or even inconsistent when ON DELETE/UPDATE SET NULL/SET Views DEFAULT and ON UPDATE CASCADE are allowed. • Combination with access permissions (later) • Due to SQL triggers an update often induces several other updates, • presentation of the actual database in a different form for some users. • non-trivial decision which updates should be triggered, • in case of inconsistencies, their origin must be analyzed, View Updates and maximal admissible subsets must be investigated, • stable models, exponential complexity. • must be mapped onto updates of the base relation(s), Investigations on this topic in the dbis group: • not always possible. • B. Ludäscher, W. May, and G. Lausen: Referential Actions • Table USER_UPDATABLE_COLUMNS in the Data Dictionary: as Logical Rules. In Proc. 16th ACM Symposium on < name > AS ... CREATE VIEW Principles of Database Systems , Tucson, Arizona, 1997. SELECT * FROM USER_UPDATABLE_COLUMNS • B. Ludäscher, W. May: Referential Actions: From Logical WHERE Table_Name = ’ < NAME > ’; Semantics to Implementation. In Proc. 6th Intl. Conf. on Extending Database Technologies , Valencia, Spain, 1998. • W. May, B. Ludäscher: Understanding the Global Semantics of Referential Actions using Logical Rules. In ACM Transactions on Database Systems , 27(4), 2002. Referential Integrity 118 View Updates 119

  52. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE View Updates View Updates Example: • derived values cannot be changed: CREATE VIEW CityCountry (City, Country) AS Example: SELECT City.Name, Country.Name CREATE OR REPLACE VIEW temp AS FROM City, Country SELECT Name, Code, Area, Population, WHERE City.Country = Country.Code; Population/Area AS Density SELECT * FROM USER_UPDATABLE_COLUMNS FROM Country; WHERE Table_Name = ’CITYCOUNTRY’; SELECT * FROM USER_UPDATABLE_COLUMNS WHERE Table_Name = ’TEMP’; Table_Name Column_Name UPD INS DEL CityCountry City yes yes yes Table_Name Column_Name UPD INS DEL CityCountry Country no no no temp Name yes yes yes • city names can be changed: temp Code yes yes yes direct mapping to City : temp Area yes yes yes UPDATE CityCountry temp Population yes yes yes SET City = ’Wien’ temp Density no no no WHERE City = ’Vienna’; INSERT INTO temp (Name, Code, Area, Population) SELECT * FROM City WHERE Country = ’A’; VALUES (’Lummerland’,’LU’,1,4) SELECT * FROM temp where Code = ’LU’; Name Country Province . . . • analogously for values that are computed by aggregate Wien A Vienna . . . . . . . functions ( COUNT , AVG , MAX , . . . ) . . . . . . . . View Updates 120 View Updates 121

  53. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE View Updates View Updates Example: • Country cannot be changed: • O RACLE : admissibility decided by heuristics, • based only on schema information, City Country • not on the current database state! Berlin Germany • key properties are important. Freiburg Germany • Key of a base table = key of the view: Mapping to base table would be ambiguous: obvious mapping possible and unambiguous. UPDATE CityCountry UPDATE CityCountry • key of a base table covers a key of the view: unambiguous SET Country = ’Poland’ SET Country = ’Deutschland’ translation, several tuples of the base table can be effected. WHERE City = ’Berlin’; WHERE Country = ’Germany’; • key of a base table does not cover any key of the view: in general, no translation possible (see exercises). DELETE FROM CityCountry DELETE FROM CityCountry WHERE City = ’Berlin’; WHERE Country = ’Germany’; View Updates 122 View Updates 123

  54. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE View Updates Example: CREATE OR REPLACE VIEW temp AS View Updates SELECT country, population • Tuples can drop out of the view definition, FROM Province A WHERE population = (SELECT MAX(population) • this can be prevented by the WITH CHECK OPTION : FROM Province B Beispiel WHERE A.Country = B.Country); CREATE OR REPLACE VIEW UScities AS SELECT * FROM temp WHERE Country = ’D’; SELECT * FROM City Country Name Population WHERE Country = ’USA’ D Nordrhein-Westfalen 17816079 WITH CHECK OPTION; UPDATE temp UPDATE UScities SET population = 0 where Country = ’D’; SET Country = ’D’ WHERE Name = ’Miami’; SELECT * FROM Province WHERE Name = ’D’; FEHLER in Zeile 1: Result: the population of the province with the highest ORA-01402: Verletzung der WHERE clause population in Germany is set to 0. Thus, the view changes! einer View WITH CHECK OPTION SELECT * FROM temp WHERE Country = ’D’; • it is allowed to delete tuples from the view/base relation. Country Name Population D Bayern 11921944 View Updates 124 View Updates 125

  55. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE User Authentification • user name • password Materialized Views • sqlplus / : authorization via UNIX account • Views are computed from scratch for every query. + always represent the current database state. Access Permissions inside ORACLE - time-consuming, inefficient if the data changes only • access permissions associated to the ORACLE account seldom. • initially defined by the DBA ⇒ Materialized Views • are computed at definition time, and Schema Concept • are updated whenever base relations change (e.g., by triggers ). • each user is assigned an own database schema where his • ⇒ problems of view maintenance . objects are located. • global addressing of tables by < username > . < table > (e.g. dbis.City ), • in the own schema by < table > . View Updates 126 Access Permissions 127

  56. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE System Privileges System Privileges GRANT < privilege-list > • entitle for schema operations < user-list > | PUBLIC [ WITH ADMIN OPTION ]; TO • PUBLIC : every user gets a permission • CREATE [ANY] TABLE/VIEW/TYPE/INDEX/CLUSTER/TRIGGER/PROCEDURE : • ADMIN OPTION : the grantee is allowed to grant this user is allowed to generate schema objects of these types, permission to other users. • ALTER [ANY] TABLE/TYPE/TRIGGER/PROCEDURE : Revoke permissions: user is allowed to change schema objects of these types, < privilege-list > | ALL REVOKE • DROP [ANY] < user-list > | PUBLIC; FROM TABLE/VIEW/TYPE/INDEX/CLUSTER/TRIGGER/PROCEDURE : only if the user has granted this permission (cascading in the user is allowed to delete schema objects of these types, case of ADMIN OPTION ). • SELECT/INSERT/UPDATE/DELETE [ANY] TABLE : Examples: user is allowed to read/create/change/delete tuples from tables. • GRANT CREATE ANY INDEX, DROP ANY INDEX TO opti-person WITH ADMIN OPTION; • ANY : operation is allowed in all schemas, allows opti-person to create and delete indexes • without ANY : operation is allowed only in the own schema. everywhere, In this course: • GRANT DROP ANY TABLE TO destroyer; GRANT SELECT ANY TABLE TO supervisor; • CREATE SESSION, ALTER SESSION, CREATE TABLE, CREATE VIEW, CREATE SYNONYM, CREATE CLUSTER . • REVOKE CREATE TABLE FROM clueless; • permissions for accessing and changing the own tables are Informations about access permissions in the data dictionary: not mentioned explicitly ( SELECT TABLE ). SELECT * FROM SESSION_PRIVS; Access Permissions 128 Access Permissions 129

  57. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Object Privileges Object Privileges Revoke permissions: allow for executing operations to existing schema objects. < privilege-list > | ALL REVOKE ON < object > • owner of a database object < user-list > | PUBLIC FROM • nobody else is allowed to use this object, except [CASCADE CONSTRAINTS]; • owner (or DBA) explicitly grants him some permissions: • CASCADE CONSTRAINTS (bei REFERENCES ): all referential < privilege-list > | ALL [( < column-list > )] GRANT integrity constraints, that are based on the revoked ON < object > REFERENCES privilege are dropped. < user-list > | PUBLIC TO • in case that a permission is obtained from several users, it [ WITH GRANT OPTION ]; is dropped with the last REVOKE . • < object > : TABLE , VIEW , PROCEDURE/FUNCTION , TYPE , • in case of GRANT OPTION , the revocation also cascades. • tables and views: detailed specification for INSERT , Granted and obtained permissions are stored in the Data REFERENCES , and UPDATE by < column-list > , Dictionary: • < privilege-list > : DELETE , INSERT , SELECT , UPDATE SELECT * FROM USER_TAB_PRIVS; for tables and views, • permissions that one has granted for the own tables, INDEX , ALTER , and REFERENCES for tables, EXECUTE for procedures, functions, and TYPE s. • permissions that one has obtained for other’s tables • ALL : all privileges that one has for the corresponding object. SELECT * FROM USER_COL_PRIVS; SELECT * FROM USER_TAB/COL_PRIVS_MADE/RECD; • GRANT OPTION : the grantee can grant the permission to other users. User roles are defined as prototypical patterns for maintaining permissions (e.g., student, dba, ...). Access Permissions 130 Access Permissions 131

  58. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Synonyms Access Restriction via Views • GRANT SELECT cannot be restricted to columns. Schema objects can be accessed under another name as originally stored: • instead: use a view. CREATE [PUBLIC] SYNONYM < synonym > FOR < schema > . < object > ; GRANT SELECT [ < column-list > ] – nicht erlaubt • Without PUBLIC : Synonym is defined only for its owner. ON < table > < user-list > | PUBLIC TO • PUBLIC creates system-wide synonyms. Only allowed if [ WITH GRANT OPTION ]; one has the CREATE ANY SYNONYM privilege. can be replaced by Example: A user often needs the relation “City” from the “dbis” schema. < view > AS CREATE VIEW • SELECT * FROM dbis.City; SELECT < column-list > • CREATE SYNONYM City FROM < table > ; FOR dbis.City; GRANT SELECT SELECT * FROM City; ON < view > < user-list > | PUBLIC TO [ WITH GRANT OPTION ]; Delete synonyms: DROP SYNONYM < synonym > ; Access Permissions 132 Access Permissions 133

  59. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Optimization of the Database Access Restrictions via Views: Example • minimize number of secondary storage accesses pol is owner of the relation Country , he wants to allow the user • keep as much data as possible in main memory geo to read and write Country without the Capital column (and the column that gives the province where the capital is located) Storage: • efficient access (search) to secondary memory − → access paths: indexes, hashing View with appropriate access permissions for geo : • try to access data that semantically belongs together with a CREATE VIEW pubCountry AS single access to secondary memory SELECT Name, Code, Population, Area − → Clustering FROM Country; GRANT SELECT, INSERT, DELETE, UPDATE Query optimization: ON pubCountry TO geo; • keep amount of data small • select early • References to views are not allowed. • internal optimization strategies < pol > : GRANT REFERENCES (Code) ON Country TO geo; < geo > : ... REFERENCES pol.Country(Code); Algorithmic optimization ! Access Permissions 134 Optimization 135

  60. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Access Paths: Indexes Access by using indexes over columns is much more efficient. Access Paths: Indexes • Trees; ORACLE: B ∗ -tree, • B ∗ -tree: nodes contain only the information for searching Access over indexed columns much more efficient: for a value, • fetch index nodes from secondary memory, • high degree, height of the tree is small. • access the node that contains the tuple 4 8 12 1 2 3 4 5 6 7 8 9 10 11 12 CREATE TABLE zip (City VARCHAR2(35) • searching by comparing keys: logarithmic effort. Country VARCHAR2(4) • fast access (logarithmic) versus higher effort for Province VARCHAR2(32) reorganization ( → algorithm theory), zip NUMBER) • multiple indexes on a table possible (over different attribute CREATE INDEX zipIndex ON zip (Country,zip); sets), SELECT * • having many indexes on a table table may lead to poor FROM zip performance for insertions, modifications, and deletions, WHERE zip = 79110 AND Country = ‘D’; • logically and physically independent from the data of the corresponding table, • no effect on the formulation of SQL statements, Optimization 136 Optimization 137

  61. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Hashing Clusters Depending on the value(s) of one or more columns ( hash key ), • collection of a group of tables which share one or more a hash function is computed which indicates where the columns (cluster key), or corresponding tuples are stored. • special case: grouping of a table depending on one or • access in constant time, more attributes. • no order of elements. • with a single secondary memory access, data that Example: semantically belongs together is fetched into main memory. • access to the information about a specific country Hash key: Country.Code Advantages of clustering: Hash key value • miminize the number of secondary memory access, Hash function • saves memory space since cluster key is stored only once. Disadvantages: F D NL • inefficient if cluster keys are updated frequently since this · · · 58317450 · · · 83536115 · · · 15568034 · · · requires a physical reorganization, 547030 356910 37330 • loss of performance when inserting into clustered tables. In ORACLE, hashing is implemented only for Clusters . Optimization 138 Optimization 139

  62. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Clustering Sea and geo_Sea with cluster key Sea.Name : Cl_Sea Mediterranean Sea Depth 5121 Clustering Province Country City by ( Province,Country ): Catalonia E Valencia E Country Province Murcia E D Nordrh.-Westf. City Population . . . Andalusia E Düsseldorf 572638 . . . Languedoc-R. F Solingen 165973 . . . Provence F . . . . USA Washington City Population . . . . . Seattle 524704 . . . Baltic Sea Depth Tacoma 179114 . . . 459 . . . . . . . . . . Province Country . . . . . Schleswig-H. D Mecklenb.-Vorp. D Szczecin PL . . . . . . Optimization 140 Optimization 141

  63. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Creating a Cluster in ORACLE Assigning tables to a cluster by CREATE TABLE , with specification of the cluster key. Creating a Cluster in ORACLE CREATE TABLE < table > ( < col > < datatype > , . . . Create cluster and declare cluster key: < col > < datatype > ) CLUSTER < cluster > ( < column-list > ); CREATE TABLE CSea CREATE CLUSTER < name > ( < col > < datatype > -list) (Name VARCHAR2(25) PRIMARY KEY, < integer > [HASH IS [INDEX | HASHKEYS < funktion > ]]; Depth NUMBER) CREATE CLUSTER Cl_Sea (SeaName VARCHAR2(25)); CLUSTER Cl_Sea (Name); CREATE TABLE Cgeo_Sea (Province VARCHAR2(32), Default: indexed Cluster , i.e., rows are indexed according to the Country VARCHAR2(4), cluster key. Sea VARCHAR2(25)) Optional: HASH , with specifying a hash function for the cluster CLUSTER Cl_Sea (Sea); key values. Creating the cluster key index: (must be done before the first DML command). < name > ON CLUSTER CREATE INDEX < cluster > ; ֒ → CREATE INDEX ClSeaInd ON CLUSTER Cl_Sea; Optimization 142 Optimization 143

  64. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Procedural Extensions: PL/SQL Block Structure of PL/SQL • no procedural concepts in SQL (loops, if, variables) • many tasks can only be performed awkwardly by using Block Header intermediate tables, or even impossible: IS – transitive closure Declaration Section • programs represent application-specific procedural BEGIN knowledge that is not contained in the database. Execution Section EXCEPTION Extensions Exception Section • embedding of SQL into procedural host languages END ; ( embedded SQL ); e.g., C, C++, or recently Java (JDBC), • extending SQL with procedural elements inside the SQL • block header: type of the object (function, procedure, or environment, PL/SQL ( Procedural language extensions to anonymous (inside another block)), and parameter SQL ). declarations, • advantages of PL/SQL: better integration of procedural • declaration section: declarations of variables, features into the database: procedures, functions, and • execution section: command sequence of the block, triggers. • exception section: reactions on errors. • required for object methods. PL/SQL 144 PL/SQL 145

  65. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Procedures Functions CREATE [OR REPLACE] PROCEDURE < proc_name > Analogously, additionally the result type is specified: [( < parameter-list > )] IS < pl/sql-body > ; CREATE [OR REPLACE] FUNCTION < funct_name > / [( < parameter-list > )] • OR REPLACE : if procedure definition already exists, it is RETURN < datatype > overwritten. IS < pl/sql body > ; / • ( < parameter-list > ) : declaration of formal parameters: • PL/SQL functions are left by ( < variable > [IN|OUT|IN OUT] < datatype > , . RETURN < expression > ; . . Each function must contain at least one RETURN statement < variable > [IN|OUT|IN OUT] < datatype > ) in its < body > . • IN , OUT , IN OUT : specify how the procedure/function uses • Functions must not have side effects. the parameter (read, write, both). Important: after the semicolon, a slash (“/”), must follow for • default: IN . executing the declaration!!! • in case of OUT and IN OUT , the argument must always be In case of “... created with compilation errors”: an variable, in case of IN , also constants are allowed. SHOW ERRORS; • < datatype > : all data types that are supported in PL/SQL; without length specification ( VARCHAR2 instead of gives a more detailed error description. VARCHAR2(20) ). Procedures and functions are deleted by < pl/sql-body > contains the definition of the procedure in • < name > . DROP PROCEDURE/FUNCTION PL/SQL. PL/SQL 146 PL/SQL 147

  66. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Example: Procedure • Simple procedure: PL/SQL-Body contains only SQL Procedures and Functions statements • Invocation of procedures in a PL/SQL body: < procedure > (arg1,...,argn) ; Information about countries is distributed over several relations. (if a formal parameter is declared as OUT or INOUT, the CREATE OR REPLACE PROCEDURE InsertCountry respective argument must be a variable) (name VARCHAR2, code VARCHAR2, area NUMBER, pop NUMBER, • Invocation of procedures in SQLPlus: gdp NUMBER, inflation NUMBER, pop_growth NUMBER) < procedure > (arg1,...,argn) ; execute IS BEGIN • Usage of functions in PL/SQL: < function > (arg1,...,argn) ... INSERT INTO Country (Name,Code,Area,Population) ... VALUES (name,code,area,pop); as in other programming languages. INSERT INTO Economy (Country,GDP,Inflation) VALUES (code,gdp,inflation); The system-owned table DUAL is commonly used for displaying INSERT INTO Population (Country,Population_Growth) thr return value of functions: VALUES (code,pop_growth); < function > (arg1,...,argn) SELECT END; FROM DUAL; / EXECUTE InsertCountry (’Lummerland’, ’LU’, 1, 4, 50, 0.5, 0.25); PL/SQL 148 PL/SQL 149

  67. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Example: Function PL/SQL-Variables and Data Types. • Simple function: population density of a country Declaration of the PL/SQL Variables in the declaration section: CREATE OR REPLACE FUNCTION Density (arg VARCHAR2) < datatype > [NOT NULL] [DEFAULT < variable > < value > ]; . RETURN number . . IS < datatype > [NOT NULL] [DEFAULT < variable > < value > ]; temp number; BEGIN SELECT Population/Area Simple data types: INTO temp BOOLEAN : TRUE , FALSE , NULL , FROM Country BINARY_INTEGER , PLS_INTEGER : Signed integers, WHERE code = arg; RETURN temp; NATURAL , INT , SMALLINT , REAL , . . . : Numerical data types. END; / amount NUMBER DEFAULT 0; SELECT Density(’D’) name VARCHAR2(30); FROM dual; PL/SQL 150 PL/SQL 151

  68. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Variable Assignment • “classical way” in the program: anchored Type Declaration a := b; By giving a PL/SQL variable or a table column (!) whose type • assigning a (single-column and single-row!) result of a should be used for a new variable: database query to a PL/SQL variable: SELECT ... < variable > < variable’ > %TYPE INTO < PL/SQL-Variable > [NOT NULL] [DEFAULT < value > ]; FROM ... or < variable > < table > . < col > %TYPE [NOT NULL] [DEFAULT < value > ]; Example: • cityname City.Name%TYPE the_name country.name%TYPE use the type of the Name column of the City table as the . . . datatype of the newly defined variable. SELECT name • %TYPE is detected at compile time. INTO the_name FROM country WHERE name=’Germany’; PL/SQL 152 PL/SQL 153

  69. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Assignment to Records PL/SQL Data Types: Records • Aggregate assignment: two variables of the same record A RECORD consists of several fields, corresponding to a tuple of type: the database: < variable > := < variable’ > ; TYPE city_type IS RECORD • assignment of a single field: (Name City.Name%TYPE, < record.field > := < variable > | < value > ; Country VARCHAR2(4), • SELECT INTO : result of a query that yields a single tuple: Province VARCHAR2(32), Population NUMBER, SELECT ... Longitude NUMBER, INTO < record-variable > Latitude NUMBER); FROM ... ; the_city city_type; the_country country%ROWTYPE . . . anchored Type Declaration for Records SELECT * INTO the_country Records can be declared using a table definition: %ROWTYPE : FROM country WHERE name=’Germany’; < variable > < table-name > %ROWTYPE; equivalent to the above example: Comparison of Records: the_city city%ROWTYPE; For comparing records, each field must be compared. PL/SQL 154 PL/SQL 155

  70. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE PL/SQL Data Types: PL/SQL Tables PL/SQL Data Types: PL/SQL Tables Array-like structure, a single column with an arbitrary datatype (including RECORD types), usually indexed by BINARY_INTEGER . PL/SQL tables provide built-in functions and procedures: < type > IS TABLE OF TYPE < datatype > < variable > := < pl/sql-table-name > . < built-in-function > ; [INDEX BY BINARY_INTEGER]; or < var > < type > ; < pl/sql-table-name > . < built-in-procedure > ; zip_table_type IS TABLE OF City.Name%TYPE • COUNT (fct): number of non-empty entries. INDEX BY BINARY_INTEGER; zip_table.count = 2 zip_table zip_table_type; • EXISTS (fct): TRUE is table non-empty. • Addressing: < var > (1) • DELETE (proc): deletes all entries of a table. zip_table(79110):= Freiburg; • FIRST/LAST (fct): lowest/highest used index. zip_table(33334):= Kassel; zip_table.first = 33334 • sparse : only those rows are stored that actually contain • NEXT/PRIOR(n) (fct): yields the next higher/lower used values. index value, starting from n . zip_table.next(33334) = 79110 Tables can also be assigned as a whole: other_table := zip_table; PL/SQL 156 PL/SQL 157

  71. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE SQL-Statements in PL/SQL Control Structures • DML-commands INSERT , UPDATE , DELETE , and SELECT • IF THEN - [ELSIF THEN] - [ELSE] - END IF , INTO statements. • several kinds of loops: • these SQL statements may also contain PL/SQL variables. • Simple LOOP : LOOP ... END LOOP; • commands that effect only a single tuple can assign teir • WHILE LOOP : results to PL/SQL variables by using RETURNING : < condition > LOOP ... END LOOP; WHILE UPDATE ... SET ... WHERE ... • Numeric FOR LOOP : RETURNING < expr-list > < loop_index > IN FOR INTO < variable-list > ; < from > .. [REVERSE] < to > E.g., return the row-ID of the affected tuple: LOOP ... END LOOP; The variable < loop_index > is declared automatically as DECLARE rowid ROWID; INTEGER . BEGIN . • EXIT [WHEN < condition > ] : leave LOOP . . . INSERT INTO Politics (Country,Independence) • the well-known GOTO statement with labels: VALUES (Code,SYSDATE) << label_i >> ... GOTO label_j; RETURNING ROWID • NULL values always lead into the ELSE branch. INTO rowid; • GOTO : it is not allowed to jump into an IF , a LOOP , or a local . . . block; also not from one IF branch into another. END; • after a label, an executable statement must follow; • DDL-Statements are not supported directly by PL/SQL: • NULL Statement (is executable). DBMS_SQL-Package. PL/SQL 158 PL/SQL 159

  72. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Cursor-Based Database Access Row-wise access to a relation from a PL/SQL program. Cursor declaration in the declaration section : < cursor-name > [( < parameter-list > )] CURSOR Nested Blocks IS < select-statement > ; Inside the execution section , anonymous blocks can be used for structuring. Here, the Declaration Section is introduced by • ( < parameter-list > ) : parameter list. DECLARE (there is no block header): • only IN allowed for parameter communication. BEGIN • between SELECT and FROM , PL/SQL variables and PL/SQL- -- statements of the outer block -- Functions are allowed. PL/SQL variables can also be used DECLARE in the WHERE , GROUP , and HAVING clauses. -- declarations of the inner block BEGIN -- statements of the inner block Example END; Compute all cities which are located in the country specified by -- statements of the outer block -- the variable the_country : END; DECLARE CURSOR cities_in (the_country Country.Code%TYPE) IS SELECT Name FROM City WHERE Country=the_country; PL/SQL 160 PL/SQL 161

  73. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Cursors < cursor-name > INTO • FETCH < record-variable > ; or < cursor-name > INTO FETCH < variable-list > ; moves the cursor to the next row of the result of the query and copies this row into the given record variable or Cursors variable list. • OPEN < cursor-name > [( < argument-list > )]; The variable can e.g. be declared with the record type of the cursor by using < cursor-name > %ROWTYPE : creates a virtual table for the result of the given SELECT statement and defines a “window” that is placed over one < variable > < cursor-name > %ROWTYPE; of the tuples and can be moved forwards stepwise. OPEN • CLOSE < cursor-name > ; closes the cursor. executes the query and initializes the cursor: OPEN cities_in (’D’); Example OPEN DECLARE CURSOR cities_in Name (the_country Country.Code%TYPE) FETCH Bonn IS SELECT Name FETCH FETCH FROM City Kiel FETCH WHERE Country=the_country; Hamburg . city_in cities_in%ROWTYPE; . . BEGIN OPEN cities_in (’D’); FETCH cities_in INTO city_in; CLOSE cities_in; END; PL/SQL 162 PL/SQL 163

  74. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Cursors not allowed: OPEN cities_in (’D’); Cursor FOR LOOP OPEN cities_in (’CH’); < record_index > IN FOR < cursor-name > FETCH cities_in INTO < variable > ; LOOP ... END LOOP; • one parameterized cursor, < record_index > is automatically declared as a variable of • • not a family of cursors! the type < cursor-name > %ROWTYPE , < record_index > is always of a record type (including • Cursors: Attributes one-column records). • OPEN is executed automatically. • < cursor-name > %ISOPEN : Cursor open? • for each execution of the loop body, FETCH is done • < cursor-name > %FOUND : as long as the preceding FETCH automatically , operation has been successful (i.e., the cursor has been • → loop body does not contain a FETCH statement, moved to a valid tuple), < cursor-name > %FOUND = TRUE . • at the end, CLOSE is also executed automatically, • < cursor-name > %NOTFOUND : TRUE if all rows of a cursor have been FETCH ed. • columns must be addressed explicitly. • < cursor-name > %ROWCOUNT : number of tuples that have already been read from the cursor. • not allowed inside SQL expressions. PL/SQL 164 PL/SQL 165

  75. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Cursor FOR LOOP Cursor FOR LOOP • SELECT statement can also be written directly into the FOR clause. Example: for every city in a given country, a procedure “request_Info” should be invoked: CREATE TABLE big_cities DECLARE CURSOR cities_in (name VARCHAR2(25)); (the_country country.Code%TYPE) IS SELECT Name BEGIN FROM City FOR the_city IN WHERE Country = the_country; SELECT Name FROM City BEGIN WHERE Country = the_country the_country:=’D’; % or something else AND Population > 1000000 FOR the_city IN cities_in(the_country) LOOP LOOP INSERT INTO big_cities request_Info(the_city.name); VALUES (the_city.Name); END LOOP; END LOOP; END; END; PL/SQL 166 PL/SQL 167

  76. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Access Permissions Writing on a Cursor Invocation permission for functions/procedures: < procedure/function > TO • GRANT EXECUTE ON < user > ; With WHERE CURRENT OF < cursor-name > , the most recently • procedures and functions are always executed with the FETCH ed tuple of < cursor-name > can be accessed: access permissions of the owner . UPDATE < table-name > • after SET < set_clause > WHERE CURRENT OF < cursor_name > ; < procedure/function > TO GRANT EXECUTE ON < user > ; the user can execute this procedure/function, even if he DELETE FROM < table-name > has no access permission for the tables that are used by WHERE CURRENT OF < cursor_name > ; the procedure. Note that the placement of the cursor over a base table tuple • possibility for defining access permissions that are more uniquely gives the position of the update (in contrast to View < table > TO ... : strict than GRANT ... ON Updates). access is allowed only in a special context that is defined by the procedure/function. PL/SQL 168 PL/SQL 169

  77. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Nested Tables under PL/SQL Nested Tables under PL/SQL CREATE TABLE tempCountries (Country VARCHAR2(4), Nested_Languages Language VARCHAR2(20), Country Languages Percentage NUMBER); D German 100 CREATE OR REPLACE PROCEDURE Search_Countries (the_Language IN VARCHAR2) CH German 65 IS CURSOR countries IS French 18 SELECT Code Italian 12 FROM Country; Romansch 1 BEGIN FL NULL DELETE FROM tempCountries; FOR the_country IN countries F French 100 LOOP . . . . . . INSERT INTO tempCountries SELECT the_country.code,Name,Percentage The use of nested tables in O RACLE causes some problems: “Give all countries where german is spoken, and give the FROM THE(SELECT Languages percentage of the german language in these countries” FROM Nested_Language WHERE Country = the_country.Code) Such a query has to search the inner table for every tuple in WHERE Name = the_Language; Nested_Languages . END LOOP; • SELECT THE returns only a single object, END; / • no correlation with the surrounding tuple. EXECUTE Search_Countries(’German’); • use a (Cursor) loop. SELECT * FROM tempCountries; PL/SQL 170 PL/SQL 171

  78. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Intermezzo: integrity constraints • column constraints and table constraints, • domain constraints, • prohobiting Null values, • uniqueness and primary key constraints, • CHECK -constraints, • Up to now: functions and procedures are explicitly called by the user. ! these are only conditions on a single row of a single table. • Triggers: invocation is caused by an event inside the database. Assertions • conditions that are concerned with the whole database state. < name > CHECK ( < condition > ) CREATE ASSERTION • not supported by O RACLE 8. ⇒ other solution? PL/SQL 172 PL/SQL 173

  79. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Trigger CREATE [OR REPLACE] TRIGGER < trigger-name > Trigger BEFORE | AFTER {INSERT | DELETE | UPDATE} [OF < column-list > ] • special form of PL/SQL procedures, [ OR {INSERT | DELETE | UPDATE} [OF < column-list > ]] • are invoked when a certain event takes place. . . . • Special case of active rules according to the [ OR {INSERT | DELETE | UPDATE} [OF < column-list > ]] E vent- C ondition- A ction paradigm. ON < table > • assigned to a table (often, to a certain column of this table). < name > NEW AS [REFERENCING OLD AS < name > ] [FOR EACH ROW] • invocation is caused by detection of some event in the table [WHEN ( < condition > )] (insertion, modification, or deletion of a row). < pl/sql-block > ; • execution also depends on a condition on the database • BEFORE , AFTER : trigger is invoked before/after the activating state. operation. • action: < column > (only for UPDATE ) restricts the activating event • OF • before or after execution of the activating statement to the specified column. • executed once per activating statement (statement trigger) • access to the fields of the tuple before and after executing or once for each effected row (Row-Trigger). the activating action by :OLD or :NEW . (Aliasing by REFERENCING OLD AS ... NEW AS ... ). • the body of the trigger can read the old and the new value of the tuple, Writing the :NEW values only with BEFORE triggers. • the body of the trigger can write the new value of the tuple . • FOR EACH ROW : row-Trigger, otherwise statement trigger. • WHEN ( < condition > ) : additional condition; OLD and NEW are allowed in < condition > . PL/SQL 174 PL/SQL 175

  80. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Trigger: Example Trigger: Example If a country code is changed, this modification is propagated to If a country is created, an entry in Politics is created with the the relation Province : current date: CREATE OR REPLACE TRIGGER change_Code CREATE TRIGGER new_Country BEFORE UPDATE OF Code ON Country AFTER INSERT ON Country FOR EACH ROW FOR EACH ROW BEGIN BEGIN UPDATE Province INSERT INTO Politics (Country,Independence) SET Country = :NEW.Code VALUES (:NEW.Code,SYSDATE); WHERE Country = :OLD.Code; END; END; / / INSERT INTO Country (Name,Code) UPDATE Country VALUES (’Lummerland’, ’LU’); SET Code = ’UK’ WHERE Code = ’GB’; SELECT * FROM Politics; PL/SQL 176 PL/SQL 177

  81. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Trigger: Mutating Tables INSTEAD OF Triggers • row-based trigger are always called immediately before/after changing the row • view updates : updates must be translated to base tables. • each invocation of the triggers sees another database state • view updating mechanisms are restricted. of the table on which it is defined, and of the tables which are changed by the trigger • INSTEAD OF -Trigger: modification of a view is replaced by other SQL statements. • ❀ result depends on the order of tuples . CREATE [OR REPLACE] TRIGGER < trigger-name > O RACLE : affected tables are marked as mutating during the INSTEAD OF whole action. They cannot be read by the trigger. {INSERT | DELETE | UPDATE} ON < view > Problem: a too strict criterion. < name > NEW AS [REFERENCING OLD AS < name > ] [FOR EACH STATEMENT] • if a trigger should access the table on which it is defined: < pl/sql-block > ; – only the activating tuple should be read/written by the trigger: Use a BEFORE trigger and the :NEW and :OLD • cannot be restricted to columns variables • no WHEN clause – additional tuples must be used: if possible, use a • Default: FOR EACH ROW statement trigger – otherwise, use auxiliary tables. PL/SQL 178 PL/SQL 179

  82. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE View Updates and INSTEAD OF Triggers CREATE OR REPLACE TRIGGER InsAllCountry View Updates and INSTEAD OF Triggers INSTEAD OF INSERT ON AllCountry FOR EACH ROW CREATE OR REPLACE VIEW AllCountry AS BEGIN SELECT Name, Code, Population, Area, INSERT INTO GDP, Population/Area AS Density, Country (Name,Code,Population,Area) Inflation, population_growth, VALUES (:NEW.Name, :NEW.Code, infant_mortality :NEW.Population, :NEW.Area); FROM Country, Economy, Population INSERT INTO Economy (Country,Inflation) WHERE Country.Code = Economy.Country VALUES (:NEW.Code, :NEW.Inflation); AND Country.Code = Population.Country; INSERT INTO Population (Country, Population_Growth,infant_mortality) INSERT INTO AllCountry VALUES (:NEW.Code, :NEW.Population_Growth, (Name, Code, Population, Area, GDP, :NEW.infant_mortality); Inflation, population_growth, infant_mortality) END; VALUES (’Lummerland’,’LU’,4,1,0.5,0,25,0); / Error message: "Uber ein Join-View kann nur eine Basistabelle • updates Country , Economy and Population . modifiziert werden. • trigger New_Country ( AFTER INSERT ON COUNTRY ) also updates Politics . PL/SQL 180 PL/SQL 181

  83. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Error Handling • Declaration Section: declaration (of names) of user-defined exceptions. Triggers/Error Handling: Example < exception > EXCEPTION; DECLARE • Exception Section: Definition of actions that have to be executed in case of an exception. WHEN < exception > THEN < PL/SQL-Statement > ; In the afternoon, it is not allowed to delete cities: WHEN OTHERS THEN < PL/SQL-Statement > ; CREATE OR REPLACE TRIGGER bla • Exceptions can be raised on arbitrary places on the BEFORE DELETE ON City PL/SQL block by the RAISE statement. BEGIN IF < condition > IF TO_CHAR(SYSDATE,’HH24:MI’) THEN RAISE < exception > ; BETWEEN ’12:00’ AND ’18:00’ THEN RAISE_APPLICATION_ERROR (-20101,’Unerlaubte Aktion’); END IF; Execution END; / • raise of an exception • execute the corresponding action in the WHEN • leave innermost block (use anonymous blocks) PL/SQL 182 PL/SQL 183

  84. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Example CREATE OR REPLACE TRIGGER bla INSTEAD OF INSERT ON AllCountry FOR EACH ROW Further PL/SQL Features BEGIN IF user=’may’ THEN NULL; END IF; • Packages : encapsulate data and programs; ... END; • FOR UPDATE option in cursor declarations; / • cursor variables ; • exception handlers ; INSERT INTO AllCountry (Name, Code, Population, Area, GDP, Inflation, • named parameter passing; population_growth, infant_mortality) • PL-SQL built-in functions: parsing, string operations, date VALUES (’Lummerland’,’LU’,4,1,0.5,0,25,0); operations, numerical functions; • built-in packages. 1 Zeile wurde erstellt. SQL> select * from allcountry where Code=’LU’; • definition of complex transactions, Es wurden keine Zeilen ausgewaehlt • usage of SAVEPOINT s for transactions, (from A. Christiansen, M. Höding, C. Rautenstrauch and G. Saake, O RACLE 8 effizient einsetzen, Addison-Wesley, 1998) PL/SQL 184 PL/SQL 185

  85. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Object Orientation • distinction between the state and behavior of an object . • in O RACLE 8: tables of tuples vs. object tables (which contain objects) Object-Relational Database Systems • in contrast to a tuple , an object has attributes (which describe its state) and methods (for querying and changing Integration of relational concepts and object orientation: its state). • complex data types: extend the domain concept of SQL-2 • type defines signature of a set of instances (objects) • abstract data types (“Object types”): object identity and • already mentioned: complex attribute types, having only encapsulation of internal functionality. value attributes , no methods. • specialization: class hierarchy; subtypes as specialization • methods: procedures and functions of more general types. • MAP/ORDER -function: order of instances of an object type • subtables. • columns in a relational table can be object-valued or • functions as parts of ADT’s or tables, or free functions. reference-valued . • method calls inside of SELECT statements • Objects: value attributes and reference attributes . • O RACLE 8: no subtypes, no inheritance. Type declaration: attributes, signatures of methods, READ/WRITE access characteristics. Type Body: implementation of the methods in PL/SQL. Object Orientation in O RACLE 8 186 Object Orientation in O RACLE 8 187

  86. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE PRAGMA Clauses: Object Type Declarations Read/Write Access Characteristics < type > AS OBJECT CREATE [OR REPLACE] TYPE ( < attr > < datatype > , < pragma-declaration-list > : . . . for every method, a PRAGMA clause is given: < attr > REF < object-datatype > , . . PRAGMA RESTRICT_REFERENCES . ( < method_name > , < feature-list > ); < func-name > [( < parameter-list > )] MEMBER FUNCTION RETURN < datatype > , < feature-list > : . . . WNDS Writes no database state, < proc-name > [( < parameter-list > )], MEMBER PROCEDURE Writes no package state, WNPS . . . RNDS Reads no database state, [ MAP MEMBER FUNCTION < func-name > Reads no package state. RNPS RETURN < datatype > , | ORDER MEMBER FUNCTION < func-name > ( < var > < type > ) Functions: are only executed if it is explicitly asserted that RETURN < datatype > ,] they do not change the database state: [ < pragma-declaration-list > ] PRAGMA RESTRICT_REFERENCES ); ( < function_name > , WNPS, WNDS); / MAP/ORDER functions: no database access allowed < parameter-list > as in PL/SQL, • PRAGMA RESTRICT_REFERENCES • similar to CREATE TABLE , but no integrity constraints (are ( < function-name > , WNDS, WNPS, RNPS, RNDS) done later with the definition of (object) tables) ⇒ uses only the state of the object itself. Object Orientation in O RACLE 8 188 Object Orientation in O RACLE 8 189

  87. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Example: Geo-Coordinates • method Distance (geo-coord-value) • MAP method: distance from Greenwich. CREATE OR REPLACE TYPE GeoCoord AS OBJECT (Longitude NUMBER, Type Body Latitude NUMBER, MEMBER FUNCTION • Implementation of object methods, Distance (other IN GeoCoord) • has to conform with the signature given for CREATE TYPE , RETURN NUMBER, • for all declared methods, an implementation must be given. MAP MEMBER FUNCTION Distance_Greenwich RETURN NUMBER, • variable SELF for accessing the attributes of the host object. PRAGMA RESTRICT_REFERENCES (Distance, WNPS, WNDS, RNPS, RNDS), PRAGMA RESTRICT_REFERENCES (Distance_Greenwich, WNPS, WNDS, RNPS, RNDS) ); / Object Orientation in O RACLE 8 190 Object Orientation in O RACLE 8 191

  88. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Type Body CREATE [OR REPLACE] TYPE BODY < type > AS < func-name > [( < parameter-list > )] MEMBER FUNCTION RETURN < datatype > IS Object Creation [ < var-decl-list > ;] < PL/SQL-code > END; BEGIN • Constructor method: . . < type > ( < arg_1 > , ..., < arg_n > ) . < proc-name > [( < parameter-list > )] MEMBER PROCEDURE IS [ < var-decl-list > ;] Method Invocation < PL/SQL-code > END; BEGIN . . . (from a PL/SQL program) [MAP MEMBER FUNCTION < func-name > < object > . < method-name > ( < argument-list > ) < datatype > | RETURN using SELF , < object > can invoke its own methods. ORDER MEMBER FUNCTION < func-name > ( < var > < type > ) RETURN < datatype > IS [ < var-decl-list > ;] < PL/SQL-code > END;] BEGIN END; / Object Orientation in O RACLE 8 192 Object Orientation in O RACLE 8 193

  89. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Example: Geo-Coordinates Column Objects CREATE OR REPLACE TYPE BODY GeoCoord • Attribute of a tuple (or of an object) can be object-valued, AS • no OID, i.e., not referencable. MEMBER FUNCTION Distance (other IN GeoCoord) RETURN NUMBER Example: Geo-Coordinates IS BEGIN CREATE TABLE Mountain RETURN 6370 * ACOS(COS(SELF.latitude/180*3.14) (Name VARCHAR2(20) CONSTRAINT MountainKey PRIMARY KEY, * COS(other.latitude/180*3.14) Height NUMBER CONSTRAINT MountainHeight * COS((SELF.longitude - CHECK (Height >= 0), other.longitude)/180*3.14) Coordinates GeoCoord CONSTRAINT MountainCoord + SIN(SELF.latitude/180*3.14) CHECK ((Coordinates.Longitude >= -180) AND * SIN(other.latitude/180*3.14)); (Coordinates.Longitude <= 180) AND END; (Coordinates.Latitude >= -90) AND (Coordinates.Latitude <= 90))); MAP MEMBER FUNCTION Distance_Greenwich • Constraints are given as usual with the table definition: RETURN NUMBER IS INSERT INTO Mountain BEGIN VALUES (’Feldberg’, 1493, GeoCoord(8, 48)); RETURN SELF.Distance(GeoCoord(0, 51)); SELECT Name, mt.coordinates.distance(geocoord(0, 90)) END; FROM Mountain mt; • use the tuple-variable mt for disambiguating the navigation END; path to coordinates.distance . / Object Orientation in O RACLE 8 194 Object Orientation in O RACLE 8 195

  90. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Row Objects Example: City_Type Row Objects CREATE OR REPLACE TYPE City_Type AS OBJECT • elements of Object tables , (Name VARCHAR2(35), Province VARCHAR2(32), • have a unique OID and are referencable. Country VARCHAR2(4), • OID corresponds to the primary key and is specified Population NUMBER, together with (further) integrity constraints in the table Coordinates GeoCoord, definition. MEMBER FUNCTION Distance (other IN City_Type) • seamless combination with referential integrity constraints RETURN NUMBER, from object tables to existing relational tables. PRAGMA RESTRICT_REFERENCES (Distance, WNPS, WNDS, RNPS, RNDS)); < name > OF CREATE TABLE < object-datatype > / [( < constraint-list > )]; CREATE OR REPLACE TYPE BODY City_Type < constraint-list > : AS MEMBER FUNCTION Distance (other IN City_Type) • attribute constraints correspond to column constraints: RETURN NUMBER < attr-name > [DEFAULT IS < value > ] [ < colConstraint > ... BEGIN < colConstraint > ] RETURN SELF.coordinates.distance(other.coordinates); • table constraints: syntax as for relational tables. END; END; / Object Orientation in O RACLE 8 196 Object Orientation in O RACLE 8 197

  91. Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Using Objects Object Tables: Row Objects • select a row object as a whole , • the (multi-column) primary key is specified as a table VALUE ( < var > ) condition, in combination with aliasing • primary key must not contain reference attributes, FROM < table > < var > • the foreign key constraint to the relational table Country is • e.g. for a comparison or in an ORDER BY clause. also specified as a table condition: Example CREATE TABLE City_ObjTab OF City_Type (PRIMARY KEY (Name, Province, Country), SELECT VALUE(cty) FOREIGN KEY (Country) REFERENCES Country(Code)); FROM City_ObjTab cty; • Objects are inserted into object tables by using the object VALUE (Cty)(Name, Province, Country, Population, constructor < object-datatype > : Coordinates(Longitude, Latitude)) INSERT INTO City_ObjTab City_Type(’Berlin’, ’Berlin’, ’D’, 3472009, GeoCoord(13, 52)) SELECT City_Type City_Type(’Bonn’, ’Nordrh.-Westf., ’D’, 293072, GeoCoord(8, 50)) (Name, Province, Country, Population, City_Type(’Stuttgart’, ’Baden-Wuertt., ’D’, 588482, GeoCoord(9, 49)) GeoCoord(Longitude, Latitude)) . . . FROM City WHERE Country = ’D’ AND NOT Longitude IS NULL; Object Orientation in O RACLE 8 198 Object Orientation in O RACLE 8 199

Recommend

DATABASE SYSTEMS Database programming in a web environment Database System Course AGENDA FOR

DATABASE SYSTEMS Database programming in a web environment Database System Course AGENDA FOR

DATABASE SYSTEMS Database programming in a web environment Database System Course AGENDA FOR TODAY The final project Advanced Mysql Database programming Recap: DB servers in the web Web programming architecture HTTP on a need-to-know basis.

688 views • 54 slides
DATABASE SYSTEMS Database programming in a web environment Database System Course, 2016-2017

DATABASE SYSTEMS Database programming in a web environment Database System Course, 2016-2017

DATABASE SYSTEMS Database programming in a web environment Database System Course, 2016-2017 AGENDA FOR TODAY The final project Advanced Mysql Database programming Recap: DB servers in the web Web programming architecture HTTP on a

845 views • 53 slides
Database Programming in SQL/O RACLE The Database: M ONDIAL Continents Wolfgang May

Database Programming in SQL/O RACLE The Database: M ONDIAL Continents Wolfgang May

Database Programming in SQL/ORACLE Database Programming in SQL/O RACLE The Database: M ONDIAL Continents Wolfgang May Countries Mountains Economy Administrative Rivers Population Divisions Lakes Languages

992 views • 74 slides
Project # 1: Database Programming CSE462 Database Concepts Demian Lessa Department of Computer

Project # 1: Database Programming CSE462 Database Concepts Demian Lessa Department of Computer

Project # 1: Database Programming CSE462 Database Concepts Demian Lessa Department of Computer Science and Engineering State University of New York, Buffalo February 21, 2011 Outline Database Programming 1 Database Application Design 2

406 views • 29 slides
DATABASE SYSTEMS The very basics of Web programming Database Systems Course BEFORE WE START

DATABASE SYSTEMS The very basics of Web programming Database Systems Course BEFORE WE START

DATABASE SYSTEMS The very basics of Web programming Database Systems Course BEFORE WE START This lecture is an overview of the very complicated world of web programming. If you are a (very) experienced web developer - take a 2.5hr break

1.43k views • 74 slides
DATABASE SYSTEMS Introduction to web programming Database Systems Course, 2016 AGENDA FOR TODAY

DATABASE SYSTEMS Introduction to web programming Database Systems Course, 2016 AGENDA FOR TODAY

DATABASE SYSTEMS Introduction to web programming Database Systems Course, 2016 AGENDA FOR TODAY Client side programming HTML CSS Javascript Server side programming: PHP Installing a local web-server Basic PHP usage DB programming with PHP

1.04k views • 63 slides
Database Access via Programming Languages 5DV119 Introduction to Database Management Ume a

Database Access via Programming Languages 5DV119 Introduction to Database Management Ume a

Database Access via Programming Languages 5DV119 Introduction to Database Management Ume a University Department of Computing Science Stephen J. Hegner hegner@cs.umu.se http://www.cs.umu.se/~hegner Database Access via Programming

348 views • 20 slides
Database Programming with Ada Ada Europe 2010, Valencia Frank Piron, frank.piron@konad.de KonAd

Database Programming with Ada Ada Europe 2010, Valencia Frank Piron, frank.piron@konad.de KonAd

Database Programming with Ada Ada Europe 2010, Valencia Frank Piron, frank.piron@konad.de KonAd GmbH, In der Reis 5, D-79232 March-Buchheim Content Why Ada for Database Programming? The Database Access Library The User Interface

671 views • 27 slides
CS 61: Database Systems Access via programming languages Agenda 1. Direct database access 2.

CS 61: Database Systems Access via programming languages Agenda 1. Direct database access 2.

CS 61: Database Systems Access via programming languages Agenda 1. Direct database access 2. Web APIs 3. Node.js 2 Python can directly query the database Steps to access MySQL from Python 1. Install connector to MySQL: sudo pip install

578 views • 14 slides
Database Access via Programming Languages SQL is a direct query langu age; as such, it has

Database Access via Programming Languages SQL is a direct query langu age; as such, it has

Database Access via Programming Languages SQL is a direct query langu age; as such, it has limitations. Some reasons wh y access to databases via programming languag es is needed : Complex computational processing of the data.

690 views • 9 slides
Database Design and Programming Peter Schneider-Kamp DM 505, Spring 2012, 3 rd Quarter 1 Course

Database Design and Programming Peter Schneider-Kamp DM 505, Spring 2012, 3 rd Quarter 1 Course

Database Design and Programming Peter Schneider-Kamp DM 505, Spring 2012, 3 rd Quarter 1 Course Organisation Literature Database Systems: The Complete Book Evaluation Project and 1-day take-home exam, 7 scale

504 views • 33 slides
5. Technology Applications 5.1 What is a Database? 5.2 Types of Databases 5.3 Choosing the

5. Technology Applications 5.1 What is a Database? 5.2 Types of Databases 5.3 Choosing the

5. Technology Applications 5.1 What is a Database? 5.2 Types of Databases 5.3 Choosing the Right Database 5.4 Database Programming Tools 5.5 How to Search Your Database 5.6 Data Warehousing and Mining 5.7 Enterprise-Wide Data Systems

1.14k views • 88 slides
ECE 650 Systems Programming &amp; Engineering Spring 2018 PostgreSQL Database and C++ Interface

ECE 650 Systems Programming &amp; Engineering Spring 2018 PostgreSQL Database and C++ Interface

ECE 650 Systems Programming &amp; Engineering Spring 2018 PostgreSQL Database and C++ Interface Tyler Bletsch Duke University Slides are adapted from Brian Rogers (Duke) PostgreSQL Also called Postgres Open source relational database

327 views • 11 slides
Database Programming Joan Boone jpboone@email.unc.edu Summer 2020 Slide 1 Topics Part 1

Database Programming Joan Boone jpboone@email.unc.edu Summer 2020 Slide 1 Topics Part 1

INLS 560 Programming for Information Professionals Database Programming Joan Boone jpboone@email.unc.edu Summer 2020 Slide 1 Topics Part 1 Database overview Basic SQL Part 2 SQLite introduction DB Browser for SQLite

636 views • 39 slides
DB Programming - Cont Database Systems 1 Agenda SWT Updating the UI (Why do we need

DB Programming - Cont Database Systems 1 Agenda SWT Updating the UI (Why do we need

DB Programming - Cont Database Systems 1 Agenda SWT Updating the UI (Why do we need Threads?) 2 SWT (Standard Widget Toolkit) Developed by IBM, maintained today by Eclipse Easy implementation Not portable requires

569 views • 54 slides
Accelerating Tandem MS Protein Database Searches Using OpenCL Programming devices the

Accelerating Tandem MS Protein Database Searches Using OpenCL Programming devices the

Rick Weber, David D. Jenkins, Nicholas Lineback, Robert Hettich, Gregory D. Peterson Accelerating Tandem MS Protein Database Searches Using OpenCL Programming devices the intractable way Programming devices with OpenCL T andem MS/MS

721 views • 17 slides
Database Access via Programming Languages SQL is a direct query language; as such, it has

Database Access via Programming Languages SQL is a direct query language; as such, it has

Database Access via Programming Languages SQL is a direct query language; as such, it has limitations. Some reasons why access to databases via programming languages is needed : Complex computational processing of the data.

796 views • 9 slides
Database Design and Programming Peter Schneider-Kamp DM 505, Spring 2009, 3 rd Quarter 1 Course

Database Design and Programming Peter Schneider-Kamp DM 505, Spring 2009, 3 rd Quarter 1 Course

Database Design and Programming Peter Schneider-Kamp DM 505, Spring 2009, 3 rd Quarter 1 Course Organisation Literature Database Systems: The Complete Book Evaluation Project and 1-day take-home exam, 7 scale Project

978 views • 64 slides
The Database as a Value Rich Hickey What is Datomic? A functional database A sound model

The Database as a Value Rich Hickey What is Datomic? A functional database A sound model

The Database as a Value Rich Hickey What is Datomic? A functional database A sound model of information, with time Provides database as a value to applications Bring declarative programming to applications Focus on reducing

688 views • 51 slides
Programming Styles and Objects Fermilab - TARGET 2018 Week 3 Programming styles Imperative

Programming Styles and Objects Fermilab - TARGET 2018 Week 3 Programming styles Imperative

Programming Styles and Objects Fermilab - TARGET 2018 Week 3 Programming styles Imperative programming - Procedural programming - Object oriented programming Declarative programming - Database languages (Structured Query Language) -

541 views • 11 slides
First-Class Functions For First-Order Database Engines Torsten Grust Alexander Ulrich Database

First-Class Functions For First-Order Database Engines Torsten Grust Alexander Ulrich Database

First-Class Functions For First-Order Database Engines Torsten Grust Alexander Ulrich Database Systems @ Universit at T ubingen DBPL 2013, August 30, 2013 1/25 Widely available and implemented database query and programming languages

457 views • 33 slides
Previous weeks Database-enabled web technology DB Programming Introductions on PHP

Previous weeks Database-enabled web technology DB Programming Introductions on PHP

Previously in this course . . . Previous weeks Database-enabled web technology DB Programming Introductions on PHP git Instructor: C a gr C oltekin A summary of DB design and SQL. c.coltekin@rug.nl An

345 views • 5 slides
Database Utilities 10/17/2007 DC/Win Database Utilities Opening Database Utilities From File on

Database Utilities 10/17/2007 DC/Win Database Utilities Opening Database Utilities From File on

Database Utilities 10/17/2007 DC/Win Database Utilities Opening Database Utilities From File on the Menu Bar DC/Win Database Utilities Options Vary Depending on Database Two Types of Databases Sequel (SQL) Database Access Database

460 views • 6 slides
A CLASSIC HORROR STORY Ease of Performance Programming THE PIT AND THE PENDULUM Lorenzo

A CLASSIC HORROR STORY Ease of Performance Programming THE PIT AND THE PENDULUM Lorenzo

A CLASSIC HORROR STORY Ease of Performance Programming THE PIT AND THE PENDULUM Lorenzo Alvisi Database Programmer Ease Ease of Performance of Performance Programming Programming Database Programmer Database

515 views • 27 slides

More recommend