-> StartTime TIME, -> EndTime TIME, -> WeekDay TINYINT, -> TutorEmail varchar(40), -> FOREIGN KEY (TutorEmail) REFERENCES Tutor(Email) ); Explanation of Tables Client: Stores all information about a client (customer). Filled upon registration. Can be updated using the Profile’ button on the client profile page. ‘Edit Tutor: Stores first name, last name, and email address. IsAdmin boolean value determines whether the staff member has admin privileges. Appointment: Stores tutor email, student ID, start time, and duration. Times: Each entry stores a start and end time for a tutor. Multiple entries are made for each tutor each individual shift that they work (for each day). – The WeekDay field stores a number 1-7 inclusive, representing the day of the week. 1 represents Sunday, and 7 represents Saturday. This is used to match with the DAYOFWEEK(date) function in MySQL, which returns a number as described above. Appointment stores the DATETIME of the appointment. If a row is found in Times, and the time in Appointment doesn’t match the time in Times, and the currently selected date doesn’t match the date found in Appointments, display the time as available to be scheduled. x = select * from Times; y = select * from Appointments where DATE(StartTime) != selectedDate; for each value i in x: if( i.startTime NOT found in y ) display the appointment as available. User Interfaces Below is a list of all user interfaces: Client Side:
-> StartTime TIME, -> EndTime TIME, -> WeekDay TINYINT, -> TutorEmail varchar(40), -> FOREIGN KEY (TutorEmail) REFERENCES Tutor(Email) ); Explanation of Tables Client: Stores all information about a client (customer). Filled upon registration. Can be updated using the ‘Edit Profile’ button on the client profile page. Tutor: Stores first name, last name, and email address. IsAdmin boolean value determines whether the staff member has admin privileges. Appointment: Stores tutor email, student ID, start time, and duration. Times: Each entry stores a start and end time for a tutor. Multiple entries are made for each tutor – each individual shift that they work (for each day). The WeekDay field stores a number 1-7 inclusive, representing the day of the week. 1 represents Sunday, and 7 represents Saturday. This is used to match with the DAYOFWEEK(date) function in MySQL, which returns a number as described above. Appointment stores the DATETIME of the appointment. If a row is found in Times, and the time in Appointment doesn’t match the time in Times, and the currently selected date doesn’t match the date found in Appointments, display the time as available to be scheduled. x = select * from Times; y = select * from Appointments where DATE(StartTime) != selectedDate; for each value i in x: if( i.startTime NOT found in y ) display the appointment as available. User Interfaces Below is a list of all user interfaces: Client Side:
cs499f14estudio.docx