Database design and implementation - HenglinShi/PWP GitHub Wiki
📑 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
- Understand database basics
- Understand how to use ORM to create database schema and populate a database
- Setup and configure database
- Implement database backend
- 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.📑 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/
✔️ Evaluation criteria(max 3.25 points)
You can get a maximum of 3.25 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
✏️ The table can have the following structure
- Account
- Job
- Transaction
- Result
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 |
id | Int | Unique | Primary key | ||
name | String | Unqiue | |||
String | Format like: [email protected] | ||||
billing_unit | Float | Tokens for purchasing Jobs | Default=0.0 | ||
registered_time | DateTime |
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 |
id | Int | Unique | Primary key | ||
user_id | String | Unqiue | Foreign key | ||
created_time | DateTime | Time the job submitted | |||
job_type | String | ||||
status | String | ||||
result_id | String | Unqiue | Foreign key | ||
transaction_id | String | Unqiue | Foerign key | ||
job_type | String |
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 |
id | Int | Unique | Primary key | ||
start_time | DateTime | Time the job started | |||
finish_time | DateTime | Time the job finnished | |||
run_time | DateTime | Job running time by hour | |||
unit_price | Float | Time of token comsumed per hour | |||
total_price | Float | run_time * unit_price |
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 |
id | Int | Unqiue | Primary key | ||
content | String |
✏️ Do not forget to include a diagram presenting the relations
💻 TODO: SOFTWARE TO DELIVER IN THIS SECTION
The code repository must contain:- The ORM models and functions
- 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.
- The scripts used to generate your database (if any)
- If you are using python, the requirements.txt file.
- 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.
- Instruction on how to run the tests of your database.
- 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.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!
- 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 naming of the attributes and relations are self-descriptive and additional functions for the models (if any) are correctly documented (input/output): 0.75
- All models from design tables are implemented. All implemented models appear in the design tables: 0.75
✏️ You do not need to write anything in this section, just complete the implementation.
💻 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.foreign keys violations work correctly)
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 that onModify and onDelete work as expected
- Test possible errors conditions (e.g. foreign keys violation or other situation where Integrity error might be raised)
✔️ Evaluation criteria(max 2.5 points)
In this section you can get a maximum of 2.5 points.- Methods in the test are correctly documented (functionality of each method): 1(0.25 if not all covered / 0 if just a few are covered)
- Each model is tested with each one of the 4 CRUD operations (create, read, update and delete). Ondelete and onModify behaviour should also be tested: 1.0 (0.75 if few operations missing. 0.5 if one model not tested. 0 if more than one model not tested).
- 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
- Additionally onModify and onDelete should be tested
- In order to get full points, all CRUD methods should be tested for each model. For each model the script should, at least:
- Test cases includes possible error situation (e.g. foreign key violation): 0.5
- Tests run correctly: 0.5
- The model implementation do not have errors. Note that course staff might do additional tests : 0.5
✏️ You do not need to write anything here. Just complete the implementation
Task | Student | Estimated time |
---|---|---|