The following are the business rules for the UHC HR database.
1.Each employee should be assigned a job code; a job code could belong to zero, one, or more employees.
2.Each employee must have a person record
3.Each employee must belong to one cost center
4.Each cost center must be part of at least one business unit; A business unit can have multiple cost centers
5.A benefit plan can have many employees; an employee is not required to enroll in a benefit plan.
Job Code
1. Job_ ID (number, PK) (you can create a sequence for this one. Starting with 5, increment by 5)
2. Job title (varchar)
3. Comp Grade (varchar)
4. FSLA_Status (Active or Not Active)
5. Job Family
6. Date_Created (can this be a trigger?)
Cost Center
1. CC_ ID (number, 4)
2. Cost Center Name (varchar)
3. BU_ID (FK)
Business Unit
1. BU_ID (PK)
2. BU Name (varchar)
3. Location (varchar)
4. Active (Yes or No)
5. Description
Person Record
1. National_ID (PK, number, 11)
2. DOB (Date)
3. Address 1 (varchar)
4. City (varchar)
5. State (varchar)
6. Zip (number, 5)
Benefits
1. Benefit Plan (PK, Varchar)
2. Vendor (varhcar)
3. Description (varchar)
4. Effective Date (date)
5. Amt. Deduction (number)
Employee
1. EE_ID (PK, number, 6) You can make this a sequence – starting at 100001 increment by 1
2. First_Name (varchar)
3. Last_Name (varchar)
4. Hire_Date (varhcar)
5. Benefit_Plan (FK)
6. National_ID (FK)
7. Job_ID (FK)
8. CC_ID (FK)
9. Date_Created (can this be a trigger?)
10. Date_Modified (a trigger as well)
Create Indexes for Natural, Foreign Key, and Frequently Queried Columns
Unlike primary keys which have unique indexes created automatically, you must create indexes for ever natural key that is not included in the primary/composite key as well as all foreign keys and frequently queried columns. Note: you may not yet have queries built for your database yet but you will during project part 4. Keep this in mind as you will need to create indexes to support these queries.
Create a Minimum of Two Sequences
You are required to create at least two sequences though if you are using surrogate keys this number will at least be equal to the number of entities that use said keys.
Create a Minimum of Two Triggers
You are required to create at least two triggers though the number of triggers should exceed this minimum if more than two sequences are deployed and to accommodate the automatic population of the auditing columns (see next requirement).