<aside> 📌 Disclaimer: This is a case study project or final assignment during my undergraduate studies given in the course 'Introduction to Database’

</aside>

Description

NTESLE is a food and beverage company overseeing sales and purchase transactions. Sales transactions involve customer purchases managed by staff, while purchase transactions involve restocking ingredients from suppliers. Currently, NTESLE relies on a manual management system for handling these transactions.

Goals

Create a database system that can store data and maintain the sales transactions and purchase transactions.

Task

As a database administrator in this project, my responsibilities are:

• Create Entity Relationship Diagram to maintain sales and purchase transactions
• Create a database system using DDL syntax that relevant with sales and purchase transaction procedures
• Create query using DML syntax to fill database and simulate the transactions process for sales and purchase transaction

Process and Steps

1. Creation of Entity Relationship Diagram (ERD)

   

   • Analyzing

     First, I read and analyzed the case to identify the entities involved, and then proceeded to begin the creation of the ERD.

   • Entities and Attributes Identification

     In creating the ERD, I identified the primary entities involved in the sales and purchase transaction processes at NTESLE Company. I began by identifying main entities such as Customer, Staff, Product, Supplier, and Ingredient. At this stage, I also determined the attributes associated with each entity.

   • Defining Relationship between Entities

     The next step involved determining relationships among these entities. For example, the relationship between Customer and Sales Transaction (where a single customer can conduct multiple transactions), Staff with Sales Transaction (where one staff member can handle multiple sales transactions), and so forth.

     I also ensured that each entity possessed keys, such as primary keys and foreign keys, enabling the establishment of appropriate relationships within the diagram.

2. Database Creation using DDL syntax

   

   Query to Create Database

   For the database creation, I use Microsoft SQL Server Management Studio. The first step is creating a database. After that, I create tables for each entity previously defined in the ERD. At this stage, I ensured that each attribute aligned with the requirements specified by NTESLE Company. For example:

   • The customer identification must follow the format "CSXXX", where X represents a number from 1 to 9
   • Staff gender can only be filled with “Male” or “Female” value
   • Ingredient expired date must be more than 2022, and so forth.

3. Insert Data Into Tables

   

   Data Dummy

   Before adding data to the database, I created dummy data in Excel. This is done for convenience, because in this project it is only allowed to use the syntax to entry the data into tables. Therefore, I had to entry the data one by one, which took quite a long time. It is more efficient if we are allowed to import data directly from Excel into the database using the features provided by Microsoft SQL Server Management Studio.

   Below is query to insert data into tables:

   

   Query to Insert Data Into Tables

4. Simulate the Sales and Purchase Transaction Process

   I created two scenarios to simulate the sales and purchase transactions.

   • Purchase Transaction

     “NTESLE Company will collaborate with a supplier named Cardi Food, located at 10D Sulaiman Street, West Jakarta, with the phone number 08136341335670. NTESLE Company aims to enhance the quality of nugget production, thus selecting Cardi Food to replace Aisyah Food as the supplier of chicken breast fillet.”

     

   • Sales Transaction

     “On June 10, 2021, a Customer named Dylan Perdana with the ID CS010 conducted a transaction, with the staff on duty that day being Joseph New with the ID ST105.”

     

5. Requested Database Queries by NTESLE Company Manager

   To support database management process in NTESLE Company the manager of NTESLE Company asked me to provide some query that resulting important data. Below are the requested requirements along with the corresponding queries.

   • Display CustomerName, Customer Phone Number (obtained by replacing “+62” with “0”), ProductName, Total Transaction (obtained from the total number of transaction), and Total Product Price (obtained from the sum of all product price purchased) for every product whose name has at least two words and expired year after

     

     Query for Sales Transaction

     

     Result

   • Display SupplierID, SupplierName, Supplier Local Phone Number (obtained by changing the first number to ‘+62’), SupplierAddress, and Total Price (obtained from sum of Ingredient Price times Quantity) for every purchase from a supplier whose last name is food and the total price is greater than the average total price. Then sort the data in descending format based on the total price.

     

     Query for “alias subquery”

     

     Result

   • Create a view named “PurchaseTransactionView” to display SupplierName, SupplierPhoneNumber, Total Transaction (obtained from the count of Purchase Transaction ID), IngredientExpiredDate, IngredientName, IngredientPrice, and Total Ingredient Price (obtained from the sum of Ingredient Price) for every Ingredient that expires in 2023 and the Total Ingredient Price is more than 60000.

     

     Query for Purchase Transaction View

     

     Result

Conclusion