Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

Develop a database for the Department of Public Safety which records students, their automobiles, the decal # of the cars, and the parking lots with

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- smith99@bryant.edu,
2. Ms. Mary Gitten, female, Townhouse A, Bryant University, Smithfield, RI,02917, commuter-No;
cell phone: 4012326111; email-gitten99@bryant.edu,
3. Mr. John Prichard, male, Townhouse B, Bryant University, Smithfield, RI,02917, commuter-No;
cell phone: 4012326222; email-prichard99@bryant.edu
4. Ms. Mary Wilson, female, Townhouse F, Bryant University, Smithfield, RI,02917, commuter-No;
cell phone: 4012326333; email-wilson99@bryant.edu
5. Mr. Mike Pence, male, 75 Hope Street, Smithfield, RI,02917, commuter-Yes; cell phone:
4012326444; email-pence99@bryant.edu
6. Ms. Hillary Dutton, female, Townhouse H, Bryant University, Smithfield, RI,02917, commuter-No;
cell phone: 4012326688; email-dutton99@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. Q1: List all students who are females and their details.
2. Q2: List all students who are male and are commuters and their details
3. Q3: List automobiles and their details for automobiles valued between $13000 and
$18000 and are assigned to lots A or C
4. Q4: 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

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Flash XML Applications Use AS2 And AS3 To Create Photo Galleries Menus And Databases

Authors: Joachim Schnier

1st Edition

0240809173, 978-0240809175

More Books

Students also viewed these Databases questions

Question

Ensure continued excellence in people management.

Answered: 1 week ago

Question

Enhance the international team by recruiting the best people.

Answered: 1 week ago