-- DDL ---
CREATE DATABASE SaleDB;
USE SaleDB;
CREATE TABLE Product
(
ProductID INT NOT NULL,
ProductName VARCHAR(150) NOT NULL,
ProductPrice FLOAT NOT NULL
)
ALTER TABLE Product
ADD CONSTRAINT PK_Product
PRIMARY KEY (ProductID);
CREATE TABLE Customer(
CustID INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
CustName NVARCHAR(150) NOT NULL,
CustDOB DATE NOT NULL CHECK( YEAR(GETDATE()) - YEAR(CustDOB) >= 18 ),
CustGender BIT NOT NULL,
CustAddress VARCHAR(150) NOT NULL,
CustLevel VARCHAR(50) NOT NULL
CHECK(CustLevel IN ('Business','Economic','Free') )
);
CREATE TABLE Invoice(
InvoiceNo INT PRIMARY KEY NOT NULL,
InvoiceDate DATETIME NOT NULL,
CustID INT NOT NULL,
CONSTRAINT fk_CustID_Customer
FOREIGN KEY (CustID) REFERENCES
Customer(CustID)
);
CREATE TABLE InvoiceLine(
InvoiceNo INT NOT NULL,
ProductID INT NOT NULL,
Quantity FLOAT NOT NULL,
UnitPrice FLOAT NOT NULL,
CONSTRAINT PK_InvoiceLine
PRIMARY KEY (InvoiceNo,ProductID),
CONSTRAINT FK_Invoice
FOREIGN KEY (InvoiceNo)
REFERENCES Invoice(InvoiceNo),
CONSTRAINT FK_Product
FOREIGN KEY (ProductID)
REFERENCES Product(ProductID)
);