CSE 480 Database Systems Lecture 5 Relational Data

CSE 480: Database Systems Lecture 5: Relational Data Model 1

Lecture Outline l Relational Model Concepts – Relations, Attributes, Domains, Tuple, Schema – Characteristics of relations l Integrity Constraints – Domain constraints – Key constraints – Entity integrity – Referential integrity l Relational Update Operations and Dealing with Constraint Violations 2

Relational Model l Represents the database as a collection of relations – Informally, each relation is a table of values u The table contains a set of rows (called tuples) u Each column header corresponds to an attribute 3

Terminology Informal Terms Formal Terms Table Relation Row Tuple Column Header Attribute Set of all possible column values Domain Table Definition Relation Schema Populated Table Relation State 4

Relation Schema l Schema corresponds to the definition of a relation – Represented as R(A 1, A 2, . . . An) where R is the relation name and A’s are the attributes and their corresponding domains l Example : CUSTOMER (Cust-id: INT, Cust-name: VARCHAR(40), Address: VARCHAR(50)) – CUSTOMER is the relation name – Attributes: Cust-id, Cust-name, Address – INT and VARCHAR are domains of the attribute values l Relation schema also includes other integrity constraints (to be discussed later) 5

Relation State l A relation state r of a schema R(A 1, A 2, . . . An) is a set of m-tuples r(R) = {t 1, t 2, …, tm} – where each tuple t is an ordered set of values < v 1, v 2, …, vn> and each value v is derived from an appropriate domain 6

Characteristics of a Relation l Ordering of tuples in a relation r(R) is irrelevant – Both relations are considered identical 7

Characteristics of a Relation l All attribute values must be atomic (indivisible) l A special null value is used to represent values that are unknown or inapplicable to certain tuples 8

Integrity Constraints l Constraints are conditions that specify restrictions on the database state – Protects database from errors l Types of constraints captured in the relational model (i. e. , they are automatically checked by DBMS): – – l Domain constraints Key constraints Entity integrity constraints Referential integrity constraints Constraints not captured in the relational model – semantic constraints (business rules) u E. g. , “Buy 5 Get 1 Free!”, “Can’t view profile unless you’ve logged in” 9

University Database 10

Domain Constraint l Domain constraint – The value of each attribute in a tuple must be atomic and consistent with its domain (or it could be null, if allowed for that attribute) 11

Violations of Domain Constraints CREATE TABLE Student ( Id INT, Name CHAR(30), Status ENUM( ‘Freshman’, ‘Sophomore’, ‘Junior’, ‘Senior’), Address CHAR(30) ); Inserting the tuple (211223344, ‘Billy Bob’, 3, ‘ 46 Grand Ave. ’) OK Inserting the tuple (‘ 311211111’, ‘Mark Smith’, ‘Senior’, ‘ 1 Oak St. ’) Inserting the tuple (33333, ‘Bart Simpson’, ‘Graduate’, ‘Fox 5 TV’); 12

Key Constraint l Every relation must have a key l A key is a subset of attributes {A 1, …, Am} of a relation schema R with the following properties: – Uniqueness: u No two tuples in any valid relation state will have the same combination of values for A 1, …, Am – Minimality: u l No subset of A 1, …, Am has the uniqueness property Sometimes row-ids or sequential numbers are assigned as keys to identify the rows in a table – Called artificial key or surrogate key 13

Key Constraint l Superkey – a set of attributes containing a key – Satisfies uniqueness property (minimality poperty not necessary) – Every key is a superkey, but not vice-versa – {License. No} is a key u It is also a superkey of CAR – {License. No, Make} is a superkey but not a key 14

Key Constraint l A relation can have several keys: – If a relation has several keys, they are referred to as candidate keys (one of them is designated as the primary key) – The primary key attributes are underlined in the relation schema – Examples: u CAR(License. No, Engine. Serial. No, Make, Model, Year) – Candidate keys: {License. No} or {Engine. Serial. No} – Primary key: {License. No} u STUDENT(Id, Name, Address, Phone. No) – Candidate keys: {Name, Address} or {Id} – Primary key: {Id} 15

Exercise Identify the candidate and primary keys for each of the following schema: DEPARTMENT(Id: STRING, Name: STRING, Address: STRING) 16

Entity Integrity Constraint l Primary key attributes PK cannot have null values in any tuple – This is because primary key values are used to identify the individual tuples. – If PK has several attributes, null is not allowed in any of these attributes l Note: Other attributes of R can also be constrained to disallow null values, even though they are not members of the primary key. CREATE TABLE Student ( Id INT PRIMARY KEY, Name CHAR(30) NOT NULL, Status ENUM( ‘Freshman’, ‘Sophomore’, ‘Junior’, ‘Senior’), Address CHAR(30) ); 17

Relationship Constraints Semester Id PROFESSOR TEACH Crs. Code COURSE In relational databases, it is common for tuples in one relation to reference tuples in the same or other relations 18

Referential Integrity l Referential integrity constraint – Tuples in the referencing relation must refer to tuples that exist in the referenced relation l Referential integrity is violated if the referenced tuple does not exist in the appropriate relation – A tuple (123456789, MGT 123, F 1994) in TEACHING relation violates referential integrity constraint because there is no tuple with Id = 123456789 in PROFESSOR relation l Referential integrity constraint is enforced in the DB using FOREIGN KEY on attributes in the referencing relation 19

Referential Integrity l Tuples in the referencing relation R 1 have attributes FK (called foreign key attributes) that reference the primary key** attributes PK of the referenced relation R 2 Referencing relation R 1 Referenced relation R 2 ** The book considers PK to be primary key, but in general, PK can be any candidate key 20

Referential Integrity R 1(FK) references R 2(PK) l FK and PK are attributes that have the same domains (although not necessarily the same attribute names) l The value in the FK of the referencing relation R 1 can be either: – An existing value of a corresponding PK in the referenced relation R 2, or – Null u l In this case, the FK cannot be part of the primary key for R 1 and R 2 need not be distinct relations – Employee(Id: INT, Mgr. Id: INT, …. ) 21

Example TEACHING (Prof. Id) REFERENCES PROFESSOR (Id) l Not all rows in PROFESSOR need to be referenced – Some PROFESSORs may not have taught any classes yet l Names of FK and PK attributes need not be the same – Prof. Id of Teaching references Id of Professor l Value of a foreign key can be NULL – The PROFESSOR who teaches a course might not be known yet – Caveat: Prof. Id cannot be part of the primary key for TEACHING 22

Exercise l Identify all the referential integrity constraints in the student registration system DEPARTMENT(Id: STRING, Name: STRING, Address: STRING) 23

Exercise PROFESSOR(Dept. Id) REFERENCES DEPARTMENT(Id) COURSE(Dept. Id) REFERENCES DEPARTMENT(Id) TRANSCRIPT(Stud. Id) REFERENCES STUDENT(Id) TRANSCRIPT(Crs. Code, Semester) REFERENCES TEACHING(Crs. Code, Semester) TEACHING(Prof. Id) REFERENCES PROFESSOR(Id) TEACHING(Crscode) REFERENCES COURSE(Crs. Code) TRANSCRIPT(Crs. Code) REFERENCES COURSE(Crs. Code) is redundant since it is implied by Teaching(Crscode). 24

Relational Database Schema l A set of relation schemas that belong to the same database – S = {R 1, R 2, . . . , Rn} – S is the name of the whole database schema – R 1, R 2, …, Rn are the names of individual relation schemas within the database S l Referential integrity constraints are displayed as directed arcs from the foreign key attributes to the referenced table – Can also point to the primary key of the referenced relation for clarity 25

Example: COMPANY Database Schema 26

Referential Integrity Constraints Schema diagram with referential integrity constraints 27

Relational Database State l Relational database state is the union of all the relation states COMPANY Database 28

Update Operations on Relations l Whenever the database is changed/updated, a new database state arises l Basic operations for updating the database: – INSERT a new tuple into a relation – DELETE an existing tuple from a relation – UPDATE/MODIFY an attribute of an existing tuple in a relation l Integrity constraints must not be violated by the update operations – Updates may propagate to cause other updates automatically. This may be necessary to maintain integrity constraints. 29

Example If we change the SSN of John Smith to 11111, this may require updates to some of the tuples in Works_ON relation (depending on the update option used) 30

Possible violations for INSERT l Domain constraint – if one of the attribute values provided for the new tuple is not of the specified attribute domain l Key constraint – if the value of a key attribute in the new tuple already exists in another tuple in the relation l Entity integrity – if the primary key value is null in the new tuple l Referential integrity – if a foreign key value in the new tuple references a primary key value that does not exist in the referenced relation 31

Example Insert <‘John’, ‘M’, ‘Doe’, NULL, ‘ 1977 -01 -01’, ‘ 123 Main, TX’, ‘M’, 45000, NULL, 4> into EMPLOYEE will violate entity constraint u Insert <‘Mary’, ‘M’, ‘Doe’, 123456789, ‘ 1977 -01 -01’, ‘ 123 Main, TX’, ‘M’, 45000, NULL, 4> into EMPLOYEE will violate key constraint u u. Insert <‘Tom’, NULL, ‘Doe’, ‘ 44444’, 1957, NULL, ‘M’, ’ 100 K’, NULL, 4> into EMPLOYEE will violate domain constraint 32

Example CREATE TABLE WORKS_ON ( Essn INT, Pno INT, Hours FLOAT, PRIMARY KEY (Essn, Pno), FOREIGN KEY Essn REFERENCES EMPLOYEE(Ssn) FOREIGN KEY Pno REFERENCES PROJECT(Pnum)); Insert <99999, 1, 34. 5> into WORKS_ON will violate referential integrity constraint u 33

Possible violations for DELETE l DELETE may violate only referential integrity: – If the primary key value of the tuple being deleted is referenced by other tuples in the database u Can be remedied by several actions: RESTRICT, CASCADE, SET NULL (more details are given in Chapter 8) – One of the above options must be specified for each foreign key constraint 34

Example l Deletion from referenced relation Professor: Teaching x Professor x Request to delete a row in Professor – RESTRICT: Reject if row in Teaching references row to be deleted – SET NULL: Set value of foreign key in Teaching to NULL – SET DEFAULT: Set value of foreign key in Teaching to default value which must exist in B – CASCADE: Delete referencing row(s) in Teaching as well 35

Example (in SQL) Teaching x Professor x CREATE TABLE Teaching ( Prof. Id INT, Crs. Code CHAR(10), Semester CHAR(10), PRIMARY KEY (Crs. Code, Semester), FOREIGN KEY (Prof. Id) REFERENCES Professor(Id) ON DELETE SET NULL, FOREIGN KEY (Crs. Code) REFERENCES COURSE(Crs. Code) ON DELETE RESTRICT ); ** More details in Chapter 8 36

Possible violations for UPDATE l Domain, key, entity integrity, and referential integrity constraints may be violated, depending on the attribute being updated: – Updating the primary key (PK): u Similar to a DELETE operation followed by an INSERT operation u Need to specify similar options to DELETE – Updating a foreign key (FK): u May violate referential integrity – Updating an ordinary attribute (neither PK nor FK): u Can only violate domain constraints 37

Example (in SQL) Teaching x Professor x CREATE TABLE Teaching ( Prof. Id INT, Crs. Code CHAR(10), Semester CHAR(10), PRIMARY KEY (Crs. Code, Semester), FOREIGN KEY Prof. Id REFERENCES Professor(Id) ON DELETE SET NULL ON UPDATE SET NULL, FOREIGN KEY Crs. Code REFERENCES COURSE(Crs. Code) ON DELETE RESTRICT ON UPDATE CASCADE ); 38