‼️  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 5 points after completing this section. More detailed evaluation is provided in the evaluation sheet in Lovelace.

---

📑  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

Attribute	Type	Description	Restrictions
id	string (max 255)	Unique identifier for the auth session.	PRIMARY KEY
user	dbref to User	User associated with the auth session.	REQUIRED
api_key	string (max 255)	API key for the auth session.	REQUIRED
createdAt	timestamp	Timestamp when the auth session was created.	REQUIRED
updatedOn	timestamp	Timestamp when the auth session was last modified.	REQUIRED

• Auth Session.user < User

Attributes:

• id: A unique identifier for the authentication session, which is a string with a maximum length of 255 characters. It is the primary key for the Auth Session table.
• user: A reference to the User associated with the authentication session.
• api_key: A string that represents the API key for the authentication session, with a maximum length of 255 characters.
• createdAt: A timestamp indicating when the authentication session was created.
• updatedOn: A timestamp indicating when the authentication session was last modified.

Attribute	Type	Description	Restrictions
id	string (max 255)	Unique identifier for the user.	PRIMARY KEY
username	string (max 255)	username of the user.	UNIQUE, REQUIRED
phoneNumber	string (max 255)	Contact number of the user, must be unique.	UNIQUE
address	string (max 255)	Address of the user.
email	string (max 255)	Email address of the user, must be unique.	UNIQUE, REQUIRED
password	string (max 255)	Encrypted password of the user. Must include on upper letter character and number	REQUIRED
reviewStat	float	Review statistics of the user.
createdProducts	array of references to product	Products posted by the user for sell.
purchasedProducts	array of references to product	Products purchased by user
favourites	array of strings (max 255)	product ids marked as favorite by the user.
createdAt	timestamp	Timestamp when the user was created.	REQUIRED
updatedOn	timestamp	Timestamp when the user was last modified.	REQUIRED

• User.createdProducts < Product.id
• User.purchasedProducts < Product.id
• User.favourites < Product.id

Note: This is saparate table Attributes:

• id: A unique identifier (ObjectId in MongoDB) for the user. It is the primary key for the User table.
• username: The Unique username of the user, which is a string with a maximum length of 255 characters.
• phoneNumber: The contact number of the user, which is a string with a maximum length of 255 characters and must be unique.
• address: The address of the user, which is a string with a maximum length of 255 characters.
• email: email address of the user, which is a string with a maximum length of 255 characters and must be unique.
• password: The encrypted password of the user, which is a string with a maximum length of 255 characters.
• reviewStat: The review statistics of the user, which is a floating-point number.
• createdProducts: An array of products that represent products posted by the user for sale. Technically when user posts a product its DbReference is added to the this array.
• purchasedProducts: An array of DbReferences to Products that user has purchased.
• favorites: An array of DbReferences to Products user has added to favourites.

Attribute	Type	Description	Restrictions
id	string (max 255)	Unique identifier for the product.	PRIMARY KEY
name	string (max 255)	Name of the product.	UNIQUE, REQUIRED
images	array of strings	Images of the product.
description	string (max 255)	Description of the product.	REQUIRED
location	location	Location of the product.	REQUIRED
price	number	Price of the product.	REQUIRED
tags	array of strings	Tags associated with the product.	REQUIRED
owner	username	username who owns the product (created).	REQUIRED
buyer	username	username who bought the product.
createdAt	timestamp	Timestamp when the product was created.	REQUIRED
updatedOn	timestamp	Timestamp when the product was last modified.	REQUIRED
sold	boolean	Boolean to hold status is the product sold	REQUIRED, DEFAULT=FALSE

• Products.owner < username (unique field from User identifies owner)
• Products.buyer < username (unique field from User that identifies buyer)

• id: A unique identifier for the product, which is a string with a maximum length of 255 characters. It is the primary key for the Product table.
• name: The name of the product, which is a string with a maximum length of 255 characters.
• images: An array of strings that represent images of the product
• description: The description of the product, which is a string with a maximum length of 255 characters.
• location: The location of the product.
• price: The price of the product, which is a number.
• tags: An array of strings that represent tags associated with the product. This is used to filter products accordingly.
• owner: A reference to the User (username) who owns the product.
• buyer: A reference to the User (username) who bought the product.
• createdAt: A timestamp indicating when the product was created.
• updatedOn: A timestamp indicating when the product was last modified.
• sold: A boolean that holds status if the product is sold or not

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

💻     TODO: SOFTWARE TO DELIVER IN THIS SECTION

The code repository must contain:

1. The ORM models and functions
2. A .sql dump (or similar data structure) of a database or the .db file (if you are using SQlite). The provided document must contain enough information to replicate your database. You must provide a populated database in order to test your models.
3. The scripts used to generate your database (if any)
4. 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.
5. If you are using python a `requirements.txt` with the dependencies

---

https://github.com/laurilako/PWP/tree/main/Database

---

Task	Student	Estimated time
Database design, implementation	Konsta Laurila	10
Writing documentation, planning, research on topic	Bishwas Wagle	0
Writing documentation, planning, research on topic	Kurosh Husseini	10
Writing documentation, planning, research on topic	Aleksi Illikainen	0