· 5 years ago · Mar 29, 2020, 09:50 PM
1/****** Procedure: Creates New StoredProcedure [dbo].[Create_eCommerce_Tables] ******/
2CREATE PROCEDURE [dbo].[Create_eCommerce_Tables]
3AS
4IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='customer')
5 CREATE TABLE customer(
6 custID int NOT NULL,
7 fName varchar(20) NOT NULL,
8 lName varchar(20) NOT NULL,
9 phone varchar(10) NOT NULL,
10 email varchar(50) NOT NULL,
11 gender varchar(1) NOT NULL,
12 CONSTRAINT PK_customer PRIMARY KEY (custID)
13 );
14IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='shippingAddress')
15 CREATE TABLE shippingAddress(
16 sID int NOT NULL,
17 custID int NOT NULL,
18 address_1 varchar(30) NOT NULL,
19 address_2 varchar(30) NOT NULL,
20 city varchar(50) NOT NULL,
21 state varchar(2) NOT NULL,
22 zip varchar(10) NOT NULL,
23 primaryYN BIT NOT NULL,
24 CONSTRAINT PK_shippingAddress PRIMARY KEY (sID)
25 );
26IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='paymentInfo')
27 CREATE TABLE paymentInfo(
28 custID int NOT NULL,
29 pName varchar(10) NOT NULL,
30 ccType varchar(20) NOT NULL,
31 ccNum varchar(16) NOT NULL,
32 ccExpire DATE NOT NULL,
33 billAddress varchar(30) NOT NULL,
34 city varchar(50) NOT NULL,
35 state varchar(2) NOT NULL,
36 zip varchar(10) NOT NULL,
37 CONSTRAINT PK_paymentinfo PRIMARY KEY (custID)
38 );
39IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='shippingMethods')
40 CREATE TABLE shippingMethods(
41 shipID int NOT NULL,
42 company varchar(30) NOT NULL,
43 method varchar(25) NOT NULL,
44 fRate number(5,2) NOT NULL,
45 vRate number(5,2) NOT NULL,
46 baseWeight number(6,2) NOT NULL,
47 CONSTRAINT PK_shippingMethods PRIMARY KEY (shipID)
48 );
49IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='category')
50 CREATE TABLE category(
51 catID int NOT NULL,
52 deptID int NOT NULL,
53 name varchar(25) NOT NULL,
54 description varchar(100) NOT NULL,
55 CONSTRAINT PK_category PRIMARY KEY (catID,deptID)
56 );
57IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='dept')
58 CREATE TABLE dept(
59 deptID int NOT NULL,
60 name varchar(25),
61 description varchar(100) NOT NULL,
62 CONSTRAINT PK_dept PRIMARY KEY (deptID)
63 );
64IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='orders')
65 CREATE TABLE orders(
66 OID int NOT NULL,
67 custID int NOT NULL,
68 orderDt date NOT NULL,
69 shipID int NOT NULL,
70 shipCost number(5,2) NOT NULL
71 CONSTRAINT PK_orders PRIMARY KEY(OID)
72 );
73IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='orderItems')
74 CREATE TABLE orderItems(
75 OID int NOT NULL,
76 prodID int NOT NULL,
77 qty int NOT NULL,
78 price number(6,2) NOT NULL,
79 CONSTRAINT PK_orderItems PRIMARY KEY(OID,prodID)
80 );
81IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='product')
82 CREATE TABLE product(
83 prodID int NOT NULL,
84 prodName varchar(100) NOT NULL,
85 description varchar(500) NOT NULL,
86 rPrice number(6,2) NOT NULL,
87 sPrice number(6,2) NOT NULL,
88 catID int NOT NULL,
89 keywords varchar(100) NOT NULL,
90 CONSTRAINT PK_product PRIMARY KEY(prodID)
91 );
92IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='upSells')
93 CREATE TABLE upSells(
94 custID int NOT NULL,
95 prodID int NOT NULL,
96 percentage number(3,2) NOT NULL,
97 CONSTRAINT PK_upSells PRIMARY KEY(custID,prodID)
98 );
99IF NOT EXISTS (SELECT * FROM sys.Tables WHERE name='crossSells')
100 CREATE TABLE crossSells(
101 prodID1 int NOT NULL,
102 prodID2 int NOT NULL,
103 percentage number(3,2) NOT NULL,
104 CONSTRAINT PK_crossSells PRIMARY KEY(prodID1,prodID2)
105 );
106
107
108
109
110
111GO