ER Diagrams and Functional Dependencies Modeling Subclasses The

E/R Diagrams and Functional Dependencies

Modeling Subclasses The world is inherently hierarchical. Some entities are special cases of others • We need a notion of subclass. • This is supported naturally in object-oriented formalisms. Products Software products Educational products

Subclasses in E/R Diagrams name category price Product isa Software Product platforms isa Educational Product Age Group

Understanding Subclasses • Think in terms of records: – Product – Software. Product – Educational. Product field 1 field 2 field 3 field 1 field 2 field 4 field 5

Product Subclasses to Relations name category price Software Product platforms Price Category Gizmo 99 gadget Camera 49 photo Toy 39 gadget Sw. Product isa Name platforms Gizmo unix Ed. Product Educational Product Age Group Name Age Group Gizmo todler Toy retired

Modeling Union Types with Subclasses Furniture. Piece Person Company Say: each piece of furniture is owned either by a person, or by a company

Modeling Union Types with Subclasses Say: each piece of furniture is owned either by a person, or by a company Solution 1. Acceptable, imperfect Person Furniture. Piece owned. By. Person Company owned. By. Company (What’s wrong ? )

Modeling Union Types with Subclasses Solution 2: better Owner isa owned. By Company Person Furniture. Piece

Constraints in E/R Diagrams Finding constraints is part of the modeling process. Commonly used constraints: Keys: social security number uniquely identifies a person. Single-value constraints: a person can have only one father. Referential integrity constraints: if you work for a company, it must exist in the database. Other constraints: peoples’ ages are between 0 and 150.

Keys in E/R Diagrams name Underline: category price No formal way to specify multiple keys in E/R diagrams Product Person address name ssn

Single Value Constraints makes v. s. makes

Referential Integrity Constraints Product makes Company

Other Constraints <100 Product makes What does this mean ? Company

Weak Entity Sets Entity sets are weak when their key comes from other classes to which they are related. affiliation Team sport number University name

Handling Weak Entity Sets affiliation Team sport number Convert to a relational schema (in class) University name

The Relational Data Modeling E/R diagrams Relational Schema Tables: column names: attributes rows: tuples Physical storage Complex file organization and index structures.

Recalling The Terminology Table name or relation name Products: Name Category Manufacturer $19. 99 gadgets Gizmo. Works Power gizmo $29. 99 gadgets Gizmo. Works Single. Touch $149. 99 photography Canon Multi. Touch $203. 99 household Hitachi gizmo Price Attribute names Tuples or rows or records

First Normal Form (1 NF) • A database schema is in First Normal Form Student if all tables are flat Student Name GPA Courses Math Alice Bob Carol 3. 8 3. 7 3. 9 Name GPA Alice 3. 8 Bob 3. 7 Carol 3. 9 DB Takes OS Student Course Alice Math Course Carol Math Alice DB DB Bob DB OS Alice OS Carol OS DB OS Math OS Course

Functional Dependencies • A form of constraint – hence, part of the schema • Finding them is part of the database design • Also used in normalizing the relations

Functional Dependencies Definition: If two tuples agree on the attributes A 1 , A 2, … An they must also agree on the attributes B 1, B 2, … B m Formally: A 1 , A 2, … An B 1, B 2, … B m

Examples Emp. ID E 0045 E 1847 E 1111 E 9999 Name Smith John Smith Mary Phone 1234 9876 1234 Position Clerk Salesrep Lawyer • Emp. ID Name, Phone, Position • Position Phone • but Phone Position

In General • To check A B, erase all other columns • check if the remaining relation is many-one (called functional in mathematics) Note: this is the mathematical definition of a function. Book is wrong.

Example Emp. ID E 0045 E 1847 E 1111 E 9999 Name Smith John Smith Mary Phone 1234 9876 1234 Position Clerk Salesrep Lawyer

Typical Examples of FDs Product: name price, manufacturer Person: ssn name, age Company: name stockprice, president

Formal definition of a key • A key is a set of attributes A 1, . . . , An s. t. for any other attribute B, A 1, . . . , An B • A minimal key is a set of attributes which is a key and for which no subset is a key • Note: book calls them superkey and key

Examples of Keys • Product(name, price, category, color) name, category price category color Keys are: {name, category} and all supersets • Enrollment(student, address, course, room, time) student address room, time course student, course room, time Keys are: [in class]

Finding the Keys of a Relation Given a relation constructed from an E/R diagram, what is its key? Rules: 1. If the relation comes from an entity set, the key of the relation is the set of attributes which is the key of the entity set. Person(address, name, ssn) Person address name ssn

Finding the Keys Rules: 2. If the relation comes from a many-many relationship, the key of the relation is the set of all attribute keys in the relations corresponding to the entity sets name Product Person buys price name date buys(name, ssn, date) ssn

Finding the Keys Except: if there is an arrow from the relationship to E, then we don’t need the key of E as part of the relation key. sname Product name card-no Purchase Payment Method Person Store ssn Purchase(name , sname, ssn, card-no)

Finding the Keys More rules: • Many-one, one-many, one-one relationships • Multi-way relationships • Weak entity sets (Try to find them yourself, or check book)