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

✏️ 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
VoteType int 1 to 5 1 being less liked and 5 being more liked video vote 0 NA
AvailableResolutions string 240,360,480 etc Available resolution of HLS videos null NA
IsApproved boolean true or false If video is approved by admin, it is marked as approved null NA
IsSellingVideo boolean true or false If video is for sale or not false NA

✏️ This database model represents a table of users and videos details uploaded by users will be stored in the videos table represented in the schema below. Each user can vote or bookmark one or more videos.

OpenAI Logo

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

✏️

  1. We have used code first approach using Entity Framework 6, C# for the generation of ORM Models. Code is pushed in the following repository. Details to replicate the process in the Readme file
  2. SQL populated Database File is generated by C# code first approach and details are in the Readmefile
  3. Scripts to populate the database and script to test delete cascade
  4. Readme
  5. Important steps to replicate Code first approach can be found in the readme file

Resources allocation

Task Student Estimated time
ER Diagram Muhammad Talha Arshad 11 hours
Documentation Muhammad Ahmed 10 hours
ERD refinment Muhammad Ahmed 1 hour
⚠️ **GitHub.com Fallback** ⚠️