PROJECT REPORT subject databases subject name market online management project

OVERVIEW OF THE TOPIC

WHY CHOOSING THIS SUBJECT?

The shift from traditional food purchasing methods to online platforms reflects the evolving digital economy, where consumers now seek detailed information about the origin and quality of agricultural products To meet this demand, businesses must enhance food quality, improve branding, and invest in effective communication channels A dedicated sales app emerges as a vital tool, enabling companies to reach the growing number of internet users in Vietnam—approximately 41 million, or 45% of the population—many of whom are young and eager to shop online With one-third of the population actively searching for products and services online, having a sales app is essential for attracting potential customers and staying competitive in a rapidly evolving market.

PROJECT OBJECTIVES

Enhance your sales efforts by allowing customers to shop anytime and anywhere, eliminating the need for them to visit your store during operating hours This flexibility not only maximizes sales opportunities but also increases customer satisfaction, making their shopping experience more convenient and enjoyable.

Implementing a sales app can significantly lower business expenses by minimizing costs associated with store rentals, warehousing, and staffing Furthermore, a sales app enhances customer convenience by facilitating seamless payment options, allowing customers to use ATM cards and various electronic payment methods.

A dedicated sales app enhances a business's reputation and brand value by serving as its online face The app's design, features, and information effectively communicate the company's image to customers, reinforcing brand value in the public's perception.

EXPECTED RESULTS

Design database for the application of ordering products through the app.

Build management and business processes for the app model.

PRACTICAL SIGNIFICANCE

The development of the software has significantly simplified shopping and cooking for housewives It includes features for customer management and food import/export management, enabling efficient food delivery to customers This comprehensive data monitoring allows managers to optimize usage and enhance decision-making processes effectively.

Understanding the process of building a database from idea creation, modeling, data collection, storage and operation on SQL.

Designing a software based on the available data.

Project is the basis that my group can continue to develop in the following subjects. download by : skknchat@gmail.com

INTRODUCTION

MODEL PARTICIPANT

GENERAL DESCRIPTION OF THE COMPANY

APP serves as a vital intermediary, facilitating the swift and convenient delivery of sellers' products to consumers through its website To ensure the efficient provision of clean food, APP is structured with key departments, including Sales and Marketing, Accounting, IT, and Warehouse Management.

GENERAL BUSINESS PROCESS

1 Import process download by : skknchat@gmail.com

Step 1 Plan and contract with supplier

Step 2 Check the goods and compare

Step 3 Make an inventory receipt

Step 4 Completion of warehousing and payment

Step 4 Delivery and processing of returned orders

MAIN FUNCTION OF THE BUSINESS MODEL

1 Import process download by : skknchat@gmail.com

- Plan and contract with supplier

Staff sends purchase request information to Seller including: real name, product, quantity to be purchased, delivery schedule. download by : skknchat@gmail.com

The import department communicates with the supplier to inform them of the plan and updates relevant parties before signing a purchase contract Once the contract is signed, the details are stored in the Contract database, where each contract is assigned a unique contract code for efficient management.

- Check the goods and compare

The warehouse department keeper relies on the purchase order or request form to compare inventory information from the Warehouse database and at the same time check their quality

When the goods inspection is completed, all paperwork and receipt request will be transferred to the accounting department to compare again before conducting the transaction and printing the receipt.

Information about the goods entering the warehouse and the receipt will be saved in the Warehouse database to manage imported products

- Completion of warehousing and payment

The department store keeper manages stock storage by reconciling it with sales data and updating the warehouse card accordingly Once payment is successfully processed to the supplier, the invoice details are recorded in the Corporate Purchase Invoice database.

2 Sales process download by : skknchat@gmail.com

Customers through the information provided about the product and choose to purchase through the application

To ensure successful order processing, essential customer order details such as order code, customer code, order date, and product code will be securely stored in the Order database for effective management For further inquiries, please contact: skknchat@gmail.com.

The system receives order information from the Order database, the company checks orders and generates inventory information to prepare goods and shipping Delivery Company.

Export slip information is stored in the Sales Voucher database

The accounting department transfers order information to issues invoices to customers

- Delivery and processing of returned orders

The company will check the information and issue the same slip with row for the Delivery company The company receives the goods and carries out the communication hang.

The delivery company receives and delivers the delivery The delivery company will confirm and send the bill of lading code and update the order status for the company.

MAIN FUNCTION OF APP

Showcasing detailed information and multiple images of the product allows customers to perceive its authenticity from various angles, thereby reflecting the overall quality of the company's offerings.

The product evaluation function provides customers with essential information to make informed purchasing decisions, while also enabling the company to effectively manage quality and streamline its operational processes.

Promoting special offers and discounts enables customers to discover the company's products, particularly new and standout items Additionally, these promotional activities enhance brand visibility and strengthen the company's reputation through positive consumer feedback.

An advanced product search and filtering function enhances the user experience by allowing customers to easily narrow down their options from a large inventory, saving them time and reducing frustration This feature fosters trust and appreciation among users, as they can quickly find the products they desire without sifting through irrelevant items.

Enhance customer shopping experiences by showcasing related products, which encourages additional purchases An essential feature of any e-commerce app is the ability to display supplementary items, recommended products, and items frequently bought by other customers, thereby increasing overall sales potential.

Service experience: provides customers the option to add products to cart, create a list of favorite products.

The app offers high-quality, fresh ingredients along with standard recipes that enhance family meals, making them more delicious and enjoyable In addition to traditional Vietnamese dishes, it regularly updates recipes from renowned chefs, providing customers with a diverse and rich menu to choose from Download now at skknchat@gmail.com.

ENTITY RELATIONSHIP DIAGRAM

IDENTIFIES ENTITIES AND ATTRIBUTES

Entity Attribute Key Multivalued Composite Required attribute Attribute Attribute attribute

Supplier SupplierID SupplierI Address Address SupplierID

Employee EmployeeID Employee Position Address EmployeeID

EmployeeOf EmployeeOffID Employee EmployeeOffID f ManagerID OffID ManagerID

DepartmentName ntID DepartmentName download by : skknchat@gmail.com

Contract ContractID ContractI QualityProdu ContractID

WarehouseL WarehouseID Warehous Location InventoryID ist InventoryName eID InventoryName

InventoryLis WarehouseID ProductI WarehoueID t ProductID D ProductID

Customer CustomerID Customer Address Address CustomerID

CustomerNameA ID CustomerName ddress Address

Delivery CompanyID Company Address CompanyID

RELATIONSHIP OF ENTITIES

Description download by : skknchat@gmail.com

R1 Supplier – Contract One supplier has zero or more

One contract has only 1 supplier

R2 Contract – ContractDetail A contract has one or more

A contract detail belongs to only one contract.

R3 Supplier – GoodsReceiptNote A supplier has one or more goods

A goods received note has only one supplier.

R4 GoodsReceiptNote – Goods A goods received notes has one or

Receipt Note Detail 1-n more goods received notes details.

A goods received notes detail belongs only to one goods received note.

R5 Department – Employee A department has one or more

An employee is part of only one department.

R6 Manager – EmployeeOnl One manager manages one or

An online employee is managed by one manager.

R7 Manager – EmployeeOff One manager manages one or

An offline employee is managed by one manager.

R8 EmployeeOff – GoodsReceipt An employee creates zero or more

A goods received note is generated by one employee.

R9 WarehouseList – Goods A warehouse list has one or more

ReceiptNoteDetail 1-n goods received note details.

A goods received note detail belongs only to one warehouse list.

R10 ContractDetail – Product A contract detail has one or more

A product belongs only to one contract detail.

R11 InventoryList – Warehouse List An inventory list has one or more

A warehouse list belongs to only one inventory list.

R12 InventoryList – Product 1-n An inventory list has zero or more products.

A product belongs to only one inventory list.

R13 Customer – Order A customer has zero or more

An order belongs to only one customer.

R14 Order – OrderDetail An order has one or more order

An order detail is part of a single order.

R15 Order - Invoice One order has one invoice.

1-1 A invoice is part of an order.

R16 Invoice – InvoiceDetail A invoice has one or more invoice

A invoice detail belongs to only one invoice.

R17 InvoiceDetail – Goods A invoice detail with zero or one

A goods delivery detail belongs to only one invoice detail.

R18 GoodsDeliveryNote – Goods A goods delivery note has one or

DeliveryNoteDetail 1-n more goods delivery note details.

A goods delivery note detail only belongs to one goods delivery note.

R19 DeliveryCompany – Goods A delivery company has one or

DeliveryNote 1-n more goods delivery notes.

A goods delivery note belongs to only one company delivery.

R20 DeliveryCompany – Delivery A delivery company has one or

A delivery status belong to delivery company.

R21 Customer – Invoice A customer has zero or more

A invoice belongs to only one customer.

R22 GoodsDeliveryNote – Returned A goods delivery note has zero or

GoodsReceivedNote 1-1 one returned goods received note.

A returned goods received note belongs to only one goods delivery note.

R23 ReturnedGoods ReceivedNote – A returned goods received note

ReturnedGoodsReceived has one or more returned goods

A returned goods received note detail belongs to only one returned goods received note.

R24 InventoryList – Returned A inventory list has one or more

GoodsReceivedNoteDetail 1-n returned goods received note details.

A returned goods received note detail belongs to only one inventory list.

ENTITY RELATIONSHIP DIAGRAM (ERD)

download by : skknchat@gmail.com download by : skknchat@gmail.com

LOGICAL DATABASE MODEL

download by : skknchat@gmail.com

PHYSICAL DATABASE MODEL

DATA TYPES

Attribute Key Data type Length Allow null

LastName nvarchar 10 download by : skknchat@gmail.com

Attribute Key Data type Length Allow null download by : skknchat@gmail.com

Value numeric 10, 2 download by : skknchat@gmail.com

InventoryNam nvarchar 50 X e download by : skknchat@gmail.com

ReceiptNoteID PK char 8 download by : skknchat@gmail.com

Inventory ID char 3 download by : skknchat@gmail.com

Attribute Key Data type Length Allow null download by : skknchat@gmail.com

Order_ID PK, FK char 8

Table 15: GoodsDeliveryNoteDetail download by : skknchat@gmail.com

CustomerID char 8 download by : skknchat@gmail.com

Table 19: DeliveryStatus download by : skknchat@gmail.com

NoteID PK, FK char 8 download by : skknchat@gmail.com

DATABASE CREATION STATEMENT

FROM SYS.SYSREFERENCES R JOIN SYS.SYSOBJECTS O ON (O.ID R.CONSTID AND O.TYPE = 'F')

WHERE R.FKEYID = OBJECT_ID('CONTRACT') AND O.NAME 'FK_CONTRACT_R1_SUPPLIER')

DROP CONSTRAINT FK_CONTRACT_R1_SUPPLIER

GO download by : skknchat@gmail.com

FROM SYS.SYSREFERENCES R JOIN SYS.SYSOBJECTS O ON (O.ID

WHERE R.FKEYID = OBJECT_ID('CONTRACT_DETAIL') AND O.NAME 'FK_CONTRACT_DETAIL_R2_CONTRACT')

DROP CONSTRAINT FK_CONTRACT_DETAIL_R2_CONTRACT

WHERE R.FKEYID = OBJECT_ID('GOODS_RECEIPT_NOTE') AND O.NAME 'FK_GOODS_RECEIPT_NOTE_R3_SUPPLIER')

ALTER TABLE GOODS_RECEIPT_NOTE

DROP CONSTRAINT FK_GOODS_RECEIPT_NOTE_R3_SUPPLIER

WHERE R.FKEYID = OBJECT_ID('GOODS_RECEIPT_NOTE') AND O.NAME 'FK_GOODS_RECEIPT_NOTE_R4_GOODS_RECEIPT_NOTE_DETAIL')

ALTER TABLE GOODS_RECEIPT_NOTE download by : skknchat@gmail.com

FK_GOODS_RECEIPT_NOTE_R4_GOODS_RECEIPT_NOTE_DETAIL GO

WHERE R.FKEYID = OBJECT_ID('EMPLOYEE') AND O.NAME 'FK_EMPLOYEE_R5_DEPARTMENT')

DROP CONSTRAINT FK_EMPLOYEE_R5_DEPARTMENT

WHERE R.FKEYID = OBJECT_ID('EMPLOYEE_OFF') AND O.NAME 'FK_EMPLOYEE_OFF_INHERITAN_EMPLOYEE')

DROP CONSTRAINT FK_EMPLOYEE_OFF_INHERITAN_EMPLOYEE GO

= R.CONSTID AND O.TYPE = 'F') download by : skknchat@gmail.com

WHERE R.FKEYID = OBJECT_ID('EMPLOYEE_ON') AND O.NAME

= 'FK_EMPLOYEE_ON_INHERITAN_EMPLOYEE')

DROP CONSTRAINT FK_EMPLOYEE_ON_INHERITAN_EMPLOYEE GO

WHERE R.FKEYID = OBJECT_ID('MANAGER') AND O.NAME 'FK_MANAGER_INHERITAN_EMPLOYEE')

DROP CONSTRAINT FK_MANAGER_INHERITAN_EMPLOYEE GO

WHERE R.FKEYID = OBJECT_ID('EMPLOYEE_ON') AND O.NAME

DROP CONSTRAINT FK_EMPLOYEE_ON_R6_MANAGER

WHERE R.FKEYID = OBJECT_ID('EMPLOYEE_OFF') AND O.NAME

DROP CONSTRAINT FK_EMPLOYEE_OFF_R7_MANAGER

WHERE R.FKEYID = OBJECT_ID('GOODS_RECEIPT_NOTE') AND O.NAME 'FK_GOODS_RECEIPT_NOTE_R8_EMPLOYEE_OFF')

DROP CONSTRAINT FK_GOODS_RECEIPT_NOTE_R8_EMPLOYEE_OFF GO

WHERE R.FKEYID = OBJECT_ID('GOODS_RECEIPT_NOTE_DETAIL') AND O.NAME = 'FK_GOODS_RECEIPT_NOTE_DETAIL_R9_WAREHOUSE_LIST') ALTER TABLE GOODS_RECEIPT_NOTE_DETAIL download by : skknchat@gmail.com

FK_GOODS_RECEIPT_NOTE_DETAIL_R9_WAREHOUSE_LIST

WHERE R.FKEYID = OBJECT_ID('PRODUCT') AND O.NAME

DROP CONSTRAINT FK_PRODUCT_R10_CONTRACT_DETAIL

WHERE R.FKEYID = OBJECT_ID('INVENTORY_LIST') AND O.NAME = 'FK_ INVENTORY_LIST_R11_WAREHOUSE_LIST')

DROP CONSTRAINT FK_ INVENTORY_LIST _R11_WAREHOUSE_LIST GO

IF EXISTS (SELECT 1 download by : skknchat@gmail.com

WHERE R.FKEYID = OBJECT_ID('PRODUCT') AND O.NAME

DROP CONSTRAINT FK_PRODUCT_R12_INVENTORY_LIST

WHERE R.FKEYID = OBJECT_ID('ORDER') AND O.NAME

DROP CONSTRAINT FK_ORDER_R13_CUSTOMER

WHERE R.FKEYID = OBJECT_ID('ORDER_DETAIL') AND O.NAME 'FK_ORDER_DETAIL_R14_ORDER')

DROP CONSTRAINT FK_ORDER_DETAIL_R14_ORDER

WHERE R.FKEYID = OBJECT_ID('ORDER') AND O.NAME

DROP CONSTRAINT FK_ORDER_R15_INVOICE

WHERE R.FKEYID = OBJECT_ID('INVOICE_DETAIL') AND O.NAME

DROP CONSTRAINT FK_INVOICE_DETAIL_R16_INVOICE

WHERE R.FKEYID = OBJECT_ID('GOODS_DELIVERY_NOTE_DETAIL') AND O.NAME = 'FK_GOODS_DELIVERY_NOTE_DETAIL_R17_INVOICE_DETAIL') ALTER TABLE GOODS_DELIVERY_NOTE_DETAIL download by : skknchat@gmail.com

FK_GOODS_DELIVERY_NOTE_DETAIL_R17_INVOICE_DETAIL

WHERE R.FKEYID = OBJECT_ID('GOODS_DELIVERY_NOTE_DETAIL') AND O.NAME 'FK_GOODS_DELIVERY_NOTE_DETAIL_R18_GOODS_DELIVERY_NOTE') ALTER TABLE GOODS_DELIVERY_NOTE_DETAIL

FK_GOODS_DELIVERY_NOTE_DETAIL_R18_GOODS_DELIVERY_NOTE GO

WHERE R.FKEYID = OBJECT_ID('GOODS_DELIVERY_NOTE') AND O.NAME

= 'FK_GOODS_DELIVERY_NOTE_R19_DELIVERY_COMPANY')

ALTER TABLE GOODS_DELIVERY_NOTE

FK_GOODS_DELIVERY_NOTE_R19_DELIVERY_COMPANY

WHERE R.FKEYID = OBJECT_ID('DELIVERY_STATUS') AND O.NAME

= 'FK_DELIVERY_STATUS_R20_DELIVERY_COMPANY')

DROP CONSTRAINT FK_DELIVERY_STATUS_R20_DELIVERY_COMPANY GO

WHERE R.FKEYID = OBJECT_ID('INVOICE') AND O.NAME 'FK_INVOICE_R21_CUSTOMER')

DROP CONSTRAINT FK_INVOICE_R21_CUSTOMER

WHERE R.FKEYID = OBJECT_ID('RETURNED_GOODS_RECEIPT_NOTE') AND O.NAME 'FK_RETURNED_GOODS_RECEIPT_NOTE_R22_GOODS_DELIVERY_NOTE') ALTER TABLE RETURNED_GOODS_RECEIPT_NOTE download by : skknchat@gmail.com

FK_RETURNED_GOODS_RECEIPT_NOTE_R22_GOODS_DELIVERY_NOTE GO

WHERE R.FKEYID OBJECT_ID('RETURNED_GOODS_RECEIPT_NOTE_DETAIL') AND O.NAME

'FK_RETURNED_GOODS_RECEIPT_NOTE_DETAIL_R23_RETURNED_GOO

ALTER TABLE RETURNED_GOODS_RECEIPT_NOTE_DETAIL

FK_RETURNED_GOODS_RECEIPT_NOTE_DETAIL_R23_RETURNED_GOOD S_RECEIPT_NOTE

WHERE R.FKEYID OBJECT_ID('RETURNED_GOODS_RECEIPT_NOTE_DETAIL') AND O.NAME

'FK_RETURNED_GOODS_RECEIPT_NOTE_DETAIL_R24_INVENTORY_LIST') ALTER TABLE RETURNED_GOODS_RECEIPT_NOTE_DETAIL download by : skknchat@gmail.com

FK_RETURNED_GOODS_RECEIPT_NOTE_DETAIL_R24_INVENTORY_LIS

WHERE ID = OBJECT_ID('SUPPLIER')

WHERE ID = OBJECT_ID('EMPLOYEE')

WHERE ID = OBJECT_ID('EMPLOYEE') download by : skknchat@gmail.com

WHERE ID = OBJECT_ID('EMPLOYEE_ONL')

DROP INDEX EMPLOYEE_ONL R6_FK

WHERE ID = OBJECT_ID('EMPLOYEE_ONL')

WHERE ID = OBJECT_ID('EMPLOYEE_OFF') download by : skknchat@gmail.com

DROP INDEX EMPLOYEE_OFF.R7_FK

WHERE ID = OBJECT_ID('EMPLOYEE_OFF')

WHERE ID = OBJECT_ID('MANAGER')

DROP INDEX MANAGER.INHERITANCE_1_FK

WHERE ID = OBJECT_ID('MANAGER')

WHERE ID = OBJECT_ID('DEPARTMENT')

WHERE ID = OBJECT_ID('CONTRACT')

WHERE ID = OBJECT_ID('CONTRACT_DETAIL')

DROP INDEX CONTRACT_DETAIL.R2_FK

WHERE ID = OBJECT_ID('CONTRACT_DETAIL')

WHERE ID = OBJECT_ID('WAREHOUSE_LIST')

DROP INDEX WAREHOUSE_LIST.R11_FK

WHERE ID = OBJECT_ID('WAREHOUSE_LIST')

WHERE ID = OBJECT_ID('INVENTORY_LIST')

WHERE ID = OBJECT_ID('PRODUCT')

WHERE ID = OBJECT_ID('GOODS_RECEIPT_NOTE') AND NAME = 'R3_FK'

DROP INDEX GOODS_RECEIPT_NOTE.R3_FK

WHERE ID = OBJECT_ID('GOODS_RECEIPT_NOTE')

DROP TABLE GOODS_RECEIPT_NOTE

WHERE ID = OBJECT_ID('GOODS_RECEIPT_NOTE_DETAIL') AND NAME = 'R4_FK'

DROP INDEX GOODS_RECEIPT_NOTE_DETAIL.R4_FK

WHERE ID = OBJECT_ID('GOODS_RECEIPT_NOTE_DETAIL') AND TYPE = 'U')

DROP TABLE GOODS_RECEIPT_NOTE_DETAIL

WHERE ID = OBJECT_ID('CUSTOMER')

WHERE ID = OBJECT_ID('ORDER')

WHERE ID = OBJECT_ID('ORDER_DETAIL')

DROP INDEX ORDER_DETAIL.R14_FK

WHERE ID = OBJECT_ID('ORDER_DETAIL')

WHERE ID = OBJECT_ID('GOODS_DELIVERY_NOTE') AND NAME = 'R22_FK'

AND INDID < 255) download by : skknchat@gmail.com

DROP INDEX GOODS_DELIVERY_NOTE.R22_FK

WHERE ID = OBJECT_ID('GOODS_DELIVERY_NOTE')

DROP TABLE GOODS_DELIVERY_NOTE

WHERE ID = OBJECT_ID('GOODS_DELIVERY_NOTE_DETAIL') AND NAME = 'R18_FK'

DROP INDEX GOODS_DELIVERY_NOTE_DETAIL.R18_FK GO

WHERE ID = OBJECT_ID('GOODS_DELIVERY_NOTE_DETAIL') AND TYPE = 'U') download by : skknchat@gmail.com

DROP TABLE GOODS_DELIVERY_NOTE_DETAIL

WHERE ID = OBJECT_ID('INVOICE_DETAIL')

DROP INDEX INVOICE_DETAIL.R16_FK

WHERE ID = OBJECT_ID('INVOICE')

FROM SYSOBJECTS download by : skknchat@gmail.com

WHERE ID = OBJECT_ID('INVOICE')

WHERE ID = OBJECT_ID('INVOICE_DETAIL')

WHERE ID = OBJECT_ID('DELIVERY_COMPANY')

WHERE ID = OBJECT_ID('DELIVERY_STATUS')

AND NAME = 'R20_FK' download by : skknchat@gmail.com

DROP INDEX DELIVERY_STATUS.R20_FK

WHERE ID = OBJECT_ID('DELIVERY_STATUS')

WHERE ID = OBJECT_ID('RETURNED_GOODS_RECEIPT_NOTE') AND NAME = 'R23_FK'

DROP INDEX RETURNED_GOODS_RECEIPT_NOTE.R23_FK GO

FROM SYSOBJECTS download by : skknchat@gmail.com

WHERE ID = OBJECT_ID('RETURNED_GOODS_RECEIPT_NOTE') AND TYPE = 'U')

DROP TABLE RETURNED_GOODS_RECEIPT_NOTE

WHERE ID OBJECT_ID('RETURNED_GOODS_RECEIPT_NOTE_DETAIL')

DROP INDEX RETURNED_GOODS_RECEIPT_NOTE_DETAIL.R24_FK GO

WHERE ID OBJECT_ID('RETURNED_GOODS_RECEIPT_NOTE_DETAIL')

DROP TABLE RETURNED_GOODS_RECEIPT_NOTE_DETAIL GO download by : skknchat@gmail.com

SUPPLIER_ID CHAR(8) NOT NULL,

SUPPLIER_NAME VARCHAR(50) NOT NULL,

PHONE VARCHAR(11) NOT NULL, EMAIL

CONSTRAINT PK_SUPPLIER PRIMARY KEY (SUPPLIER_ID) )

MANAGER_ID CHAR(8) NOT NULL,

EMPLOYEE_ID CHAR(8) NOT NULL,

DEPARTMENT_ID CHAR(3) NOT NULL,

NULL, ADDRESS VARCHAR(50) NOT NULL,

PHONE VARCHAR(11) NOT NULL, EMAIL

HIRE_DATE DATETIME NOT NULL,

CONSTRAINT PK_MANAGERPRIMARY KEY (MANAGER_ID) ) download by : skknchat@gmail.com

CREATE NONCLUSTERED INDEX INHERITANCE_1_FK ON MANAGER(EMPLOYEE_ID ASC)

LAST_NAME VARCHAR(30) NOT NULL,

FIRST_NAME VARCHAR(10) NOT NULL,

CONSTRAINT PK_EMPLOYEE PRIMARY KEY (EMPLOYEE_ID) )

CREATE NONCLUSTERED INDEX R5_FK ON EMPLOYEE

(ATOTAL_AMOUNTRIBUTE_14 ASC) download by : skknchat@gmail.com

EMPLOYEE_OFF_ID CHAR(8) NOT NULL,

CONSTRAINT PK_EMPLOYEE_OFFLINE PRIMARY

CREATE NONCLUSTERED INDEX R6_FK ON EMPLOYEE_OFFLINE (MANAGER_ID ASC)

EMPLOYEE_ONL_ID CHAR(8) NOT NULL,

CONSTRAINT PK_EMPLOYEE_ONLINE PRIMARY

CREATE NONCLUSTERED INDEX R7_FK ON

EMPLOYEE_ONL (MANAGER_ID ASC)

ATOTAL_AMOUNTRIBUTE_14 CHAR(3) NOT NULL,

DEPARTMENT_NAME VARCHAR(50) NOT NULL,

CONSTRAINT PK_DEPARTMENTPRIMARY KEY (DEPARTMENT_ID) )

CONTRACT_ID CHAR(8) NOT NULL,

NULL, SUPPLIER_ID CHAR(8) NOT NULL,

CONSTRAINT PK_CONTRACT PRIMARY KEY

CREATE NONCLUSTERED INDEX R1_FK ON CONTRACT

CONTRACT_TIME DATETIME NOT NULL,

LINE_NUMBER INT NOT NULL,

PRODUCT_ID CHAR(8) NOT NULL,

UNIT_PRICE NUMERIC(10,2) NOT NULL,

TOTAL_AMOUNT NUMERIC(10,2) NOT NULL,

CONSTRAINT PK_CONTRACT_DETAIL PRIMARY KEY (CONTRACT_TIME, CONTRACT_ID, LINE_NUMBER)

CREATE NONCLUSTERED INDEX R2_FK ON CONTRACT_DETAIL

(CONTRACT_TIME ASC, CONTRACT_ID ASC)

WAREHOUSE_ID CHAR(3) NOT NULL,

WAREHOUSE_NAME VARCHAR(50) NOT NULL,

PRODUCT_ID CHAR(8) NULL, download by : skknchat@gmail.com

CONSTRAINT PK_WAREHOUSE_LIST PRIMARY KEY (WAREHOUSE_ID) )

CREATE NONCLUSTERED INDEX R11_FK ON WAREHOUSE_LIST (PRODUCT_ID ASC)

WAREHOUSE_ID CHAR(3) NOT NULL,

CONSTRAINT PK_INVENTORY_LIST PRIMARY KEY (PRODUCT_ID) )

PRODUCT_NAME VARCHAR(50) NOT NULL,

CONSTRAINT PK_PRODUCT PRIMARY KEY (PRODUCT_ID) download by : skknchat@gmail.com

CREATE NONCLUSTERED INDEX R10_FK ON PRODUCT (CONTRACT_TIME ASC,

CONTRACT_ID ASC, LINE_NUMBER ASC)

CREATE TABLE GOODS_RECEIPT_NOTE (

RECEIPT_NOTE_ID CHAR(8) NOT NULL,

RECEIPT_TIME DATETIME NOT NULL,

SUPPLIER_ID CHAR(8) NOT NULL,

EMPLOYEE_OFF_ID CHAR(8) NOT NULL,

CONSTRAINT PK_ GOODS_RECEIPT_NOTE PRIMARY

KEY (RECEIPT_TIME, RECEIPT_NOTE_ID)

CREATE NONCLUSTERED INDEX R3_FK ON GOODS_RECEIPT_NOTE

CREATE TABLE GOODS_RECEIPT_NOTE _DETAIL (

RECEIPT_NOTE_ID CHAR(8) NOT NULL, download by : skknchat@gmail.com

RECEIPT_TIME DATETIME NOT NULL,

CONSTRAINT PK_ GOODS_RECEIPT_NOTE _DETAIL PRIMARY KEY (RECEIPT_TIME, RECEIPT_ID, LINE_NUMBER)

CREATE NONCLUSTERED INDEX R4_FK ON GOODS_RECEIPT_NOTE _DETAIL (TGLP ASC,

CUSTOMER_ID CHAR(8) NOT NULL,

NULL, ADDRESS VARCHAR(50) NOT NULL,

PHONE VARCHAR(11) NOT NULL, download by : skknchat@gmail.com

CONSTRAINT PK_CUSTOMER PRIMARY KEY (CUSTOMER_ID)

ORDER_TIME DATETIME NOT NULL,

CONSTRAINT PK_ORDER PRIMARY KEY (ORDER_TIME, ORDER_ID)

CREATE NONCLUSTERED INDEX R13_FK ON ORDER (CUSTOMER_ID ASC) GO

ORDER_TIME DATETIME NOT NULL,

PRODUCT_ID CHAR(8) NOT NULL, download by : skknchat@gmail.com

CONSTRAINT PK_ORDER_DETAIL PRIMARY KEY (ORDER_TIME, ORDER_ID, LINE_NUMBER)

CREATE NONCLUSTERED INDEX R14_FK ON ORDER_DETAIL (TGLP ASC, ORDER_ID ASC)

CREATE TABLE GOODS_DELIVERY_NOTE (

DELIVERY_NOTE_ID CHAR(8) NOT NULL,

NULL, ORDER_ID CHAR(8) NOT NULL,

DELIVERY_COMPANY_ID CHAR(8) NOT NULL,

CONSTRAINT PK_GOODS_DELIVERY_NOTE PRIMARY KEY

(DELIVERY_TIME, DELIVERY_NOTE_ID)

GOODS_DELIVERY_NOTE (DELIVERY_COMPANY_ID ASC)

CREATE TABLE GOODS_DELIVERY_NOTE_DETAIL (

DELIVERY_TIME DATETIME NOT NULL,

CONSTRAINT PK_GOODS_DELIVERY_NOTE_DETAIL PRIMARY

KEY (DELIVERY_NOTE_ID, DELIVERY_TIME, LINE_NUMBER)

GOODS_DELIVERY_NOTE_DETAIL (DELIVERY_TIME

ASC, DELIVERY_NOTE_ID ASC, LINE_NUMBER ASC)

INVOICE_ID CHAR(8) NOT NULL,

INVOICE_TIME DATETIME NOT NULL,

CONSTRAINT PK_INVOICE PRIMARY KEY (INVOICE_TIME, INVOICE_ID) ) download by : skknchat@gmail.com

CREATE NONCLUSTERED INDEX R16_FK ON INVOICE (INVOICE_TIME ASC,

INVOICE_ID CHAR(8) NOT NULL,

INVOICE_TIME DATETIME NOT NULL,

LINE_NUMBER INT NOT NULL, PRODUCT_ID

NUMERIC(7,2) NOT NULL, UNIT_PRICE

NUMERIC(10,2) NOT NULL, TOTAL_AMOUNT

CONSTRAINT PK_INVOICE_DETAIL PRIMARY KEY (INVOICE_TIME, INVOICE_ID, LINE_NUMBER)

INVOICE_DETAIL (INVOICE_TIME ASC, INVOICE_ID ASC)

DELIVERY_COMPANY_NAME VARCHAR(50) NOT NULL,

CONSTRAINT PK_DELIVERY_COMPANY PRIMARY KEY

DELIVERY_ID CHAR(8) NOT NULL,

DELIVERY_TIME DATETIME NOT NULL,

CONSTRAINT PK_DELIVERY_STATUS PRIMARY KEY (DELIVERY_ID, DELIVERY_TIME)

CREATE NONCLUSTERED INDEX R22_FK ON DELIVERY_STATUS (DELIVERY_COMPANY_ID ASC)

CREATE TABLE RETURNED_GOODS_DELIVERY_NOTE (

RETURN_NOTE_ID CHAR(6) NOT NULL,

RETURN_TIME DATETIME NOT NULL,

CONSTRAINT PK_RETURNED_GOODS_DELIVERY_NOTE PRIMARY KEY (RETURN_TIME, RETURN_NOTE_ID)

RETURNED_GOODS_DELIVERY_NOTE (RETURN_TIME ASC,

CREATE TABLE RETURNED_GOODS_DELIVERY_NOTE_DETAIL

( RETURN_NOTE_ID CHAR(6) NOT NULL,

RETURN_TIME DATETIME NOT NULL,

DELIVERY_NOTE_ID CHAR(8) NOT

NULL, PRODUCT_ID CHAR(8) NOT NULL,

UNIT_PRICE NUMERIC(10,2) NOT NULL, download by : skknchat@gmail.com

CONSTRAINT PK_RETURNED_GOODS_DELIVERY_NOTE_DETAIL PRIMARY KEY (RETURN_TIME, RETURN_NOTE_ID, LINE_NUMBER) )

RETURNED_GOODS_DELIVERY_NOTE_DETAIL (RETURN_TIME ASC, RETURN_NOTE_ID ASC, DELIVERY_NOTE_ID ASC)

ADD CONSTRAINT FK_CONTRACT_R1_ SUPPLIER FOREIGN KEY (SUPPLIER_ID)

ADD CONSTRAINT FK_CONTRACT_DETAIL_R2_CONTRACT FOREIGN KEY (CONTRACT_TIME, CONTRACT_ID)

REFERENCES CONTRACT (CONTRACT_TIME, CONTRACT_ID, LINE_NUMBER)

ADD CONSTRAINT FK_ GOODS_ RECEIPT_NOTE_R3_SUPPLIER

ALTER TABLE GOODS_ RECEIPT_NOTE_DETAIL

ADD CONSTRAINT FK_ GOODS_ RECEIPT_NOTE_DETAIL _R4_ GOODS_ RECEIPT _NOTE FOREIGN KEY (RECEIPT_TIME, RECEIPT_NOTE_ID)

REFERENCES GOODS_ RECEIP _NOTE (RECEIPT_TIME,

RECEIPT_NOTE_ID, LINE_NUMBER)

ADD CONSTRAINT FK_ EMPLOYEE _R5_ DEPARTMENT FOREIGN KEY (ATTRIBUTE_14)

ADD CONSTRAINT FK_ MANAGER_INHERITAN_ EMPLOYEE

REFERENCES EMPLOYEE (EMPLOYEE_ID) download by : skknchat@gmail.com

ADD CONSTRAINT FK_EMPLOYEE_ONL_INHERITAN_

EMPLOYEE FOREIGN KEY (EMPLOYEE_ID)

ADD CONSTRAINT FK_EMPLOYEE_ONL_R6_MANAGER FOREIGN KEY (MANAGER_ID)

ADD CONSTRAINT FK_EMPLOYEE_OFF_INHERITAN_

EMPLOYEE FOREIGN KEY (EMPLOYEE_ID)

ADD CONSTRAINT FK_EMPLOYEE_OFF_R7_MANAGER FOREIGN KEY (MANAGER_ID)

REFERENCES MANAGER(MANAGER_ID) download by : skknchat@gmail.com

ALTER TABLE GOODS_DELIVERY_NOTE

ADD CONSTRAINT FK_ GOODS_DELIVERY_NOTE _R8_EMPLOYEE_OFF FOREIGN KEY (EMPLOYEE_OFF_ID)

REFERENCES EMPLOYEE_OFF(EMPLOYEE_OFF_ID)

ALTER TABLE GOODS_RECEIPT_NOTE_DETAIL

ADD CONSTRAINT FK_ GOODS_ RECEIPT_NOTE_DETAIL _R9_

WAREHOUSE_ID FOREIGN KEY (WAREHOUSE_ID)

REFERENCES WAREHOUSE_LIST (WAREHOUSE_ID)

ADD CONSTRAINT FK_PRODUCT_R10_CONTRACT_DETAIL FOREIGN KEY (CONTRACT_TIME, CONTRACT_ID, LINE_NUMBER)

REFERENCES CONTRACT_DETAIL (CONTRACT_TIME,

ADD CONSTRAINT FK_ WAREHOUSE_LIST _R11_ INVENTORY_LIST

FOREIGN KEY (PRODUCT_ID) download by : skknchat@gmail.com

REFERENCES INVENTORY_LIST (PRODUCT_ID)

ADD CONSTRAINT FK_PRODUCT_R12_INVENTORY_LIST FOREIGN KEY (PRODUCT_ID)

ADD CONSTRAINT FK_ORDER_R13_CUSTOMER FOREIGN

ADD CONSTRAINT FK_ORDER_DETAIL_R14_ORDER FOREIGN

KEY (ORDER_TIME, ORDER_ID)

REFERENCES ORDER (ORDER_TIME, ORDER_ID)

ADD CONSTRAINT FK_ORDER_R15_INVOICE FOREIGN KEY

(ORDER_TIME, ORDER_ID) download by : skknchat@gmail.com

REFERENCES INVOICE (INVOICE_TIME, INVOICE_ID)

ADD CONSTRAINT FK_INVOICE_DETAIL_R16_INVOICE FOREIGN KEY (INVOICE_TIME, INVOICE_ID)

REFERENCES INVOICE (INVOICE_TIME, INVOICE_ID)

ALTER TABLE GOODS_DELIVERY_NOTE_DETAIL

ADD CONSTRAINT FK_CT_PHIEU_R17_CT_HOA_D FOREIGN

KEY (DELIVERY_TIME, DELIVERY_ID, LINE_NUMBER)

REFERENCES INVOICE_DETAIL (INVOICE_TIME, INVOICE_ID,

ALTER TABLE GOODS_DELIVERY_NOTE_DETAIL

ADD CONSTRAINT FK_CT_PHIEU_R18_PHIEU_XU FOREIGN

KEY (DELIVERY_TIME, DELIVERY_NOTE_ID)

(DELIVERY_TIME, DELIVERY_NOTE_ID)

ALTER TABLE GOODS_DELIVERY_NOTE download by : skknchat@gmail.com

ADD CONSTRAINT FK_GOODS_DELIVERY_NOTE_R19_

DELIVERY_COMPANY FOREIGN KEY (DELIVERY_COMPANY _ID)

REFERENCES DELIVERY_COMPANY (DELIVERY_COMPANY_ID) GO

ADD CONSTRAINT FK_ DELIVERY_STATUS_R20_

DELIVERY_COMPANY FOREIGN KEY (DELIVERY_COMPANY_ID) REFERENCES DELIVERY_COMPANY (DELIVERY_COMPANY_ID) GO

ADD CONSTRAINT FK_INVOICE_R21_CUSTOMER FOREIGN KEY (CUSTOMER_ID)

ALTER TABLE RETURNED_GOODS_DELIVERY_NOTE

ADD CONSTRAINT FK_RETURNED_GOODS_DELIVERY_NOTE _R22_GOODS_DELIVERY_NOTE FOREIGN KEY (RETURN_TIME, RETURN_ID, DELIVERY_ID)

REFERENCES GOODS_DELIVERY_NOTE (DELIVERY_TIME,

ALTER TABLE RETURNED_GOODS_DELIVERY_NOTE_DETAIL

FK_RETURNED_GOODS_DELIVERY_NOTE_DETAIL_R23_

RETURNED_GOODS_DELIVERY_NOTE FOREIGN KEY

(RETURN_TIME, RETURN_NOTE_ID, DELIVERY_NOTE_ID)

REFERENCES RETURNED_GOODS_DELIVERY_NOTE

(RETURN_TIME, RETURN_NOTE_ID, LINE_NUMBER)

ALTER TABLE RETURNED_GOODS_DELIVERY_NOTE_DETAIL

ADD CONSTRAINT FK_ RETURNED_GOODS_DELIVERY_NOTE_DETAIL _R24_ INVENTORY_LIST FOREIGN KEY (PRODUCT_ID)

ADD CONSTRAINT FK_NHANVIEN_MAPB

ADD CONSTRAINT FK_HOPDONG_MANCC

REFERENCES NHACUNGCAP (MANCC) download by : skknchat@gmail.com

ADD CONSTRAINT FK_PHIEUNHAP_MANCC

REFERENCES NHACUNGCAP (MANCC) download by : skknchat@gmail.com

SQL QUERY

Q1: Display information of customers whose first letter is the letter a.

SELECT FIRST_NAME, LAST_NAME

Q2: Display customer information, invoice code, order number of customers who buy the product "Fried Combo 1".

SELECT cus.CUSTOMER_ID, cus.CUSTOMER_NAME, cus.ADDRESS, cus.PHO

NE, inv.INVOICE_ID, invd.INVOICE_ID, pro.PRODUCT_NAME

ON cus.MaKH=hod.MaKH

ON inv.INVOICE_TIME=invd.INVOICE_TIME and inv.INVOICE_ID =invd.INVOICE_ID

ON invd.PRODUCT_ID=pro PRODUCT_ID

WHERE PRODUCT_NAME like N'% Fried Combo 1%'

Q3: Display customer code with orders in January 2021.

SELECT cus.CUSTOMER_ID,ord.ORDER_TIME

ON cus.CUSTOMER_ID=ord.CUSTOMER_ID download by : skknchat@gmail.com

Q4: Display products with more than 2 times the number of returns.

SELECT PRODUCT_ID, PRODUCT_NAME, (SELECT

RETURNED_GOODS_DELIVERY_NOTE_DETAIL redd

WHERE pro.PRODUCT_ID = redd.PRODUCT_ID) AS ' Number of returns' FROM PRODUCT pro

Q5: Display information of the department have more than 8 employees.

SELECT emp.department_id, dep.department_name, COUNT(*) AS

"Employees" FROM employee as emp

ON dep.department_id=emp.department_id

GROUP BY emp.department_id, dep.department_id

Q6: Display information of employee who has the highest salary for each department.

SELECT emp.department_id , emp.employee_id, MAX(emp.Salary) AS

ON dep.department_id = emp.department_id

GROUP BY emp.department_id , emp.employee_id

Q7: Display product_id and product_name which are not sold in March, 2021.

SELECT DISTINCT product_id, product_name download by : skknchat@gmail.com

FROM product, goods_delivery_note

WHERE product_is NOT IN (SELECT DISTINCT a.product_id

FROM goods_delivery_note_detail as a

JOIN goods_delivery_note as b

WHERE b.date LIKE '2021-07' or b.date> '2021-07' and b.date (SELECT AVG(a.SALARY) download by : skknchat@gmail.com

FROM EMPLOYEES a) order by e.SALARY desc

Q11: Minimum number of sales of each type of product in all sales.

SELECT od.PRODUCT_ID,pro.PRODUCT_NAME,

MIN(od.SL) AS "Minimum quantity sold "

JOIN PRODUCT pro On od.PRODUCT_ID=pro.PRODUCT_ID GROUP BY od.PRODUCT_ID, pro.PRODUCT_NAME

Q12: Displays contract code, coupon period, vendor code, supplier name that offers contract value not between 34500000 and 550000000.

C.TIME,C.CONTRACT_ID,C.SUPPLIER_ID,S.SUPLLIER_NAME,cd.VALUE FROM CONTRACT C

ON C.SUPPLIER_ID=S.SUPPLIER_ID

WHERE VALUE not between 34500000 and 550000000

Q13: For each product, know the product code, product name and number of sales.

FROM KHACHHANG KH download by : skknchat@gmail.com

Định dạng
Số trang	94
Dung lượng	358,96 KB