Database design and implementation
📑 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
- Understand database basics
- Understand how to use ORM to create database schema and populate a database
- Setup and configure database
- 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
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/
✏️
SQLite was chosen as the database for this project. For this reason, we'll design it with the Entity-Relationship model in mind.
The database consists of 3 tables:
Student: represents a student enrolled at the institution. It holds the columns student_id, first_name, last_name, date_of_birth and ssn. Student_id is the primary key of the table. Since it is a primary key, it will not store any null or duplicate values. Student_id would also be an auto-incremented field in the database. The student_id would also act as a foreign key in the Assessment table described below. The Social Security Number is held in Finnish format, and should be valid for the date_of_birth inserted.
Assessment: represents the grade that a student has achieved for a specific course. It holds the columns course_id, student_id, grade and date The grade of the student would be between the range 0 to 5. If the student would fail the course or be absent for the submissions to the extent of not meeting the evaluation criteria, they would be graded 0. For obvious reasons, the date has to be at most the day of the insertion. Furthermore, there can only be one pairing between a given student and a given course.
Course: represents a course offered by the institution, with all its identifying information. It holds the columns course_id, title, teacher, code and ects. Course_id is the primary key of the table, referenced in the Assessment table. The code of the course is expected to be a unique alphanumeric value, whereas the ects field would accept values greater than 0.
The database stores a collection of students, a collection of grades, and a collection of assessments linking students and courses. Each assessment represents the grade a student has achieved in the related course
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 | it has to be before today's date | not null | ||
| ssn | varchar(11) | has to be valid for the given date of birth | social security number in Finnish format | not null, unique |
Assessment table
| Name | Type | Restrictions | Description | Characteristics | Links |
|---|---|---|---|---|---|
| course_id | integer | not null | primary key, foreign key(Course) | ||
| student_id | integer | not null | primary key, foreign key(Student) | ||
| grade | integer | 0<=grade<=5 | not null | ||
| date | date | date is at most today | date in which the exam was taken | not null |
Course table
| Name | Type | Restrictions | Description | Characteristics | Links |
|---|---|---|---|---|---|
| course_id | integer | not null, autogenerated | primary key | ||
| title | varchar(255) | not null | |||
| teacher | varchar(255) | not null | |||
| code | varchar(12) | Code that contains information about the course | not null, unique | ||
| ects | integer | ects>0 | not null |
💻 TODO: SOFTWARE TO DELIVER IN THIS SECTION
The code repository must contain:- The ORM models and functions
- 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.
- The scripts used to generate your database (if any)
- 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.
- 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.
| Task | Student | Estimated time |
|---|---|---|
| Meetings and diagram | All | 1.5h |
| Models implementation and testing | Lorenzo Medici | 2h |
| Database Initialisation Script | Daniel Szabo | 2h |
| Documentation | Alessandro Nardi | 45 min |
| Documentation | Pranav Bahulekar | 45 min |