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:
A visitor to the site, “Alice,” should be able search for restaurants that matching some combination of:
From the list of restaurants returned, Alice should be able to click on a restaurant and see the following information about it:
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.
The database does not need to support any features beyond those outlined above.
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.
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.
Save the above queries in MS Access.
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: