Database - jpehkone21/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
- 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/
We have four models, these are creatures, humans, animals and quotes. Below, each model and their attributes has been specified.
First model we have is Creatures-model and it has six attributes and one relationship. This model includes the list of creatures that gives quotes.
| 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 | integer | primary key | identifier for each creature | - | primary key |
| name | string | max length 32, nullable, unique | unique name for each creature | unique | - |
| age | integer | nullable | age for each character | - | - |
| picture | string | max length 256, nullable | ASCII image of the creature | - | - |
| type | string | max length 128, nullable | type pf the character, e.g. alien | - | - |
| special force | string | max length 128, nullable | characters special force | - | - |
| quotes | relationship/string | foreign key | quotes of the character | one-to-many relationship | foreign key |
Second model is Humans. Overall it is the same as the first one, the type of characters it holds is different, because it is for our human characters. Humans-model has six attributes and one relationship.
| 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 | integer | primary key | identifier for each human | - | primary key |
| name | string | max length 32, nullable, unique | unique name for each human | unique | - |
| age | integer | nullable | age for each human | - | - |
| picture | string | max length 256, nullable | ASCII image of the human | - | - |
| relation | string | max length 128, nullable | relation of a human, e.g. aunt | - | - |
| hobby | string | max length 128, nullable | hobby of the human | - | - |
| quotes | relationship/string | foreign key | quotes of the human | one-to-many relationship | foreign key |
Third model is Animals. This model holds our animal characters, and it has six attributes and one relationship.
| 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 | integer | primary key | identifier for each animal | - | primary key |
| name | string | max length 32, nullable, unique | unique name for each animal | unique | - |
| age | integer | nullable | age for each animal | - | - |
| picture | string | max length 256, nullable | ASCII image of the animal | - | - |
| species | string | max length 128, nullable | specified species of the animal, e.g. moose | - | - |
| environment | string | max length 128, nullable | environment where the animal lives | - | - |
| quotes | relationship/string | foreign key | quotes of the animal | one-to-many relationship | foreign key |
The fourth and last model is Quotes. This model holds all of the quotes we have, and are speicied to the characters. It has six attributes and three relationships. Quote can only have one of creature_name, human_name or animal_name at a time.
| 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 | integer | primary key | identifier for each quote | - | primary key |
| quote | string | max length 256, nullable, unique | unique quote | unique | - |
| mood | float | not nullable | mood of the specific quote | optional value | - |
| creature name | string | foreign key to creatures.name, nullable, ondelete=set_null | name of the creature | - | - |
| human name | string | foreign key to humans.name, nullable, ondelete=set_null | name of the human | - | - |
| animal name | string | foreign key to animals.name, nullable, ondelete=set_null | name of the animal | - | - |
| creatures | relationship/string | foreign key | relationship to creatures model | one-to-many relationship | foreign key |
| humans | relationship/string | foreign key | relationship to humans model | one-to-many relationship | foreign key |
| animals | relationship/string | foreign key | relationship to animals model | one-to-many relationship | foreign key |
Image of the relations:
๐ป 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.
๐ If you have use AI during this deliverable, explain what tool/s you have used and how. The constraint that checks that only one field out of creature_name, human_name or animal_name is given to a quote, is generated with chatGPT. All the other code is self written.
| Task | Student | Estimated time |
|---|---|---|
| database code | Johanna Pehkonen | 2h |
| design&implementation writing | Iina Nikkarikoski | 1,5h |
| - | Iiris Kivelรค | 0h |