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 5 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/

---

palikka_user

Name	Type	Restrictions	Description	Characteristics	Links
Name of the attribute	Attribute type	Values that the type can take	Description of the attribute	Uniquenes, default...	keys and foreign keys
id	integer			auto incremented, unique, not null	primary key
username	varchar			unique, not null
password	varchar		Hashed value of the original password	not null	unique key
salt	varchar		Salt used in hashing the password	not null
created_at	timestamp			not null, default current_timestamp
last_updated_at	timestamp			nullable, default null
root	boolean	true/false	Whether this user is root, i.e. unmodifiable, has access to everything	not null, default false
active	boolean	true/false	Whether this user is active, i.e. can login	not null, default true

palikka_role

Name	Type	Restrictions	Description	Characteristics	Links
Name of the attribute	Attribute type	Values that the type can take	Description of the attribute	Uniquenes, default...	keys and foreign keys
id	integer			auto incremented, unique, not null	primary key
name	varchar			unique, not null	unique key
description	varchar

palikka_privilege

Name	Type	Restrictions	Description	Characteristics	Links
Name of the attribute	Attribute type	Values that the type can take	Description of the attribute	Uniquenes, default...	keys and foreign keys
id	integer			auto incremented, unique, not null	primary key
domain	varchar			not null	composite unique key with "name"
name	varchar			not null	composite unique key with "domain"
domain_description	varchar		Description of the privilege in its domain

---

Database implementation

💻     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

---

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

---

Resources allocation

Task	Student	Estimated time
Everything	Janne Tahkola	~4 hours