Defining Tables
Michael has a complete list of fields and attributes to be stored in the tables. He has also defined the structure of the database and the various reports to be generated. He decides to create his tables in design view.
| Field | Data Type |
| Social Security Number | Text |
| Employee’s Last Name | Text |
| Employee’s First Name | Text |
| Address | Text |
| City | Text |
| State | Text |
| Zip | Text |
| Phone | Text |
| Date Of Birth | Date/Time |
| Withholdings | Text |
| Emergency Contact Last Name | Text |
| Emergency Contact First Name | Text |
| Address | Text |
| City | Text |
| State | Text |
| Zip | Text |
| Phone | Text |
| Job Code | Auto Number |
| Job Position | Text |
| C | Yes /No |
| C++ | Yes /No |
| Java | Yes /No |
| Perl | Yes /No |
| HTML | Yes /No |
| Visual Basic | Yes /No |
| Javascript | Yes /No |
The Social security number and Job code are unique, and can therefore qualify as primary keys. Since each table can have only one primary key, the social security number will be the primary key in the employee table. Job code will be the primary key in the job table.
The two tables would contain the following information:
| Employee Table | Job Table |
| soc_sec_no | Job_code |
| emp_fname | Job_position |
| emp_lname | |
| Address | |
| City | |
| State | |
| Zip | |
| Phone | |
| birth_date | |
| Withholdings | |
| Job_code |
- We are splitting the tables because when we add job positions, we want to change the job table and not the employee table. Also we want to ensure that the admin does not enter jobs such as Programmer Analyst, programmer analyst, PROGRAMMER ANALYST, etc.
- The primary key of the job table is the foreign key in the employee table.
- The relationship between the job table and the employee table is a one-to-many relationship. i.e. for everyone job code listed in the job table there will be many occurrences of that job code in the employee table.
- The field names have been shortened but are descriptive of the field.
Having decided upon the employee table and the Job table the various fields and the relationships, Michael decides to add two more tables (emergency) contacts and skills. This table will contain the following fields
| Contact Table | Skills Table |
| soc_sec_no | soc_sec_no |
| emg_lname | C |
| emg_fname | C++ |
| emg_address | Java |
| emg_city | Perl |
| emg_state | HTML |
| emg_zip | VB |
| emg_phone | Javascript |
The relationships for each of the tables are as follows:
| Relationship | Table | Table | Remarks |
| One-to-one | Employee | Contact | For every one occurrence of a soc security no in the employee table there is exactly one occurrence of soc security no in the contact table |
| One-to-one | Employee | Skills | For every one occurrence of a soc security no in the employee table there is exactly one occurrence of soc security no in the skills table |
| One-to-many | Job | employee | For every one occurrence of a job code in the jobs table there could be many occurrences of the same job code in the employeetable |
