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/

---

✏️ The table can have the following structure b

Student table

Name	Type	Restrictions	Description	Characteristics	Links
student_id	integer			not null, autogenerated	primary key
first_name	varchar(64)			not null
last_name	varchar(64)			not null
date_of_birth	date			not null
ssn	varchar(11)		social security number	not null

Assesment table

Name	Type	Restrictions	Description	Characteristics	Links
course_id	integer			not null, autogenerate	primary key, Foreing key(Course)
student_id	integer			not null	primary key, foreing key(student)
grade	integer	0<=grade<=5		not null
date	date		date in which the exam was taken	not null

Course table

Name	Type	Restrictions	Description	Characteristics	Links
course_id	integer			not null	primary key
title	varchar(255)			not null
teacher	varchar(255)			not null
date	date		date in which the exam was taken	not null
ects	integer	ects>0		not null

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