Database design and implementation - Pepetti/PWP GitHub Wiki

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
  • A simple testing showing that your ORM works as expected
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
  5. Write tests
✔️     Chapter evaluation (max 10 points) You can get a maximum of 10 points after completing this section. More detailed evaluation is provided after each heading.

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/

✔️     Evaluation criteria(max 3 points) You can get a maximum of 3 points after completing this section.
  • Design of database is coherent: 1.0
  • Each model and its attributes are named: 0.5
  • Details for attributes are provided (datatype, default value, characteristics etc.): 0.5
  • Foreign keys follow the relationship diagram from deadline 1: 1.0
      • if necessary, you can update your diagram

    ✏️

    alt-text

    ✏️
    The database will be implemented with MongoDB. So the database will be noSQL, document driven database. The basic model as a diagram is below:

    alt-text

    Here is the diagram opened up as JSON format in javascript file, containing example data:

    const user = {
    id: 1, //typeof number
    firstName: "Oskari", //typeof string
    lastName: "Kotajärvi", //typeof string
    email: "[email protected]", //typeof string
    days: [] //Contains 1 or more activities. Can be null if user hasn't done any activities yet
}

const day = {
    id: 20190629, //typeof number
    activities: [] //Contains 1 or more activities
}

const activity = {
    id: 1, //typeof number
    aerobic: false, //typeof boolean
    dayid: day.id, //typeof number
    routines: [] //Contains 1 or more routines
}

const routine = {
    id: 1, //typeof number
    sets: [{weight: 20}, {weight: 30}, {weight: 40}], //Array holding weight information of sets
    reps: 10 //typeof number
}

/**
 * User can have 0 to many days.
 * Day can have 1 to many activites.
 * Activity can have 1 to many routines
 * --> User has Days, Day has Activities, Activities have Routines
 */

    Database implementation

    💻     TODO: SOFTWARE TO DELIVER IN THIS SECTION The code repository must contain:
    1. The ORM models and functions
    2. A .sql dump of a database or the .db file (if you are using SQlite). You must provide a populated database in order to test your models.
    3. The scripts used to generate your database (if any)
    4. If you are using python, the requirements.txt file.
    5. A README.md file containing:
      • All dependencies (external libraries) and how to install them
      • Define database 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.
      • Instruction on how to run the tests of your database.
    6. If you are using python a `requirements.txt` with the dependencies

    NOTE: Your code MUST be clearly documented. Check Exercise 1 for examples on how to document the code.

    In addition, it should be clear which is the code you have implemented yourself and which is the code that you have borrowed from other sources.

    ✔️     Evaluation criteria(max 3.5 points)
    • Instructions to set up the database and run the tests are provided in the README.md file: 0.5
      • this means there should be no undocumented extra steps in running the code/tests!
    • Table in the previous section covers all implemented models: 0.5
    • All properties of the table in the previous section are implemented correctly in the model (using correct types...): 1.0
    • The code has clear structure and naming for variables and methods: 1.0
    • On delete behavior for foreign keys and relationship attributes have been explicitly set 1.0

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

    Database testing

    💻  Software that must be included in the section

    In this course, showing that your code works is primarily your responsibility. Therefore we expect test cases that show that all of your methods work not just with correct parameters, but that they also handle error situations correctly. Tests should always cover the most common error scenarios that are easy to predict (e.g. trying to edit something that doesn't exist, trying to create duplicate primary keys etc.) Each test case has to clearly show what it tests, what test parameters it uses and finally to show that result was as expected.

    You should follow the testing methodologies shown in Exercise 1.

    Some guidelines for the testing:

    • The code of the test MUST be commented indicating what are you testing in each case.
    • For each model the test script should, at least:
      • Create a new instance of the model
      • Retrieve an existing instance of the model (recommended trying with different filter options)
      • Update an existing model instance (if update operation is supported by this model)
      • Remove an existing model instance from the database
      • Test possible errors conditions (e.g. breaking foreign keys relations)
  • We recommend to include a set of scripts to setup your database and run your test.
    • ✔️     Evaluation criteria(max 2.5 points) In this section you can get a maximum of 3.5 points.
    • The test case cover all models in the database: 1.0 (0.5 if not all models covered / 0 if just a one model covered)
      • For each model the script should, at least:
        • Create a new instance of the model
        • Retrieve an existing instance of the model (recommended trying with different filter options)
        • Update an existing model instance (if update operation is supported by this model)
        • Remove an existing model instance from the database
      • You should try to force errors (for instance, try to break foreign keys relations)
    • Test cases cover also errors scenarios: 1.5
    • The model implementation do not have errors : 1.0

    ✏️ You do not need to write anything here. Just complete the implementation

    Resources allocation

    Task Student Estimated time
    ⚠️ **GitHub.com Fallback** ⚠️