Database design and implementation - shanz007/IceHockeyTrackerAPI 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
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 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/

Model 1: Role model represents roles for the users: player, coach, manager, and Administrator

Name Type Restrictions Description Characteristics Links
role_id int positive Role Id not null & auto generated primary key
role_name String(64) Alphanumeric Role Name not null, unique
role_code String(64) Alphanumeric Role Code not null, unique
role_description String(256) Alphanumeric Role Description not null

Model 2: RankBase model represents rank score for the users: player, coach, manager

Name Type Restrictions Description Characteristics Links
rank_base_id int positive Rank Base Id not null & auto generated primary key
rank_score int positive Rank Score not null
role_id int positive Role Id not null foreign key(role)
rank_base_description String(256) Alphanumeric Rank Base Description not null,unique

Model 3: User model represents all the users: players, coaches, and manager

Name Type Restrictions Description Characteristics Links
user_id int positive User Id not null & auto generated primary key
full_name String(256) Alphanumeric Full Name not null
nick_name String(64) Alphanumeric Nick Name not null, unique
password String(256) Alphanumeric Password in hashed format not null
date_of_birth date type non-past from current date Date of birth not null
email String(64) Alphanumeric Email not null, unique
role_id int positive value either one of (player, coach, manager) foreign key(role)
ssn String(64) Alphanumeric SSN in finnish-format not null, unique
rank int positive Rank has to be in 0 <= rank <= 10 not null & default=0

Model 4: UserRank model represents a list of ranking details for the user.

Name Type Restrictions Description Characteristics Links
id int positive Team Id not null & auto generated primary key
user_id int positive User Id not null, unique primary key,foreign Key(user)
rank_base_id int positive User Id not null, unique primary key,foreign Key(user)
user_comment String(512) Alphanumeric Ranker COmment not null
ranker_id int positive User id of the Ranker not null primary key, foreign key(User)
added_date date date type Ranked date >= current date not null

Model 5: Team model represents a list of teams details.

Name Type Restrictions Description Characteristics Links
team_id int positive Team Id not null & auto generated primary key
team_name String(128) Alphanumeric Team Name not null,unique
team_description String(256) Alphanumeric Team Description not null
team_coach_user_id int positive User Id of the coach not null,unique foreign key(user)

Model 6: TeamUser model represents a list of teams with its player Information.

Name Type Restrictions Description Characteristics Links
team_id int positive Team Id not null primary key,foreign Key(team)
user_id int positive User Id not null, unique primary key,foreign Key(user)
contract_start_date date date type Contract Start Date not null
contract_end_date date date type contract_end_date > contract_start_date not null

Model 7: Match model represents a list of matches with Information.

Name Type Restrictions Description Characteristics Links
match_id int positive Match Id not null,auto generated
host_team_id int positive Host Team Id not null primary key,foreign key(Team)
rival_team_id int positive Rival Team Id not null primary key,foreign key(Team)
winner_team_id int positive Winner Team Id not null primary key,foreign key(Team)
match_date date Non future Date not null
match_level String(256) Current match level should be either ( entry, quarterfinal , semifinal, final) not null

Model 8: MatchFeedback model represents a list of Feedback details for matches.

Name Type Restrictions Description Characteristics Links
id int positive Team Id not null & auto generated primary key
match_id int positive Match Id not null, unique foreign Key(user)
user_comment String(512) Alphanumeric Ranker COmment not null
ranker_id int positive User id of the Ranker not null foreign key(User)
added_date date date type Ranked date >= current date not null

Representation of the relationships between above models:

User:

  • One-to-One with Team: One user can be associated with only one team.
  • One-to-Many with TeamUser: One user can only be part of a team, and one team can have multiple users
  • One-to-One with Role: One user can be associated with only one role.
  • One-to-Many with Match: One user can be associated with multiple matches.
  • One-to-Many with UserRank: One user can be associated with many ranking scores.

Team:

  • One-to-Many with TeamUser: One team can have multiple team users.
  • One-to-Many with Match: One team can participate in multiple matches.

TeamUser:

  • Many-to-One with both User and Team: Many users can belong to many teams, and many teams can have many users.

Match:

  • Many-to-One with both User and Team: Many matches can involve many users and many teams.
  • Many-to-Many with MatchFeedback and Match: Many matches can involve many feedbacks.

Database Diagram

✏️ 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
Meetings All 2h
Database design,implementation,Documentation Shanaka Badde Liyanage Don 15h
Database design,implementation,Documentation Emmanuel Ikwunna 8h
Database design,implementation,Documentation Md Mobusshar Islam 10h
⚠️ **GitHub.com Fallback** ⚠️