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

UserProfile Model

  • Name: UserProfile
  • Description: Represents a user's profile in the system, including their personal details and facial recognition data.
Name Type Restrictions Description Characteristics Links
id Integer None The primary key for the user profile Unique, Autoincrement Primary Key
name String(50) Maximum length of 50 The name of the user None None
facial_data LargeBinary None Binary data for the user's facial recognition None None

AccessPermission Model

  • Name: AccessPermission
  • Description: Stores access permission levels assigned to user profiles.
Name Type Restrictions Description Characteristics Links
id Integer None The primary key for the access permission Unique, Autoincrement Primary Key
user_profile_id Integer None Foreign key to the UserProfile model None Foreign Key to UserProfile.id
permission_level String(50) Maximum length of 50 The level of permission granted None None

AccessRequest Model

  • Name: AccessRequest
  • Description: Records each access request made by a user, including the outcome, timestamp, and associated permissions.
Name Type Restrictions Description Characteristics Links
id Integer None The primary key for the access request Unique, Autoincrement Primary Key
user_profile_id Integer None Foreign key to the UserProfile model None Foreign Key to UserProfile.id
timestamp DateTime None The date and time when the access request was made Default (current timestamp) None
outcome String(50) Maximum length of 50 The outcome of the access request None None
associated_permission String(50) Maximum length of 50 Minimum access level or access groups required None None

AccessLog Model

  • Name: AccessLog
  • Description: Keeps a log of details associated with access requests.
Name Type Restrictions Description Characteristics Links
id Integer None The primary key for the access log Unique, Autoincrement Primary Key
access_request_id Integer None Foreign key to the AccessRequest model None Foreign Key to AccessRequest.id
details String(255) Maximum length of 255 Descriptive details about the access log None None

ER Diagram

ER Diagram

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
define database models Tharindu Muthukuda Walawwe ([email protected]) 1h
implement related DB service functions for every model Tharindu Muthukuda Walawwe ([email protected]) 3h
test functions in app.py Tharindu Muthukuda Walawwe ([email protected]) 1.5h
⚠️ **GitHub.com Fallback** ⚠️