Bạn chạy tạo Database JesupDB chưa?
Mở sql server 2005 chép đoạn code dưới vào nhấn F5 chạy tạo Database nhé!
[sql]----------------------------------------------
-- CREATE DB JessupDB --
----------------------------------------------
USE master
go
IF exists(SELECT * FROM sysdatabases WHERE name= 'JessupDB')
DROP DATABASE JessupDB
go
-----------------------------------------------
CREATE DATABASE JessupDB
go
SET DATEFORMAT dmy
go
USE JessupDB
go
CREATE TABLE Region
(
RegionID nvarchar(7) CONSTRAINT pk_RegionID PRIMARY KEY,
RegionDesc nvarchar(200)
)
CREATE TABLE Employee_Details
(
EmpID nvarchar(7) CONSTRAINT pk_Employee PRIMARY KEY,
RegionID nvarchar(7) CONSTRAINT fk_RegionID FOREIGN KEY REFERENCES Region(RegionID),
Firstname nvarchar(20) NOT NULL,
Lastname nvarchar(20) NOT NULL,
Department nvarchar(30) NOT NULL,
Designation nvarchar(30) NOT NULL,
Address nvarchar(50),
City nvarchar(30),
State nvarchar(20),
Zip nvarchar(10),
Phone nvarchar(20),
Email nvarchar(50),
Username nvarchar(20),
PassLogin nvarchar(18),
Permission bit
)
go
CREATE TABLE Product_Details
(
ProductCode nvarchar(7) CONSTRAINT pk_Product PRIMARY KEY,
ProductDesc nvarchar(50) NOT NULL,
ProductType nvarchar(30) NOT NULL,
Unit_Price int CONSTRAINT chk_Unit_Price CHECK (Unit_Price>0),
)
go
CREATE TABLE Scheme_Details
(
SchemeID nvarchar(7) CONSTRAINT pk_SchemeID PRIMARY KEY,
SchemeDesc nvarchar(50) NOT NULL,
ProductCode nvarchar(7) CONSTRAINT fk_ProductCode FOREIGN KEY REFERENCES Product_Details(ProductCode),
DiscPercentage int CONSTRAINT chk_DiscPercentage CHECK (DiscPercentage>= 0 AND DiscPercentage<= 100),
CompProCode nvarchar(7),
CompProPrice int
)
go
CREATE TABLE WholeSaler_Details
(
DealerID nvarchar(7) CONSTRAINT pk_Dealer PRIMARY KEY,
RegionID nvarchar(7) CONSTRAINT fk_ReID FOREIGN KEY REFERENCES Region(RegionID),
FullName nvarchar(30) NOT NULL,
Address nvarchar(50) NOT NULL,
City nvarchar(30) NOT NULL,
State nvarchar(20),
Zip nvarchar(10),
Phone nvarchar(20)
)
go
CREATE TABLE SalesData
(
SalesDataID nvarchar(7) CONSTRAINT pk_SalesDataID PRIMARY KEY,
Date_SD datetime,
DealerID nvarchar(7) CONSTRAINT fk_DealerID FOREIGN KEY REFERENCES WholeSaler_Details(DealerID)
)
go
CREATE TABLE SalesData_Details
(
SDD_ID nvarchar(7) CONSTRAINT pk_SDD_ID PRIMARY KEY,
SalesDataID nvarchar(7) CONSTRAINT fk_SalesDataID FOREIGN KEY REFERENCES SalesData(SalesDataID),
ProductCode nvarchar(7) CONSTRAINT fk_ProductC FOREIGN KEY REFERENCES Product_Details(ProductCode),
Quantity int CONSTRAINT chk_Q CHECK (Quantity>0),
PricePerUnit int CONSTRAINT chk_PricePerUnit CHECK (PricePerUnit> 0)
)
go
CREATE TABLE Promotional_Scheme
(
PromID nvarchar(7) CONSTRAINT pk_PromID PRIMARY KEY,
Date_PS datetime,
DealerID nvarchar(7) CONSTRAINT fk_DlID FOREIGN KEY REFERENCES WholeSaler_Details(DealerID)
)
go
CREATE TABLE P_Scheme_Details
(
PSD_ID nvarchar(7) CONSTRAINT pk_PSD_ID PRIMARY KEY,
PromID nvarchar(7) CONSTRAINT fk_PromID FOREIGN KEY REFERENCES Promotional_Scheme(PromID),
ProductCode nvarchar(7) CONSTRAINT fk_ProCode FOREIGN KEY REFERENCES Product_Details(ProductCode),
Quantity int CONSTRAINT chk_Quantity CHECK (Quantity> 0),
PricePerUnit int CONSTRAINT chk_PricePUnit CHECK (PricePerUnit> 0),
DiscountAmount int CONSTRAINT chk_DiscountA CHECK (DiscountAmount>= 0),
SchemeID nvarchar(7) CONSTRAINT fk_SchemeID FOREIGN KEY REFERENCES Scheme_Details(SchemeID),
CompProCode nvarchar(7)
)
go
-----------------------------------------------------------------------
-- CREATE FUNCTION Add ID Auto --
-----------------------------------------------------------------------
CREATE FUNCTION AddID(@makt char(2))
RETURNS char(7)
AS
BEGIN
DECLARE @iMaso int
DECLARE @vMaso varchar(9)
IF @makt='RG' SET @iMaso= (SELECT MAX(RIGHT(RegionID,5)) FROM Region)
IF @makt='EM' SET @iMaso= (SELECT MAX(RIGHT(EmpID,5)) FROM Employee_Details)
IF @makt='PD' SET @iMaso= (SELECT MAX(RIGHT(ProductCode,5)) FROM Product_Details)
IF @makt='SM' SET @iMaso= (SELECT MAX(RIGHT(SchemeID,5)) FROM Scheme_Details)
IF @makt='WS' SET @iMaso= (SELECT MAX(RIGHT(DealerID,5)) FROM WholeSaler_Details)
IF @makt='PR' SET @iMaso= (SELECT MAX(RIGHT(PromID,5)) FROM Promotional_Scheme)
IF @makt='PS' SET @iMaso= (SELECT MAX(RIGHT(PSD_ID,5)) FROM P_Scheme_Details)
IF @makt='SD' SET @iMaso= (SELECT MAX(RIGHT(SalesDataID,5)) FROM SalesData)
IF @makt='ST' SET @iMaso= (SELECT MAX(RIGHT(SDD_ID,5)) FROM SalesData_Details)
IF (@iMaso IS NULL) SET @vMaso= @makt+ CONVERT(varchar(7),'00001')
ELSE
BEGIN
SET @iMaso= @iMaso+1
SET @vMaso= '0000'+ CONVERT(varchar,@iMaso)
SET @vMaso= @makt+ RIGHT(@vMaso,5)
END
RETURN @vMaso
END
go
ALTER TABLE Region ADD CONSTRAINT def_Reg DEFAULT dbo.AddID('RG') FOR RegionID
ALTER TABLE Employee_Details ADD CONSTRAINT def_Emp DEFAULT dbo.AddID('EM') FOR EmpID
ALTER TABLE Product_Details ADD CONSTRAINT def_Pro DEFAULT dbo.AddID('PD') FOR ProductCode
ALTER TABLE Scheme_Details ADD CONSTRAINT def_Sch DEFAULT dbo.AddID('SM') FOR SchemeID
ALTER TABLE WholeSaler_Details ADD CONSTRAINT def_Who DEFAULT dbo.AddID('WS') FOR DealerID
ALTER TABLE Promotional_Scheme ADD CONSTRAINT def_Prom DEFAULT dbo.AddID('PR') FOR PromID
ALTER TABLE P_Scheme_Details ADD CONSTRAINT def_P_Scheme DEFAULT dbo.AddID('PS') FOR PSD_ID
ALTER TABLE SalesData ADD CONSTRAINT def_SalesData DEFAULT dbo.AddID('SD') FOR SalesDataID
ALTER TABLE SalesData_Details ADD CONSTRAINT def_SDD DEFAULT dbo.AddID('ST') FOR SDD_ID
go
-----------------------------------------------------------------------
-- INSERT DATA --
-----------------------------------------------------------------------
INSERT Region(RegionDesc) VALUES(N'Trung tâm Jessup')
INSERT Region(RegionDesc) VALUES(N'Miền Bắc')
INSERT Region(RegionDesc) VALUES(N'Miền Trung')
INSERT Region(RegionDesc) VALUES(N'Đông Nam Bộ')
INSERT Region(RegionDesc) VALUES(N'Tây Nam Bộ')
INSERT Region(RegionDesc) VALUES(N'Tây Nguyên')
go
INSERT Employee_Details(RegionID, Firstname, Lastname, Department, Designation, Address, City,
State, Zip, Phone, Email, Username, PassLogin, Permission)
VALUES('RG00001', N'Trương Ngọc', N'Hữu', N'IT', N'Quản trị IT', N'Thủ Đức', N'TP.HCM',
N'TP.HCM', '01291-9495','090910111', '
NgocHuu@gmail.com', 'admin', 'admin', 1)
INSERT Employee_Details(RegionID, Firstname, Lastname, Department, Designation, Address, City,
State, Zip, Phone, Email, Username, PassLogin, Permission)
VALUES('RG00001', N'Phách Phú', N'Thành', N'Kinh Doanh', N'Trưởng phòng', N'Biên Hòa', N'TP Biên Hòa',
N'Đồng Nai', '09932-2121','092120102', '
Phuthanh@yahoo.com', 'truongphong', 'chief', 1)
INSERT Employee_Details(RegionID, Firstname, Lastname, Department, Designation, Address, City,
State, Zip, Phone, Email, Username, PassLogin, Permission)
VALUES('RG00003', N'Lê', N'Minh', N'Kinh Doanh', N'Nhân viên bán hàng', N'Thủ Đức', 'TP.HCM',
N'TP.HCM', NULL,'091291212', '
Leminh@gmail.com', 'minh', '123', 0)
INSERT Employee_Details(RegionID, Firstname, Lastname, Department, Designation, Address, City,
State, Zip, Phone, Email, Username, PassLogin, Permission)
VALUES('RG00002', N'Lưu Đức', N'Hòa', N'Kinh Doanh', N'Nhân viên bán hàng', N'Dĩ An', N'Bình Dương',
N'Bình Dương', NULL,'092345682', '
LuuDucHos@gmail.com', 'hoa', '456', 0)
INSERT Employee_Details(RegionID, Firstname, Lastname, Department, Designation, Address, City,
State, Zip, Phone, Email, Username, PassLogin, Permission)
VALUES('RG00004', N'Trần Anh', N'Vũ', N'Kinh Doanh', N'Nhân viên tổng hợp', N'Thủ Đức', 'TP.HCM',
N'TP.HCM', NULL,'090000009', '
AnhVu@gmail.com', 'kuvu', 'chuoi', 0)
INSERT Employee_Details(RegionID, Firstname, Lastname, Department, Designation, Address, City,
State, Zip, Phone, Email, Username, PassLogin, Permission)
VALUES('RG00002', N'Huy', N'Hiền', N'Kinh Doanh', N'Nhân viên học việc', N'Vũng Tàu', N'Vũng Tàu',
N'Bà Rịa - Vũng Tàu', NULL, '00072-0192', '
HuyHien@yahoo.com', 'huyhien', 'hh', 0)
go
INSERT Product_Details(ProductDesc, ProductType, Unit_Price)
VALUES(N'Tivi LG 24" Plasma', N'LCD', 5000000)
INSERT Product_Details(ProductDesc, ProductType, Unit_Price)
VALUES(N'Tivi LG 21"', N'CRT', 2500000)
INSERT Product_Details(ProductDesc, ProductType, Unit_Price)
VALUES(N'Máy giặt Samsung - 7,6kg', N'Automatic', 4350000)
INSERT Product_Details(ProductDesc, ProductType, Unit_Price)
VALUES(N'Đầu DVD Panasonic DMR-E87H', N'4MP - AVI', 1610000)
INSERT Product_Details(ProductDesc, ProductType, Unit_Price)
VALUES(N'Bàn ủi Philip HD1172', N'Small', 230000)
INSERT Product_Details(ProductDesc, ProductType, Unit_Price)
VALUES(N'Quạt Asian B400T', N'Để bàn', 120000)
INSERT Product_Details(ProductDesc, ProductType, Unit_Price)
VALUES(N'Tivi Sony Bravia KLV-V26A10', N'24" - LCD', 7500000)
INSERT Product_Details(ProductDesc, ProductType, Unit_Price)
VALUES(N'Tủ lạnh Toshiba GR-R21VPD', N'180 Lít', 3050000)
go
INSERT WholeSaler_Details(RegionID, FullName, Address, City, State, Zip, Phone)
VALUES('RG00003',N'Trung tâm Điện máy Nguyễn Kim', N'Sài Gòn', 'TP.HCM', 'TP.HCM', NULL, '0809091019')
INSERT WholeSaler_Details(RegionID, FullName, Address, City, State, Zip, Phone)
VALUES('RG00003', N'Siêu thị Hoàn Vũ', N'Biên Hòa', N'Biên Hòa', N'Đồng Nai', '10909-1111', '06130301345')
INSERT WholeSaler_Details(RegionID, FullName, Address, City, State, Zip, Phone)
VALUES('RG00002', N'Cửa hàng Điện gia dụng AAA', N'Nha Trang', N'Nha Trang', N'Khánh Hòa', NULL, '0129001290')
INSERT WholeSaler_Details(RegionID, FullName, Address, City, State, Zip, Phone)
VALUES('RG00004', N'Shop Here', N'Trung tâm thương mại', N'Quận Ninh Kiều', N'Cần Thơ', '09191-7321', '0808099097')
go
INSERT Scheme_Details(SchemeDesc, ProductCode, DiscPercentage, CompProCode, CompProPrice)
VALUES(N'Khuyến mãi nhân 10 năm thành lập', 'PD00003', 2, 'PD00006', 120000)
INSERT Scheme_Details(SchemeDesc, ProductCode, DiscPercentage, CompProCode, CompProPrice)
VALUES(N'Khách hàng lâu năm', N'PD00001', 5, 'No Item', 0)
INSERT Scheme_Details(SchemeDesc, ProductCode, DiscPercentage, CompProCode, CompProPrice)
VALUES(N'Lễ 30-04 và 1-5', N'PD00007', 10, 'PD00005', 230000)
INSERT Scheme_Details(SchemeDesc, ProductCode, DiscPercentage, CompProCode, CompProPrice)
VALUES(N'Chia sẽ bạn bè', N'PD00004', 0, 'PD00006', 120000)
go
INSERT SalesData(Date_SD, DealerID) VALUES('15-04-2010', 'WS00002')
INSERT SalesData(Date_SD, DealerID) VALUES('10-04-2010', 'WS00001')
INSERT SalesData(Date_SD, DealerID) VALUES('14-04-2010', 'WS00004')
go
INSERT SalesData_Details(SalesDataID, ProductCode, Quantity, PricePerUnit)
VALUES('SD00001', 'PD00007', 50, 7500000)
INSERT SalesData_Details(SalesDataID, ProductCode, Quantity, PricePerUnit)
VALUES('SD00002', 'PD00008', 5, 3050000)
INSERT SalesData_Details(SalesDataID, ProductCode, Quantity, PricePerUnit)
VALUES('SD00002', 'PD00001', 12, 5000000)
INSERT SalesData_Details(SalesDataID, ProductCode, Quantity, PricePerUnit)
VALUES('SD00003', 'PD00007', 7, 7500000)
INSERT SalesData_Details(SalesDataID, ProductCode, Quantity, PricePerUnit)
VALUES('SD00003', 'PD00004', 23, 1610000)
go
INSERT Promotional_Scheme(Date_PS, DealerID) VALUES('15-04-2010', 'WS00002')
INSERT Promotional_Scheme(Date_PS, DealerID) VALUES('10-04-2010', 'WS00001')
go
INSERT P_Scheme_Details(PromID, ProductCode, Quantity, PricePerUnit, DiscountAmount, SchemeID, CompProCode)
VALUES('PR00001', 'PD00007', 10, 7500000, 7500000, 'SM00003', 'PD00005')
INSERT P_Scheme_Details(PromID, ProductCode, Quantity, PricePerUnit, DiscountAmount, SchemeID, CompProCode)
VALUES('PR00001', 'PD00004', 5, 5000000, 0, 'SM00004', 'PD00006')
INSERT P_Scheme_Details(PromID, ProductCode, Quantity, PricePerUnit, DiscountAmount, SchemeID, CompProCode)
VALUES('PR00002', 'PD00001', 5, 3050000, 762500, 'SM00002', 'No Item')
INSERT P_Scheme_Details(PromID, ProductCode, Quantity, PricePerUnit, DiscountAmount, SchemeID, CompProCode)
VALUES('PR00002', 'PD00004', 12, 5000000, 0, 'SM00004', 'PD00006')
go
/*
SELECT * FROM Region
SELECT * FROM Employee_Details
SELECT * FROM Product_Details
SELECT * FROM WholeSaler_Details
SELECT * FROM Scheme_Details
SELECT * FROM SalesData
SELECT * FROM SalesData_Details
SELECT * FROM Promotional_Scheme
SELECT * FROM P_Scheme_Details
*/
[/sql]
