Database design and implementation

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

Name	Type	Restrictions	Description	Characteristics	Links
Recipeingredient Table					relationship to tables: Recipe, Ingredient, Unit
id	Integer	primary_key	Id of the recipe	Proxies certain recipe with ingredient information	ForeignKey recipe.id
ingredient_id	Integer	primary_key	Id of the recipes ingredient	Proxies certain ingredient with this table	ForeignKey ingredient.id
amount	Integer		Amount of the ingredient within certain recipe
unit_id	Integer	primary_key	Specifies the unit, E.G "Cups"		ForeignKey unit.id
Recipe table					relationship to User table
id	Integer	primary_key	Id of the recipe
user_id	Integer		Links user with certain recipe		ForeignKey user.id
name	String	64 characters, not nullable, unique	Name of the recipe
difficulty	String	20 characters	Tells how difficult the recipe is to follow	is nullable
description	String	2000 characters, not nullable	The recipe text
User table
id	Integer	primary_key	id of the user
name	String	100 characters, not nullable	Name of the user
address	String	100 characters	Address of the user	is nullable
email	String	100 characters, not nullable, unique	Email of the users
password	String	100 characters, not nullable	Password of the user
Ingredient table
id	Integer	primary_key	Id of the ingredient
name	String	100 characters	unique, not nullable
Unit table
id	Integer	primary_key	Id of the unit
unit	String	not nullable	The used unit of the ingredient, E.G "cups"

✏️ 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