Relational modeling.pdf - Relational Model Logical data model Data tables shows using ER will be stored as relational DB INF 551 Wensheng Wu 1 Lecture

Relational modeling.pdf - Relational Model Logical data...

This preview shows page 1 out of 46 pages.

You've reached the end of your free preview.

Want to read all 46 pages?

Unformatted text preview: Relational Model Logical data model. Data tables shows using ER will be stored as relational DB. INF 551 Wensheng Wu 1 Lecture Outline • Relational model • Translating ER into relational model 2 Motivations & comparison of ER with relational model ... 3 Database Modeling & Implementation Ideas High-level Design Diagrams (E/R) ER more richer because contains entity and relationships. Relational Model contains only tables. B+ Tree: (binary search key) one key. Relational Schema Tables: columns: attributes rows: tuples Physical storage Complex file organization and index structures. 4 ER Model vs. Relational Model • Both are used to model data • ER model has many concepts – entities, relationships, is-a, etc. – well-suited for capturing the app. requirements – not well-suited for computer implementation • Relational model – has just a single concept: relation – world is represented with a collection of tables – well-suited for efficient manipulations on computers 5 The basics of the relational model ... Relation Algebra: Sigma σ - subset of rows Pi Π - subset of columns Join |><| - 6 An Example of a Relation Table/relation name Products: Name Category Manufacturer $19.99 gadgets GizmoWorks Power gizmo $29.99 gadgets GizmoWorks SingleTouch $149.99 photography Canon MultiTouch household Hitachi gizmo tuples Attribute names Price $203.99 7 Domains • • • • Each attribute has a type Must be atomic type Called domain Examples: – – – – Integer String Real ... 8 Schemas vs. instances (very important, make sure you know the difference) 9 Schemas Schema: describe the structure of data The Schema of a Relation: – Relation name plus attribute names – E.g. Product(Name, Price, Category, Manufacturer) – In practice we add the domain for each attribute The Schema of a Database – A set of relational schemas – E.g. Product(Name, Price, Category, Manufacturer), Vendor(Name, Address, Phone), ....... 10 Instances Schema instance = data • Relational schema = R(A1, ..., Ak): Instance = relation (of "type" R) with a collection of tuples – Each has k values from the domains of their corresponding attributes • Database schema = R1(...), R2(...), ..., Rn(...) Instance = n relations, of types R1, R2, ..., Rn 11 Example Relational schema:Product(Name, Price, Category, Manufacturer) Instance: Name Price Category Manufacturer gizmo $19.99 gadgets GizmoWorks Power gizmo $29.99 gadgets GizmoWorks SingleTouch $149.99 photography Canon MultiTouch household Hitachi $203.99 12 Updates The database maintains a current database state. Updates to the data: 1) add a tuple insert/update 2) delete a tuple delete making changes to metadata 3) modify the values of some attributes in a tuple alter Updates to the data happen very frequently. Updates to the schema: relatively rare. Rather painful. Why? 13 Schemas and Instances • Analogy with programming languages: – Schema = type/class – Instance = value/instance • Important distinction: – Database Schema = stable over long periods of time – Database Instance = changes constantly, as data is inserted/updated/deleted 14 How should we talk about relations (that is, represent them)? 15 Two Mathematical Definitions of Relations Product(Name, Price, Category, Manufacturer) Relation as a subset of Cartesian product • Tuple = element of string x int x string x string • E.g. t = ("gizmo", 19, "gadgets", "GizmoWorks") • Relation = subset of string x int x string x string • Order in the tuple is important! Order of inserting elements to a table should be same as the order of the attributed of the table. – ("gizmo", 19, "gadgets", "GizmoWorks") – ("gizmo", 19, "GizmoWorks", "gadgets") • No (explicit) attributes (in tuple expression) 16 Relation as a set of functions • Fix the set of attributes – A={name, price, category, manufacturer} t(attribute name) = attribute value • A tuple = function t: A attribute domains • Relation = a set of tuples/functions • E.g. t(name) = "gizmo", t(price) = 19, t(category) = "gadgets", t(manufacturer) = "GizmoWorks" • Order in a tuple is not important • Attribute names are important 17 Examples of Insert • Positional tuples, without specifying attribute names – E.g., insert into Employee values (123, 'john', 35, 'los angeles) • Relational schemas with attribute names – E.g., insert into Employee(id, name) values (123, 'john') 18 Now the fun part: translating from ER to relational model 19 Translating ER Diagram to Rel. Model • Basic cases – entity set E => relation with attributes of E – relationship R => relation with attributes being keys of related entity sets + attributes of R • Special cases – combining two relations – translating weak entity sets – translating is-a relationships and subclasses 20 name price category product(name, category, price) An Example makes(prod_name, prod_category, com_name) In this case, only prod_name and prod_category are used for primary key because many to one relationship. makes name Company Product Relationship table using primary keys of product and person as foreign key. all together will be primary key. buys(prod_name, prod_category, person_ssn) in buys, all three will be key because all three needed for unique values buys Stock price employs Person address name ssn 21 Option 1: Merge makes and product tables (won’t cause any redundancy) product(name, category, price, com_name) only name and category required as key because of many to one relationship. won’t happen when same product and same category will be by a different company. Company name can be null, but won’t happen because we need to get stock price. ? Option 2: Merge makes with company (will cause redundancy) company(com_name, stock_price, prod_name, prod_category) - prod_name and prod_category as keys But since the stock_price is attached to the com_name, it will be repeated. Company name can be null. Final Conclusion: Relationship table can be merged with the many side entity table. Basic cases ... 22 Entity Sets to Relations name category price Product Product: Name Category gizmo gadgets Price $19.99 23 Relationships to Relations price name Not a good idea to merge with product or company because many to many category Start Year makes merged with product - price repeated name makes merged with company stock price repeated makes Company Product Stock price Relation Makes (watch out for attribute name conflicts) Product-name Product-Category Company-name Starting-year gizmo gadgets gizmoWorks 1963 24 Relationship to Relation: Another Example keys of likes table - (combination of drinker_name and beer_name) because manay to many name husband addr Drinkers 1 name Likes manf Beers 2 Buddies Favorite wife Married Likes(drinker, beer) Favorite(drinker, beer) Buddies(name1, name2) Married(husband, wife) husband and wife are one to one relation - so either husband or wife will be key 25 Special cases: 1) many-one relations 2) weak entity sets 3) is-a cases 26 Combining Two Relations price name category Start Year makes name Company Product No need for Makes. Just modify Product: name Stock price category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorks 27 Combining Relations • Combine relation for an m-1 relationship R with the relation for the entity set on the many side of R name addr Drinkers name Favorite manf Beers • Example: combine Drinkers(name, addr) and Favorite(drinker, beer) => Drinkers(name, addr, favoriteBeer). – But any drawback from doing this? 28 Risk with Many-Many Relationships • Combining Drinkers with Likes would be a mistake. It leads to redundancy, as: name addr Drinkers name Likes manf Beers name addr beer Sally 123 Maple Bud Sally 123 Maple Miller Redundancy 29 Handling Weak Entity Sets player(uname, sportst, num) - all keys affiliation Player sports_team Relation Player: SportTeam Trojan ) number University name aff(uname, sportst, num, uname) —> 3 from player and 1 from uni. But we dont need two uname. when we remove one, it becomes exactly like player table. so, dont need another table for affiliation. Number 15 Affiliated University USC - need all the attributes that contribute to the key of Player - don't need a separate relation for Affiliation. (why ?) 30 Handling Weak Entity Sets • Relation for a weak entity set must include attributes for its complete key (including those belonging to other entity sets), as well as its own, nonkey attributes. • A supporting (double-diamond) relationship is redundant and yields no relation. 31 Another Example name time Logins name At Hosts Hosts(hostName) Logins(loginName, hostName, time) At(loginName, hostName, hostName2) At becomes part of Logins Must be the same 32 Translating Subclass Entities P / \ sp ep OOP Product(name, price, category, manufacturer) Product Platforms required memory isa isa Educational Product Software Product P contains only product P / sp contains product + software product ageGroup topic P \ ep P / \ sp ep contains product + educational product contains product + educational product + software product 33 Option #1: the OO Approach 4 tables: each object can only belong to a single table One table for each subtree rooted at Product Product(name, price, category, manufacturer) products that arent educational and arent software EducationalProduct( name, price, category, manufacturer, ageGroup, topic) products that are educational and arent software SoftwareProduct( name, price, category, manufacturer, that arent educational and are platforms, requiredMemory) products software EducationalSoftwareProduct( name, price, category, manufacturer, ageGroup, topic, products that are educational and are software platforms, requiredMemory) (Values of) all names in different tables are distinct 34 Option #2: the E/R Approach Product(name, price, category, manufacturer) EducationalProduct( name, ageGroup, topic) SoftwareProduct( name, platforms, requiredMemory) No need for a relation EducationalSoftwareProduct The same name value (i.e., product) may appear in several relations 35 Option #3: The Null Value Approach Has one table: Product ( name, price, category, manufacturer, age-group, topic, platforms, required-memory) Some values in the table will be NULL, meaning that the attribute does not make sense for the specific product. Problem: too many NULLs 36 Translating Subclass Entities: The Rules Three approaches: 1. Object-oriented : each entity belongs to exactly one class; create a relation for each possible subtree including the root, with all its attributes. 2. E/R style : create one relation for each subclass, with only the key attribute(s) and attributes attached to that entity set. 3. Use nulls : create one relation; entities have null in attributes that don't belong to them. 37 Example name Beers manf isa color Ales 38 Object-Oriented name Beers isa color Ales manf name manf Bud Anheuser-Busch Beers name manf Summerbrew Pete's Ales color dark 39 E/R Style name Beers isa color Ales manf name manf Bud Anheuser-Busch Summerbrew Pete's Beers name Summerbrew color dark Ales finding color using manf will be difficult in ales table and OO is better that time. 40 Using Nulls name Beers manf isa color Ales name Bud Summerbrew manf Anheuser-Busch Pete's Beers color NULL dark 41 Comparisons • O-O approach good for queries like "find the color of ales made by Pete's." – Just look in Ales relation. • E/R approach good for queries like "find all beers (including ales) made by Pete's." – Just look in Beers relation. • Using nulls might waste space if there are lots of attributes that are usually null. 42 Mixed-Type Inheritance in PostgreSQL • CREATE TABLE cities (name text, population real, altitude int); • CREATE TABLE capitals (state char(2) ) INHERITS (cities); 43 Example • insert into capitals(name, population, altitude, state) values('Sancramento', 2000, 112, 'CA'); This is more like OO-approach 44 Caveat • PostgreSQL *logically" adds a tuple into cities – Cities may be regarded as view – View: (select * from "non-capital cities") union (select name, population, altitude from capitals) This is more of ER approach • delete * from capitals – Will remove "logical" tuple from cities as well 45 Translation Review • Basic cases – entity to table, relationship to table – selecting attributes based on keys • Special cases – – – – many-one relation can be merged merging many-many is dangerous translating weak entity sets translating isa hierarchy • 3 choices, with trade-offs 46 ...
View Full Document

  • Fall '14
  • Relational model, Summerbrew Pete

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

Stuck? We have tutors online 24/7 who can help you get unstuck.
A+ icon
Ask Expert Tutors You can ask You can ask You can ask (will expire )
Answers in as fast as 15 minutes