|
AC 2
Database
and Table Creation
1. Create a
database named “yourname Emplist” on your USB device.
2. Create a
table and name it “yourname Employees”. Use the word table
below to define the fields, but read the instructions first.
This step is very important. Your queries will not work
right if you do not do define the table properly. All text
fields should have the appropriate size for the data
a. The table called “yourname
Employees” has the following characteristics.
·
Emp Number is
the primary key. It is not used in math calculations
·
Emp name must be
defined in two fields: Last Name, First Name. Do not forget to
make the sizes appropriate.
·
The date hired
field is used in a comparison and must be of the correct type
for queries to work properly.
·
SSN (Social
Security Number) is not used in calculations.
·
The Type field
should have the type and size set appropriately to the data.
|
Emp
Number |
Emp
Name |
Date hired |
SSN |
Type |
|
001 |
George Allen |
6/1/90 |
456-94-1234 |
Mgr |
|
002 |
Tom
Landry |
9/15/90 |
440-83-1234 |
Mgr |
|
003 |
Darryl Royal |
6/15/94 |
420-78-1234 |
Tech |
|
004 |
Casey Stengal |
6/1/65 |
367-80-1234 |
Clerical |
|
005 |
Billy Martin |
7/15/75 |
501-35-1234 |
Clerical |
|
006 |
Ed
Adams |
9/15/90 |
123-45-6789 |
Tech |
b. After you complete the
definition of the table, enter the data above
3. Create a
second table
a. Call it “yourname
Compensation” and define the fields as described below
- SSN
(Social Security Number) should be the primary key and it
will not be used in calculations
- The
hours per week fields and the salary rate fields will all be
used in calculations
- Salary
rate is a dollar type field and should have the appropriate
data type.
|
SSN |
Hours week1 |
Hours week2 |
Hours week3 |
Hours week4 |
Salary Rate |
|
456-94-1234 |
40 |
43 |
50 |
39 |
100 |
|
440-83-1234 |
45 |
45 |
50 |
35 |
150 |
|
420-78-1234 |
40 |
42 |
35 |
40 |
50 |
|
367-80-1234 |
40 |
40 |
55 |
35 |
10 |
|
501-35-1234 |
40 |
40 |
40 |
20 |
8 |
|
123-45-6789 |
50 |
50 |
10 |
50 |
40 |
b. Enter
the data above in the yourname Compensation Table.
|