Relational Data Model Ch. 7.1 – 7.3Why Study Relational Model?Anatomy of a RelationDomain of an AttributeTuplesSchema and InstanceSchema & Instance UpdateRelational DatabaseA University Database see p. 204, Fig. 7.5Constraints of Relational DBDomain ConstraintHandle Multi-Valued AttributesSlide 13Access-by-Content ConstraintSuperkeySuperkey ExampleCandidate KeyPrimary KeyKey ConstraintEntity Integrity ConstraintForeign KeyForeign Key ExampleReferential Integrity ConstraintUpdate OperationsRelational Model: SummarySlide 27Look AheadRelational Data ModelCh. 7.1 – 7.3John OrtizLecture 3 Relational Data Model 2Why Study Relational Model? Most widely used model. Vendors: IBM, Informix, Microsoft, Oracle, Sybase, etc.“Legacy systems” in older models E.G., IBM’s IMS (hierarchical model)Recent competitor: object-oriented model ObjectStore, Versant, Ontos, O2A synthesis emerging: object-relational modelInformix UDS, UniSQL, Oracle, DB2Lecture 3 Relational Data Model 3Anatomy of a RelationEach relation is a table with a name.An attribute is a column heading.The heading is the schema of the relation Students(SSN, Name, Age, GPA)SID Name Age GPA 1002 John Smith 20 3.2 1005 Mary Day 18 2.9 1020 Bill Lee 19 2.7 StudentsRelation nameAttribute nameColumnTupleLecture 3 Relational Data Model 4Domain of an Attribute The domain of an attribute A, denoted by Dom(A), is the set of values that the attribute can take. A domain is usually represented by a type. E.g.,SID char(4) Name varchar(30) --- character string of variable length up to 30 Age number --- a numberLecture 3 Relational Data Model 5TuplesA tuple of a relation is a row in its table.If t is a tuple of a relation R and A is an attribute of R, we use t[A] to represent the value of t under A in R. Example: If t is the second tuple in Students t[Name] = ‘Mary Day’ t[Age] = 18, t[Name, Age] = (‘Mary Day’, 18)Lecture 3 Relational Data Model 6Schema and Instance A relation schema, denoted by R(A1, A2, …, An), consists of the relation name R and a list of attributes A1, …, An.R.A denotes attribute A of R.# of attributes = degreeA relation instance (state) of a relation schema R(A1, …, An), denoted by r(R), is a set of tuples in the table of R at some instance of time.# of tuples = cardinalityLecture 3 Relational Data Model 7Schema & Instance UpdateThe schema of a relation may change (e.g., adding, deleting, renaming attributes and deleting a table schema) but it is infrequentThe state of a relation may also change (e.g., inserting or deleting a tuple, and changing an attribute value in a tuple) & it is frequentA schema may have different states at different times.Lecture 3 Relational Data Model 8Relational Database A relational database schema is a set of relation schemas S={R1, …, Rm}. A relational database is a set of relations DB(S)={r(R1), …, r(Rm)}. A database state is a set of relation instances at some instance of time. In addition, a relational database must satisfy a number of constraints (more to come later).Lecture 3 Relational Data Model 9A University Databasesee p. 204, Fig. 7.5SID Name Major GPA 1002 J. Smith CS 3.2 1005 M. Day Math 2.9 1020 B. Lee EE 2.7 StudentsCno Sno Semester Prof CS374 001 F2000 Zhang CS455 002 S2000 Smith Math210 001 F1999 Brown SectionsCno Name Hour Dept CS374 Database 3 CS CS455 Network 3 CS CS100 Prog.Lang 4 CS Math210 Calculus 3 Math CoursesName Room Chair CS SB220 Hansen EE EB318 Johnson Math AB119 Miller DepartmentsLecture 3 Relational Data Model 10Constraints of Relational DB Relations must satisfy the following constraints. Domain (1NF) Constraint.Access-by-Content Constraint.Key (Unique Tuple) Constraint.Entity Integrity Constraint.Referential Integrity Constraint.Integrity constraints are enforced by the RDBMS.Lecture 3 Relational Data Model 11Domain ConstraintAlso known as the First Normal Form (1NF): Attributes can only take atomic values (I.e., set values are not allowed).How to handle multivalued attributes?Use multiple tuples, one per valueUse multiple columns, one per valueUse separate tablesWhat problems does these solutions have?Lecture 3 Relational Data Model 12Handle Multi-Valued AttributesEID Name Age Dependents 1234 Bob 34 {Allen, Ann} 1357 Mary 23 {Kathy} 2468 Peter 54 {Mike, Sue, David} EmployeesEID Name Age Dependents 1234 Bob 34 Allen 1234 Bob 34 Ann 1357 Mary 23 Kathy 2468 Peter 54 Mike 2468 Peter 54 Sue 2468 Peter 54 David EmployeesMultiple Values:Use Multiple Tuples:Lecture 3 Relational Data Model 13Handle Multi-Valued AttributesEID Name Age Dep1 Dep2 Dep3 1234 Bob 34 Allen Ann 1357 Mary 23 Kathy 2468 Peter 54 Mike Sue David EmployeesEID Name Age 1234 Bob 34 1357 Mary 23 2468 Peter 54 EmployeesEID Name 1234 Allen 1234 Ann 1357 Kathy 2468 Mike 2468 Sue 2468 David DependentsUse Multiple Columns:Use SeparateRelations:Lecture 3 Relational Data Model 15Access-by-Content ConstraintA tuple is retrieved only by values of its attributes, i.e., the order of tuples is not important.This is because a relation is a set of tuples. Although the order of tuples is insignificant for query formulation, it is significant for query evaluation.Lecture 3 Relational Data Model 16Superkey A superkey of a relation is a set of attributes whose values uniquely identify the tuples of the relation.Every relation has at least one superkey (default is all attributes together?). Any superset of a superkey is a superkey. From a state of a relation, we may determine that a set of attributes is not a superkey, but can not determine that a set of attributes is a superkey.Lecture 3 Relational Data Model 17Superkey ExampleFind all superkeys of the Students relation.SID Name Major GPA 1002 J. Smith CS 3.2 1005 M. Day Math 2.9 1020 B. Lee EE 2.7 StudentsA B C D A1 B2 C1 D2 A2 B2 C3 D2 A2 B1 C2 D1 A3 B3 C4 D1 RWith the only state of R, is A a superkey? What about {A, B}?Lecture 3 Relational Data Model 18 Candidate Key A candidate key of a relation is a set of attributes of the relation such that it is a superkey, andnone of its proper subsets is a superkey.Find all candidate keys in Students relation.Is it true that every relation has at least