Question 6 (8 marks)
The following ER diagram describes an online recipe database scenario.
Convert the ER diagram into a relational schema expressed as a collection of PostgreSQL CREATE TABLE statements. Your schema must show all primary key constraints and foreign key constraints. You may add any new attributes that you need to represent all aspects of the ER diagram; assign them types and constraints as appropriate. The precise name of a new attribute is not important, as long as it is different from existing attributes. If there are any semantic aspects suggested by the diagram and comments below which cannot be represented in the relational schema using standard SQL constraints, document these.
The following gives more details on the precise semantics of the scenario:
- every recipe is owned by a user; every recipe has at least one ingredient
- any attribute called id is a serial attribute
- attributes name, email, title, are unit are text strings, and must have a value
- the amount attribute is a positive integer quantity
- a recipe can be associated with zero or more tags, which give additional features of the recipe
- each tag is a text string such as 'vegetarian', 'spicy', 'korean', 'easy-to-make', etc.
Exercise: Write an SQL schema that accurately represents the above ER design.
Instructions:
- Type your answer to this question into a file called q6.txt
- Submit via: give cs3311 exam_q6 q6.txt or Webcms3