Relational Model Prof Sharad Mehrotra Information and Computer Science Department University of California at Irvine Chapter 3 and 6 from SKS Outline Relational model basic modeling concepts relation or a table Integrity constraints in the relational model referential integrity constraints key constraints functional dependencies multivalued dependencies join dependencies Relational Languages relational algebra algebraic basic operators expressions in relational algebra relational calculus logic based will not be covered in class safety Relational Model Quick Example Ssno Name Street city Account Custid Number NULL Frank 12 Main balance Champaign 1200331 Cath y 3 Neil Urbana 2000312 Bill 45 W Oak Urbana 34 1000201 10 799 45 1200331 1 345 63 2000312 100 345 A relational schema consists of a set of tables and constraints Tables customer account Constraints Key constraints ssno is the key for customer table both accountno and custid are keys for account table Referential Integrity constraints foreign keys the custid attribute in account table takes values from ssno in customer table Null Constraint customer name cannot take null values 3 Relational Model A database schema consists of a set of relation schema and a set of constraints over the relation schema Relational Schema denoted by R A B C Graphically drawn as a table R relation name Recall relation is a subset of cartesian product of sets relation is a set of n tuples where n degree of the relation A B C are attribute names With each attribute a domain is specified In relational model attributes are atomic the values are not divisible That is we cannot refer to or directly see a subpart of the value an attribute can take a special null value Null value represents either attributes whose value is not known or do not exist 4 Example of a Relation diagnosis an example relation table Patient Jim Jane Jerry joe Disease Schizophrenic Obsessive Comp Manic null null in this case may mean that diagnosis is not complete and disease has not been identified Notice possibility of confusion that null means that the patient has no disease This is one of the reasons why using nulls is not a great idea We will see other reasons as well later 5 Constraints What are they represent the semantics of the miniworld being modeled restrict the set of possible database states Why do we want to specify the constraints Useful information to application programmers They can write programs to prevent constraints violation constraint acct balance should not fall below 0 dollars programmer writing code for debit application should check at the end if the account gets overdrawn DBMS might enforce specified constraints directly making task of application writer easier If DBMS guarantees that account does not get overdrawn then debit application programmer need not worry about checking for overdrawn account condition 6 Constraints Why specify constraints knowledge of some type of constraints enables us to identify redundancy in schemas and hence specification of constraints helps in database design we will see this later Knowledge of some type of constraints can also help the DBMS in query processing Constraint specification in Data Models ER model domain and key constraints over entities participation and cardinality constraints over relations Relational Model domain constraints entity identity key constraint functional dependencies generalization of key constraints referential integrity inclusion dependencies generalization of referential integrity 7 Domain Constraint In the schema every attribute is declared to have a type integer float date boolean string etc An insertion request can violate the domain constraint DBMS can check if insertion violates domain constraint and reject the insertion 8 Disallowing null Values Some fields of a relation are too important to contain null values For example in sales customer salesman date amount saleID we do not want customer to contain a null value because then we cannot send a bill to the customer for the sale Similarly a primary key should not contain a null value since then it may not be possible to identify some tuples If more than one tuple has a null value in its primary key we may not be able to distinguish them entity integrity constraint 9 Key Constraint Superkey for relation set of attributes such that if two tuples agree on those attributes then they agree on all the attributes of the relation Note the set of all the attributes of a relation is always a superkey Candidate key superkey no subset of which i s a superkey Primary key one of the candidate keys A set of candidate keys can be associated by user with the relations insertion and modification may violate key constraint DBMS can check if the the tuple being inserted matches an existing tuple on the attributes in the key and disallow such insertions 10 Foreign Key and Referential Integrity Constraint Consider following 2 relation schemas R1 A1 A2 An R2 B1 B2 Bm Let PK be subset of A1 A2 An be primary key of R1 A set of attributes FK of relation scheme R2 is a foreign key of R2 if the following 2 holds attributes in FK have the same domain as the attributes in PK For all tuples t2 in R2 there exists a tuple t1in R1 such that t2 FK t1 PK A referential integrity constraint from the attributes FK of R2 to R1 means that FK is a foreign that refers to the primary key of R1 Insertion deletion and modification may violate referential integrity 11 Example of Referential Integrity G student Susan Jane John C Semester CS101 1 91 CS101 1 91 CS101 1 91 grade A B Q yping error should be an A If we keep a list of legal grades LegalGrades Grade A and have a referential integrity B constraint saying that C every value of G grade D must also be a value of F LegalGrades grade Audit Ex then we can reject the request insert into G values John CS1010 1 91 Q 12 Inclusion Dependencies Generalization of referential integrity constraint Inclusion dependency R1 A1 An R2 B1 Bn means that the values in the first relation R1 refer to the values in the second relation Formally R1 A1 An R2 B1 Bn iff the following holds for all t1 in R1 there exists a t2 in R2 such that t1 A1 An t2 B1 Bn Notice that referential integrity constraint is an inclusion dependency in which B1 Bn is the primary key of R2 Examples G Grade LegalGrade Grade CourseOffering C Courses C Takes S Students S CourseOffering Professor UIEmployee E 13 Inclusion dependencies specially referential integrity constraints are