INTRODUCTION TO RELATIONAL DATABASE SYSTEMS DATENBANKSYSTEME 1 (INF - - PowerPoint PPT Presentation

INTRODUCTION TO RELATIONAL DATABASE SYSTEMS

DATENBANKSYSTEME 1 (INF 3131)

Torsten Grust Universität Tübingen Winter 2019/20

1

THE ENTITY-RELATIONSHIP MODEL

The Entity-Relationship Model (ER model) has been defined to concisely describe mini-worlds: ER describes objects (entities) and their relationships. Objects and relationships carry aributes that characterize them further. ER comes with a graphical notation (ER diagrams) that helps to establish a quick overview of complex mini-worlds. Also a great way to communicate models to domain experts/DB non- experts/future DB users. There are no ER model DBMSs. ER is also known as a semantic data model. Instead, ER diagrams are translated into the constructs of a concrete data model (e.g., relational

• r JSON) to generate a database schema.

While the ER model/diagram is being designed, the focus exclusively remains on mini-world

• aspects. Details of the implementing data model — e.g., types, constraints — only come into play

when the (automatic) translation is performed.

‐ ‐ ‐ ‐ ‐

2

ER: DIAGRAMS

Sample ER Diagram

3

ER CONSTRUCTS: ENTITIES, ENTITY TYPES

Entity and Entity Types Entities represent relevant mini-world objects to be held in the database. Examples: LEGO sets, bricks, earthquakes, calendars. Each entity has a specific entity type that defines its aributes. The mini-world can contain only a finite number of objects. Entities are distinguishable from one another, i.e., entities possess some form of identity. Notes: Entities do not have to correspond to objects of physical existence but may also represent conceptual objects like, for example, vacations. Entity identity is inherent, not based on (aribute) values. Upon translation, however, entity identity may often be implemented in terms of aribute values (e.g., through LEGO brick IDs, travel agency booking numbers).

‐

1. 2.

4

ER CONSTRUCTS: RELATIONSHIPS, RELATIONSHIP TYPES

Relationship and Relationship Type Relationships establish a connection between two entities (binary relation). One entity may participate in multiple relationships (or none). See cardinalities below. Each relationship has a specific relationship type that defines its aributes and the two (not necessarily different) participating entity types. Examples: “A LEGO set ‹entity type› contains ‹relationship type› one or more LEGO bricks ‹entity type›.” “An earthquake ‹entity type› occurs in ‹relationship type› one given geographical region ‹entity type›.” Relationship types may be viewed from the angle of both participating entity types: “A LEGO set contains one or more LEGO bricks.” — “A LEGO brick is contained in one or more LEGO sets.”

‐

5

ER CONSTRUCTS: ATTRIBUTES

Aributes Entity and relationship types may carry aributes to represent properties of entities and relationships. Aribute values may be of arbitrary data types (typically simple/atomic, e.g., string, number, date…). ER is first-order: aributes may not have values of type entity or

• relationship. Upon translation, the target data model deals with the representation of

aribute values. Selected aributes of an entity type may form a key (those aributes are underlined in ER diagrams): no two entities of the same entity type may feature the same key values. Key aributes exist in addition to entity identity and are helpful while translating an ER model into a target data model with value-based keys (e.g., relational).

‐

6

SAMPLE ER INSTANCE (MINI-WORLD STATE)

ER Diagram and Sample Instance

7

ER DIAGRAM SEMANTICS

ER Diagram Semantics The ER Diagram Semantics 𝔽ℝ interpret the symbols of an ER diagram by defining a finite set 𝔽ℝ(𝑓) (of entities) for every entity type 𝑓, a mapping 𝔽ℝ(𝘣) : 𝔽ℝ(𝑓) → 𝕎(𝘣) for every aribute 𝘣 of an entity type 𝑓 (if 𝘣 is a key aribute, the mapping is injective), a binary relation 𝔽ℝ(𝑠) ⊆ 𝔽ℝ(𝑓₁) × 𝔽ℝ(𝑓₂) for every relationship type 𝑠 between entity types 𝑓₁ and 𝑓₂, a mapping 𝔽ℝ(𝘣) : 𝔽ℝ(𝑠) → 𝕎(𝘣) for every aribute 𝘣 of a relationship type 𝑠. Recall that 𝕎(𝘣) denotes the set of admissable values for aribute 𝘣. 1. 2. 3. 4.

‐

8

ER DIAGRAM SEMANTICS

Example: The ER diagram semantics associated with the ER diagram and instance shown earlier defines: 𝔽ℝ(LEGO set) = {𝑡₁, 𝑡₂} 𝔽ℝ(LEGO brick) = {𝑐₁, 𝑐₂, 𝑐₃} 𝔽ℝ(set) = 𝑔₁, 𝔽ℝ(name) = 𝑔₂, 𝔽ℝ(brick) = 𝑔₃, 𝔽ℝ(weight) = 𝑔₄ with 𝑔₁(𝑡₁) = 9472 𝑔₁(𝑡₂) = 79004 𝑔₂(𝑡₁) = 'Weathertop' 𝑔₂(𝑡₂) = 'Barrel Escape' 𝑔₃(𝑐₁) = 2339 𝑔₃(𝑐₂) = 11010 𝑔₃(𝑐₃) = 30136 𝑔₄(𝑐₁) = 2.1 𝑔₄(𝑐₂) = 0.06 𝑔₄(𝑐₃) = 0.69 𝔽ℝ(contains) = {(𝑡₁,𝑐₁), (𝑡₁,𝑐₂), (𝑡₂,𝑐₂), (𝑡₂,𝑐₃)}

‐

1. 2. 3.

9

ER RELATIONSHIP CARDINALITIES

In general, there is no restriction on how often a given entity participates in a relationship . Specific application semantics, however, may dictate that participation is optional, mandatory, or that a minimum and/or maximum number of participations is required. Relationship Cardinality (min/max Notation)

∀ 𝑓 ∊ 𝔽ℝ(𝑓₁): 𝑛₁ ⩽ | { (𝑦,𝑧) ∊ 𝔽ℝ(r) | 𝑦 = e } | ⩽ 𝑜₁ ∀ 𝑓 ∊ 𝔽ℝ(𝑓₂): 𝑛₂ ⩽ | { (𝑦,𝑧) ∊ 𝔽ℝ(r) | 𝑧 = e } | ⩽ 𝑜₂

Notation: 𝑜ᵢ = * is interpreted as 𝑜ᵢ = ∞.

‐ ‐ ‐

10

ER DIAGRAM EXAMPLES

Sketch ER diagrams to model the following mini-worlds. Identify entities and relationships, then restrict relationship cardinalities as required by the application: “A man can be married to at most one woman and vice versa.” “An airport lies in exactly one country. A country may have arbitrarily many airports (and maybe none at all).” “Orders are placed by customers. Some customers might have not placed an order yet.” “An order can contain several products.” “In the Euclidean 2d plane, a line segment connects two points. A simple closed polygon is formed by a list of three or more line segments. The interior of a polygon is colored.”

‐

1. 2. 3. 4. 5.

11

ER: NOTES ON RELATIONSHIP CARDINALITIES

Cardinality (0,*) represents an unrestricted relationship. A cardinality restriction (𝑛₁,𝑜₁) is weaker than (𝑛₂,𝑜₂) if 𝑛₁ ⩽ 𝑛₂ and 𝑜₂ ⩽ 𝑜₁. Relationship cardinalities denote constraints and have to be translated as such into the target data model. If the target is relational, the important cardinalities (0,1), (1,1), and (0,*) can be directly enforced by relational constraints, e.g., through NOT NULL or keys. General cardinality constraints (𝑛,𝑜) may not translate (directly), however. Conventionally, relationships are categorized by their maximum cardinalities on both sides:

─(𝑛₁,𝑜₁)─◇─(𝑛₂,𝑜₂)─ Relationship Category 𝑜₁ = 1, 𝑜₂ = 1

• ne-to-one (1:1)

𝑜₁ = *, 𝑜₂ = 1

• ne-to-many (1:𝑂)

⚠ 𝑜₁ = 1, 𝑜₂ = * many-to-one (𝑂:1) ⚠ 𝑜₁ = *, 𝑜₂ = * many-to-many (𝑁:𝑂)

‐ ‐ ‐ ‐

12

EXTENDED ER (EER): WEAK ENTITIES

Common mini-world scenario: use an entity type to represent a detail of a superordinate master entity type. Without the master, the detail would not exist. Example: “One hotel has many rooms.” (General: “One master has many exclusive details.”) Detail entity type Room needs to form a composite key (incorporating the key of master entity type Hotel) for full identification. Implicit constraint: if the detail entity 𝑓₂ is in relationship with master entity 𝑓₁, both agree in the master’s key (here: the name aribute).

‐ ‐ ‐ ‐ ‐

13

EER: WEAK ENTITIES

Weak Entity Type In ER diagrams, a weak entity type (detail entity type) and its relationship with the master entity type are drawn using double-stroked lines. The weak entity type implicitly inherits the master key’s aributes (and adds its own key aributes to form a composite key). The existence of weak entities depends on their master entity (affects translation to the target data model). Example (continued):

‐

14

EER: WEAK ENTITIES

Examples of master–detail scenarios in mini-worlds (identify existence dependencies and composite keys): “An invoice has a number of positions.” “A section in a book is identified by a chapter and section title.” “A web page URI is composed of a web server DNS address and a path on that server.” Develop an ER diagram to model quizzes (multiple choice tests): "Each quiz is identified by a title, each question within a quiz is numbered, and each possible answer to a given question is referenced by a leer. For each question and answer, the associated text is stored. Answers are classified into correct and incorrect ones." (What is the complete key for each of the occurring entity types?)

‐

1. 2. 3.

‐

15

EER: INHERITANCE

Inheritance (Entity Sub/Supertypes) Entity type 𝑓ₛ is a subtype of entity type 𝑓 if every entity of subtype 𝑓ₛ indeed also is an entity of supertype 𝑓 (i.e., 𝔽ℝ(𝑓ₛ) ⊆ 𝔽ℝ(𝑓)). (Specialization) Subtype 𝑓ₛ inherits all aributes and relationships from 𝑓. On top of these 𝑓ₛ may add further aributes and relationships. Supertype 𝑓 may have multiple subtypes 𝑓ₛ₁, …, 𝑓ₛₙ. Consider the following inheritance scenarios: Disjoint (vs. overlap): One entity may belong to a single subtype only, i.e., ∀ 𝑗 ≠ 𝑘: 𝔽ℝ(𝑓ₛᵢ) ∩ 𝔽ℝ(𝑓ₛⱼ) = ∅. Total (vs. partial): Every entity must be a member of a subtype, i.e., 𝔽ℝ(𝑓) = 𝔽ℝ(𝑓ₛ₁) ∪ ∪ 𝔽ℝ(𝑓ₛₙ) (cf. with abstract classes in OOPLs).

‐

1. 2.

16

EER: INHERITANCE

EER diagram: partial inheritance (━ ), disjoint subtypes (d in ): EER diagram: total inheritance ( ), overlapping subtypes (o in ): 1. 2.

17

EER: INHERITANCE (EXAMPLES)

EER diagram example

18

EER: INHERITANCE (EXAMPLES)

EER diagram example (specialization hierarchy)

19

MAPPING EER TO RELATIONAL TARGET (SQL)

Step #1: Entity Types

Transforming entity type 𝑓: CREATE TABLE named 𝑢 = 𝑞𝑚𝑣𝑠𝘣𝑚(𝑓) (each row of the table will hold one instance of type 𝑓). Pluralization (“class” → “classes”, “story” → “stories”, “person” → “people”) : (e.g., Damian Conway, Monash U, “A pluralization algorithm for English”, hp://www.csse.monash.edu.au/~damian/papers/HTML/Plurals.html ) Python: plural() (in package/module inflect) Ruby: String#pluralize (see Ruby on Rails’ ActiveSupport) Each aribute 𝘣 of entity type 𝑓 becomes a column 𝘣 of table 𝑢 (map aribute data type to SQL data type with appropriate value domain). If any, list aributes marked as key first.

‐

1.

‐ ‐ ‐

2.

20

MAPPING EER TO RELATIONAL TARGET (SQL)

Step #1b: Entity Type Keys

Transforming entity type 𝑓 (target table 𝑢): If present, a value-based primary key of the entity type becomes the primary key of table 𝑢 (ALTER TABLE 𝑢 ADD PRIMARY KEY (…)) If 𝑓’s key is composite, so will be the relational key. Otherwise, implement the inherent entity identity in terms of an artificial key column, say __id__, of auto-incrementing integer type:

CREATE TABLE 𝑢 (…);

• - establish artificial key column to implement entity identity

ALTER TABLE 𝑢 ADD COLUMN __id__ integer GENERATED ALWAYS AS IDENTITY; ALTER TABLE 𝑢 ADD PRIMARY KEY (__id__);

‐

1.

‐

2.

21

MAPPING EER TO RELATIONAL TARGET (SQL)

Step #2: Weak Entity Types

Assume table 𝑢ᵢ = 𝑞𝑚𝑣𝑠𝘣𝑚(𝑓ᵢ) translates 𝑓ᵢ. In 𝑢₂, add column 𝑠 as a foreign key referencing table 𝑢₁ (implements relationship 𝑠). Replace key of 𝑢₂ with composite primary key (𝑠, 𝑙₂). Implement existence dependency via an ON DELETE CASCADE action on foreign key 𝑠:

ALTER TABLE 𝑢₂ ADD COLUMN 𝑠 ‹type of 𝑙₁› NOT NULL; ALTER TABLE 𝑢₂ DROP CONSTRAINT 𝑢₂_pkey; -- ⯇─┬─ replace primary key ALTER TABLE 𝑢₂ ADD PRIMARY KEY (𝑠, 𝑙₂); -- ⯇─┘ of table 𝑢₂ ALTER TABLE 𝑢₂ ADD FOREIGN KEY (𝑠) REFERENCES 𝑢₁(𝑙₁) ON DELETE CASCADE;

1. 2. 3. 4.

22

MAPPING EER TO RELATIONAL TARGET (SQL)

Step #3: One-to-Many Relationship Types

Assume table 𝑢ᵢ = 𝑞𝑚𝑣𝑠𝘣𝑚(𝑓ᵢ) translates 𝑓ᵢ. Implement 𝑠 and 𝘣: add column 𝑠 to 𝑢₂ and establish as a foreign key referencing table 𝑢₁ (no dependency, thus no ON DELETE action). Add column 𝘣 to 𝑢₂:

• - establish foreign key to implement relationship 𝑠

ALTER TABLE 𝑢₂ ADD COLUMN 𝑠 ‹type of 𝑙₁› NOT NULL; -- ⯇─┬─ realize (1,_) card ALTER TABLE 𝑢₂ ADD COLUMN 𝘣 ‹type of 𝘣› NOT NULL; -- ⯇─┘ ALTER TABLE 𝑢₂ ADD FOREIGN KEY (𝑠) REFERENCES 𝑢₁(𝑙₁);

1. 2.

23

MAPPING EER TO RELATIONAL TARGET (SQL)

Step #3: One-to-Many Relationship Types (Cardinalities)

─(0,*)─ ─(1,1)─ The key value of a given 𝑓₁ entity does not necessarily appear in column 𝑠 of 𝑢₂. ─(0,*)─ ─(1,1)─ The key value of a given 𝑓₁ entity may appear multiple times in column 𝑠 of 𝑢₂. ─(0,*)─ ─(1,1)─ Value of column 𝑠 in 𝑢₂ is NOT NULL (remove NOT NULL for ─(0,*)─ ─(0,1)─). ─(0,*)─ ─(1,1)─ Column 𝑠 has exactly one value in each row of 𝑢₂. But: Relational NOT NULL, key, and foreign key constraints cannot implement general ER cardinality constraints. Unimplementable: ─(𝑛₁,*)─ ─(_,1)─ with 𝑛₁ > 0 and ─(0,𝑜₁)─ ─(_,1)─ with 𝑜₁ > 0, 𝑜₁ ≠ * 1. 2. 3. 4.

‐

24

MAPPING EER TO RELATIONAL TARGET (SQL)

Step #3b: Many-to-Many Relationship Types

Assume table 𝑢ᵢ = 𝑞𝑚𝑣𝑠𝘣𝑚(𝑓ᵢ) translates 𝑓ᵢ. Implement 𝑠 and 𝘣: CREATE TABLE 𝑠 with columns 𝑙₁, 𝑙₂, 𝘣 to represent the relationship. Establish (𝑙₁, 𝑙₂) as composite key for table 𝑠. In table 𝑠, establish 𝑙ᵢ as foreign key referencing table 𝑢ᵢ with ON DELETE actions.

CREATE TABLE 𝑠 (𝑙₁ ‹type of 𝑙₁›, 𝑙₂ ‹type of 𝑙₂›, 𝘣 ‹type of 𝘣›); ALTER TABLE 𝑠 ADD PRIMARY KEY (𝑙₁, 𝑙₂); ALTER TABLE 𝑠 ADD FOREIGN KEY (𝑙₁) REFERENCES 𝑢₁(𝑙₁) ON DELETE CASCADE; ALTER TABLE 𝑠 ADD FOREIGN KEY (𝑙₂) REFERENCES 𝑢₂(𝑙₂) ON DELETE CASCADE;

1. 2.

25

MAPPING EER TO RELATIONAL TARGET (SQL)

Step #3b: Many-to-Many Relationship Types

Quiz: Explain the choice of composite primary key (𝑙₁, 𝑙₂) for table 𝑠. What kind of mini-world is implemented if we establish column 𝑙₁ (or 𝑙₂) as the only key column in table 𝑠? Is the result useful? Notes on many-to-many relationship cardinalities: Cardinalities other than ─(0,*)─ ─(0,*)─ cannot be enforced by the relational model. In particular, ─(𝑛,*)─ ─(_,*)─ with 𝑛 > 0 cannot be translated faithfully. If such scenarios are important in mini-world modelling, the cardinality constraint needs to checked by the application program or a general SQL constraint mechanism (non-relational, in the strict sense).

‐

1. 2.

‐ ‐ ‐

1. 2.

26

MAPPING EER TO RELATIONAL TARGET (SQL)

Step #3c: One-to-One Relationship Types

Assume table 𝑢ᵢ = 𝑞𝑚𝑣𝑠𝘣𝑚(𝑓ᵢ) translates 𝑓ᵢ. Two options: host implementation of 𝑠 and 𝘣 in 𝑢₁ or 𝑢₂. Here: choose 𝑢₁ since every 𝑓₁ entity participates in 𝑠-relationship (─(1,1)─ ): Add column 𝑠 to 𝑢₁ and establish as a foreign key referencing table 𝑢₂ (no dependency, thus no ON DELETE action). Add column 𝘣 to 𝑢₁.

ALTER TABLE 𝑢₁ ADD COLUMN 𝑠 ‹type of 𝑙₂› NOT NULL; ALTER TABLE 𝑢₁ ADD FOREIGN KEY (𝑠) REFERENCES 𝑢₂(𝑙₂); ALTER TABLE 𝑢₁ ADD UNIQUE (𝑠); -- ⚠ 𝑠 becomes candidate key in 𝑢₁ ALTER TABLE 𝑢₁ ADD COLUMN 𝘣 ‹type of 𝘣› NOT NULL;

1. 2.

27

MAPPING EER TO RELATIONAL TARGET (SQL)

Step #3c: One-to-One Relationship Types (Cardinalities)

Consider how the relational translation implements the ER cardinality constraints: ─(1,1)─ ─(0,1)─ : ─(1,1)─ ─(0,1)─ : ─(1,1)─ ─(0,1)─ : ─(1,1)─ ─(0,1)─ :

‐

1. 2. 3. 4.

28

MAPPING EER TO RELATIONAL TARGET (SQL)

Step #3c: One-to-One Relationship Types (Alternatives)

─(0,1)─ ─(0,1)─ : Two options: Drop NOT NULL constraint on column 𝑠 in table 𝑢₁. Represent 𝑠 (and 𝘣) in a separate table 𝑠 with two foreign keys 𝑙ᵢ referencing 𝑢ᵢ. Both 𝑙ᵢ are candidate keys in table 𝑠. ─(1,1)─ ─(1,1)─ : Merge tables 𝑢₁ and 𝑢₂ and column 𝘣 into single table 𝑢. No NULL values allowed to enforce minimum cardinalities of 1. Both 𝑙ᵢ are candidate keys in table 𝑢.

‐

1. 2.

‐

29

MAPPING EER TO RELATIONAL TARGET (SQL)

Translating Inheritance

Assume table 𝑢ᵢ = 𝑞𝑚𝑣𝑠𝘣𝑚(𝑓ᵢ) will hold the translation of 𝑓ᵢ. Implement partial, overlapping inheritance: CREATE TABLEs 𝑢₂ and 𝑢₃ using SQL’s INHERITS declaration to indicate that both inherit from table 𝑢₁.

CREATE TABLE 𝑢₁ (…); CREATE TABLE 𝑢₂ (…) INHERITS (𝑢₁); CREATE TABLE 𝑢₃ (…) INHERITS (𝑢₁);

The implementation of total or disjoint inheritance requires a bit more SQL machinery (e.g., PostgreSQL’s CREATE RULE mechanism, see below). 1. 2. 3.

30

SQL: TABLE INHERITANCE VIA INHERITS

INHERITS (PostgreSQL) If subtable 𝑡 INHERITS from tables 𝑢₁, …, 𝑢ₙ,

CREATE TABLE 𝑡(…) [ INHERITS (𝑢₁ [, …]) ]

subtable 𝑡 features all columns of tables 𝑢ᵢ and those declared locally for 𝑡, i.e., 𝑡𝑑𝒾(𝑢ᵢ) ⊆ 𝑡𝑑𝒾(𝑡), rows inserted/updated/deleted in 𝑡 will also affect all tables 𝑢ᵢ (but not vice versa), i.e., 𝑗𝑜𝑡𝑢(𝑡)|𝑡𝑑𝒾(𝑢ᵢ) ⊆ 𝑗𝑜𝑡𝑢(𝑢ᵢ) (|𝐷 ≡ restricted to the columns in set 𝐷). In the PostgreSQL implementation of INHERITS, the subtable does not inherit key, foreign key,

• r UNIQUE constraints of its supertables. Re-declare these for the subtable.

Note: TABLE 𝑢ᵢ produces 𝑗𝑜𝑡𝑢(𝑢ᵢ). TABLE ONLY 𝑢ᵢ produces 𝑗𝑜𝑡𝑢(𝑢ᵢ) \ (𝑗𝑜𝑡𝑢(𝑡)|𝑡𝑑𝒾(𝑢ᵢ)). 1. 2.

‐ ‐

31

MAPPING EER TO RELATIONAL TARGET (SQL)

Inheritance scenarios can alternatively be approximated using regular ER constructs only. Core idea is to establish an is-a relationship between subtypes 𝑓₂ ₃ and supertype 𝑓₁: Mapping according to weak entities/one-to-one translation scheme:

𝑢₁₃ 𝑙₁ 𝘣 𝑑 𝑢₂ is_a → 𝑢₁₃(𝑙₁) ON DELETE CASCADE 𝑐

‐ ‐

32

THE COMPLETE LEGO DATABASE ER MODEL

33