Develop a database for the Department of Public Safety which records students, their automobiles, the decal # of

the cars, and the parking lots with their capacities. The goal is to see that students are parking their cars in the

right parking lot and that the decal details matches the physical car.

A student may have one or more cars but a car can only belong to one student.

Decide on tables, their fields, the data$-$types of the fields, their primary and foreign keys if any.

Develop the queries

Develop a forms$($main form & sub$-$form.

Note that eventually, the system needs to handle $3000$ students, $4000+$ cars, and $40$ parking lots. The parking lot is

eventually going to have a lot more details than shown here such as location, lighting situation, etc

The database file should be of type $..$accdb.

A partial data relating to students are as follows:

$$

$1.$ Mr$.$ Charles Smith, male, $91$ Winter Street, Providence, RI$,02920,$ commuter$-$Yes; cell phone:

$4012326999$; email$-$ smith$99$@bryant.edu,

$2.$ Ms$.$ Mary Gitten, female, Townhouse A$,$ Bryant University, Smithfield, RI$,02917,$ commuter$-$No;

cell phone: $4012326111$; email$-$gitten$99$@bryant.edu,

$3.$ Mr$.$ John Prichard, male, Townhouse B$,$ Bryant University, Smithfield, RI$,02917,$ commuter$-$No;

cell phone: $4012326222$; email$-$prichard$99$@bryant.edu

$4.$ Ms$.$ Mary Wilson, female, Townhouse F$,$ Bryant University, Smithfield, RI$,02917,$ commuter$-$No;

cell phone: $4012326333$; email$-$wilson$99$@bryant.edu

$5.$ Mr$.$ Mike Pence, male, $75$ Hope Street, Smithfield, RI$,02917,$ commuter$-$Yes; cell phone:

$4012326444$; email$-$pence$99$@bryant.edu

$6.$ Ms$.$ Hillary Dutton, female, Townhouse H$,$ Bryant University, Smithfield, RI$,02917,$ commuter$-$No;

cell phone: $4012326688$; email$-$dutton$99$@bryant.edu

$$ The details of automobiles owned by the students are as follows.

$$ Mr$.$ Smith owns the following automobiles

$1.$

$1.(1)$ Plate# $2345,$ Mazda, Miata, $2011,$ value $$13500,$ Decal# $1$ and $(2)$ Plate #$1234,$ Acura,

MDX$,2013,$ value $$24500,$ Decal# $2.$ Both assigned to parking lot A$.$

$$ Ms$.$ Gitten owns the following: Plate # $5678,$ Ford, Fusion, $2017,$ value $$15500,$ Decal# $3.$ Assigned to

Parking lot B

$$ Mr$.$ Prichard owns the following automobiles

$1.$

$1.(1)$ Plate# $23456,$ Toyota, Corola, $2011,$ value $$17500,$ Decal# $4$ and $(2)$ Plate #$234511,$

Acura, RDX$,2017,$ value $$35500,$ Decal# $5.$ Both assigned to parking lot A

$$ Ms$.$ Wilson owns the following: Plate # $15678,$ Ford, Fusion, $2018,$ value $$15500,$ Decal# $6.$ Assigned to

Parking lot C

$$ Mr$.$ Pence owns the following automobiles

$1.$

$1.(1)$ Plate# $12345,$ Honda, Civic, $2017,$ value $$23500,$ Decal# $7$ and $(2)$ Plate #$11234,$

Acura, TL$,2013,$ value $$24500,$ Decal# $8.$ Both assigned to parking lot B

$$ Ms$.$ Dutton owns the following: Plate # $1567811,$ Ford, Escape, $2017,$ value $$15500.,$ Decal# $9$ Assigned to

Parking lot C

$$ Parking lot A has capacity for $400$ cars, parking lot B has capacity for $550$ cars and parking lot C has

capacity for $250$ cars

$$ Questions:

$1.$ Add $1$ more record for a student owning a single car $.$ Make up your own data in the format

above. The car values to be $$35000$ and the cars to be assigned to Parking lot A

$2.$ Develop tables that are needed to store details of students, cars and parking lots.

$3.$ Design tables with appropriate datatypes and assign primary keys to the tables and foreign keys

to relate the tables

$4.$ Insert data as above including your two records into the tables.

$5.$ Develop queries as follows:

$1.$ Q$1$: List all students who are females and their details.

$2.$ Q$2$: List all students who are male and are commuters and their details

$3.$ Q$3$: List automobiles and their details for automobiles valued between $$13000$ and

$$18000$ and are assigned to lots A or C

$4.$ Q$4$: List all student ID and Plate Ids for cars that are assigned to parking lot having

capacity more than $500$ cars and have car values above $$20000$

$5.$ Develop a main$-$form and sub$-$form where the main$-$form displays the details of the

student and sub$-$form displays the details of the automobiles