Commonly used constraints: Keys: social security number uniquely identifies a person. Single-value constraints: a person can have only one spouse. Referential integrity constraints: if you work for a company, it must exist in the database. Domain constraints: peoples' ages are between 0 and 150. General constraints : all others (e.g., at most 50 students can enroll in a class)
31 Why Constraints are Important? • Give more semantics to the data – help us better understand it • Allow us to refer to entities (e.g, using keys) • Enable efficient storage – E.g., store ages as tiny integer (1 byte for example) • Enable efficient lookup – E.g., creating an index on key
32 Keys in E/R Diagrams address name ssn Person Product name category price No formal way to specify multiple keys in E/R diagrams Indicated by underlines:
33 More about Keys •Every entity set must have a key – why? •A key can consist of more than one attribute •There can be more than one key for an entity set – one key will be designated as primary key •Requirement for key in an isa hierarchy – Root entity set has all attributes needed for a key
34 Product name category price isa isa Educational Product Software Product Age Group platforms Subclasses in ER Diagrams
35 Single Value Constraint • An entity has at most one value for a given attribute or relationship • An attribute of an entity set has a single value or NULL – i.e., the value may be missing • A many-one relationship also implies a single value constraint makes Company Product
36 Referential Integrity Constraint • Ref. int. constraint: exactly one value exists in a given role • An attribute has a non-null, single value – this can be considered a kind of ref. int. constraint • However, we more commonly use such constraints to refer to relationships
37 Referential Integrity Constraints • In some formalisms we may refer to other object but get garbage instead – e.g. a dangling pointer in C/C++ • The Referential Integrity Constraint on relationships explicitly requires a reference to exist
38 Referential Integrity Constraints Company Product makes Company Product makes • This will be even clearer once we get to relational databases
Roadmap • What we will cover – basic stuff – subclasses – constraints – weak entity sets – design principles 39
40 Weak Entity Sets Entity sets are weak when (some or all of) their key attributes come from other entity sets to which they are related. This happens when: - part-of relationships - splitting n-ary relationships to binary. University Player affiliation number sports team name ) addr
41 Converting Multiway Relationships to Binary Purchase Person Store Product StoreOf ProductOf BuyerOf date ) ) )
42 Now, about design techniques ...
43 Design Principle 1: Be Faithful Purchase Product Person President Person Country Teaches Course Instructor
44 Design Principle 2: Avoid Redundancy Purchase Product Store date personName
You've reached the end of your free preview.
Want to read all 58 pages?
- Fall '14