Eat and Drink Database

Problem Description

Eat&Drink is a start-up company owned by two fresh graduates from Curtin University. The company is planning to launch a website that would allow people to search for information about restaurants in Perth. The following is a list of requirements from a preliminary analysis:

Search Function

A visitor to the site, “Alice,” should be able search for restaurants that matching some combination of:

• The name or part of the name of a restaurant.
• Cuisine type / regional origin (e.g., “Chinese”, “Indian”, “Japanese”, “Italian”, “Vegetarian,” etc.). [Note: A restaurant can serve different cuisine types and food from different regional origins.]
• Location identified as a neighborhood (e.g., “Bentley”) or postal code (e.g., “6102”).
• The search should result in a list of restaurants with a name of each restaurant and its average subscriber rating.

Details about Restaurants

From the list of restaurants returned, Alice should be able to click on a restaurant and see the following information about it:

• The name of the restaurant
• The address and phone number
• Opening hours
• Web URL (if available)
• Cuisine types / regional origins
• Price range (from “$” to “$$$$$”)
• Up to three most recent comments from subscribers, which would include a rating, from one to five stars.
• The average of subscriber ratings
• A list of available dishes

Subscribers’ Comments

A visitor can subscribe to the website and make comments on the restaurants. The website records the following information for each subscriber: user name, password, email, gender, date of birth, home address, and work address.

• When looking at a comment by “Bob”, Alice should be able to click on Bob's name to see Bob's profile and the list of all Bob's comments. The list of Bob's comments show which restaurant each comment refers to.
• Comments on restaurants should be “threaded.” That is, Alice should be able to post a comment in response to a comment by Bob. The web interface should somehow show which comments are in response to other comments.
• Alice should be able to mark Bob's comment as “helpful” or “unhelpful.” When applicable, comments would be shown with a note saying something like “24 out of 32 people found this helpful.”

The database does not need to support any features beyond those outlined above.

Tasks

Design (ER Diagram + Business Rules + Data Dictionary)

Create a data model for Eat&Drink. The design has to be in 3NF. This is not straightforward and you will probably work through several design iterations. Do not include early designs in your documentation.

If any parts of your design depend on information not provided in the outline above, then you should explain your assumptions. (However, you assumptions should not directly contradict the stated requirements.)

For example, some groups may assume that Eat&Drink allows users to review a restaurant several times, whereas other groups may assume that Eat&Drink allows users to make no more than one review for each restaurant. Both assumptions are valid, but they need to be stated as part of the design documentation.

Marks will be deducted for assumptions that do not make any business sense or assumptions that are made to simplify the design exercise. For example, an assumption that each comment can only receive one response simplifies the design significantly but it does not make a lot of business sense. This kind of assumptions should be avoided.

Implementation

• Write SQL statements to implement your design in MS Access (i.e., table creation). Save the SQL statements in MS Access. Remember to include primary key and foreign key constraints in the SQL statements. Other constraints can be implemented using the graphical user interface in MS Access.
• Input enough data (however much you decide) so that you can fully test your design and the queries you will write for Part 3. There is no need to use SQL statements to insert the data. You can enter the data directly in the Datasheet view in MS Access.

Queries Supporting Restaurant Search and Subscribers’ Comments

A web developer will be hired to take care of the actually web development and the design of the user interface. However, you will need to provide him with the following queries that support the restaurant search and subscribers’ comments functions.

• Return a list of restaurants given the following information:
  • the name or part of the name of a restaurant
  • the cuisine type / regional origin
  • the neighborhood (e.g., “Bentley”) or postal code (e.g., “6102”)
• Return the properties of a given restaurant. The properties that need to be returned are:
  • The name of the restaurant
  • The address and phone number
  • Opening hours
  • Web URL (if available)
  • Cuisine types / regional origins
  • Price range (from “$” to “$$$$$”)
  • A list of available dishes
• List up to three most recent subscriber comments for a restaurant, together with their ratings.
• Compute the average subscriber rating for a restaurant.
• Compute the number of people who find a comment helpful.
• Compute the total number of people who have rated the helpfulness of a comment.

Save the above queries in MS Access.

Business Intelligence

The owners of Eat&Drink would like to recommend a new restaurant to subscribers who will most likely like the restaurant.

Come up with a strategy that will identify the subscribers who will probably like a given new restaurant. Then, write an SQL statement to implement it.

For example, if the new restaurant is a Japanese restaurant, you may search for subscribers who consistently Japanese restaurants positively. (Your strategy is expected to be different from this.)

[Note: Strategies that are harder to implement (e.g., those involving the deduction of a subscriber’s preference based on his or her similarity with other subscribers) will be awarded a higher mark. These strategies tend to require longer SQL statements and involve multiple joins. However, it does not mean that long SQL statements are always better. In fact, you will demonstrate poor SQL programming skills if you have a very long SQL statement but the same result can be achieved by a much shorter statement.]

Submit the following:

• Explain your business intelligence strategy
• Write an SQL statement to implement it