Relational ModelProf. Sharad MehrotraInformation and ComputerScience DepartmentUniversity of California at IrvineChapter 3 and 6 from SKSChapter 3 in UWOutline Relational model basic modeling concepts for schema specification Mapping ER diagrams to Relational Model Relational Languages relational algebra (algebraic) basic operators, expressions in relational algebra relational calculus (logic based) /*will not becovered in class */3Relational Model -- Quick ExampleSsno Name Street cityNULL Frank 12 Main Champaign1200331 Cathy3 Neil Urbana2000312 Bill 45 W. Oak UrbanaAccountNumberCustid balance34 1000201 10,79945 1200331 1,34563 2000312 100,345 Key constraints:•ssno is the key for customertable•both accountno and custid arekeys for account tableReferential Integrity constraints:(foreign keys)The custid attribute in account tabletakes values from ssno in customertableNull Constraint:customer name cannottake null values A relational schema = tables and constraints. Tables: customer, account Constraints:4Relational Model Database schema consists of a set of relation schema a set of constraints over the relation schema Relational Schema = name(attributes).Graphically drawn as table. Example: employee(ssno, name, salary) Recall: relation is a subset of cartesian product ofsets relation is a set of n-tuples where n = degreeof the relationAttributes With each attribute a domain is specified In relational model attributes are atomic: the values are not divisible. That is, wecannot refer to or directly see a subpart of thevalue. an attribute can take a special null value Null value represents either attributeswhose value is not known, or do not exist6null in this case may mean that diagnosis is not complete anddisease has not been identified. Notice possibility of confusionthat null means that the patient has no disease! This is one ofthe reasons why using nulls is not a great idea! We will seeother reasons as well laterExample of a RelationDiagnosis: an example relation/tablePatient DiseaseJim SchizophrenicJane Obsessive-CompJerry ManicJoe null7Constraints What are they? represent the semantics of the domain being modeled. restrict the set of possible database states Why do we want to specify the constraints? Useful information to application programmers. Theycan write programs to prevent constraints violation constraint -- acct balance should not fall below 0 dollars programmer writing code for debit application shouldcheck at the end if the account gets overdrawn! DBMS might enforce specified constraints directlymaking task of application writer easier If DBMS guarantees that account does not get overdrawn,then debit application programmer need not worry aboutchecking for overdrawn account condition.8Constraints Why specify constraints? knowledge of some type of constraintsenables us to identify redundancy in schemasand hence specification of constraints helps indatabase design (we will see this later) Knowledge of some type of constraints canalso help the DBMS in query processingSpecifying Constraints in DataModels ER model domain and key constraints over entities participation and cardinality constraints overrelationships Relational Model domain constraints, entity identity, keyconstraint, functional dependencies --generalization of key constraints, referentialintegrity, inclusion dependencies --generalization of referential integrity.10Domain Constraint In the schema, every attribute isdeclared to have a type --- integer, float,date, boolean, string, etc. An insertion request can violate thedomain constraint. DBMS can check if insertion violatesdomain constraint and reject theinsertion.11Key Constraint Each relation has a primary key.Superkey: set of attributes such that if two tuples agreeon those attributes, then they agree on all theattributes of the relation Note: the set of all the attributes of a relationis always a superkey.Candidate key: superkey no subset of which i s a superkeyPrimary key: one of the candidate keys12Disallowing Null Values Some fields of a relation are tooimportant to contain null values. Example: in sales(customer, salesman, date, amount,saleID) we may not want ‘customer’ tocontain a null value.Entity Integrity Constraint A primary key must not contain a nullvalue. Else it may not be possible to identify sometuples. For Example, if more than one tuple has a nullvalue in its primary key, we may not be ableto distinguish them .14Foreign Key and ReferentialIntegrity Constraint Consider following 2 relation schemas: R1(A1, A2, …An) and R2(B1, B2, … Bm) Let PK be subset of {A1, …,An} be primary keyof R1 A set of attributes FK is a foreign key of R2 if: attributes in FK have same domain as the attributes inPK For all tuples t2 in R2, there exists a tuple t1in R1 suchthat t2[FK] = t1[PK]. A referential integrity constraint from attributesFK of R2 to R1 means that FK is a foreign thatrefers to the primary key of R1.15 student-grades student C# Semester grade Susan CS101 1-91 A Jane CS101 1-91 B LegalGrades GradeA we will have a referential integrity B constraint saying that C every value of student-grades.grade D must also be a value of F LegalGrades.grade, Audit ExExample of Referential Integrity16 Generalization of referential integrity constraint. Inclusion dependency R1[A1,...,An] ⊆==R2 [B1,...,Bn]means that the values in the first relation R1 refer tothe values in the second relation Formally, R1[A1,...,An] ⊆==R2 [B1,...,Bn] iff thefollowing 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 aninclusion dependency in which {B1, .. Bn} is theprimary key of R2.Inclusion DependenciesExample student-grade[Grade] ⊆=LegalGrade[Grade] CourseOffering[C#] ⊆==Courses[C#] Takes[S#] ⊆==Students[S#] CourseOffering[Professor] ⊆==UCEmployee[E#]18Data Modification and IntegrityConstraints Modification, insertion and deletion
View Full Document