Please Read All Sections as the final solution is at the bottom of page, Table 5. All other information included in the tables is the way it was worked out to reduce redundancy.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Table 1
In the assignment, there was no mention of Participant Email...
|
TABLE |
TABLE |
|
V-Tel Site (PK) |
V-Tel Site (FK) |
| V-Tel Coordinator First Name | Participant First Name |
| V-Tel Coordinator Last Name | Participant Last Name |
| V-Tel Assistant First Name | Participant Phone |
| V-Tel Assistant Last Name | Participant Mailing Address |
| Participant City | |
| Phone | Participant State |
| Mailing Address | Participant Zip |
| City | |
| State | |
| Zip |
keep scrolling down.......
Table
2
(still working on a solution based on Wayne's examples.........also thought you might wish to have Participant email included in the
following schematic)
| Table Community V-Tel |
Table V-Tel Coordinator |
Table V-Tel Assistant |
Table Participants |
| V-Tel ID (PK) | V-Tel ID (FK) | V-Tel ID (FK) | V-Tel ID (FK) |
| V-Tel Site | Coordinator First Name | Assistant ID (PK) | Assistant ID (FK) |
| V-Tel Phone | Coordinator Last Name | Assistant First Name | Participant First Name |
| V-Tel Address | Coordinator Email | Assistant Last Name | Participant Last Name |
| V-Tel City | Coordinator Phone | Assistant Email | Participant Email |
| V-Tel State | Coordinator Address | Assistant Phone | Participant Phone |
| V-Tel Zip | Coordinator City | Assistant Address | Participant Address |
| Coordinator State | Assistant City | Participant City | |
| Coordinator Zip | Assistant State | Participant State | |
| Assistant Zip | Participant Zip |
keep on scrolling....
Table 3
(condensed version still has some redundancy)
| Table Community V-Tel |
Table V-Tel Coordinator* |
Table Participants |
| V-Tel ID (PK) | V-Tel ID (FK) | V-Tel ID (FK) |
| V-Tel Site | Coordinator First Name | Assistant First Name |
| V-Tel Phone | Coordinator Last Name | Assistant Last Name |
| V-Tel Address | Coordinator E-Mail | Participant First Name |
| V-Tel City | Coordinator Phone | Participant Last Name |
| V-Tel State | Coordinator Address | |
| V-Tel Zip | Coordinator City | Phone |
| Coordinator State | Address | |
| Coordinator Zip | City | |
| State | ||
| Zip |
* Perhaps, a coordinator is also a participant, in which case, there would be a coordinator ID established under the V-Tel Coordinator Table as a Primary Key (PK) and carried over to the Participant Table as a Foreign Key (FK). Are coordinators also participants????
I have designed these in such a way as to not include them as participants, but the above would be how I would implement them into the participants if, indeed, the tech coordinators were participating in the program.
TABLE 4:
I thought that this table has no more redundancy...
| Table Community V-Tel |
Table Participants |
| V-Tel ID (PK) | V-Tel ID (FK) |
| V-Tel Site | Assistant First Name |
| Coordinator First Name | Assistant Last Name |
| Coordinator Last Name | Participant First Name |
| Coordinator E-Mail | Participant Last Name |
| Phone | |
| Address | Phone |
| City | Address |
| State | City |
| Zip | State |
| Zip |
After last session, I felt as if there needed to be a different table as a final solution to the assignment. Please see below for (hopefully) final solution. Furthermore, I thought perhaps that you needed to have the web addresses of the participants and V-Tel assistants.
TABLE 5:
RITELink DB1 Assignment
FINAL SOLUTION
|
Table |
Table |
Table |
Table |
| Participant ID (PK) | Participant ID (PK) | Coordinator ID (PK) | V-Tel ID (PK) |
| Participant First Name | V-Tel ID (FK) | Coordinator First Name | V-Tel Site |
| Participant Last Name | Coordinator Last Name | V-Tel Phone | |
| Participant Web Site | Coordinator Email | V-Tel Address | |
| Participant Email | Coordinator Phone | V-Tel City | |
| Participant Phone | Coordinator Address | V-Tel State | |
| Participant Address | Coordinator City | V-Tel Zip | |
| Participant City | Coordinator State | V-Tel | |
| Participant State | Coordinator Zip | Coordinator ID (FK) | |
| Participant Zip | V-Tel ID (FK) | Participant ID (FK) | |
| Classification (Assist/Participant) | |||
| V-Tel ID (FK) |
