Estimated time to complete: 10-12 hours
In this phase of the project, you implement your design by creating tables and putting data into them, creating an Access database that links to the tables, and contains some forms and reports.
Create a new MySQL database named vbs.
Write CREATE TABLE statements to create tables needed for this phase (review these specs to determine which tables are needed). Note that you need to create only a subset of the tables in your design. Make sure to define foreign keys, and define unique constraints to enforce uniqueness on columns which are candidate keys. You may not use generative AI to help you do this step. Execute the statements to define tables in your MySQL database. 20 points.
Insert data into your database to create children, parents, and attendance records, using this sample data for the details. One way to do this is to create a SQL script with insert statements. You may use generative AI to assist you in creating the SQL script with INSERT statements if you wish. Note that the data in the CSV files will not necessarily match the structure of your database tables. For example, the CSV files have guardian name and phone mixed in with child data, but you should put the guardian data in a separate parent / guardian table. The CSV files have values like “Male” and “Yes,” but your database should use codes like ‘M’ and ‘Y’.
Note that you should assign classes to refreshment locations as follows:
20 points.
In this part of the assignment, you create an Access database that provides a front-end to your MySQL database. You will design a form and a report as specified below. Neatness counts: forms and reports must have fields that are layed out neatly and sized appropriately, and labeled with user-friendly names (don’t necessarily use the column names unless they make sense to an end-user).
You may not use Generative AI to help you create forms or reports.
Create an Access database named vbs.accdb that links to your MySQL VBS tables. Name your ODBC connection vbs.
Create a form named Registration that allows the user to view / update records in the table with data about children. The form should use Single Form view to display one record at a time. Gender, Grade Finished, Ride Bus, and Guardian fields should be dropdowns. The data in the dropdowns should be sorted alphabetically when the dropdown is open. There should also be a dropdown that shows the bus that the child is assigned to. All dropdowns should display text rather than foreign keys / codes. 20 points.
Create a report named Refreshments that displays the number of children at each refreshment location each day. The report should be grouped by day. For each day, display the date in a header section, and in the detail section give a list of refreshment locations along with the number of children at each location. Fit as many days on a page as possible.
No collaboration with other humans (other than the instructor) is permitted on this assignment. This includes debugging help. You may post questions clarifying assignment requirements on Microsoft Teams.
You may use generative AI as specified above.
Include the following Academic Integrity Statement at the end of your report, specifying any use of generative AI debugging help received.
“By affixing my signature below, I certify that the accompanying work represents my own intellectual effort. Furthermore, I have received no outside help other than what is documented below.”
Signed: ________ (Type your name here)
Help Report:
| Date | Person/AI Helping | Nature of Help | Time Spent |
Use the following command to export your MySQL database to a SQL script:
mysqldump -u root -p -R --set-gtid-purged=OFF --result-file=vbs.sql vbs
Review vbs.sql carefully to ensure that it contains the create table statements and insert statements that you expect. Also make sure that the attendance data matches my sample data; when I run your refreshment report I will expect to see numbers based on the attendance data I provided.
Submit the following to canvas:
Select * from Refreshments