-
Notifications
You must be signed in to change notification settings - Fork 0
/
dbDDL.sql
99 lines (85 loc) · 2.2 KB
/
dbDDL.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
CREATE TABLE USERS_DETAILS
(
U_ID VARCHAR(10) NOT NULL,
F_NAME VARCHAR(10),
L_NAME VARCHAR(10),
CITY VARCHAR(10),
STREET VARCHAR(10),
PHONE VARCHAR(12),
PRIMARY KEY (U_ID));
CREATE TABLE GENRE
(
G_ID VARCHAR(10) NOT NULL,
NAME VARCHAR(10),
PRIMARY KEY(G_ID));
CREATE TABLE BOOKS_DETAILS
(
ISBN VARCHAR(10) NOT NULL,
TITLE VARCHAR(25),
AUTHOR VARCHAR(15),
RATING INT,
G_ID VARCHAR(10),
POSITION VARCHAR(10),
DISCOUNT INT,
PRIMARY KEY (ISBN),
FOREIGN KEY(G_ID) REFERENCES GENRE(G_ID) ON DELETE CASCADE);
CREATE TABLE CUSTOMER
(
CUST_ID VARCHAR(10) NOT NULL,
TYPE VARCHAR(10),
PRIMARY KEY (CUST_ID),
FOREIGN KEY(CUST_ID) REFERENCES USERS_DETAILS(U_ID) ON DELETE CASCADE);
CREATE TABLE STAFF
(
S_ID VARCHAR(10) NOT NULL,
SALARY INT,
PRIMARY KEY (S_ID),
FOREIGN KEY(S_ID) REFERENCES USERS_DETAILS(U_ID) ON DELETE CASCADE);
CREATE TABLE PURCHASE
(
P_ID VARCHAR(10) NOT NULL,
U_ID VARCHAR(10) NOT NULL,
ISBN VARCHAR(10),
P_DATE DATE,
PRICE FLOAT(5),
PRIMARY KEY(P_ID),
FOREIGN KEY(U_ID) REFERENCES USERS_DETAILS(U_ID) ON DELETE CASCADE,
FOREIGN KEY(ISBN) REFERENCES BOOKS_DETAILS(ISBN) ON DELETE CASCADE);
CREATE TABLE RENT
(
R_ID VARCHAR(10) NOT NULL,
U_ID VARCHAR(10) NOT NULL,
ISBN VARCHAR(10),
R_DATE DATE,
VALID_TILL DATE,
PRICE FLOAT(5),
PRIMARY KEY(R_ID),
FOREIGN KEY(U_ID) REFERENCES USERS_DETAILS(U_ID) ON DELETE CASCADE,
FOREIGN KEY(ISBN) REFERENCES BOOKS_DETAILS(ISBN) ON DELETE CASCADE);
CREATE TABLE PUBLISHER
(
PUB_ID VARCHAR(10) NOT NULL,
ISBN VARCHAR(10) NOT NULL,
NAME VARCHAR(15),
EDITION INT,
PRIMARY KEY (ISBN),
FOREIGN KEY(ISBN) REFERENCES BOOKS_DETAILS(ISBN) ON DELETE CASCADE);
CREATE TABLE LOGIN_DETAILS
(
U_ID VARCHAR(10) NOT NULL,
PASSWORD VARCHAR(10) NOT NULL,
PRIMARY KEY (U_ID),
FOREIGN KEY (U_ID) REFERENCES USERS_DETAILS(U_ID) ON DELETE CASCADE);
CREATE TABLE COPY
(
COPY_ID VARCHAR(10) NOT NULL,
ISBN VARCHAR(10),
TYPE VARCHAR(10),
PRICE FLOAT(5),
PRIMARY KEY (COPY_ID),
FOREIGN KEY (ISBN) REFERENCES BOOKS_DETAILS(ISBN) ON DELETE CASCADE);
CREATE VIEW DAILY_PURCHASE (DATE_OF_PURCHASE,NO_OF_PURCHASES,TOTAL_AMOUNT)
AS SELECT P_DATE,COUNT(*),SUM(PRICE)
FROM PURCHASE
WHERE P_DATE=CURRENT_DATE
GROUP BY P_DATE;