Database

SyntheFiZer

SyntheFiZer is a music shop that sells synthesizer located in South Jakarta. Managed by your friend, Bob, he manages all of activities that belongs to SyntheFiZer like selling synthesizer to customer and restocking with manufacturer.

Every staff that hired by SyntheFiZer have a task to serve a customer who wants to buy synthesizer and restock directly to the manufacturer. Every staff must be following the procedures to become a staff, which are:

• Every staff hired must have a personal information like name, gender, birth date, address, and phone number. Every staff has an identification number with the following format:

STXXX

X => number between 0 9

• Staff can purchase product with manufacturer.
• Every purchase transaction made with the manufacturer have all the information about staff, manufacturer, purchase date, product purchased, and the quantity of each product. Every purchase transaction has an identification number with the following format:

PUXXX

X => number between 0 9

• Staff can also serve a customer who wants to buy a product.
• Every sales transaction made by the customer have all the information about staff, customer, transaction date, product sold, and the quantity of each product. Every sales transaction has an identification number with the following format:

SAXXX

X => number between 0 9

• Every product sold by SyntheFiZer have its own name, price, rating, and all the information about its category. Every product has an identification number with the following format:

PRXXX

X => number between 0 9

• Every category has its own name, and an identification number with the following format:

CAXXX

X => number between 0 9

Every customer that wants to buy synthesizer must follow the sales transaction procedures, those are:

• Every customer that wants to purchase a product must already completed personal information like name, email, gender, birth date, and phone number. Every customer has an identification number with the following format:

CUXXX

X => number between 0 9

• Customer can purchase more than one product in every transaction.

Every manufacturer that wants to sell their product follow the purchase transaction procedures, those are:

• Every manufacturer that wants to sell their product must already completed personal information like name, and email. Every manufacturer has an identification number with the following format:

MAXXX

X => number between 0 9

• Manufacturer can sell more than one product in every transaction.

Notes:

• Manufacturer name should have more than 0 character.
• Manufacturer email must have @.
• Product name should have more than 0 character.
• Product price should be more than 0 dollar.
• Product rating must be between 1 and 10 (inclusive).
• Staff name should have more than 0 character.
• Staff gender must be either Male or Female.
• Customer name should have more than 0 character.
• Customer email must have @.

Now SyntheFiZer still using manual management system to maintain the sales and purchase transactions. You as his precious friend wants to help SyntheFiZer to create a database system that can store data and maintain the sales and purchase transactions. The tasks that you must do are:

1. Create Entity Relationship Diagram to maintain sales and purchase transactions.
2. Create a database system using DDL syntax that relevant with sales and purchase transactions.
3. Create query using DML syntax to fill the tables in database systems with data based on the following conditions:

• Master table must be filled with more than or equals 15 data.
• Transaction table must be filled with more than or equals 15 data.
• Transaction detail table must be filled with more than or equals 25 data.

4. Create query using DML syntax to simulate the transactions process for sales and purchase transactions.

Note: DML syntax to fill database and DML syntax to simulate the transactions process should be a different query.

5. To support database management process in SyntheFiZer, Bob asked you to provide some query that resulting important data. The requirements that asked from her are:

1. Display Staff First Name (obtained from the first word of StafName), Staff Phone Number, Purchase Count (obtained from the total purchases made) for every Male staff where has managed purchase transaction that has Quantity more than 5.

2. Display CustomerName, CustomerGender, Total Synthesizer Bought (obtained from the sum of all sales transactions), for every customer that bought product(s) with rating more than 6 and happened in 2020. Show data in descending order based on the Total Synthesizer Bought.

3. Display Product Name, Manufacturer Name, Total Product Purchased (obtained from the sum of purchased quantity), Maximum Purchased (obtained from the maximum of purchased quantity) for every product that has number on its name and the category is not 'CA002' nor 'CA001'.

4. Display Product Name (ProductName all in Uppercase), Product Price (obtained by adding $ in front of ProductPrice), CategoryName, Average Quantity Bought (obtained from the average of sales quantity), Total Sales (obtained by adding $ in front of the sum of Quantity * ProductPrice) for every sales transaction in 2020 with product that has price more than 1000 dolar.

5. Display distinct Product Number (obtained by replacing 'PR' with 'Product #' in ProductID and remove the leading zero), ProductName, Product Price (obtained by adding '$ ' in front of ProductPrice), ProductRating, ManufacturerName for Manufacturer that has id 'MA001' and the ProductPrice is more than the average of all ProductPrices in database.

(alias subquery)

6. Display SalesID, SalesDate (SalesID in format like '23 January 2001'), Customer (obtained by adding 'Mr./Mrs. ' in front of Customer's first name), ProductName, Quantity, for every sales transaction that has quantity more than the average and excluding product that has category 'CA002'.

(alias subquery)

7. Display Product Number (obtained by replacing 'PR' with 'Product #' in ProductID and remove the leading zero), Manufacturer Name (ManufacturerName in all lowercase), Highest Purchase (obtained by adding ' item(s)' at the end of the maximum of total quantity purchased) for every product that are not manufactured by 'MA001' and only count purchase transaction that happened after May.

(alias subquery)

8. Display CustomerName, New Email (obtained from the last 3 digit of CustomerID appended to '@synthefizer.org'), Age (obtained by subtracting the current date with CustomerBirthDate), Total Spending (obtained by adding '$ ' in front of the SUM of ProductPrice * Quantity) for every customer who is over 17 years old that has spent more than the average total spending.

(alias subquery)

9. Create a view named 'Product Portfolio 2020' to Display ProductID, ProductName, CategoryName, Revenue (obtained from the sum of ProductPrice * Quantity), Average Sales (obtained from the average of Quantity) for every product in the 2020 sales transaction, except product with id 'PR004'.

10. Create a view named 'Manufacturer Relationship' to Display ManufacturerID, ManufacturerName, ManufacturerEmail, Total Purchase (obtained from the count of distinct PurchaseID and ' time(s)' at the end) Total Money Spent (obtained from the sum of ProductPrice * Quantity) for every Manufacturer with the exception of Manufacturer with ID 'MA006' and product with id 'PR005'.