Database Programming in SQL/O RACLE The Database: M ONDIAL - PDF document

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 2001 • Cities • Seas • Religions • Organizations • Deserts • Ethnic Groups • CIA World Factbook • “Global Statistics”: Countries, Adm. Divisions, Cities • TERRA-Database of the Institut für Programmstrukturen und Datenorganisation der Universit"at Karlsruhe • . . . some more Web-Pages • Data Integration has been done with FLORID Introduction 2 Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Literature • Textbooks on Databases (in german): SQL-3 Standard/ORACLE 8: A. Kemper, A. Eickler: Datenbanksysteme - Eine Einf"uhrung, Oldenbourg, 1996 • ER-Modeling G. Vossen: Datenmodelle, Datenbanksprachen und • Schema Generation Datenbankmanagement-Systeme. Addison-Wesley, 1994. • Queries • Textbook on SQL (in german): G. Matthiessen and M. Unterstein: Relationale • Views Datenbanken und SQL: Konzepte der Entwicklung und • Complex attributes, nested tables Anwendung. Addison-Wesley, 1997. • Database Optimization • The book on the practical DB training at Uni Karlsruhe with • Access Control/Authorization TERRA: M. Dürr and K. Radermacher: Einsatz von • Transactions Datenbanksystemen. Springer Verlag, 1990. • Updates, Schema Modifications • Explanation of the SQL-2 Standard: • Referential Integrity C. Date and H. Darwen: A guide to the SQL standard: a user’s guide to the standard relational language SQL. • PL/SQL: Triggers, Procedures, Functions Addison-Wesley, 1994. • Object-relational Features • Textbooks on relational databases and SQL: • Embedded SQL H. F. Korth and A. Silberschatz: Database System • JDBC (Embedding into Java) Concepts. McGraw-Hill, 1991. J. Ullman and J. Widom: A First Course in Database Systems. Prentice Hall, 1997. and some more ... Introduction 1 Introduction 3

Database Programming in SQL/ORACLE 4 Mountain Island Lake Structuring concepts for describing a database schema in the ERM: Entities Database Programming in SQL/ORACLE Desert River Entity type: An entity type represents a concept in the real Sea Entity Relationship Model (ERM; Chen, 1976) world. It is given as a pair ( E, { A 1 , . . . , A n } ) , where E is the name and { A 1 , . . . , A n } , n ≥ 0 are the attributes (value properties) of a type. Attribute: a relevant property of entities of a given type. Each attribute can have values from a given domain . Organization Ethnic Grp. Language Entity: each entity describes a real-world object. Thus, it must • Entity types ( ≡ Object types) and Religion be of one of the defined entity types E . It assigns a value to each attribute that is declared for the entity type E . Key attributes: a key is a set of attributes of an entity type, Semantic Modeling: whose values together allow for a unique identification of all • Relationship types amongst all entities of a given type (cf. candidate keys, primary keys ). Continent Province Country City ER-Model ER-Model 6 Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Entities: Germany Entities and Relationships 356910 D name area code ent.0815 federal republic is_capital 83536115 population government Country Province in_Prov City gross product inflation independence 2% 1.452.200.000 1871 is_capital belongs to Feldberg ent.4711 Black Forest Country encompasses Continent name Mountain mountains 7.5 1493.8 longitude height borders geo coord 47.5 latitude ER-Model 5 ER-Model 7

Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Relationships Relationship type: describes a concept of relationships between entities. It is given as a triple Complexities of relationships ( B, { RO 1 : E 1 , . . . , RO k : E k } , { A 1 , . . . , A n } ) , where B is the name, { RO 1 , . . . , RO k } , k ≥ 2 , is a list of roles , Every relationship type is assigned a complexity that specifies { E 1 , . . . , E k } is a list of entity types associated to the roles, the minimal and maximal number of relationships in which an and { A 1 , . . . , A n } , n ≥ 0 is the set of attributes of the entity of a given type may be involved. relationship type. The complexity degree of a relationship type B wrt. one of its Roles are pairwise different – the associated entity types are roles RO is an expression of the form ( min, max ) . not necessarily pairwise distinct. In case that E i = E j for i � = j , there is a recursive relationship. A set b of relationships satisfies the complexity degree ( min, max ) of a role RO if for all entities e of the corresponding Attribute: relevant properties of relationships of a given type. entity type, the following holds: there exist at least min and at Relationship: A relationship of a relationship type B is defined most max relationships b in which e is involved in the role RO . by the entities that are involved in the relationship, according to their associated roles. For each role, there is exactly one entity involved in the relationship, and every attribute is assigned a value. ER-Model 8 ER-Model 10 Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Relationships Relationships City in Country Freiburg Germany is_capital relationship with attributes < 1 , 1 > < 0 , ∗ > Province in_Prov City < 0 , ∗ > < 1 , ∗ > encompasses continent Country < 0 , 1 > < 1 , 1 > Europe Russia percent is_capital belongs to 20 relationship with roles < 1 , 1 > < 1 , ∗ > is_capital City is of Country Country encompasses Continent < 1 , ∗ > < 1 , ∗ > Berlin Germany < 0 , ∗ > < 0 , ∗ > recursive relationship borders main river River flows_into tributary river Rhein, Main ER-Model 9 ER-Model 11

Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE n-ary Relationships: A river flows into a sea/lake/river; more detailed, this point can be described by giving one or two countries. river sea flows into < 0 , n > < 0 , n > Weak Entity Types < 0 , n > A weak entity type is an entity type without a key. Country Thus their entities must be identified by the help of another entity. Aggregation: • Weak entity types must be involved in at least one n : 1 -relationship with a strong entity type (where the strong Useful to introduce an Aggregate type mouth : entity type stands on the 1-side). Mouth • They must have a local key, i.e., a set of attributes that can be extended by the primary keys of the corresponding river sea flows into < 0 , 1 > < 0 , n > strong entity type to provide a key for the weak entity type. < 1 , 2 > in < 0 , ∗ > Country ER-Model 12 ER-Model 14 Database Programming in SQL/ORACLE Database Programming in SQL/ORACLE Weak Entity Types area pop. 248678 61170500 name Country code BRD D ent_4711 < 0 , ∗ > Generalization/Specialization area pop. in • Generalization: rivers, lakes, and seas are waters . These 35751 10272069 can e.g. be involved in located-at relationships with cities: name Province Baden-W. ent_1997 name < 0 , ∗ > Water located City < 0 , ∗ > < 0 , ∗ > < 1 , 1 > in Prov. g < 1 , 1 > name pop. City River Lake Sea Freiburg 198496 ent_0815 latitude longitude length depth area depth area 48 7.8 There is also a Freiburg/CH and Freiburg/Elbe, LowerSaxonia (Niedersachsen) ER-Model 13 ER-Model 15