2023年11月25日

数据库代写 | Autumn 2020Informatics Databases Assignment 1

本次数据库代写是设计一个食品相关的数据模型及系统

Autumn 2020Informatics
Databases Assignment 1

1. You have a large number of recipes you use and would like to keep
track these, of the ingredients stored in your kitchen, and of the history
of meals you prepare. You’d like to setup a database for this.
(a) Develop a data model (expressed in Chen notation) according to
the informal specification shown in Figure 1.
Make sure you identify keys for all entity types. Also consider
which attributes are composite, multi-valued, or derived attributes.
If you discover an issue that is not specified or underspecified,
make an educated guess and discuss (document your decision).
By their nature, informalspecifications are practically never 100%
complete. For the sake of the exercise, and to keep the model as
small as possible, do not invent any features that are not mentioned in the specification.
[28 marks]
(b) Add cardinality constraints to your model. Include some documentation to explain the cardinality constraints in the style of
Slide 18, Topic 1.2. Include references to the specification to
corroborate your decisions.
[8 marks]
(c) Add participation constraints to your model. Add some documentation to explain the participation constraints in the style of
Slide 18, Topic 1.2. Include references to the specification to
corroborate your decisions.
[8 marks]
(d) Instead of cardinality and participation constraints give multiplicity constraints for the four relationships in the above EntityRelationship model of the cooking database. Assume that recipes
can never have more than 64 steps.
[8 marks]
Marking Criteria: The following criteria will be used to mark your
model
• extent to which your model is correct, minimal (no feature modelled twice), complete and expressive
• correct use of Chen notation (including special attributes)
2
• correct cardinality and participation constraints (including notation)
• readability and presentation of your answer. You can design the
diagram in any software system you like, as long as you stick to
the notation used in the lectures and embed it into your submission. For scanned hand-written diagrams at least 10% points will
be deducted depending on the appearance.
A recipe should have a unique name, duration, number of (people)
servings, which course(s) it is for, which ethnicity(ies) it originates from,
a rating, a complexity indicator, and flags indicating whether it is gluten
free, vegan or vegetarian. Each recipe uses a certain number of ingredients and has a sequence of steps.
Each step has a duration and a description of the procedure to take.
Each ingredient has a unique name, has flags indicating wether it’s
vegetarian, vegan or gluten-free, has a nutritional value (composed of
carbohydrates, protein and fat quantities), and a unit by which the ingredient is measured. Certain quantities of ingredients are used by
each recipe.
Ingredients are stored in a store which has a unique identifier, and each
ingredient has a flag indicating if it has low stock.
You cook all your meals according to some recipe and you’d like to
have a record of when each was cooked and how many people ate.
Figure 1: Specification of the Food Database
2. Consider the Enhanced Entity-Relationship Diagram in Figure 2 on
the next page and answer the following questions by ONE sentence
each. Only the first sentence will be marked. Note that attribute total
refers to the total billing amount of an order.
(a) Explain whether there can be an order that is neither a phone
order nor a table order. [4 marks]
(b) Which attribute, or set of attributes, uniquely identifies a table?
[4 marks]
(c) Explain whether it is possible to compute the total billing amount
3
of all (phone and table) orders placed in one specific restaurant.
[4 marks]
3. For each of the following enterprise constraints, decide whether
• it is already expressed in the Entity-Relationship model (ER) of
Figure 2 on the next page
• it is not already expressed in the ER of Figure 2 on the next page,
but could be expressed using only features of an ER model.
• cannot be expressed in the ER of Figure 2 on the next page using
only features of an ER model.
In each case explain your answer briefly but clearly.
(a) A table order originates from exactly one table of one restaurant.
[4 marks]
(b) Two table orders dispatched at (roughly) the same time must
originate from different tables. [4 marks]
4. Translate the Enhanced Entity-Relationship Diagram in Figure 2 above
into a Relational Model.
Present the final model (no need for explanations or intermediate steps)
as a Relational Database Schema in textual form and not as a diagram.
Primary and foreign keys must be explicitly declared.
[28 marks]