The Relational Data Model Database Model (ODL, E/R) ODL definitions

The Relational Data Model Database Model (ODL, E/R) ODL definitions Diagrams (E/R) Relational Schema Tables: row names: attributes rows: tuples Physical storage Complex file organization and index structures.

Terminology Attribute names tuples Name Price Category Manufacturer gizmo 19.99 gadgets GizmoWorks Power gizmo 29.99 gadgets GizmoWorks SingleTouch 149.99 photography Canon MultiTouch 203.99 household Hitachi

More Terminology Every attribute has an atomic type. Relation Schema: relation name attribute names attribute types Relation instance: a set of tuples. Only one copy of any tuple! Database Schema: a set of relation schemas. Database instance: a relation instance for every relation in the schema.

More on Tuples Formally, a mapping from attribute names to (correctly typed) values: name price category manufacturer gizmo 19.99 gadgets GizmoWorks Sometimes we refer to a tuple by itself: (note order of attributes) (gizmo, 19.99, gadgets, GizmoWorks) or Product (gizmo, 19.99, gadgets, GizmoWorks).

Updates The database maintains a current database state. Updates to the data: 1) add a tuple 2) delete a tuple 3) modify an attribute in a tuple Updates to the data happen very frequently. Updates to the schema: relatively rare. Rather painful.

From ODL to Relational Schema Start simple: a class definition has only single valued attributes Interface product{ float price; string name; Enum {telephony, gadgets, books} category} Every attribute becomes a relation attribute: Name Gizmo Price Category 19.99 gadgets

Adding Non atomic Attributes Price is a record: {string currency, float amount} Name Currency Amount Category Gizmo US 19.99 gadgets Power Gizmo US 29.99 gadgets

Set Attributes One option: have a tuple for every value in the set: Name Fred Fred Joe Joe Disadvantages? SSN 123-321-99 123-321-99 909-438-44 909-438-44 Phone Number (201) (206) (908) (212) 555-1234 572-4312 464-0028 555-4000

Modeling Collection Types The problem becomes even more significant if a class has several attributes that are set types? Question: how bad is the redundancy for n set type attributes, each with possibly up to m values? Questions: How can we model bags? Lists? Fixed length arrays?

Modeling Relationships Interface Product { attribute string name; attribute float price; relationship Company madeBy; } Interface Company { attribute string name; attribute float stock-price; attribute string address; } How do we incorporate the relationship madeBy into the schema?

Option #1 Name Price made-by-name made-by-stock-price made-by-address Gizmo 19.99 gizmoWorks What’s wrong? 0.0001 Montezuma

Hint Interface Product { attribute string name; attribute float price; relationship Company madeBy; } Interface Company { attribute string name; attribute float stock-price; attribute string address; relationship set Product makes; }

Better Solution Product relation: Name Gizmo (assume: name is a key for company) Price 19.99 made-by-name gizmoWorks Company relation: Name Stock Price Address Gizmo 0.00001 Montezuma

Additional Issues 1. What if there is no key? 2. What if the relationship is multi-valued? 3. How do we represent a relationship and its inverse?