1 / 29

Relational Model

Relational Model. CS 157A Prof. Sin-Min Lee By Truc Truong. 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.

Download Presentation

Relational Model

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.


Presentation Transcript

  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

More Related