Skip to content

Database design and implementation

Jump to bottom
Iván Sánchez edited this page Jan 20, 2020 · 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
  • 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 9 points) You can get a maximum of 9 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 2.75 points) You can get a maximum of 2.75 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: 0.75
      • if necessary, you can update your diagram

    ✏️ The table can have the following structure

    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

    ✏️ 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 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 4.25 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...): 0.75
    • All Relations are correctly implemented (0.25 if 1 or 2 minor errors, 0 otherwise): 0.5
    • The code has clear structure and naming for variables and methods: 1.0
    • Methods inside models (if any) are correctly documented in the code (functionality, input, output): 0.5
    • Code documentation uses a consistent and clear format: 0.5
      • you can use an existing documenting format (e.g. Sphinx for Python) or simply come up with your own - as long as it's consistent

    ✏️ 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 from the database
      • Test possible errors conditions (e.g. Integrity Errors)
  • We recommend to include a set of scripts to setup your database and run your test.
    • ✔️     Evaluation criteria(max 3.0 points) In this section you can get a maximum of 3.0 points.
    • Methods in the test are correctly documented (functionality of each method): 0.5(0.25 if not all covered / 0 if just a few are covered)
    • The test case cover all models in the database: 1.0 (0.5 if not all methods covered (~90% coverage) / 0 if just a few methods covered).
      • In order to get full points, all CRUD methods should be tested for each model. 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

    Report index

    Clone this wiki locally