Appointment Scheduler
Narrative
The purpose of this project is to design and develop a relational database system that gives professional offices the ability to automate all client appointment scheduling. The application will have user screens that allow employees and clients perform all functions necessary for appointment scheduling. You may also choose to build a subsystem where clients receive email reminders as their appointment date approaches or reminders about the need for scheduling their next appointment; never the less, the database needs to designed where this subsystem could be accommodated.
There can be a single office or an organization with an unlimited number of offices for which to make appointments and clients can be shared among any or all offices.
Each office will have the capability to define their own availability for their professionals. In other words, an office decides how their appointment scheduling is expected to work. Offices could set things up where each work day is broken up into n number of slots with certain times blocked off. For example, appointment-slots may be every ten minutes from 9:10am to 11:40am, break for lunch, and then from 12:30pm to 4:30pm each day excluding Tuesday because the office is closed for administrative purposes. Offices should have an easy way of blocking off holidays or any other days that they wish from being scheduled for appointments. For organizations with multiple offices the application allows the separate offices to view the schedules of the other offices’ professionals. Office can add new professionals to their staff. Offices need to also be able to inactivate professionals; all pending appointments of such professionals need to be reassigned but the historical data about their previous appointments need to remain in the database.
Each professional gets to control his or her own schedule by blocking off additional days or times within the frame work set for the office they work for. So, the professional can further customize their own schedule. They can also see other professionals’ schedules who work in their own office or any other office in the organization as well and, no different than a client, could schedule a meeting with them.
The client screens will prompt the user to select a location
if the organization has multiple locations. From this point the client can
either choose a specific professional to make an appointment with or can simply
enter a date and time to see which professional(s) is/are available. A client
should be able to see all of their appointments with whom they are, etc. Email notification to the office and the
professional informing them that an appointment has been made for them along
with emails about appointments to clients will earn you extra credit. Either
the professional or the office can choose to send the client a reminder ahead
of the upcoming appointment.
Each team is expected to propose a design for a database for this project. All will be graded and only one will be chosen that everyone will implement. Begin with drawing an ERD for your proposed database.
The logical model for every table/entity you identify should provide the following:
Table-name
(attri-list) + meta data table for attributes of that table. This
Thanks to all of you for your hard
work and excellent results; I could have chosen any of five or
six submissions from your database
designs. I had to picked one, I think, we'll all be happy with
it: Group 6-Middleton Michael, Brina Pelc, and Kevin Pytel.
The design with some updates will be chosen quickly. Then all groups will be expected to produce .sql files with insert statements for each table where there is benefit drawn from seeding data; for example, office, client, or professional entities.
Upon submission of your .sql files; assuming that they are created correctly, I will make a set of files available to everyone to seed some of the tables in their database. Here is a sample .sql file: faculty Inserts
Here is the .sql file for populating your tables in project 2: data.sql
The type Date supports both date and time concepts. There is no TIME type in Oracle. We are going into run into a problem with all these inserts in data.sql for the fields defined as TIME taking on several different forms. If the field is going to be a Date field holding only TIME; then it will need to work this way:
For example:
/* create a table with a date field called d */
create table
have_date(d Date);
/* insert into this table only a time for a the attribute d */
insert into have_date
VALUES (TO_DATE('08:00','HH:MI'));
/* select the what you stored */
select
to_char(d,'HH:MM') from have_date;
My suggestion is to use VARCHAR2;
and either throw out INSERTS where the format does not match what you wish to
process or update those inserts so that they all uniformly treat the concept of
TIME the same way.
The tables for the Scheduling app need to be created in
Oracle on ORA by each group in one of their database accounts using
sqlplus. Here
is how to get into sqlplus on ORA:
Here is a sample .sql file that creates a couple of tables and performs some inserts Employee. Here is another .sql file that does some queries: EmployeeSelect. Here is another .sql file doing other select operations and some updates: EmployeeUpdate.
Keep in mind that your .sql file should not need the inserts as I will be providing you with a set of .sql files to populate your tables. Your .sql files will have some DROP TABLE commands followed by a set of CREATE TABLE and CREATE INDEX commands. You will also create two other .sql files:
One script to create the following View for your database:
a. Professional/Client name pairs for all pending appointments in the database. With duplicates permitted if there are multiple appointments for the same client and professional. Pending means all appointments today or later.
b. List of all pending appointments for every professional including their name, their office, and the name of the client, day, time, and duration of the appointment.
Another script with a set of Select statements to respond to the following Queries:
a. Professional’s appointment scheduled for a specified day where the date and the professional are variables in the query.
b. Client’s appointment schedule for a specified day where again the date/time are variables.
c. Find professional and client pairs that have multiple appointments with each other; this query includes both historical and pending appointments. Historical refers to all appointments that have already taken place.
d. Find Clients and the count of appointments they have already had where that count is more than 2.
e. Provide a list of all offices with the count of professional that have more the average number of clients.
f. List the professionals and their offices that have no pending appointments.
g. List the office(s) with the most number of professionals that have no pending appointments.
Here you need to provide three roles for the database: Client, Office, and the professional. The narrative provided earlier should clarify what functions each role should provide. The app needs a login process through which the user’s role is identified. For example, office manager who has the office role will see that interface when he or she logs in; on the other hand, if a client logs in, he or she will see the appropriate interface designed for a client.