Asgn4-MS Access DB
Important: You need to watch Ch4 videos before doing this assignment in Chapter Videos and Handouts/Tutorials.
Description:
You run a local community organization that organizes volunteer work to serve and improve the community. You want to manage and keep track of all volunteers who volunteer to work for the community through the organization and the projects that the organization assigns these volunteers to.
The entities involved in this case are:
Volunteer
,
Project
,
Skill
, and
Volunteer-Project Assignment
.
Notes:
V-Skill in Volunteer is a lookup field from Skill-Code in Skill
Proj Type is a lookup field from Skill-Code in Skill
Vol-ID in Volunteer-Project Assignment is a lookup field from Vol-ID in Volunteer
ProjID in Volunteer-Project Assignment is a lookup field from ProjID in Project
Table structure
Sample Data
Skill(
SkillCode
, Skill Name)
Skill
Skill-Code
Skill Name
LS
Landscaping
Volunteer(
VolID.
V-First Name, V-Last Name, V-PhoneNum, V-Emial, V-Skill
Volunteer
Vol-ID
V-First Name
V-Last Name
V-PhonNum
V-Email
V-Skill
JS99
Joe
Smith
(419) 555-6666
SmithJ@xyz.com
LS
Volunteer-Project Assignment (
Vol-ID, Proj_ID
, Asgn Start Date, Asgn End Date)
Volunteer-Project Assignment
Vol-ID
Proj-ID
Asgn Start Date
Asgn End Date
JS99
LS-12
3/7/2019
3/14/2019
Project (
Proj-ID
, Proj-Name, Proj Type, Proj Start Date, Proj End Date)
Project
Proj-ID
Proj Name
Proj Type
Proj Start Date
Project End Date
LS-12
School Landscaping
LS
3/1/2019
3/25/2019
Instructions:
1. Using Access
Design View
in the
CREAT
ribbon, create the tables given above in this database. Use the field names and data types listed below:
Skill
SkillCode
Short Text (5 characters) Primary Key (PK)
Skill Name Short Text (25 characters)
Volunteer
VolID
Short Text (5 characters) Primary Key (PK)
V-First Name Short Text (15 characters)
V-Last Name Short Text (15 characters)
V-PhoneNum Short Text (12 characters), put a phone number mask on this field
V-Emial Short Text (25 characters)
V-Skill Short Text (5 characters)
Volunteer-Project Assignment
Vol-ID
Short Text (5 characters) Primary Key (PK)
Proj_ID Short Text (5 characters) Primary Key (PK)
Asgn Start Date Date/Time; Format: Short Date, put a short date mask on this field
Asgn End Date Date/Time; Format: Short Date, put a short date mask on this field
Project
Proj-ID
Short Text (5 characters) Primary Key (PK)
Proj-Name Short Text (35 characters)
Proj Type Short Text (5 characters)
Proj Start Date Date/Time; Format: Short Date, put a short date mask on this field
Proj End Date Date/Time; Format: Short Date, put a short date mask on this field
2. Create relationships between the tables as given in the Entity Relationship Diagram above.
3. Enter 4 records in each of the 3 tables including your name as a volunteer the Volunteer table, just make up the data.
4.
Save your DB
Watch this three videos
https://screencast-o-matic.com/watch/cYhufAp3y0
https://screencast-o-matic.com/watch/cYhue7p3TH
https://screencast-o-matic.com/watch/cYhufZp3w2
My name is Zhangrui Liu
My phone number is 419-3407486