Relational Data Model and ER/EER-to-Relational Mapping Chapter 4 Contents 1 Relational Data Model 2 Main Phases of Database Design 3 ER-/EER-to-Relational Mapping 2 Contents 1 Relational Data Model 2 Main Phases of Database Design 3 ER-/EER-to-Relational Mapping 3 Relational Data Model Basic Concepts: relational data model, relation schema, domain, tuple, cardinality & degree, database schema, etc. Relational Integrity Constraints key, primary key & foreign key entity integrity constraint referential integrity Update Operations on Relations 4 Basic Concepts The relational model of data is based on the concept of a relation A relation is a mathematical concept based on the ideas of sets The model was first proposed by Dr. Codd of IBM in 1970 in the following paper: "A Relational Model for Large Shared Data Banks," Communications of the ACM, June 1970 5 Basic Concepts Relational data model: represents a database in the form of relations - 2-dimensional table with rows and columns of data. A database may contain one or more such tables.
A relation schema is used to describe a relation Relation schema: R(A1, A2,…, An) is made up of a relation name R and a list of attributes A1, A2,. Each attribute Ai is the name of a role played by some domain D in the relation schema R. R is called the name of this relation 6 Basic Concepts The degree of a relation is the number of attributes n of its relation schema. Domain D: D is called the domain of Ai and is denoted by dom(Ai).
It is a set of atomic values and a set of integrity constraints STUDENT(Name, SSN, HomePhone, Address, OfficePhone, Age, GPA) Degree = ?? dom(GPA) = ?? 7 Basic Concepts Tuple: row/record in table Cardinality: number of tuples in a table Database schema S = {R1, R2,…, Rm} 8 Basic Concepts A relation r (or relation state, relation instance) of the relation schema R(A1, A2, ., An), also denoted by r(R), is a set of n-tuples r = {t1, t2,. Each n-tuple t is an ordered list of n values t = <v1, v2, ., vn>, where each value vi, i=1.n, is an element of dom(Ai) or is a special null value. The ith value in tuple t, which corresponds to the attribute Ai, is referred to as t[Ai] 9 Basic Concepts Relational data model Database schema Relation schema Relation Tuple Attribute 10 Basic Concepts A relation can be conveniently represented by a table, as the example shows The columns of the tabular relation represent attributes Each attribute has a distinct name, and is always referenced by that name, never by its position Each row of the table represents a tuple. The ordering of the tuples is immaterial and all tuples must be distinct 11 Basic Concepts 12 Alternative Terminology for Relational Model Formal Terms Informal Terms Relation Table Attribute Column Header Domain All possible Column Values Tuple Row Schema of a Relation Table Definition State of the Relation Populated Table 13 Relational Integrity Constraints Constraints are conditions that must hold on all valid relation instances.
There are three main types of constraints: Key constraints Entity integrity constraints Referential integrity constraints But … 14 Relational Integrity Constraints Null value Represents value for an attribute that is currently unknown or inapplicable for tuple Deals with incomplete or exceptional data Represents the absence of a value and is not the same as zero or spaces, which are values 15 Relational Integrity Constraints - Key Constraints Superkey of R: A set of attributes SK of R such that no two tuples in any valid relation instance r(R) will have the same value for SK. That is, for any distinct tuples t1 and t2 in r(R), t1[SK] t2[SK] Key of R: A "minimal" superkey; that is, a superkey K such that removal of any attribute from K results in a set of attributes that is not a superkey 16 Relational Integrity Constraints - Key Constraints Example: The CAR relation schema: CAR(State, Reg#, SerialNo, Make, Model, Year) has two keys Key1 = {State, Reg#} Key2 = {SerialNo}, which are also superkeys. {SerialNo, Make} is a superkey but not a key If a relation has several candidate keys, one is chosen arbitrarily to be the primary key. The primary key attributes are underlined.
17 Relational Integrity Constraints - Key Constraints The CAR relation, with two candidate keys: License_Number and Engine_Serial_Number 18 Relational Integrity Constraints - Entity Integrity Relational Database Schema: A set S of relation schemas that belong to the same database. S is the name of the database: S = {R1, R2, ., Rn} Entity Integrity: primary key attributes PK of each relation schema R in S cannot have null values in any tuple of r(R) because primary key values are used to identify the individual tuples: t[PK] null for any tuple t in r(R) Note: Other attributes of R may be similarly constrained to disallow null values, even though they are not members of the primary key 19 Relational Integrity Constraints - Referential Integrity A constraint involving two relations (the previous constraints involve a single relation) Used to specify a relationship among tuples in two relations: the referencing relation and the referenced relation Tuples in the referencing relation R1 have attributes FK (called foreign key attributes) that reference the primary key attributes PK of the referenced relation R2. A tuple t1 in R1 is said to reference a tuple t2 in R2 if t1[FK] = t2[PK] A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2 20 Relational Integrity Constraints - Referential Integrity 21 Relational Integrity Constraints - Referential Integrity The value in the foreign key column (or columns) FK of the referencing relation R1 can be either: (1) a value of an existing primary key value of the corresponding primary key PK in the referenced relation R2, or (2) a NULL In case (2), the FK in R1 should not be a part of its own primary key 22 Referential integrity constraints displayed on the COMPANY relational database schema 23 Relational Integrity Constraints - Other Types of Constraints Semantic Integrity Constraints: based on application semantics and cannot be expressed by the model per se E. of hours per employee for all projects he or she works on is 56 hrs per week” A constraint specification language may have to be used to express these SQL-99 allows triggers and ASSERTIONS to allow for some of these State/static constraints (so far) Transition/dynamic constraints: e., “the salary of an employee can only increase” 24 Update Operations on Relations INSERT a tuple DELETE a tuple MODIFY a tuple Integrity constraints should not be violated by the update operations 25 Update Operations on Relations Insertion: to insert a new tuple t into a relation R.
When inserting a new tuple, it should make sure that the database constraints are not violated: The value of an attribute should be of the correct data type (i. from the appropriate domain). The value of a prime attribute (i. the key attribute) must not be null The key value(s) must not be the same as that of an existing tuple in the same relation The value of a foreign key (if any) must refer to an existing tuple in the corresponding relation Options if the constraints are violated: Homework !! 26 Update Operations on Relations Deletion: to remove an existing tuple t from a relation R.
When deleting a tuple, the following constraints must not be violated: The tuple must already exist in the database The referential integrity constraint is not violated Modification: to change values of some attributes of an existing tuple t in a relation R 27 Update Operations on Relations In case of integrity violation, several actions can be taken: Cancel the operation that causes the violation (REJECT option) Perform the operation but inform the user of the violation Trigger additional updates so the violation is corrected (CASCADE option, SET NULL option) Execute a user-specified error-correction routine Again, homework !! 28 Contents 1 Relational Data Model 2 Main Phases of Database Design 3 ER-/EER-to-Relational Mapping 29 Main Phases of Database Design Three main phases Conceptual database design Logical database design Physical database design 30 Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS Functional requirements Data requirements DBMS–independent FUNCTIONAL ANALYSIS CONCEPTUAL DESIGN High-level transaction Conceptual schema specification LOGICAL DESIGN (DATA MODEL MAPPING) DBMS–specific APPLICATION PROGRAM Database schema DESIGN PHYSICAL DESIGN TRANSACTION Internal schema IMPLEMENTATION Application program 31 Application Design Database Design Overview of Database Miniworld Design Process REQUIREMENTS - COLLECTION & ANALYSIS • Create a database Data requirements DBMS–independent schema in CONCEPTUAL DESIGN implementation data Conceptual schema model of a commercial DBMS LOGICAL DESIGN • Data model mapping (DATA MODEL MAPPING) DBMS–specific is often automated or Database schema semi-automated within the database design PHYSICAL DESIGN tool. Internal schema 32 Database Design Main Phases of Database Design Logical database design The process of constructing a model of the data used in an enterprise based on a specific data model (e. relational), but independent of a particular DBMS and other physical considerations ER- & EER-to-Relational Mapping Normalization 33 Entity-Relationship Diagram (ERD) for COMPANY Database 34 Result of mapping the ERD into a relational schema 35 Contents 1 Relational Data Model 2 Main Phases of Database Design 3 ER-/EER-to-Relational Mapping 36 ER- & EER-to-Relational Mapping ER- Step 1: Mapping of Regular Entity Types Step 2: Mapping of Weak Entity Types Step 3: Mapping of Binary 1:1 Relationship Types Step 4: Mapping of Binary 1:N Relationship Types Step 5: Mapping of Binary M:N Relationship Types Step 6: Mapping of Multivalued attributes Step 7: Mapping of N-ary Relationship Types EER- Step 8: Options for Mapping Specialization or Generalization. Step 9: Mapping of Union Types (Categories) Bước 8 + 9 làm chung với bước 1 + 2 37 ER-to-Relational Mapping Step 1: Mapping of Regular (strong) Entity Types Entity --> Relation Attribute of entity --> Attribute of relation Primary key of entity --> Primary key of relation Example: We create the relations EMPLOYEE, DEPARTMENT, and PROJECT in the relational schema corresponding to the regular entities in the ER diagram.
SSN, DNUMBER, and PNUMBER are the primary keys for the relations EMPLOYEE, DEPARTMENT, and PROJECT as shown 38 39 Step 1: Mapping of Regular (strong) Entity Types 40 ER-to-Relational Mapping Step 2: Mapping of Weak Entity Types For each weak entity type W in the ER schema with owner entity type E, create a relation R and include all simple attributes (or simple components of composite attributes) of W as attributes of R In addition, include as foreign key attributes of R the primary key attribute(s) of the relation(s) that correspond to the owner entity type(s) The primary key of R is the combination of the primary key(s) of the owner(s) and the partial key of the weak entity type W, if any Example: Create the relation DEPENDENT in this step to correspond to the weak entity type DEPENDENT.