Commonly used constraints Keys social security number uniquely identifies a

Commonly used constraints keys social security number

This preview shows page 30 - 45 out of 58 pages.

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)
Image of page 30
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
Image of page 31
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:
Image of page 32
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
Image of page 33
34 Product name category price isa isa Educational Product Software Product Age Group platforms Subclasses in ER Diagrams
Image of page 34
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
Image of page 35
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
Image of page 36
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
Image of page 37
38 Referential Integrity Constraints Company Product makes Company Product makes This will be even clearer once we get to relational databases
Image of page 38
Roadmap What we will cover – basic stuff – subclasses – constraints – weak entity sets – design principles 39
Image of page 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
Image of page 40
41 Converting Multiway Relationships to Binary Purchase Person Store Product StoreOf ProductOf BuyerOf date ) ) )
Image of page 41
42 Now, about design techniques ...
Image of page 42
43 Design Principle 1: Be Faithful Purchase Product Person President Person Country Teaches Course Instructor
Image of page 43
44 Design Principle 2: Avoid Redundancy Purchase Product Store date personName
Image of page 44
Image of page 45

You've reached the end of your free preview.

Want to read all 58 pages?

  • Fall '14

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture