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.

†††††††††††

Milestones

 

1. ERD + Logical Design. Due 3/22/06

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 Meta data table must provide the type for each attribute, the size if necessary.Each attribute should be clearly marked if it participates in forming the primary key.If an attribute or a composite of attributes form a foreign key, identify the table referenced.So, the Meta Data table needs the following columns: Attribute, data type/size, if the attribute participates in Primary key, if the attribute is a foreign key and the table it references. Also, provide a description column that would explain the intent of the attribute; this one will only be filled in when the name is not adequate in describing the purpose of the attribute.

 

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.

 

2. .sql file to populate tables.Due 3/27/2006.

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.

3. Create tables/Indexes 4/3/2006

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.

 

4. The Scheduling Application Due: 4/21/2006

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.