Vietnam National University Ho Chi Minh City UNIVERSITY OF ECONOMICS AND LAW DATABASE MIDTERM ASSIGNMENT HOANG PHUC INTERNATIONAL SALES MANAGEMENT SYSTEM Instructor: M.S Lam Hong Thanh T.A Nguyen Duy Nghia Student: Huỳnh Hồng Uyển - K204162006 Trầm Việt Tường Vy - K204162010 Đặng Xuân Mai - K204160667 Đào Phương Anh - K204160660 Ho Chi Minh City, Dec – 2021 TABLE OF CONTENT I BUSINESS MODEL INTRODUCTION Store overview: Store structure: II THE OPERATION OF THE STORE The operation of the store Entity objects related to store operations and business rules Description of the main function of business processes: 10 DFD (Data Flow Diagram) – Context Diagram 15 ER Model Design 16 III Build Entity – Relationship Model 16 Entity & Data type 17 IV Logical Database Design 22 Relation Data Model 22 Mapping the relationship & functional dependency diagram 28 V Physical database design 31 Define data type 31 Physical ERD 36 Syntax of database creating 37 VI Query 42 EXSORDIUM First of all, I would like to thank M.S Lam Hong Thanh, The person in charge of the subject, helped us with the background knowledge in the database subject to the midterm project and gave our class more time to be able to complete the project better I would like to sincerely thank T.A Nguyen Duy Nghia for sharing her knowledge, experience and preparing mini-tests during the tutoring sessions, helping us consolidate our knowledge Although there are many efforts in the learning process, as well as in the process of making the mid-term project, but the team cannot avoid shortcomings, I look forward to your valuable comments as well as your friends to improve my results Once again, I sincerely thank INTRODUCTION Why Hoang Phuc International store is chosen? In the 21st century, technology is changing all that in a fundamental way Digital transformation affects every aspect of life, every field It not only affects fields that need direct use of technology such as finance, banking, and manufacturing, but also affects other fields such as healthcare, etc., especially commodity trading Centuries have passed, the definition of "trade" has always been associated with things like face-to-face between sellers and buyers, one must always wonder about the distance of trade, how to compare prices between the two stores These things seem so familiar that many people are convinced that it will not be changed However, it has changed -Online shopping is the future of commodity trading“Trading online” takes advantage of technology to simulate or improve upon traditional methods of trading It is a solution for all, a spectacular step forward in commerce It is a great innovation, however, when it first launched, this type of commerce was not welcomed The reasons why to explain it is a lot, which can be said countless, such as worrying about cheating, the interaction becomes "not real" which lead to people don't believe in the quality of goods However, the most important reason that people don't have motivation to change and adapt when traditional learning has provided almost everything they need However, the COVID-19 pandemic made everything change The pandemic is a challenge for people to self-advocate to change, but it is also an opportunity for us to access a great way of new trading This pandemic makes shopping online popular; it is also the foundation for the development of new technology, e-commerce platform was born Businesses are also gradually changing to follow the trend of the times, and Hoang Phuc is not an exception to that trend Hoang Phuc is a famous and prestigious shoe company in Vietnam Before the pandemic, Hoang Phuc shop also created e-commerce platforms and online businesses in parallel, but still not really developed The pandemic is a challenge but also an opportunity for Hoang Phuc's development This idea came to mind of the members of the group when everything around related to learning was associated with the word "online", we immediately wondered how these really worked? Because we want to know better as well as have a more specific view, we decided to find out about this issue Target: Accessing to a large number of customers: with attractive promotions and convenience of a shopping experience tailored to customer's place With a lot of goods with different needs, and flexible equipment to suit almost every lifestyle, customers can turn to shopping online as a viable to everyone who want to purchase can purchase, and the thing they need to have just a smart device with an internet connection This can help business connect to a large number of customers everywhere because of its convenience and user-friendliness It helps to maximize revenue and profit as well as expand their markets, providing many shopping opportunities for everyone Supporting in decision marking: with customers data and comments, it can help business in general and seller in particular better understand client' needs and make appropriate changes From there, it is possible for business to understand the development trend of trading and the needs of customers in development fields Parallel development of online and offline sales: although online trading is growing, we cannot deny the value that stores on the street bring They make us attract more customers, and provide an in-person experience (something that online sales have yet to do) Expected result: Building an ordering process, management and sales process for the Hoang Phuc store I BUSINESS MODEL INTRODUCTION Store overview: − Fashion store system: Hoang Phuc International − Business items: Shoes, Sandan, Clothes − Focused business: Shoes (Kappa, Dr Martens, Ecko Unltd, Superga, Rebok, Staple) − Products: all kinds of shoes (men/women) including: • Sneakers • Sport shoes • Sandals • Leather shoes • Casual shoes • Student's shoes Store structure: Store owner Employees Store owner: • • • • Store manager Employee manager Decide on the import of goods Contact supplier Employees • • • • • • • Enter packages Enter product information Save the information provided Update item Save customer information Sell Sales report II THE OPERATION OF THE STORE The operation of the store 1.1 Import packages process − Supplier send packages information and supplier information − Employees with import duties based on market demand and orders from customers to make a list of goods to be imported and then make import orders, submit them to the store owner for review and approval, orders after being imported Approval will be sent to the supplier, the supplier confirms the order, the items can be fulfilled according to the order The two parties will agree on a delivery date − Check the quantity and quality of products − Satisfactory goods will be coded, photographed and warehoused − Defective products will be returned to the supplier by the employees 1.2 Selling process − The customer makes a purchase request Employee will check the information details of packages, in stock or not, then report product details, selling price for customers Customers confirm purchase and provide information customer Employee receive information and make Sales receipt Later delivery and payment 1.3 Payment Process − Weekly staff will check the debt book, then urge customers to owe The customer confirms the payment of the debt, the staff will make a collection note and finish payment process with customers 1.4 Returning processing − With suppliers: defective, unsatisfactory packages, employees will notify the store owner of the status of the packages If the shop owner confirms the return of g packages to the supplier, the staff will send information packages returned to the supplier The supplier confirms the packages returned to the employee will make a return receipt, return the packages and pay with the supplier − Customer: The customer does not fit, does not match the defective product may request an exchange − Packages will be re-entered warehouse 1.5 Reporting Process The report includes: − Goods sold in the month − Goods imported in the month − Debt situation Entity objects related to store operations and business rules • Supplier Suppliers provide packages Suppliers can supply a variety of packages • Package PACKAGE (package) to manage information (package ID, name, unit of measure, selling price) about the shoe products that the store sells Each package can have many suppliers • Employee EMPLOYEE (Employee) manages information (full name, date of birth, gender, number ID card, phone, address, working date) of employees àAn employee must have a unique ID, belonging to a specific department • Customer CUSTOMER (Customer) stores full name, date of birth, gender, address, phone phone, etc of the customers àA customer can buy many types of goods • Warehouse WAREHOUSE (warehouse) stores unsold and returned goods (Code, Warehouse Name, Warehouse Address, warehouse staff) àA warehouse can hold many types of goods Description of the main function of business processes: 3.1.Import process Suppiler Employee Store owner Data records Provide package information Don’t have List of customer’s demand Check the import demand Have Make a list of packages according to the demand List of packages List confirmation Order Order confirmation Import package Import Receipt Non-qualified Checking Qualified Create package ID Warehouse List of warehouse 14 IMPORT RECIEPT – DETAILS_IMPORT RECEIPT 1-n 15 SUPPLIER - IMPORT RECEIPT 1-n 16 RETURN TO SUPPLIER – DETAILS_RETURN TO SUPPLIER 1-n 17 RETURN TO SUPPLIER – SUPPLIER n-1 18 ORDER – DETAILS ORDER PACKAGE DETAILS_SALES RECEIPT PACKAGE DETAILS_RECEIVE SALES RETURN PACKAGE – DETAILS_IMPORT RECEIPT PACKAGE – DETAILS_RETURN TO SUPPLIER PACKAGE DETAILS_ORDER PACKAGE MANUFACTURER 1-n 25 PACKAGE – WARE HOUSE n-1 26 PACKAGE PACKAGETYPE n-1 19 20 21 22 23 24 1-n With each import receipt, we can have a lot of details_import receipt However, each details_import receipt belongs to one import receipt Each employee can import many packages from one supplier Each package is imported from only one supplier With each sales return to supplier, we can have a lot of details_return to supplier However, each details_return to supplier belongs to one sales return to supplier Each employee can return many packages to one supplier Each package is returned to only one supplier Each order can have a lot of details Each detail of order belongs to only one order Each package can have a lot of details 1-n Each package can have a lot of details 1-n Each package can have a lot of details 1-1 Each package can have a lot of details 1-n Each package can have a lot of details n-1 Each manufacturer has many packages, each package belongs to one manufacturer only Each warehouse stores many packages, each package belongs to one warehouse only Each packagetype has many packages, each package belongs to one packagetype only V Physical database design Define data type - Table PACKAGE_TYPE Column Name c PACKAGE_TYPE_ID varchar(50) PACKAGE_TYPE_NAME nvarchar(50) PACKAGE_ID varchar(50) NOTE_PACKAGE_TYPE nvarchar(50) - Key Allow Nulls PK X Table MANUFACTURER Column Name Data Type MANUFACTURER_ID varchar(50) MANUFACTURER_NAME nvarchar(50) NOTE_MANUFACTURER nvarchar(50) - - Data Type Key Allow Nulls X Table WAREHOUSE Column Name WAREHOUSE_ID WAREHOUSE_NAME WAREHOUSE_ADDRESS MANAGER_PHONENUMBER Data Type varchar(50) nvarchar(50) nvarchar(200) int Key PK Allow Nulls Table PACKAGE Column Name PACKAGE_ID PACKAGE_NAME COLOR SIZE PRODUCTION_YEAR MANUFACTURER_ID PACKAGE_TYPE_ID WAREHOUSE_ID NOTE_PACKAGE Data Type varchar(50) nvarchar(50) nvarchar(50) nvarchar(50) int varchar(50) varchar(50) varchar(50) nvarchar(50) Key PK Allow Nulls X PFK PFK PFK X - Table POSITION Column Name POSITION_ID POSITION_NAME MISSION - Data Type Varchar (50) Nvarchar (50) Nvarchar (200) int date Nvarchar (50) Varchar (50) Nvarchar (50) Key PK Allow Nulls X Allow Nulls X PFK X Table SUPPLIER Column Name SUPPLIER_ID SUPPLIER_NAME SUPPLIER_ADDRESS SUPPLIER_PHONENUMBER NOTE_SUPPLIER - Key PK Table EMPLOYEE Column Name EMPLOYEE_ID EMPLOYEE_NAME EMPLOYEE_ADDRESS EMPLOYEE_PHONENUMBER BIRTHDAY GENDER POSITION_ID NOTE_EMPLOYEE - Data Type varchar(50) nvarchar(50) nvarchar(50) Data Type Varchar (50) Nvarchar (50) Nvarchar (200) int Nvarchar (50) Key PK Data Type Varchar (50) Nvarchar (50) date Nvarchar (200) int Nvarchar (50) Nvarchar (50) Nvarchar (50) Key Allow Nulls X Table CUSTOMER Column Name CUSTOMER_ID CUSTOMER_NAME BIRTHDAY CUSTOMER_ADDRESS CUSTOMER_PHONENUMBER GENDER SIZE NOTE_CUSTOMER Allow Nulls X X - Table IMPORT_RECEIPT Column Name IMPORT_RECEIPT_ID EMPLOYEE_ID SUPPLIER_ID MODE_OF_PAYMENT_IMPORT IMPORT_DATE - Allow Nulls Data Type Varchar (50) Varchar (50) int int int Key PFK PFK Allow Nulls Data Type Varchar (50) Varchar (50) Varchar (50) Varchar (50) Varchar (50) date Key PK PFK PFK Allow Nulls Key PFK PFK Allow Nulls Table 11 SALES_RECEIPT Column Name SALES_RECEIPT_ID EMPLOYEE_ID CUSTOMER_ID MODE_OF_PAYMENT_SALES MODE_OF_SALES SALES_DATE - Key PK PFK PFK Table 10 DETAILS_IMPORT_RECEIPT Column Name IMPORT_RECEIPT_ID PACKAGE_ID QUANTITY_OF_IMPORT IMPORT_PRICE EXPECTED_SALES_PRICE - Data Type Varchar (50) Varchar (50) Varchar (50) Varchar (50) date Table 12 DETAILS_SALES_RECEIPT Column Name SALES_RECEIPT_ID PACKAGE_ID QUANTITY_OF_SALES SALES_PRICE Data Type Varchar (50) Varchar (50) int int - Table 13 RETURN_TO_SUPPLIER Column Name RETURN_TO_SUPPLIER_ID EMPLOYEE_ID SUPPLIER_ID MODE_OF_PAYMENT_RETURN RETURN_DATE - Key PK PFK PFK Allow Nulls Key PFK PFK Allow Nulls Table 14 DETAILS_RETURN_TO_SUPPLIER Column Name RETURN_TO_SUPPLIER_ID PACKAGE_ID QUANTITY_OF_PACKAGE_RETURN PRICE_OF_PACKAGE_RETURN EXPECTED_SALES_PRICE - Data Type Varchar (50) Varchar (50) Varchar (50) Varchar (50) date Data Type Varchar (50) Varchar (50) int int int Table 15 RECEIVE_SALES_RETURN Column Name RECEIVE_SALES_RETURN_ID EMPLOYEE_ID CUSTOMER_ID MODE_OF_PAYMENT_RECEIVE RECEIVE_DATE Data Type varchar(50) varchar(50) varchar(50) varchar(50) date Key PK PFK PFK Allow Nulls Key PFK PFK Allow Nulls - Table 16 DETAILS_RECEIVE_SALES_RETURN Column Name RECEIVE_SALES_RETURN_ID PACKAGE_ID QUANTITY_OF_SALES_RETURN PRICE_OF_SALES_RETURN Data Type Varchar (50) Varchar (50) int int - Table 17 ORDERS Column Name ORDER_ID CUSTOMER_ID EMPLOYEE_ID ORDER_DATE - - ORDER_ID PACKAGE_ID ORDER_QUANTITY - Key PK PFK PFK Allow Nulls Data Type Varchar (50) Varchar (50) int Key PFK PFK Allow Nulls Data Type Varchar (50) Varchar (50) varchar (50) date int int Key PK PFK PFK Allow Nulls Table 18 DETAILS_ORDER Column Name - Data Type Varchar (50) Varchar (50) Varchar (50) date Table 19 DEBT_RECEIPT Column Name DEBT_RECEIPT_ID SALES_RECEIPT_ID EMPLOYEE_ID DATE_OF_COLLECT_DEBTS THE_AMOUNT_OF_DEBT THE_AMOUNT_OF_MONEY_PAID_OFF Physical ERD SALES_RECEIPT DETAILS_SALES_RECEIPT SALES_RECEIPT_ID EMPLOYEE_ID CUSTOMER CUSTOMER_ID CUSTOMER_ID DEBT_RECEIPT_ID MODE_OF_PAYMENT_SALES CUSTOMER_NAME SALES_RECEIPT_ID MODE_OF_SALES BIRTHDAY SALES_DATE CUSTOMER_ADDRESS DEBT_RECEIPT EMPLOYEE_ID DATE_OF_COLLECT_DEBTS CUSTOMER_PHONENUMBER THE_AMOUNT_OF_DEBT GENDER THE_AMOUNT_OF_MONEY_PAID_OFF SIZE SALES_RECEIPT_ID PACKAGE_ID QUANTITY_OF_SALES SALES_PRICE MANUFACTURER NOTE_CUSTOMER DETAILS_RECEIVE_SALES_RETURN RECEIVE_SALES_RETURN_ID PACKAGE_ID CUSTOMER_ID QUANTITY_OF_SALES_RETURN PRICE_OF_SALES_RETURN RECEIVE_DATE ORDERS EMPLOYEE_ID ORDER_ID EMPLOYEE_NAME DETAILS_ORDER CUSTOMER_ID EMPLOYEE_ADDRESS EMPLOYEE_ID EMPLOYEE_PHONENUMBER ORDER_DATE BIRTHDAY GENDER POSITION_ID NOTE_EMPLOYEE NOTE_MANUFACTURER RECEIVE_SALES_RETURN_ID EMPLOYEE_ID MODE_OF_PAYMENT_RECEIVE EMPLOYEE MANUFACTURER_ID MANUFACTURER_NAME RECEIVE_SALES_RETURN IMPORT_RECEIPT IMPORT_RECEIPT_ID DETAILS_IMPORT_RECEIPT EMPLOYEE_ID IMPORT_RECEIPT_ID SUPPLIER_ID MODE_OF_PAYMENT_IMPORT IMPORT_DATE PACKAGE_ID SUPPLIER QUANTITY_OF_IMPORT SUPPLIER_ID IMPORT_PRICE SUPPLIER_NAME EXPECTED_SALES_PRICE PACKAGE ORDER_ID PACKAGE_ID PACKAGE_ID PACKAGE_NAME ORDER_QUANTITY COLOR PACKAGE_TYPE_ID PRODUCTION_YEAR PACKAGE_TYPE_NAME MANUFACTURER_ID PACKAGE_ID PACKAGE_TYPE_ID NOTE_PACKAGE_TYPE WAREHOUSE_ID NOTE_PACKAGE SUPPLIER_ADDRESS SUPPLIER_PHONENUMBER NOTE_SUPPLIER POSITION POSITION_ID POSITION_NAME MISSION RETURN_TO_SUPPLIER RETURN_TO_SUPPLIER_ID WAREHOUSE EMPLOYEE_ID SUPPLIER_ID MODE_OF_PAYMENT_RETURN RETURN_DATE DETAILS_RETURN_TO_SUPPLIER RETURN_TO_SUPPLIER_ID PACKAGE_ID QUANTITY_OF_PACKAGE_RETURN PRICE_OF_PACKAGE_RETURN EXPECTED_SALES_PRICE PACKAGE_TYPE SIZE WAREHOUSE_ID WAREHOUSE_NAME WAREHOUSE_ADDRESS MANAGER_PHONENUMBER Syntax of database creating PACKAGE TYPE CREATE TABLE PACKAGE_TYPE (PACKAGE_TYPE_ID VARCHAR(50) NOT NULL, PACKAGE_TYPE_NAME NVARCHAR(50) NOT NULL, PACKAGE_ID VARCHAR(50) NOT NULL, NOTE_PACKAGE_TYPE NVARCHAR(50), CONSTRAINT PACKAGE_TYPE_PK PRIMARY KEY (PACKAGE_TYPE_ID)) MANUFACTURER CREATE TABLE MANUFACTURER (MANUFACTURER_ID VARCHAR(50) NOT NULL, MANUFACTURER_NAME NVARCHAR(50) NOT NULL, NOTE_MANUFACTURER NVARCHAR(50) CONSTRAINT MANUFACTURER_PK PRIMARY KEY (MANUFACTURER_ID)) WAREHOUSE CREATE TABLE WAREHOUSE (WAREHOUSE_ID VARCHAR(50) NOT NULL, WAREHOUSE_NAME NVARCHAR(50) NOT NULL, WAREHOUSE_ADDRESS NVARCHAR(200) NOT NULL, MANAGER_PHONENUMBER INT NOT NULL, CONSTRAINT WAREHOUSE_PK PRIMARY KEY (WAREHOUSE_ID)) PACKAGE CREATE TABLE PACKAGE (PACKAGE_ID VARCHAR(50) NOT NULL, PACKAGE_NAME NVARCHAR(50) NOT NULL, COLOR NVARCHAR(50), SIZE NVARCHAR(50) NOT NULL, PRODUCTION_YEAR INT NOT NULL, MANUFACTURER_ID VARCHAR(50) NOT NULL, PACKAGE_TYPE_ID VARCHAR(50) NOT NULL, WAREHOUSE_ID VARCHAR(50) NOT NULL, NOTE_PACKAGE NVARCHAR(50), CONSTRAINT PACKAGE_PK PRIMARY KEY (PACKAGE_ID), CONSTRAINT PACKAGE_FK1 FOREIGN KEY (MANUFACTURER_ID) REFERENCES MANUFACTURER(MANUFACTURER_ID), CONSTRAINT PACKAGE_FK2 FOREIGN KEY (PACKAGE_TYPE_ID) REFERENCES PACKAGE_TYPE(PACKAGE_TYPE_ID), CONSTRAINT PACKAGE_FK3 FOREIGN KEY (WAREHOUSE_ID) REFERENCES WAREHOUSE(WAREHOUSE_ID)) POSITION CREATE TABLE POSITION (POSITION_ID VARCHAR(50) NOT NULL, POSITION_NAME NVARCHAR(50) NOT NULL, MISSION NVARCHAR(50) CONSTRAINT POSITION_PK PRIMARY KEY (POSITION_ID)) EMPLOYEE CREATE TABLE EMPLOYEE (EMPLOYEE_ID VARCHAR(50) NOT NULL, EMPLOYEE_NAME NVARCHAR(50) NOT NULL, EMPLOYEE_ADDRESS NVARCHAR(200) NOT NULL, EMPLOYEE_PHONENUMBER INT NOT NULL, BIRTHDAY DATE NOT NULL, GENDER NVARCHAR(50), POSITION_ID VARCHAR(50) NOT NULL, NOTE_EMPLOYEE NVARCHAR(50) CONSTRAINT EMPLOYEE_PK PRIMARY KEY (EMPLOYEE_ID), CONSTRAINT EMPLOYEE_FK1 FOREIGN KEY (POSITION_ID) REFERENCES POSITION(POSITION_ID)) SUPPLIER CREATE TABLE SUPPLIER (SUPPLIER_ID VARCHAR(50) NOT NULL, SUPPLIER_NAME NVARCHAR(50) NOT NULL, SUPPLIER_ADDRESS NVARCHAR(200) NOT NULL, SUPPLIER_PHONENUMBER INT NOT NULL, NOTE_SUPPLIER NVARCHAR(50) CONSTRAINT SUPPLIER_PK PRIMARY KEY (SUPPLIER_ID)) CUSTOMER CREATE TABLE CUSTOMER (CUSTOMER_ID VARCHAR(50) NOT NULL, CUSTOMER_NAME NVARCHAR(50) NOT NULL, BIRTHDAY DATE NOT NULL, CUSTOMER_ADDRESS NVARCHAR(200) NOT NULL, CUSTOMER_PHONENUMBER INT NOT NULL, GENDER NVARCHAR(50), SIZE NVARCHAR(50) NOT NULL, NOTE_CUSTOMER NVARCHAR(50) CONSTRAINT CUSTOMER_PK PRIMARY KEY (CUSTOMER_ID)) IMPORT RECEIPT CREATE TABLE IMPORT_RECEIPT (IMPORT_RECEIPT_ID VARCHAR(50) NOT NULL, EMPLOYEE_ID VARCHAR(50) NOT NULL, SUPPLIER_ID VARCHAR(50) NOT NULL, MODE_OF_PAYMENT_IMPORT VARCHAR(50) NOT NULL, IMPORT_DATE DATE NOT NULL, CONSTRAINT IMPORT_RECEIPT_PK PRIMARY KEY (IMPORT_RECEIPT_ID), CONSTRAINT IMPORT_RECEIPT_FK1 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER(SUPPLIER_ID), CONSTRAINT IMPORT_RECEIPT_FK2 FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)) DETAILS IMPORT RECEIPT CREATE TABLE DETAILS_IMPORT_RECEIPT (IMPORT_RECEIPT_ID VARCHAR(50) NOT NULL, PACKAGE_ID VARCHAR(50) NOT NULL, QUANTITY_OF_IMPORT INT NOT NULL, IMPORT_PRICE INT NOT NULL, EXPECTED_SALES_PRICE INT NOT NULL, CONSTRAINT DETAILS_IMPORT_RECEIPT_FK1 FOREIGN KEY (IMPORT_RECEIPT_ID) REFERENCES IMPORT_RECEIPT(IMPORT_RECEIPT_ID), CONSTRAINT DETAILS_IMPORT_RECEIPT_FK2 FOREIGN KEY (PACKAGE_ID) REFERENCES PACKAGE(PACKAGE_ID)) SALES RECEIPT CREATE TABLE SALES_RECEIPT (SALES_RECEIPT_ID VARCHAR(50) NOT NULL, EMPLOYEE_ID VARCHAR(50) NOT NULL, CUSTOMER_ID VARCHAR(50) NOT NULL, MODE_OF_PAYMENT_SALES VARCHAR(50) NOT NULL, MODE_OF_SALES VARCHAR(50) NOT NULL, SALES_DATE DATE NOT NULL, CONSTRAINT SALES_RECEIPT_PK PRIMARY KEY (SALES_RECEIPT_ID), CONSTRAINT SALES_RECEIPT_FK1 FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID), CONSTRAINT SALES_RECEIPT_FK2 FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)) DETAILS SALES RECEIPT CREATE TABLE DETAILS_SALES_RECEIPT (SALES_RECEIPT_ID VARCHAR(50) NOT NULL, PACKAGE_ID VARCHAR(50) NOT NULL, QUANTITY_OF_SALES INT NOT NULL, SALES_PRICE INT NOT NULL, CONSTRAINT DETAILS_SALES_RECEIPT_FK1 FOREIGN KEY (SALES_RECEIPT_ID) REFERENCES SALES_RECEIPT(SALES_RECEIPT_ID), CONSTRAINT DETAILS_SALES_RECEIPT_FK2 FOREIGN KEY (PACKAGE_ID) REFERENCES PACKAGE(PACKAGE_ID)) RETURN TO SUPPLIER CREATE TABLE RETURN_TO_SUPPLIER (RETURN_TO_SUPPLIER_ID VARCHAR(50) NOT NULL, EMPLOYEE_ID VARCHAR(50) NOT NULL, SUPPLIER_ID VARCHAR(50) NOT NULL, MODE_OF_PAYMENT_RETURN VARCHAR(50) NOT NULL, RETURN_DATE DATE NOT NULL, CONSTRAINT RETURN_TO_SUPPLIER_PK PRIMARY KEY (RETURN_TO_SUPPLIER_ID), CONSTRAINT RETURN_TO_SUPPLIER_FK1 FOREIGN KEY (SUPPLIER_ID) REFERENCES SUPPLIER(SUPPLIER_ID), CONSTRAINT RETURN_TO_SUPPLIER_FK2 FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)) DETAILS RETURN TO SUPPLIER CREATE TABLE DETAILS_RETURN_TO_SUPPLIER (RETURN_TO_SUPPLIER_ID VARCHAR(50) NOT NULL, PACKAGE_ID VARCHAR(50) NOT NULL, QUANTITY_OF_PACKAGE_RETURN INT NOT NULL, PRICE_OF_PACKAGE_RETURN INT NOT NULL, EXPECTED_SALES_PRICE INT NOT NULL, CONSTRAINT DETAILS_RETURN_TO_SUPPLIER_FK1 FOREIGN KEY (RETURN_TO_SUPPLIER_ID) REFERENCES RETURN_TO_SUPPLIER(RETURN_TO_SUPPLIER_ID), CONSTRAINT DETAILS_RETURN_TO_SUPPLIER_FK2 FOREIGN KEY (PACKAGE_ID) REFERENCES PACKAGE(PACKAGE_ID)) RECEIVE SALES RETURN CREATE TABLE RECEIVE_SALES_RETURN (RECEIVE_SALES_RETURN_ID VARCHAR(50) NOT NULL, EMPLOYEE_ID VARCHAR(50) NOT NULL, CUSTOMER_ID VARCHAR(50) NOT NULL, MODE_OF_PAYMENT_RECEIVE VARCHAR(50) NOT NULL, RECEIVE_DATE DATE NOT NULL, CONSTRAINT RECEIVE_SALES_RETURN_PK PRIMARY KEY (RECEIVE_SALES_RETURN_ID), CONSTRAINT RECEIVE_SALES_RETURN_FK1 FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID), CONSTRAINT RECEIVE_SALES_RETURN_FK2 FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)) DETAILS RECEIVE SALES RETURN CREATE TABLE DETAILS_RECEIVE_SALES_RETURN (RECEIVE_SALES_RETURN_ID VARCHAR(50) NOT NULL, PACKAGE_ID VARCHAR(50) NOT NULL, QUANTITY_OF_SALES_RETURN INT NOT NULL, PRICE_OF_SALES_RETURN INT NOT NULL, CONSTRAINT DETAILS_RECEIVE_SALES_RETURN_FK1 FOREIGN KEY (RECEIVE_SALES_RETURN_ID) REFERENCES RECEIVE_SALES_RETURN(RECEIVE_SALES_RETURN_ID), CONSTRAINT DETAILS_RECEIVE_SALES_RETURN_FK2 FOREIGN KEY (PACKAGE_ID) REFERENCES PACKAGE(PACKAGE_ID)) ORDER CREATE TABLE ORDERS (ORDER_ID VARCHAR(50) NOT NULL, CUSTOMER_ID VARCHAR(50) NOT NULL, EMPLOYEE_ID VARCHAR(50) NOT NULL, ORDER_DATE DATE NOT NULL, CONSTRAINT ORDERS_PK PRIMARY KEY (ORDER_ID), CONSTRAINT ORDERS_FK1 FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER(CUSTOMER_ID), CONSTRAINT ORDERS_FK2 FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)) DETAILS_ORDER CREATE TABLE DETAILS_ORDER (ORDER_ID VARCHAR(50) NOT NULL, PACKAGE_ID VARCHAR(50) NOT NULL, ORDER_QUANTITY INT NOT NULL, CONSTRAINT DETAILS_ORDER_FK1 FOREIGN KEY (ORDER_ID) REFERENCES ORDERS(ORDER_ID), CONSTRAINT DETAILS_ORDER_FK2 FOREIGN KEY (PACKAGE_ID) REFERENCES PACKAGE(PACKAGE_ID)) DEBT RECEIPT CREATE TABLE DEBT_RECEIPT (DEBT_RECEIPT_ID VARCHAR(50) NOT NULL, SALES_RECEIPT_ID VARCHAR(50) NOT NULL, EMPLOYEE_ID VARCHAR(50) NOT NULL, DATE_OF_COLLECT_DEBTS DATE NOT NULL, THE_AMOUNT_OF_DEBT INT NOT NULL, THE_AMOUNT_OF_MONEY_PAID_OFF INT NOT NULL, CONSTRAINT DEBT_RECEIPT_PK PRIMARY KEY (DEBT_RECEIPT_ID), CONSTRAINT DEBT_RECEIPT_FK1 FOREIGN KEY (SALES_RECEIPT_ID) REFERENCES SALES_RECEIPT(SALES_RECEIPT_ID), CONSTRAINT DEBT_RECEIPT_FK2 FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEE(EMPLOYEE_ID)) VI Query Query Import Receipt that cost more than 5.000.000 VND SELECT IMPORT_RECEIPT_ID, IMPORT_PRICE, QUANTITY_OF_IMPORT FROM DETAILS_IMPORT_RECEIPT WHERE IMPORT_PRICE > 5000000 Query all customers in Thu Duc City with a 'M' in their name SELECT CUSTOMER_NAME, CUSTOMER_ADDRESS FROM CUSTOMER WHERE CUSTOMER_ADDRESS = 'THU DUC CITY' AND CUSTOMER_NAME = '%M%' Query the names of all guests who are over 40 years old SELECT CUSTOMER_NAME, CUSTOMER.BIRTHDAY FROM CUSTOMER WHERE (YEAR(GETDATE())-YEAR(CUSTOMER.BIRTHDAY)) = 40 ORDER BY BIRTHDAY DESC Query the customer according to each order SELECT ORDERS.ORDER_ID, CUSTOMER.CUSTOMER_NAME, ORDERS.ORDER_DATE FROM ORDERS INNER JOIN CUSTOMER ON ORDERS.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID Query to get top best ordering products by order quantity SELECT PACKAGE.PACKAGE_NAME, SUM(ORDER_QUANTITY) AS 'TOP ORDER_QUANTITY' FROM DETAILS_ORDER JOIN PACKAGE ON DETAILS_ORDER.PACKAGE_ID = PACKAGE.PACKAGE_ID GROUP BY PACKAGE_NAME ORDER BY SUM(ORDER_QUANTITY) DESC LIMIT Query the customers who have not placed any orders SELECT CUSTOMER_ID, CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_ID NOT IN (SELECT DISTINCT CUSTOMER_ID FROM ORDERS)