> **Database Systems Practicum**
> ***Module 1 - Relational Database Management System***
> **Case study**
> **Assistant Code: DN**
**Name**: Evandra Rasya Fadhillah
**NPM** : 2406450352
# **Case Study: Relational Database Management System**
Global Connect Tech is a smart device manufacturer that distributes millions of products worldwide. The company requires a database system to track every individual unit using a Unique Device ID (UDID). The primary challenge currently faced is the difficulty in monitoring the real-time location of each unit as it moves from assembly centers to regional warehouses and, ultimately, to retail stores. The SDLT system aims to prevent inventory loss and ensure that device activation data is accurately recorded the moment a customer first powers on the device.
## **1. Entity Analysis**
Based on the main features of the application, the required entities are:
* **Device Manufacturing and Identification**
Stores unique identity data and specifications for each device unit.
* **Manajemen Logistik dan Inventaris Global**
Stores unit movement records and distribution location data
* **Product Deliver**
Process a transactions when a unit is being delivered
* **Product Repair**
Process a Repair Status when a product is broken
## **2. Data Dictionary**
Below are the details of the attributes for each entity analyzed.
### **Feature: Device Manufacturing and Identification**
### **Table: color**
| Attribute Name | Data Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `color_id` | Int(32) | PK,Unique, Not Null | The Device Unique Color ID |
| `color_name` | varchar(128) | Unique, Not Null | The Unique Device Color Name |
| `color_description` | varchar(2048) | Not Null | Description of Device Color |
### **Table: device_model**
| Attribute Name | Data Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `model_id` | Int(32) | PK,Unique, Not Null | The Device Unique Model ID |
| `model_name` | varchar(128) | Unique, Not Null | The Unique Device Model Name |
| `model_description` | varchar(2048) | Not Null | Description of Device Model |
### **Table: device**
| Attribute Name | Data Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `device_id` | UUID | PK,Unique, Not Null | The Unique Identification of the Device ID |
| `color_id` | Int(32) | FK, Not Null | The Device Color ID |
| `device_model_id` | Varchar | FK, Not Null | The Device Model ID |
| `storage_capacity` | Integer | Not Null | The Amount of avalible storage|
| `completion_date` | DateTime | Not Null | The completion date of the device |
| `quality_control` | Boolean | Not Null | QC Status (Pass=True, Fail=False) |
### **Table: location**
| Attribute Name | Data Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `location_id` | Int(32) | PK, Not Null | The Location Unique ID |
| `location_name` | Varchar(128) | Not Null | The Location name |
| `geographic_latitude` | Float/Decimal | Not null | The geographic latitude |
| `geographic_longitude` | Float/Decimal | Not null | The geographic longiture|
| `person_in_charge` | Varchar(256) | Not null | The Name of The Person in charge |
| `location_type` | Interger(8) | Not null | Location Tyoe 1=Warehose, 2=Distributor, 3=Store |
### **Table: device_location**
| Attribute Name | Data Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `device_location_id` | Int(32) | PK, Not Null, Unique | The Location Unique ID |
| `location_id` | Int(32) | FK, Not Null, Unique | The Location Unique ID |
| `device_id` | UUID | FK,Not Null | The Unique Identification of the Device ID |
| `arrival_time` | DateTime | Not null | The Arrival time|
| `shipping_status` | Interger(8) | Not null | The shipping status 1=In-Transit 2=Stored, 3=Arrived, 4=Out-Transit |
### **Table: Aktivasi dan Layanan Purna Jual**
| Attribute Name | Data Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `buyer_id` | UUID | FK, Not Null | The Unique Identification of Buyer |
| `device_id` | UUID | FK,Unique, Not Null | The Unique Identification of the Device ID |
| `buyer_info` | Varchar | Not Null | The Buyer info |
| `retail_info` | Varchar | Not null | The Retail info|
| `transaction_date` | Date | Not Null | The Date of transaction|
| `activation_id` | UUID | PK, Not Null | The Time it was activated|
| `activation_time` | Integer | Not Null | The Time it was activated|
| `warranty_status` | Integer | Not Null | The Warranty status |
### **Table: Product Delivery**
| Attribute Name | Data Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `delivery_id` | UUID | PK,Not Null | The Unique Identification of the Delivery |
| `device_id` | UUID | FK,Unique, Not Null | The Unique Identification of the Device Model |
| `buyer_id` | UUID | FK, Not Null | The Unique Identification of Buyer |
| `delivery_detail` | Varchar | Not Null | The Service delivering package information |
| `target_delivery` | Varchar | Not Null | The Delivery Destinations |
| `delivery_start` | Integer | Not null | The Departure time|
| `arrival_end` | Integer | Not null | The Arrival time|
| `package_status` | Varchar | Not null | The package status|
### **Table: Product Repair**
| Attribute Name | Data Type | Constraints | Description |
| :--- | :--- | :--- | :--- |
| `service_id` | UUID | PK,Not Null | The Unique Service Id |
| `device_id` | UUID | FK,Unique, Not Null | The Unique Identification of the Device Model |
| `buyer_id` | UUID | FK, Not Null | The Unique Identification of Buyer |
| `service_date` | Date | Not Null | The Date of service |
| `service_detail` | Varchar | Not Null | The Detail of Service |
## **3. Relationship Analysis**
This section explains the relationships between the entities and their cardinalities.
PK: Primary Key
FK: Foregin Key
* **Device Manufacturing to Logistics (1:N)**
One device can have many logistics records as it moves through many locations. Each logistics record go to a single device.
* **Buyer to Activation & After-Sales (1:N)**
One buyer can activate many devices. Each activation record is Unique to one buyer.
* **Buyer to Product Delivery (1:N)**
One buyer can receive multiple deliveries . Each delivery record belongs to a single buyer.
* **Buyer to Product Repair (1:N)**
One buyer can request multiple repair services . Each repair record belongs to one buyer.
* **Device Manufacturing to Product Repair (1:N)**
One device can have multiple repair records Through out its lifetime. Each repair record belong to a single device.
## **4. Schema Flow (Data Logic Flow)**
**Case: Device Repair Scenario**
1. **Device Ownership Verification**
Checking the device_id and the buyer_id to ensure verification of the device owner which also help validating the data closely tight to those
2. **Checking Warranty status**
With buyer_id we can also check the warranty_status to confirm their warranty and the device ability to be repaier for zero the cost or will the repair and the part cost the buyer
3. **Repair Product History**
Using the device_id or the buyer_id we can check past service_id and service_detail to further see past repair and common error that been happening allowing technicion to monitored and check specific place
4. **Repair Started**
Once problem is identified a repair begin with new service_id created and once the repair finished and everything is all settled details for the repair such as service_detail and service_date will be filled
4. **Finished**
When everything done the data on the repair will be recorded and a new information of repair and things like warranty will be updated
## **5. Entity Relationship Diagram (ERD)**
*(Ensure the image is clearly visible and covers all entities mentioned above)*
## **Conclusion**
Database designs are completed with satisfaction of the standard global operation, allowing proper and tracked product distribution using unique IDs for every device and tracking all processes throughout the logistics and activation, even delivery and repair. This allows a proper and reliable system for the company to run without worrying about theft or problematic usage and repair scams.
* They used normal data points separating core entities and reduced data redundancy while also improving data consistency.
* They also use many connection allowing properly interconnected database.
* They also use much data that allow absolute tracking and transparency withint the company operations.
## **References**
[1] GeeksforGeeks, “SQL Constraints,” GeeksforGeeks, 2024. [Online]. Available: https://www.geeksforgeeks.org/sql/sql-constraints/
. Accessed: Feb. 7, 2026.
[2] Oracle, “CHAR, VARCHAR, and TEXT Data Types,” MySQL 9.3 Reference Manual, Oracle Corp., 2024. [Online]. Available: https://dev.mysql.com/doc/refman/9.3/en/char.html
. Accessed: Feb. 7, 2026.
[3] Lucid Software Inc., “Entity Relationship Diagrams (ER Diagrams),” Lucidchart, 2024. [Online]. Available: https://www.lucidchart.com/pages/er-diagrams
. Accessed: Feb. 7, 2026.
[4] W3Schools, “MySQL TIMESTAMP() Function,” W3Schools, 2024. [Online]. Available: https://www.w3schools.com/sql/func_mysql_timestamp.asp
. Accessed: Feb. 7, 2026.
[5] GeeksforGeeks, “Cardinality in DBMS,” GeeksforGeeks, 2024. [Online]. Available: https://www.geeksforgeeks.org/dbms/cardinality-in-dbms/
. Accessed: Feb. 7, 2026.
---
## **Notes**
*The structure above is the minimum requirement. You may change the order, add sections, or combine explanations as long as the analysis is clear, structured, and strictly follows the reasoning behind your decisions.*
Sign in with Wallet
Connect another wallet