Relational Model

1. Relational Model CS 157A Prof. Sin-Min Lee By Truc Truong

2. What is Relational Model? • Relational model is most widely used data model for commercial data-processing. The reason it’s used so much is, because it’s simple and easy to maintain. • The model is based on a collection of tables. Users of the database can create tables, insert new tables or modify existing tables. There are several languages for database programming. • SQL, Oracle, etc.

3. History of Relational Modeling • Introduced by Ted Codd in 1970 • Ted Codd was an IBM Researcher • Laid the foundation for database theory • Many database concepts & products based on his model

4. Relational Model Basic The relational model gives us a single way to represent data: as a two-dimensional table called a relation. • Attributes • Schemas • Tuples • Domains • Equivalent Representations of a Relation

5. Attributes Attributes of a relation serve as names for the columns of the relation. Usually, an attribute describes the meaning of entries in the column below. Table = relation. Column headers = attributes. Attribute

6. Schemas • The name of a relation and the set of attributes for a relation is called a schema. • We show the schema for the relation with the relation name followed by a parenthesized list of its attributes. • Relation schema = name(attributes) + other structure info., e.g., keys, other constraints. • Order of attributes is arbitrary, but in practice we need to assume the (standard) order given in the relation schema. • Relational database schema = collection of relation schemas. • So the schema for previous slide is Movies (title, year, length)

7. Database Schema • DataBase Schema • Logical View of the Database • Database Instance • A view of data in database at anytime. • Relation Schema • Corresponds to the programming language concept of type definition • E.g. Java • String movie = “Spiderman”; • Relation Instance • Corresponds to the programming-language concept of the value of a variable • E.g. Java • String movie = “Spiderman”;

8. Tuples • The rows of a relation, other than the header row containing • The attribute names are called tuples. • A tuple has one component for each attribute of the relation. Tuple

9. Domains • Each attribute of a relation is associated with a particular elementary type called domain. • The components of any tuple of the relation must have, in each component, a value that belongs to the domain of the corresponding column. • Example: • with titlestring is associated • with yearinteger is associated

10. Equivalent Representations of a Relation • Schemas are sets of attributes (not lists). • Tuples are sets of components (not lists). • Instances are sets of tuples (not lists) •  After permutation of rows and columns the relations remains the same! (permute values and attributes)

11. Equivalent Representations of a Relation cont. • Formal notion of a tuple= a function {attributes}  {values} • title  Star Wars • year  1977 • length  121 • filmType  color • (Star Wars, 1977, 121,color) and (1977, 121,color, Star Wars) are the same object.

12. Original Relation Modified Relation

13. Relational Data Model: summary • Relation as table • Rows = tuples • Columns = components • Names of columns = attributes • Relation name + set of attribute names= schema • REL (A1,A2,...,An) • Set theoretic • Domain — set of values • like a data type • Cartesian product (or product) • D1 D2 ...  Dn • n-tuples (V1,V2,...,Vn) • s.t., V1 D1, V2 D2,...,Vn Dn • Relation=subset of cartesian product of one or more domains • FINITE only; empty set allowed • Tuples = members of a relation inst. • Arity = number of domains • Components = values in a tuple • Domains — corresp. with attributes • Cardinality = number of tuples A1 A2 A3 ... An a1 a2 a3 an b1 b2 a3 cn a1 c2 b3 bn . . . x1 v2 d3 wn Attributes C a r d i n a l i t y Tuple Component Arity

14. Schema versus Instance • DB instances change continuously (e.g., movies are added, deleted, changed,…) • The schema is stable (attributes change almost never) • A RDB instance is the set of tuples that are ‘now’ in the DB • When designing the DB only the schema is important (=the structure of the data/DB) • We only imagine typical instances to help us with the design • Intentional level: schema • Extensional level: instances

15. E/R Diagrams to Relational Designs Creation of a DB: • Design phase (on “paper”, which information, relationships, constraints, …) • Implementation phase (real RDBMS) • It is “easier” to start from ODL or E/R and later convert to RM • RM has only one concept (relation) • E/R and ODL have complementary concepts and are more flexible (constraints, …) • Converting E/R design to a relational database schema: • Turn each entity set into a relation with the same set of attributes • Replace a relationship by a relation whose attributes are the keys for the connected entity set. *Weak entity sets cannot be translated straightforwardly to relations *”Isa” relationships and subclasses require careful treatment.

16. Relational Design cont. • Design in ODL or E/R (schema+constraints)  implementation in a RDBMS • Simplest approach (not always best): convert each ODL class or E/R entity set to a relation and each relationship to a relation. Class/Entity Set  Relation Relationship  Relation

17. From Entity Sets to Relations • An entity set that is not weak, is translated into a relation with the same name and attributes. • E.g., Movie(title, year, length, filmType) Star(name, address) or Star(name, street, city) Stars-in Owns Movies Star Studios name title length address year address name filmType

18. From E/R Relationships to Relations E/R relationships are also translated to relations: • For each entity set involved in R, take key attribute(s) as part of schema • If the relationship has attributes, add them to the schema • If an entity set appears more than once in a relationship, rename its attributes to avoid doubles and for clarity! • E.g., Owns(title, year, studioName) Stars-in Owns Studios Movies Star name title length address year address name filmType

19. Relationships to Relation cont. • If an entity set appears more than once in a relationship, rename its attributes to avoid doubles!name • E.g., Contracts(title,year,starName, studioOfStar,producingStudio) Studio Producing studio Studio of star Contracts Star Movies

20. Handling Weak Entity Set If there is a weak entity sets W we do the following differently: • Attributes of W plus key attributes of other entity sets that contribute to the key of W (double-diamond; many-one). • Any relationship in which W appears must use as a key for W all of its attributes including those of the other entity sets that contribute to W’s key • Double-diamond relationships from W to another entity set do not need to be converted (this information is already in the relation for W).

21. name number address Studios Crews Unit-of Studios(name, address) Crews(number,studioName) Unit-of(number,studioName,name) Unit-of(number,name) are the same (many-one!) (Disney crew #3, Disney)  (3, Disney, Disney)

22. address name Studios Contracts(starName, studioName, title, year, salary) Relations for Movie-of, Star-of and Studio-of are superfluous… Studio Of Stars Star-of Movies Contracts Stars MovieOf Contracts StarOf length name salary title year address filmType

23. Converting Subclass Structures to Relations Differences between E/R and ODL: • In ODL: an object belongs to exactly one class. It inherits properties from superclass. • In E/R: an object may belong to several entity sets related by isa.

24. Structures to Relations cont. • Every subclass has its own relation • It has all properties of this subclass including inherited properties • E.g., Movie(title,year,length,filmType,studioName,starName) Cartoon(title,year,length,filmType,StudioName,starName, voice) • MurderMystery(title,year,length,filmType,StudioName,starName,weapon) • Cartoon-MurderMystery(title,year,length,filmType, StudioName,starName,voice, weapon)

25. An Object-Oriented Approach A hierarchy is populated by entities related by isa’s. • No relation is created for the isa relationship. • For each entity set, a separate relation with its own attributes plus key attributes of related attribute sets. Movies year isa title Bugs Bunny isa length filmType Voices Cartoons Murder- Mysteries weapon

26. Differences between E/R and ODL: • In ODL: all properties of an object together in one relation; we have to search 4 relations to find a movie object! Movie(title,year,length,filmType,studioName,starName) • Cartoon(title,year,length,filmType,StudioName, starName,voice) • MurderMystery(title,year,length,filmType,StudioName,starName,weapon) • Cartoon-MurderMystery(title,year,length,filmType, StudioName,starName,voice,weapon)

27. Differences between E/R and ODL: • In E/R: a key of an entity is repeated once for every entity set and relationship it belongs to (scattered information).

28. Using Null Values to Combine Relations • ODL: Information of a hierarchy can be given in one relation • NULL = “there is no appropriate value for this attribute” • E.g., Movie(title,year,length,filmType,StudioName, starName,voice, weapon)

29. References • Jeff Ullman and Jennifer Widom, A First Course in Database systems. 2nd edition, Prentice Hall. • Prof. Lee “Relation Model” Presentation • science.kennesaw.edu/~mguimara/8080/ch3_4.ppt • www.cs.niu.edu/~sheng/CSCI588/Lecture2.ppt