Show me the steps to solve on microsoft access.

IDS $200$ Fall $2024$ Assignment $1$

Due Friday $9/27/2024$

$45$ points

Instructions

Deadline

In order to receive full credit, this assignment must be submitted by Friday, September $27$th at $11$:$59$pm$.$ Submitting your assignment early is recommended. Late submissions will be accepted with a $15$pt penalty up to one week after the submission deadline. After that, assignments will not be accepted.

Assignment Overview

Built a Microsoft Access database file for managing employee, project, and vendor data with tables, fields, relationships, and queries as given below.

Submit your Access file $-$ the $.$accdb file NOT any lock file with the $.$laccdb extension.

Tables should not include any record data $$ TAs may import data to test your work. However, you may find it useful to temporarily add some data to your tables for query testing.

Checklist

Here are some sensible checks before you click that upload button:

$1.$ Check your fields and make sure they match all the assignment specifications.

$2.$ Check your relationships $-$ referential integrity is unnecessary here and can make testing more difficult.

$3.$ Test your queries: write one matching & one non$-$matching record for each query. then, when you run the query, you should get only the matching result. and if you do$,$ then your query is probably correct.

$4.$ Delete any records you have created in step $3.$

Assignment

Your organization needs a database to track its employees and projects. Each employee belongs to a team, which could contain many employees. Each team has a lead employee and is assigned to a project, which could have many different teams. Each project is also covered by one vendor. Employees and vendors are uniquely identified by email addresses, while projects and teams are uniquely identified by numbers.

$1)$ Create tables with the field specifications below. Primary key fields are highlighted in yellow and foreign key fields are highlighted in blue.

$$ Employee: email $($short text, maximum length $30),$ lastName $($short text, maximum length $24),$ firstName $($short text, maximum length $16),$ streetAddress $($short text, maximum length $32),$ zipCode $($short text with $00000-0000$ input mask$),$ phone $($short text with $000-000-0000$ input mask$),$ team $($number$)$

$$ Project: id $($AutoNumber$),$ name $($short text, maximum length $30),$ description $($short text, maximum length $255),$ zipCode $($short text with $00000-0000$ input mask$),$ vendor $($short text, maximum length $40)$

$$ Team: id $($AutoNumber$),$ lead $($short text, maximum length $30),$ project $($number$)$

$$ Vendor: email $($short text, maximum length $40),$ name $($short text, maximum length $32),$ description $($short text, maximum length $255),$ zipCode $($short text with $00000-0000$ input mask$),$ phone $($short text with $000-000-0000$ input mask$)$

$$ ZipCode: zip $($short text with $00000-0000$ input mask$),$ city $($short text, maximum length $32),$ state $($short text, maximum length $16)$

$2)$ Create the relationships below, linking all foreign key fields to the corresponding primary key field in the original tables

$$ Employee.zipCode, Project.zipCode, and Vendor.zipCode to ZipCode.zip

$$ Employee.team to Team.id

$$ Project.vendor to Vendor.email

$$ Team.project to Project.id

$3)$ Queries:

$$ Show the firstName, lastName, and phone fields of every employee on a team working on the Project with id $=101$

$$ Show the id and project fields $($only$)$ of every Team where the Team lead is $$Albert Alfredo$$

$$ Show all fields of every Project where the Vendor is $$GFS Delivery Services$$

$$ Show all zip fields of every ZipCode where the city is $$Chicago$$ and the state is $$Illinois$$

$$ Show the email, firstName, and lastName of every Employee with a zipCode where the state is either $$Illinois$$ or $$Wisconsin$$

$$ Show the id and lead fields for every Team where the Project vendor is $$Burger King$$