1 / 14

DATA MODELING AND DATABASE DESIGN Part 2

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. Most many to many relationships must be resolved before they can

tbroyles
Télécharger la présentation

DATA MODELING AND DATABASE DESIGN Part 2

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. DATA MODELING AND DATABASE DESIGN Part 2

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

  3. 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. Resolving Many to ManyRelationships CREATE an INTERSECTION ENTITY to control this relationship performs an EMPLOYEE_ NEW_FORCE ACTIVITY requires an

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

  5. 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

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

  7. EMPLOYEE # emp_no * position * salary o commission o activity1 o act1_start_date o act1_end_date o activity2 o act2_start_date o act2_end_date o activity3 o act3_start_date o act3_end_date Unnormalized Form

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

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

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

  11. 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 Entity Referential Column User-Defined Characteristics No part of a primary key can be NULL, and the value must be unique. Foreign key values must match a primary key or be NULL. Values in the column must match the defined datatype. Values must comply with business rules.

  12. 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 421 150 Composite Primary Key

  13. 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.

  14. 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 LAST_NAME FIRST_NAME ... DEPT_ID ... 1 Martinez Ana 10 2 Kim Toni 31 3 Takeshi Tofiro 21 4 McCoy James 10 5 Gaulet Chantal 20 DEPT Table Primary Key ID NAME REGION_ID 10 Corporate 1 20 HR 1 21 Sales 1 31 Stock 1 Primary Key

More Related