-
Notifications
You must be signed in to change notification settings - Fork 80
/
database.sql
64 lines (57 loc) · 2.52 KB
/
database.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
CREATE DATABASE BookStore;
USE BookStore;
CREATE TABLE Book(
BookID varchar(50),
BookTitle varchar(200),
ISBN varchar(20),
Price double(12,2),
Author varchar(128),
Type varchar(128),
Image varchar(128),
PRIMARY KEY (BookID)
);
CREATE TABLE Users(
UserID int not null AUTO_INCREMENT,
UserName varchar(128),
Password varchar(16),
PRIMARY KEY (UserID)
);
CREATE TABLE Customer (
CustomerID int not null AUTO_INCREMENT,
CustomerName varchar(128),
CustomerPhone varchar(12),
CustomerIC varchar(14),
CustomerEmail varchar(200),
CustomerAddress varchar(200),
CustomerGender varchar(10),
UserID int,
PRIMARY KEY (CustomerID),
CONSTRAINT FOREIGN KEY (UserID) REFERENCES Users(UserID) ON DELETE SET NULL ON UPDATE CASCADE
);
CREATE TABLE `Order`(
OrderID int not null AUTO_INCREMENT,
CustomerID int,
BookID varchar(50),
DatePurchase datetime,
Quantity int,
TotalPrice double(12,2),
Status varchar(1),
PRIMARY KEY (OrderID),
CONSTRAINT FOREIGN KEY (BookID) REFERENCES Book(BookID) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ON DELETE SET NULL ON UPDATE CASCADE
);
CREATE TABLE Cart(
CartID int not null AUTO_INCREMENT,
CustomerID int,
BookID varchar(50),
Price double(12,2),
Quantity int,
TotalPrice double(12,2),
PRIMARY KEY (CartID),
CONSTRAINT FOREIGN KEY (BookID) REFERENCES Book(BookID) ON DELETE SET NULL ON UPDATE CASCADE,
CONSTRAINT FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ON DELETE SET NULL ON UPDATE CASCADE
);
INSERT INTO `book`(`BookID`, `BookTitle`, `ISBN`, `Price`, `Author`, `Type`, `Image`) VALUES ('B-001','Lonely Planet Australia (Travel Guide)','123-456-789-1',136,'Lonely Planet','Travel','image/travel.jpg');
INSERT INTO `book`(`BookID`, `BookTitle`, `ISBN`, `Price`, `Author`, `Type`, `Image`) VALUES ('B-002','Crew Resource Management, Second Edition','123-456-789-2',599,'Barbara Kanki','Technical','image/technical.jpg');
INSERT INTO `book`(`BookID`, `BookTitle`, `ISBN`, `Price`, `Author`, `Type`, `Image`) VALUES ('B-003','CCNA Routing and Switching 200-125 Official Cert Guide Library','123-456-789-3',329,'Cisco Press ','Technology','image/technology.jpg');
INSERT INTO `book`(`BookID`, `BookTitle`, `ISBN`, `Price`, `Author`, `Type`, `Image`) VALUES ('B-004','Easy Vegetarian Slow Cooker Cookbook','123-456-789-4',75.9,'Rockridge Press','Food','image/food.jpg');