Question 1: ER Model (30 marks)
If you prefer to use something else you are more familiar with that is also ok. e.g. LucidChart, Visio, MS Paint, GIMP or even Google Drive.
Did you know you can create drawings in your Google drive?
Once you have created your diagram just insert it into your final document.
You have just been employed as a database designer in a well-established software development firm. Your first job is to design and implement a database system for a cooking website known as "Cookbook".
The key elements of the requirements for this website are detailed in the following dot points. Use this information to understand their requirements and draw an ER Diagram.
• Cookbook has a comprehensive listing of recipes on their website. Each recipe has its own unique identifier. Also recipes have a name to describe what is being created and a method which can be stored as a very long paragraph.
• Each recipe will require some ingredients in varying amounts. Each ingredient is given a unique name to identify it in addition to a description.
• A cuisine is defined as a unique style that which a recipe can be classed as having. E.g. Spaghetti Bolognese has a cuisine style of "Pasta". Each recipe can only be associated with at most, one cuisine. Each cuisine is recorded with style as its unique identifier and a country of origin.
• Each recipe is composed by at least one, but possibly several authors. Each author also needs to be recorded in the database. An author can be identified by their full name and further attributes include gender and date of birth.
• Anyone who registers an account on Cookbook is permitted to rate the recipes using the ratings system. They may also wish to leave a comment about the recipe. Each user can be identified by a user ID and further attributes include an email address and name.
According to the given description, construct an Entity Relationship (ER) diagram for the database, and make assumptions where necessary. You must represent entities, relationships and their attributes, and all applicable constraints in your ER diagram. Explain any concepts in the description that cannot be expressed in the ER diagram.
Question 2: The Relational Model (25 marks)
Convert the following ER diagram into a relational database schema.
For each relation in your relational database schema, you should:
• Underline a primary key for each relation - each relation MUST have a primary key
• Denote any foreign keys with asterisks(*) in your relations
Make sure you create a relation for each entity and relationship you see.
Question 3: Short answer (10 marks)
What is a deletion anomaly and how can we avoid encountering one in our database design? Please use an example to explain.
Responses should be limited to no more than 2 or 3 sentences but this is not a strict requirement.