SQL problem
SAROO0OON
database_design.pptxRELATIONAL DATABASES & Database design
CIS276
| EmployeeNum | FirstName | LastName | DeptNum |
| 2173 | Barbara | Hennessey | 27 |
| 4519 | Lee | Noordsy | 31 |
| 8005 | Pat | Amidon | 27 |
Employee
Table Name
Field Names
Records (rows or tuples)
Fields (columns or attributes)
Tables
| StateAbbrev | StateName | EnterUnionOrder | StateBird | StatePopulation |
| CT | Connecticut | 5 | Robin | 3,590,347 |
| MI | Michigan | 26 | Robin | 9,883,360 |
| SD | South Dakota | 40 | Pheasant | 833,354 |
Primary Key
Alternate keys
Keys
State
| StateAbbrev | StateName | EnterUnionOrder | StateBird | StatePopulation |
| CT | Connecticut | 5 | Robin | 3,590,347 |
| MI | Michigan | 26 | Robin | 9,883,360 |
| SD | South Dakota | 40 | Pheasant | 833,354 |
| StateAbbrev | CityName | CityPopulation |
| CT | Hartford | 124,062 |
| CT | Madison | 18,803 |
| CT | Portland | 9,551 |
| MI | Lansing | 119,128 |
| SD | Madison | 6,482 |
| SD | Pierre | 13,899 |
Primary key (State table)
Keys
Composite primary key (City table)
Foreign Key
State
City
Relationships- One to Many
| EmployeeNum | FirstName | LastName | DeptNum |
| 2173 | Barbara | Hennessey | 27 |
| 4519 | Lee | Noordsy | 31 |
| 8005 | Pat | Amidon | 27 |
| DeptNum | DeptName | DeptHead |
| 24 | Finance | 8112 |
| 27 | Marketing | 2173 |
| 31 | Technology | 4519 |
Primary key for the one to many relationship
Primary Key
Foreign key for the one to many relationship
Employee
Department
1:M or 1:N
Relationships- One to One
| EmployeeNum | FirstName | LastName | DeptNum |
| 2173 | Barbara | Hennessey | 27 |
| 4519 | Lee | Noordsy | 31 |
| 8005 | Pat | Amidon | 27 |
| EmployeeNum | UserName | Password |
| 2173 | bhennessey | ******** |
| 4519 | lnoordsy | ******** |
| 8005 | Pamidon | ******** |
Employee
Credential
Primary key for the one to one relationship
Foreign key for the one to one relationship
1:1
Relationships- Many to Many
| EmployeeNum | FirstName | LastName | DeptNum |
| 2173 | Barbara | Hennessey | 27 |
| 4519 | Lee | Noordsy | 31 |
| 8005 | Pat | Amidon | 27 |
| PositID | PositDesc | PayGrade |
| 1 | Director | 45 |
| 2 | Manager | 40 |
| 3 | Analyst | 30 |
| EmployeeNum | PositID | StartDate | EndDate |
| 2173 | 2 | 12/14/2011 | |
| 4519 | 1 | 04/23/2013 | |
| 4519 | 3 | 11/11/2007 | 04/22/2013 |
| 8005 | 3 | 06/05/2012 | 08/25/2013 |
| 8005 | 2 | 07/02/2010 | 06/04/2012 |
Employee
Position
Employment
Primary Key (Employee table)
Primary Key (Position table)
Composite primary key of join table
Foreign keys related to the Employee and Position tables
M:N
Integrity Constraints
Entity integrity constraint
Primary key cannot be null
Referential integrity
Each non-null foreign key value must match a primary key value in the primary table
Domain integrity constraint
A domain is a set of values from which one or more fields draw their actual values
A rule you specify for a field (text size, validation rule, etc.)
Dependencies and Determinants
| EmployeeNum | PositID | LastName | PositDesc | StartDate | HealthPlan | PlanDesc |
| 2173 | 2 | Hennessey | Manager | 12/14/2011 | B | Managed HMO |
| 4519 | 1 | Noordsy | Director | 04/23/2013 | A | Managed PPO |
| 4519 | 3 | Noordsy | Analyst | 11/11/2007 | A | Managed PPO |
| 8005 | 3 | Amidon | Analyst | 06/05/2012 | C | Health Savings |
| 8005 | 4 | Amidon | Clerk | 07/02/2010 | C | Health Savings |
StartDate
EmployeeNum
PositID
HealthPlan
LastName
PlanDesc
PositDesc
Composite Key
Transitive Dependancy
Anomalies
| EmployeeNum | PositID | LastName | PositDesc | StartDate | HealthPlan | PlanDesc |
| 2173 | 2 | Hennessey | Manager | 12/14/2011 | B | Managed HMO |
| 4519 | 1 | Noordsy | Director | 04/23/2013 | A | Managed PPO |
| 4519 | 3 | Noordsy | Analyst | 11/11/2007 | A | Managed PPO |
| 8005 | 3 | Amidon | Analyst | 06/05/2012 | C | Health Savings |
| 8005 | 4 | Amidon | Clerk | 07/02/2010 | C | Health Savings |
Composite Key
Insertion anomaly
Cannot add a record to the table because you don’t know the entire PK value
Deletion anomaly
Delete data from a table and unintentionally lose other critical data
Update anomaly
Occurs when a change to one field value requires changing more than one
Normalization
The process of identifying and eliminating anomalies
Start with collection of fields
Apply sets of rules to eliminate anomalies
Final result- a new collection of problem-free tables
Applying third normal form is considered the design standard
First Normal Form
Each entry in a table must contain a single value
Composite Key
Composite Key
Second Normal Form
Remove partial dependencies from table
Identify functional dependencies, create new tables and place all fields into it
that are functionally dependent on the entire primary key
Second Normal Form
Third Normal Form
