The coursework assigned below must be submitted as E-submission in one single pdf file on Canvas by 4 pm on Friday 6 November 2020. Note that no other formats than pdf will be accepted.
University of Sussex Autumn 2020 Informatics Databases Assignment 1 (Deadline 06.11.2020, 4pm) The coursework assigned below must be submitted as E-submission in one single pdf file on Canvas by 4 pm on Friday 6 November 2020. Note that no other formats than pdf will be accepted. You are not allowed to share or discuss solutions with other students. The university rules for collusion and plagiarism apply and any cases discov- ered will be reported and investigated. A decent presentation of your work is expected. All FOUR questions must be answered. 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, informal specifications 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 men- tioned in the specification. [28 marks] (b) Add cardinality constraints to your model. Include some doc- umentation 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 docu- mentation 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 multi- plicity constraints for the four relationships in the above Entity- Relationship 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 mod- elled twice), complete and expressive • correct use of Chen notation (including special attributes) 2 • correct cardinality and participation constraints (including nota- tion) • 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 submis- sion. 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 ingre- dients 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 in- gredient 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] 4 Figure 2: Enhanced E/R diagram for Questions 2 to 4 5