Skip to content

Database design and implementation

Jump to bottom
Chubo Zeko edited this page May 6, 2022 · 5 revisions

Important information for Deadline 2

‼️  This chapter should be completed by Deadline 2 (see course information at Lovelace)

📑  Chapter summary In this section students must design and implement the database structure (mainly the data model).

In this section you must implement:

  • The database table structure.
  • The data models (ORM)
  • Data models access methods (if needed)
  • Populating the database using the models you have created
In this section you should aim for a high quality small implementation instead of implementing a lot of features containing bugs and lack of proper documentation.

SECTION GOALS:

  1. Understand database basics
  2. Understand how to use ORM to create database schema and populate a database
  3. Setup and configure database
  4. Implement database backend
✔️     Chapter evaluation (max 5 points) You can get a maximum of 9 points after completing this section. More detailed evaluation is provided in the evaluation sheet in Lovelace.

Database design and implementation

Database design

📑  Content that must be included in the section Describe your database. The documentation must include:
  • A name and a short description of each database model. Describe in one or two sentences what the model represents.
  • An enumeration of the attributes (columns) of each model. Each attribute must include:
    • Its type and restrictions (values that can take)
    • A short description of the attribute whenever the name is not explicit enough. E.g. If you are describing the users of a "forum", it is not necessary to explain the attributes "name", "surname" or "address"
      • because their meanings are obvious.
    • Characteristics of this attribute (e.g. if it is unique, if it contains default values)
  • Connection with other models (primary keys and foreign keys)
  • Other keys
You can use the table skeleton provided below

For this section you can use a visual tool to generate a diagram. Be sure that the digram contains all the information provided in the models. Some tools you can use include: https://dbdesigner.net/, https://www.lucidchart.com/pages/tour/ER_diagram_tool, https://dbdiffo.com/

✏️ The table can have the following structure

Recipe Table

A recipe contains the meal information such as recipe name, preparation time, cooking time, meal type, servings and number of calories, as well as the instructions/steps of how to prepare and cook the meal. It is connected to various recipe ingredients that are described in the cooking steps.

Name Type Restrictions Description Characteristics Links
recipe_id INT NOT NULL unique id to each recipe created UNIQUE primary
recipe_name STRING NOT NULL a string represents the name of the recipe DEFAULT none
prep_time INT NOT NULL an integer represents the preperation time of the recipe DEFAULT none
cooking_time INT NOT NULL an integer represents the cooking time of the recipe DEFAULT none
meal_type STRING NULL a string specifies the type of the meal DEFAULT none
calories INT NOT NULL an integer represents the amount of calories that each recipe contains DEFAULT none
servings INT NOT NULL an integer represents the number of servings for each meal DEFAULT none
instructions STRING NOT NULL a string represents the instructions that should be followed to make the recipe DEFAULT none
creator_id INT NOT NULL an integer links to the creator of this recipe UNIQUE foreign links to the User table

Recipe Ingredient Table

A recipe ingredient is an ingredient that is used in a specific recipe. It defines how much of the ingredient is needed in the recipe with the measuring unit and quantity (amount).

Name Type Restrictions Description Characteristics Links
rec_ing_id INT NOT NULL an ID for each ingredient used in to prepare the recipe UNIQUE primary
recipe_id INT NOT NULL an ID for each recipe UNIQUE foreign links to the Recipe table
ingredient_id INT NOT NULL an ID for each ingredient to be used in any recipe UNIQUE foreign links to the Ingredient table
amount DOUBLE NOT NULL the amount of each ingredient DEFAULT none
unit NVARCHAR NOT NULL the unit used for measure the amount of each ingredient DEFAULT none

Ingredient Table

An ingredient is an edible item with a name, and can be used in a recipe. Once an ingredient is used in a recipe, it is used by the recipe ingredient entity to assign the amount and unit of the specific ingredient.

Name Type Restrictions Description Characteristics Links
ingredient_id INT NOT NULL an ID for each ingredient to be used in any recipe UNIQUE primary
name STRING NOT NULL name of the ingredient DEFAULT none

User Table

A user is an entity that can create, edit and remove recipes and cookbooks, and assign recipes to their personal cookbook collections. The user model contains basic details on the user, which can be updated to contain more data required for client applications.

Name Type Restrictions Description Characteristics Links
user_id INT NOT NULL an integer id that reference the creator of this recipe UNIQUE primary
username STRING NOT NULL a string represnts the name of the user DEFAULT none

Cookbook Table

A cookbook can be seen as a custom collection of recipes assigned by a user. It contains a short description that can give further details on the recipes it contains, and it is managed by the user.

Name Type Restrictions Description Characteristics Links
cookbook_id INT NOT NULL an id that represents the user cookbook UNIQUE primary
name STRING NOT NULL a string represents thar name of the user cookbook DEFAULT none
user_id INT NOT NULL an id that maps the cookbook id to the cookbook to its user id UNIQUE foreign links to the user table

Collections Table

This table links the cookbook entities to their recipe entities, where a cookbook can have many recipes, and a recipe can be assigned to multiple cookbooks.

Name Type Restrictions Description Characteristics Links
cookbook_id INT NOT NULL an id of the user's cookbook UNIQUE foreign links to the cookbook table
recipe_id INT NOT NULL an ID for each recipe UNIQUE foriegn links to recipe table

✏️ Do not forget to include a diagram presenting the relations

Database implementation

💻     TODO: SOFTWARE TO DELIVER IN THIS SECTION The code repository must contain:
  1. The ORM models and functions
  2. A .sql dump of a database or the .db file (if you are using SQlite). You must provide a populated database in order to test your models.
  3. The scripts used to generate your database (if any)
  4. If you are using python, the requirements.txt file.
  5. A README.md file containing:
    • All dependencies (external libraries) and how to install them
    • Define database (MySQL, SQLite, MariaDB, MongoDB...) and version utilized
    • Instructions how to setup the database framework and external libraries you might have used, or a link where it is clearly explained.
    • Instructions on how to setup and populate the database.
  6. If you are using python a `requirements.txt` with the dependencies

✏️ You do not need to write anything in this section, just complete the implementation.

Resources allocation

Task Student Estimated time
Database Design Hamza Abdalla 3 hrs
Database Design Sehrish Khan 2 hrs
Database Implementation (MySQL); diagrams Chubo Zeko 8 hrs
Database Implementation (MySQL) David Ochia 2 hrs

Report index

Clone this wiki locally