Table Name Entities Table Relation Column Name Attributes Row Tuple Values in a column Domain Table definition Schema of a relation Populated Table Extension Relational Model Concepts ► Relational model: represents the database as a collection of relations. ► Relation: Table of values ► Tuple represent facts: Row ► Column Header: Attribute ► Domain: Data type describing the types of values that can appear in each column. ► The model was first proposed by Dr. E.F. Codd of IBM Research in 1970 in the following paper:“ A Relational Model for Large Shared Data Banks," Communications of the ACM, June 1970 Relational Model Concepts ► Domain: ► Set of atomic values ► Represents data type, and format. ► For example, the PAK_phone_numbers may have a format: (ddd)-dddddddd where each d is a decimal digit. ► Dates have various formats such as month, date, year or yyyy-mm-dd, or dd mm,yyyy etc. ► Names: set of character strings that represent names of persons. ► Grade_point_averages: floating-point number between 0 and 4. ► Employee_ages: integer value between 15 and 80. Formal Definition of Relation ► A relation schema. ► denoted by R(A1, A2, … , An), ► made up of a relation name R ► Contains a list of attributes, A1, A2, … , An. ► Each attribute Ai is the name of a role played by some domain D in the relation schema R. D is called the domain of Ai and is denoted by dom(Ai). ► Degree (or arity) of a relation: number of attributes/columns ► Tuple t is a row: ► Ordered/ unordered list of n values t =<v1, v2, … , vn> ► each value vi, 1 ≤ i ≤ n, is an element of dom (Ai) or is a special NULL value. Example of a Relation
Degree of Relation? Relation Definition Using set theory ► A relation (or relation state) r(R) is a mathematical relation of degree n on the domains dom(A1), dom(A2), … , dom(An), which is a subset of the Cartesian product (denoted by ×) of the domains that define ► R: r(R) ⊆ (dom(A1) × dom(A2) × . . . × (dom(An)) ► Cartesian product: All possible combinations of values from the underlying domains. ► Total number of tuples in the Cartesian product: |dom(A1)| × |dom(A2)| × . . . × | dom(An)| Relational Model Concepts ► Relation State: The state of the whole database will correspond to the states of all its relations at a particular point in time. Characteristics of Relations
► Relation: set of tuples.
► Ordering of Tuples in a Relation: tuples in a relation do not have any particular order.
► Hence, the relation
displayed in Figure 5.2 is considered identical to the one shown in Figure 5.1 Characteristics of Relations
► Ordering of Attributes is not important,
because the attribute name appears with its value.
► When the attribute name and value are
included together in a tuple, it is known as self- describing data. Characteristics of Relations ► Values and NULLs in the Tuples: ► Each value in a tuple is an atomic value. ► composite and multivalued attributes are not allowed ► NULL values: values of attributes that may be unknown or may not apply to a tuple. Relational Model Constraints ► Constraints: set of rules or restrictions. They are used to maintain the quality of data. ► Constraints ensure that the data insertion, updating, and other processes have to be performed in such a way that data integrity is not affected. ► Constraints are used to guard against accidental damage to the database. Relational Model Constraints ► Schema Based Constraints ► Divided into three main categories. ► Key constraints ► Entity integrity constraints ► Referential integrity constraints ► Key Constraints: used to identify an entity within its entity set uniquely. ► Primary Key ► Candidate Key ► Super Key ► Foreign Key Key Constraints
► Primary key: uniquely identifies each record in
a table. It must have unique values and cannot have null values. ► FOREIGN KEY: Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables. ► Candidate Key: A super key with no redundant attribute is known as candidate key. ► Super Key: A super key is a set of one of more columns (attributes) to uniquely identify rows in a table. Primary Key Examples Candidate Key Example Emp_Id Emp_Nu Emp_Na mber me E01 2264 Steve E22 2278 David E23 2288 Joseph E45 The candidate keys2290 Robert we have selected are: {Emp_Id} {Emp_Number} Super Key Example Emp_Id Emp_Nu Emp_Na mber me E01 2264 Steve E22 2278 David E23 2288 Joseph E45 2290 Robert How many super keys the above table can have? 1. {Emp_Id, Emp_Number} 2. {Emp_Id, Emp_Name} 3. {Emp_Id, Emp_Number, Emp_Name} 4. {Emp_Number, Emp_Name} Foreign Key Example Course_enrollment table: Student table:
Course_Id Stu_Id Stu_Nam
Stu_Id Stu_Age e C01 101 Chaitany C02 102 101 22 a C03 101 102 Arya 26 C05 102 C06 103 103 Bran 25 C07 102 104 Jon 21 Entity Integrity Constraint ► Entity integrity constraint: primary key value can't be null. ► A table can contain a null value other than the primary key field. Referential integrity constraint • Referential integrity constraint is specified between two tables. ► Foreign key in Relation 1 refers to the Primary Key of Relation 2. ► Used to specify a relationship among tuples in two relations: the referencing relation and the referenced relation. ► A referential integrity constraint can be displayed in a relational database schema as a directed arc from R1.FK to R2.PK Slide
Referential integrity 5- 23
constraint ► 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. Referential Integrity ► 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. Referential Integrity (or foreign key) Constraint ► Statement of the constraint ► 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 a 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. Relational Model Constraints ► Domain Constraints: ► Value of each attribute must be an atomic value from the domain dom(A). ► The data type of domain includes string, character, integer, time, date, currency, etc. The value of the attribute must be available in the corresponding domain. Relational Database State ► A relational database state DB of S is a set of relation states DB = {r1, r2, ..., rm} such that each ri is a state of Ri and such that the ri relation states satisfy the integrity constraints specified in IC. ► A relational database state is sometimes called a relational database snapshot or instance. ► We will not use the term instance since it also applies to single tuples. ► A database state that does not meet the constraints is an invalid state Schema Diagram for the COMPANY Relational Database Schema Displaying a relational 29 database schema and its constraints ► Each relation schema can be displayed as a row of attribute names ► The name of the relation is written above the attribute names ► The primary key attribute (or attributes) will be underlined ► A foreign key (referential integrity) constraints is displayed as a directed arc (arrow) from the foreign key attributes to the referenced table ► Can also point the primary key of the referenced relation for clarity Other Types of Constraints ► Semantic integrity constraints: specified and enforced within the application programs that update the database, or by using a general-purpose constraint specification language. ► i.e. the salary of an employee should not exceed the salary of the employee’s supervisor ► i.e. the maximum number of hours an employee can work on all projects per week is 56. Other Types of Constraints ► Transition constraints: Transition between one database state into another database state. ► Enforced by application Program. ► i.e., salary of an employee can only increase. Update Operations, Transactions, and Dealing with Constraint Violations Constraint Violation
► Relational Model Operations: Retrievals and
updates. ► Three basic operations that can change the states of relations in the database: Insert, Delete, and Update (or Modify). ► Whenever these operations are applied, the integrity constraints specified on the relational database schema should not be violated. Violation of Constraints: Insert Operation
► Insert can violate any of the four types of constraints.
► Domain constraints: can be violated if an attribute value is given that does not appear in the corresponding domain or is not of the appropriate data type. ► Key constraints: can be violated if a key value in the new tuple t already exists in another tuple in the relation r(R). ► Entity integrity: can be violated if any part of the primary key of the new tuple t is NULL. ► Referential integrity can be violated if the value of any foreign key in table refers to a tuple that does not exist in the referenced relation. Violation of Constraints: Insert Operation Violation of Constraints: Insert Operation Violation of Constraints: Insert Operation Insert Operations on Relations
► In case of integrity violation, several actions
can be taken: ► Cancel the operation that causes the violation (RESTRICT or 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 Violation of Constraints: Delete Operation ► Delete operation can violate only referential integrity. ► This occurs if the tuple being deleted is referenced by foreign keys from other tuples in the database. Violation of Constraints: Delete Operation Violation of Constraints: Delete Operation Violation of Constraints: Delete Operation Violation of Constraints: Delete Operation
► Options to overcome violation if a
deletion operation causes a violation. ► Restrict: is to reject the deletion. ► Cascade: to cascade (or propagate) the deletion by deleting tuples that reference the tuple that is being deleted. ► Set null or set default: is to modify the referencing attribute values that cause the violation ► each such value is either set to NULL or changed to reference another default valid tuple. ► Notice that if a referencing attribute that causes a violation is part of the primary key, it cannot be set to NULL; otherwise, it would violate entity integrity. Combinations of these three options are also possible. Violation of Constraints: Update Operation • Updating an attribute that is neither part of a primary key nor part of a foreign key usually causes no problems. Violation of Constraints: Update Operation Violation of Constraints: Update Operation Violation of Constraints: Update Operation ► Any of the other constraints may also be violated, depending on the attribute being updated: ► Updating the primary key (PK): ► Similar to a DELETE followed by an INSERT ► Need to specify similar options to DELETE ► Updating a foreign key (FK): ► May violate referential integrity ► Updating an ordinary attribute (neither PK nor FK): ► Can only violate domain constraints Transaction
► A transaction is an executing program that
includes some database operations, such as reading from the database, or applying insertions, deletions, or updates to the database. ► After the transaction is completed the database must be in valid and consistent state that satisfies all the constraints. ► For example, a bank withdrawal will typically read the user account record, check if there is a sufficient balance, and then update the record by the withdrawal amount. Practice Question STUDEN T Student_No Student_Na Student_Ph Subject_No me one 1 Andrew 661592728 10 4 2 Sara 658365486 20 5 3 Harry 464756746 10 SUBJECT 3 ENROLL
