The purpose of this project is to analyze, specify, design, implement, document, and demonstrate a database management information system called the Georgia Restaurant Health Inspection System. The project will proceed in three phases as outlined in the Classical Methodology for Database Development: Analysis & Specification, Design, and Implementation & Testing. The system should be implemented using a Database Management System (DBMS) that supports standard SQL queries. Class administrators will provide you with information about how to access a college-managed MySQL server in order to implement your database. Your professor must approve alternative implementations. Under no circumstances may you use a tool that automatically generates SQL or automatically maps programming objects into the database.
Please refer to RestaurantInspection_v1.2.pdf for the project guidelines
Below are notes of all activities throughout the duration of the project.
Finished IFD Ask TA for verification and EER diagram help to be contd
ER diagram on drive started by Christine
Arnav attended TA office hours from 2:30 - 6 pm. Main focus on EER.
- Criteria should not be an entity. Instead, criteria is used for our SQL query statements.
- “Restaurant list” as an entity is not needed.
- Regarding users of the database, restaurant operators and inspectors require username and password to access database as registered users while customers don’t. So we can have two superclasses: registered users whose subclasses are restaurant operators and inspectors, and guests (a.k.a customer).
- A customer must create a complaint in order to be logged into the database. A complaint must be made. Many customers can complain about the same thing just as many complaints can be made by a customer. Complaint is a weak entity type with description, and Date as a partial key attribute; complaint is related to customer and the restaurant.
- Major changes from above added to EER. Will add necessary attributes to each entity later.
By Christine
- change customers to customer
- change customer to weak entity type
- Attribute added
TA suggested that for each GUI, we create an IFD. If you want to learn SQL quickly and easily, go to http://www.w3schools.com/sql/default.asp Questions to ask TAs:
- “Detailed Descriptions+Scores” as one single attribute?
Proposing the following assumptions (let’s make a list over here for now which we can add on to):
- Inspections occur annually for all the restaurants.
- Upon creating a restaurant, restaurant won’t have an inspection until a year after being in service.
- Upon being hired as a restaurant inspector, the inspector won’t immediately start inspecting restaurants (only in the beginning of the job for a very small amount of time, an inspector would have 0 inspection reports filed).
- Enter any assumptions starting here. Need to start creating list of constraints as well.
submitted phase 1
started mapping EER Ask TA: how to map derived attributes
Arnav created all SQL statements which will be checked during office hours.
Learn PHP GUIs needed coding
- guest restaurant search
- guest restaurant results display
- guest enter food/safety complaint
- operator enter restaurant info
- operator restaurant health inspection search
- operator restaurant health inspection search results
- inspector input of inspection report, comment on specific items
- inspector display report of restaurant inspections for specified month and year organized by county and cuisine in sorted order.
- Inspector run summary report about restaurant inspections for specified county and year.
Completed most of queries. Christine does 7d, Will does 6b, Arnav does 6a. Must finish before attending office hours.
Christine and Arnav will attend TA office hours, go over GUIs and corresponding queries, ask if queries meet all cases, ask about edge cases.