DATA MODELING AND DATABASE DESIGN Part 2 Objectives

DATA MODELING AND DATABASE DESIGN Part 2

Objectives • Resolving ERD with LINK entities • Recursive relationships • Levels of normalization • Integrity constraints • Candidate keys, primary and foreign keys

Resolving Many to Many Relationships Most many to many relationships must be resolved before they can be mapped to a database design. We resolve that by creating new entity called LINK or INTERSECTION ENTITY. CREATE an INTERSECTION ENTITY to control this relationship EMPLOYEE_ NEW_FORCE performs an requires an ACTIVITY

RESOLVED ERD performed by EMPLOYEE -ACTIVITY * per_diem_rate performs an EMPLOYEE_ NEW_FORCE # emp_no * position o job_description * salary o commission for requires an ACTIVITY # activity_id * description o start_date o end_date * internal

Recursive Relationships • Define a relationship between an entity and itself as a recursive relationship. • Represent that relationship with a “pig’s ear. ” EMPLOYEE #* id * last name o first name * salary * start date o manager_id managed by the manager of

Levels of Normalization Benefits • Minimizes data redundancy • Reduces integrity problems • Identifies missing entities and relationships Level Characteristics First normal form All attributes must be single-valued. Second normal form An attribute must depend upon its entity's entire UID (not only on part of UID) Third normal form No non-UID attribute may be dependent upon another non-UID attribute.

Unnormalized Form EMPLOYEE # emp_no * position * salary o commission o activity 1 o act 1_start_date o act 1_end_date o activity 2 o act 2_start_date o act 2_end_date o activity 3 o act 3_start_date o act 3_end_date

First Normal Form EMPLOYEE # emp_no * position * salary o commission ACTIVITY # activity_id * description o start_date o end_date

Convert to third Normal Form EMPLOYEE employed # id in * last name has * department location DEPARTMENT # id * name

Third Normal Form EMPLOYEE # id * last name employed in has DEPARTMENT # id * name * location

Integrity Constraints • Ensure data consistency • Should be enforced by the database server or the application software • Correspond to keys – Primary key, unique key and foreign key Type Characteristics Entity No part of a primary key can be NULL, and the value must be unique. Referential Foreign key values must match a primary key or be NULL. Column Values in the column must match the defined datatype. User-Defined Values must comply with business rules.

Defining a Primary Key • A primary key (PK) allows no duplicate values and cannot be NULL. • Each row is uniquely identified by a column or set of columns (composite primary key). • There may be only one PK, but there may be many candidate keys. Table EMPLOYEE_ACTIVITY Emp_no activity_id per_diem_rate ----------------------12 1011 500 15 1011 400 16 910 300 07 Composite Primary Key 421 150

Defining a Foreign Key • A foreign key (FK) is a column in one table that refers to a PK in the same table or in another table. • The value must either match the PK value in the ‘parent table’ or be NULL. • If FK is part of a PK, then it cannot be NULL.

Defining a Foreign Key: Example The DEPT_ID column is an FK in the EMP table, and refers to values in the ID column of the DEPT table. Foreign Key EMP Table ID 1 2 3 4 5 LAST_NAME Martinez Kim Takeshi Mc. Coy Gaulet FIRST_NAME Ana Toni Tofiro James Chantal . . . DEPT_ID 10 31 21 10 20 . . . DEPT Table Primary Key ID 10 20 21 31 NAME Corporate HR Sales Stock Primary Key REGION_ID 1 1