Constraints 1 Overview Unique Identifiers Arcs Domains Various

Constraints 1

Overview Unique Identifiers Arcs Domains Various other constraints 2

Rembrandt 3

Identification and Representation G. Papini, please? EMPLOYEES Name Initials PAPINI HIDE PAPINI BAKER G. T. M. G. S. J. T. Birthdate 02 -FEB-1954 11 -JUN-1961 02 -FEB-1945 24 -SEP-1958 4

Unique Identifier Examples JOB COMPUTER IN NETWORK TELEPHONE Name IP Address Country code, Area code, Telephone number Employee number Name, Initials, Birth Date Name, Owner EMPLOYEE MAIL LIST 5 or

Unique Identifier Indicates Unique Identifier ORDER # Date by responsible for CUSTOMER # Family Name o Initials # Address o Telephone Indicates Unique Identifier 6

Unique Identifiers USER # Name owner of part of owned by contains MAIL LIST # Name ROOM # No FLOOR # No HOTEL # Name 7

Multiple Relationship UID USER # Name owner of part of contains is referred to owner of owned by LIST # Name contains referring to LIST ITEM 8 contained in

Well-defined Unique Identifiers Z # Z 1 o Z 2 o Z 3 # Z 4 Q # Q 1 P # P 1 Y # Y 1 # Y 2 K L # L 1 X # X 1 XY M # M 1 R # R 1 T # T 1 9 S

Incorrect Unique Identifiers K # K 1 L # L 1 G # G 1 F # F 1 P # P 1 KL T o # T 1 G # G 1 Q # Q 1 H 10 R # R 1

Information-Bearing Codes 54. 0. 093. 81 Product Group In Production? Factory Sequence Number PRODUCT GROUP # Code PRODUCT # Code * In Production? * Sequence No FACTORY # Id 11

Arcs Contract Conditions 1 2 3 4 5 6 Std? Indicates relationship in arc “A contract consists of contract components; these are standard conditions or customized conditions” CONTRACT STANDARD basis for CONDITION based consists in on of CUSTOMIZED CONDITION Arc in part of referring to CONTRACT COMPONENT 12

Exclusive Arc USER owner of owned by LIST container of is referred to contained referring to in LIST ITEM referring to is referred to 13

Possible Arc Constructs 14

Some Incorrect Arc Constructs • The arc “belongs” to one entity • Relationships in the arc must be of the same optionality • Arcs must contain at least two relationships An arc may be correct, but is quite difficult to implement. . . 15

Arc or Subtype USER ADDRESS owner of USER owner of owned by LIST is referred to contains referring to LIST is referred to referring to contains is referred to in referring to LIST ITEM in LIST ITEM 16

Arc and Subtypes A A 1 2 R P Q A A 3 B A B C 4 5 R P Q B C C Q P 17 R P Q

Subtypes Hide Relationships in Arc Every A is either a B or a C Every B is an A Every C is an A • Every A must be a B or be a C • • Every B must be an A Every C must be an A A A B is is C C is 18

Value sets YESNO # Code * Description A B GENDER # Code * Description CODE TYPE # Id * Name * Max Length of Description CODE # Code * Description WEEKDAY # Code * Description 19

Other Constraints: Range Check EMPLOYEE * Name * Address with JOB * Title * Minimum Salary * Maximum Salary of referring to for EMPLOYMENT * Start Date o End Date * Salary 20 between

EMPLOYEE * Name * Address * Current Marital Status Possible to Marital Status Transitions from Single Married Widowed Divorced Domestic Partnership 21 Sin Mar Wid Div DP Other Constraints: State Value Transition

Conditional Relationship CONTRACT # Id * Standard Indicator consists of STANDARD CONDITION in basis for based on CUSTOMIZED CONDITION in part of referring to CONTRACT COMPONENT 22

Boundaries unrelated entity EXTERNAL # Id * Description * Value and possible implementation EXTERNALS Id Description 1 2 3 4 Value added tax % Maximum available Space per Mail User in Mbyte Maximum level of Nested Mail Folders Maximum level of Nested Mail Lists 23 15 500 3 16

Summary Identification Can be a real problem in the real world Models cannot overcome this Entities must have at least one Unique Identifiers consist of attributes or relationships or both Arcs Many types of constraint are not represented in ER model 24

Practices Identification Please Identification Moonlight UID Tables Modeling Constraints 25