The goal of this database is to connect students who need a ride to a vaccination site with sites that have vaccines available using RIT owned cars driven by RIT employees. To obtain we need to gather information on the students who need a ride, the vaccination sites with available vaccines, the types of vaccines they have available (including one shot or two), the vehicles that will be used to drive the students to the vaccination sites and the RIT employees who will drive the vehicles. The central entity in all of this will be the "appointment".

Business Rules:

To make this happen, imagine we have data entry people who will interact with the vaccination sites to find out how many, and at what time they have appointments available. The data entry people will also interact with students who want to get vaccinated and will schedule drivers and vehicles to take the students to the sites.

Students will be able to sign up for a ride that leaves at a given time, from one of three locations on campus (you can call them whatever you want). The students would then be driven (in our vehicle with our employee as the driver) to a vaccination site where we have set up an appointment for the student. Vaccination site scheduling is out of scope for this project, your goal is to assign students to appointments. Because this is important, students, should be alerted via text or email the day before their appointment and 30 minutes before their ride leaves.

Vehicles of different capacity will be used to drive students. We have four, five passenger sedans, two, nine passenger vans and one, 25 passenger bus. Drivers need to be scheduled to drive the vehicles; all drivers can drive all vehicles. Drivers have an employee number, name, and phone number (just in case we need to get in touch with them). Vehicles have a Vehicle Identification Number, a capacity by passenger, a and a color.

There are four sites available for students to get vaccinations. Each site has a name, address, opening and closing time (you may make them up), and administers one type of vaccine. You can assume that as long as the site is open, students can get an appointment to be vaccinated at the site. You are not responsible for anything related to the sites.

There are only three types of vaccines available; Pfizer, Moderna, and Johnson & Johnson. The Pfizer and Moderna vaccines require that students return to the same location for a second appointment two weeks after the first. The Johnson & Johnson vaccine only requires one appointment.

You might want to have an appointment table that combines information from the other tables. Even though it might violate the rules for normalization, you may have an appointment one and appointment two if you need to do that for the Pfizer and Moderna vaccines.

One easy way to message students about upcoming appointments might be to have a messages table. The messages table might have three records, one that says "Studentname, you have an appointment tomorrow at xx:xx to get the XX vaccine", another record that says "Studentname, your appointment is in fifteen minutes at the YY bus stop", and a last record that says "Studentname, congratulations for getting your XX vaccine today."

You might want to use the following E-R Diagram as a starting point for your database. It might not be the best diagram as it uses the appointment table to store links to all the other tables. What are your thoughts about this? (attached)

The goal for this database is to create one functional and useful database that connects students with sites, vehicles with drivers and reminders. You don't have to store tons of data, just enough to get the job done.

1) Develop an E-R Diagram that shows the tables and relations between the tables.

2) Normalize your data at least through the first normal form.

3) Develop the relations for each of the tables, don't forget to include the constraints and mei (must exist in) statements for the primary and foreign keys.

4) Write the SQL statements necessary to create one database called 'covid_1and implement the E-R diagram as tables in your database. You will need to implement all appropriate and needed constraints based on the E-R diagram and specifications provided.

5) Enter data using "inset into" statements for ten students, to go to one of three sites, over a period of two weeks in August, for one of three vaccines, driven by one of four drivers.

6) Write queries that will generate reminders for the students.

7) Write queries that will generate a schedule for each of your drivers.

8) Use a tool (I like visio, but you can use any tool you prefer) that will mock-up the data entry screen that your data entry people might use to enter a student appointment.

4.15. In a binary communication system the signals A, OSt