Modeling Constraints Extracting constraints is what modeling is

Modeling Constraints Extracting constraints is what modeling is all about. But how do we express them? Examples: 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. Domain constraints: peoples’ ages are between 0 and 150. Why are these constraints useful in the implementation?

Single Value Constraints An entity (or object) may have at most one value for a given attribute or relationship. Person: name, social-security number Company: stock price How do we do this in ODL? In E/R, every attribute has at most one value. Arrows tell us about multiplicity of relations. If we have a single-valued constraint, we can either: 1. Require that the value exist (see referential integrity shortly) 2. Allow null values.

Referential Integrity Constraints A relationship has one value and the value must exist. Example: Product made. By Company: company must exist. How do we enforce referential integrity constraints? (otherwise, we get dangling pointers) - forbid to delete a reference object, or - delete the objects that reference an object we’re deleting. In E/R diagrams: Product makes Company

Weak Entity Sets Entity sets are weak when their key attributes come from other classes to which they are related. This happens if: - part-of hierarchies - splitting n-ary relations to binary. affiliation Team sport number University name

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

Terminology Attribute names tuples Product table Name Price Category Manufacturer gizmo $19. 99 gadgets Gizmo. Works Power gizmo $29. 99 gadgets Gizmo. Works Single. Touch $149. 99 photography Canon Multi. Touch $203. 99 household Hitachi What can’t you say in the relational model?

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 Gizmo. Works Sometimes we refer to a tuple by itself: (note order of attributes) (gizmo, $19. 99, gadgets, Gizmo. Works) or Product (gizmo, $19. 99, gadgets, Gizmo. Works).

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. Why?

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} Class becomes a relation, and every attribute becomes a relation attribute: Product Name Price Category Gizmo $19. 99 gadgets

Adding Non atomic Attributes Price is a record: {string currency, float amount} Product Name Currency Amount Category Gizmo US$ 19. 99 gadgets Power Gizmo US$ 29. 99 gadgets

Set Attributes Interface person{ string name; integer SSN; set of integers Phone Number; } One option: have a tuple for every value in the set: Name SSN Phone Number Fred Joe Disadvantages? 123 -321 -99 909 -438 -44 (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> made. By; } Interface Company { attribute string name; attribute float stock-price; attribute string address; } How do we incorporate the relationship made. By into the schema?

Option #1 Name Price made-by-name made-by-stock-price made-by-address Gizmo $19. 99 gizmo. Works What’s wrong? 0. 0001$ Montezuma

Hint Interface Product { attribute string name; attribute float price; relationship <Company> made. By; } 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 gizmo. Works 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?

From E/R Diagrams to Relational Schema Easier than ODL (using a liberal interpretation of the word “easy”) - relationships are already independent entities - only atomic types exist in the E/R model. Entity sets relations Relationships relations Special care for weak entity sets.

name category name price makes Company Product Stock price buys employs Person address name ssn

Entity Sets to Relations name category price Product: Name Category gizmo gadgets Price $19. 99

Relationships to Relations price name category Start Year makes name Company Product Stock price Relation Makes (watch out for attribute name conflicts) Product-name gizmo Product-Category Company-name gadgets gizmo. Works Starting-year 1963

Handling Weak Entity Sets affiliation Team sport number University name Relation Team: Sport mud wrestling Number Affiliated University 15 Montezuma State U. - need all the attributes that contribute to the key of Team - don’t need a separate relation for Affiliation.

Modeling Subclass Structure Product Platforms required memory age. Group topic isa Educational Product Software Product isa Educ-software Product Educational-method

Option #1: the ODL Approach 4 tables: each object can only belong to a single class Product(name, price, category, manufacturer) Educational. Product( name, price, category, manufacturer, age. Group, topic) Software. Product( name, price, category, manufacturer, platforms, required. Memory) Educational. Software. Product( name, price, category, manufacturer, age. Group, topic, platforms, required. Memory)

Option #2: the E/R Approach Product(name, price, category, manufacturer) Educational. Product( name, age. Group, topic) Software. Product( name, platforms, required. Memory) No need for a relation Educational. Software. Product Unless, it has a specialized attribute: Educational. Software. Product(name, educational-method)

Option #3: The Null Value Approach Have one table: Product ( name, price, manufacturer, age-group, topic, platforms, required-memory, educational-method) Some values in the table will be NULL, meaning that the attribute not make sense for the specific product. How many more meanings will NULL have? ?