Skip to content

Database design and implementation

Jump to bottom
pranavb1211 edited this page Feb 10, 2023 · 45 revisions

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 The database consists of 3 tables :

Student : It has the columns such as student_id,first_name,last_name,date_of_birth and SSN. student_id would be the primary key in the context of this table.Since it is a primary key it would by default not store any null or duplicate values.The student_id would also be a auto incremented field in the database. The student_id would also act as a foreign key in the Assessment table(to be explained below).

Assessment: The assessment would have columns such as course_id which would ,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 crtiteria,they would be graded 0. The grade 0 would signify that the student has failed the course.

Course: The course table consits of the course_id coulmn acting as the primary key which would be referenced in the Assesment table. It contains the information regarding the title, teacher who would take the course. The code of the course is expected to be an alpha numeric value where as the ECTS would accept .

The overview of database can be summarized as a single student who can have multiple assessments for multiple courses.

✏️ 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 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 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 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 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
code varchar(12) Code that contains information about the course not null, unique

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

Report index

Clone this wiki locally