Database Design and Implementation

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/

---

Our database has totally 4 entities:

1. Entity users

• Description: Represents a user in the system.
• Attributes:

  Name	Type	Restrictions	Description	Characteristics	Links
  _id	ObjectId		MongoDB-generated unique identifier for the user	Unique	Primary key
  username	string	unique, required	user's username	Unique
  password	string	required	user's password
  email	string	unique, required	user's email	Unique
  firstName	string		user's first name
  lastName	string		user's last name
  avatar	string		URL or path to user's avatar
  status	string	online, offline, typing (enum)	user's current status	Required, default: 'offline'
  friends	array of ObjectId (ref: 'user')		Array of user objects representing the user's friends		Foreign key referencing 'user' collection

2. Entity chats

• Description: Represents a chat in the system, which can be either direct or group.
• Attributes:

  Name	Type	Restrictions	Description	Characteristics	Links
  _id	ObjectId		MongoDB-generated unique identifier for the chat	Unique	Primary key
  type	string	direct, group (enum)	Type of chat (direct or group)	Required
  members	array of ObjectId (ref: 'user')	required if type is direct	Array of user objects representing chat members	Required if type is direct	Foreign key referencing 'user' collection
  messages	array of ObjectId (ref: 'message')	default: []	Array of message objects in the chat		Foreign key referencing 'message' collection
  group	ObjectId	required if type is group	Reference to the associated group	Required if type is group	Foreign key referencing 'group' collection

3. Entity groups

• Description: Represents a group in the system.
• Attributes:

  Name	Type	Restrictions	Description	Characteristics	Links
  _id	ObjectId		MongoDB-generated unique identifier for the group	Unique	Primary key
  groupName	string	required	Name of the group	Required
  members	array of ObjectId (ref: 'user')		Array of user objects representing group members		Foreign key referencing 'user' collection
  createdBy	ObjectId		Reference to the user who created the group		Foreign key referencing 'user' collection
  createdAt	Date	default: Date.now	Date and time when the group was created	Default value: current date and time
  groupDescription	string		Description or additional information about the group

4. Entity message

• Description: Represents a message in the system.
• Attributes:

  Name	Type	Restrictions	Description	Characteristics	Links
  _id	ObjectId		MongoDB-generated unique identifier for the message	Unique	Primary key
  sender	ObjectId	required	Reference to the user who sent the message	Required	Foreign key referencing 'user' collection
  content	string	required	Text content of the message	Required
  sentAt	Date	default: Date.now	Date and time when the message was sent	Default value: current date and time
  type	string	read, unread (enum)	Type of message (read or unread)	Required, default: 'unread'
  seenBy	array of ObjectId (ref: 'user')	required	Array of user objects representing users who have seen the message	Required	Foreign key referencing 'user' collection

✏️ Do not forget to include a diagram presenting the relations

Here is the figure illustating Entity Relationship Diagram (ERD), which is generated using Moon Modeler - a database design tool for noSQL databases like MongoDB that helps draw ERD and generate scripts for the model:

---

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
- Task 1: Database Design and Main Entities	Hung Trinh & Mazen Hassaan	5 hours
- Task 2: Database Implementation	Saim Akhtar & Toseef Ahmed	5 hours
- Task 3: Review and Coordination	All Students	3 hours