-
Notifications
You must be signed in to change notification settings - Fork 1
/
Copy pathDDL.sql
105 lines (81 loc) · 5.29 KB
/
DDL.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
SET client_encoding = 'UTF8';
SET standard_conforming_strings = off;
SET check_function_bodies = false;
SET client_min_messages = warning;
SET escape_string_warning = off;
CREATE SCHEMA DealDone;
SET search_path = DealDone;
CREATE TABLE Customer (
custID char(9) NOT NULL,
fname Varchar(15),
lname Varchar(15),
Contact Varchar(13),
Email_id Varchar(20),
PRIMARY KEY (custID)
);
CREATE TABLE CustomerAddress (
custID char(9) NOT NULL,
Address Varchar(30),
PRIMARY KEY (custID,Address),
FOREIGN KEY (custID) REFERENCES Customer(custID)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Category(
catID char(9),
catName Varchar(20),
PRIMARY KEY (catID)
);
CREATE TABLE Items (
itemCode Varchar(5) NOT NULL,
Name Varchar(20),
catID char(9),
Stock integer,
Brand Varchar(20),
Discount numeric(3,1),
Price Decimal(8,2),
Rating numeric(2,1),
Description Varchar(30),
PRIMARY KEY (itemCode),
FOREIGN KEY (catID) REFERENCES Category(catID)
ON DELETE SET NULL ON UPDATE CASCADE
);
CREATE TABLE DeliveryService (
delivery_address Varchar(30),
delivery_charge numeric(5,2),
contact_no Varchar(13),
PRIMARY KEY (delivery_address)
);
CREATE TABLE purchased(
itemCode Varchar(5),
custID char(9),
Quantity integer,
purchased_date date,
PRIMARY KEY (itemCode, custID, purchased_date),
FOREIGN KEY (itemCode) REFERENCES Items(itemCode)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (custID) REFERENCES Customer(custID)
ON DELETE CASCADE ON UPDATE CASCADE
);
CREATE TABLE Bills(
Bill_no Varchar(5) NOT NULL,
custID char(9),
itemCode Varchar(5),
delivery_address Varchar(30),
bill_date date,
Mode_of_Payment Varchar(15),
expected_delivery_date Date,
PRIMARY KEY(bill_no,itemCode),
FOREIGN KEY (custID,itemCode, bill_date) REFERENCES purchased(custID,itemCode, purchased_date)
ON DELETE SET NULL ON UPDATE CASCADE,
FOREIGN KEY (delivery_address) REFERENCES DeliveryService(delivery_address)
ON DELETE SET NULL ON UPDATE CASCADE
);
CREATE TABLE wishes_to_buy(
custID char(9),
itemCode Varchar(5),
PRIMARY KEY (itemCode, custID),
FOREIGN KEY (itemCode) REFERENCES Items(itemCode)
ON DELETE CASCADE ON UPDATE CASCADE,
FOREIGN KEY (custID) REFERENCES Customer(custID)
ON DELETE CASCADE ON UPDATE CASCADE
);