database-project-Shrabanti94/ddl.sql at main · DatabaseSystem19/database-project-Shrabanti94 · GitHub

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
106
107
108
109
110
111
112
113
114
115
drop table payment177;
drop table issue177;
drop table booking177;
drop table payment_method177;
drop table package177;
drop table user177;
drop table admin177;


CREATE TABLE admin177 (
  admin_id number(20),
  username VARCHAR(50) NOT NULL,
  password VARCHAR(50) NOT NULL,
  primary key(admin_id)
);


CREATE TABLE user177 (
  user_id number(20),
  name VARCHAR(100) NOT NULL,
  email VARCHAR(100) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  primary key(user_id)
);


CREATE TABLE package177 (
  package_id number(20),
  admin_id number(20),
  name VARCHAR(100) NOT NULL,
  description VARCHAR(200) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  primary key(package_id),
  FOREIGN KEY (admin_id) REFERENCES admin177 (admin_id)
);


CREATE TABLE booking177 (
  booking_id number(30),
  user_id number(20),
  package_id number(20),
  start_date VARCHAR(10),
  end_date VARCHAR(10),
  booking_date VARCHAR(10),
  status VARCHAR(20) NOT NULL,
  primary key(booking_id),
  FOREIGN KEY (user_id) REFERENCES user177 (user_id),
  FOREIGN KEY (package_id) REFERENCES package177 (package_id)
);


CREATE TABLE issue177 (
  issue_id number(20),
  booking_id number(30),
  admin_id number(20),
  user_id number(20),
  description VARCHAR(200) NOT NULL,
  issue_date VARCHAR(10),
  status VARCHAR(20) NOT NULL,
  primary key(issue_id),
  FOREIGN KEY (booking_id) REFERENCES booking177 (booking_id),
  FOREIGN KEY (user_id) REFERENCES user177 (user_id),
  FOREIGN KEY (admin_id) REFERENCES admin177 (admin_id)
);


CREATE TABLE payment_method177 (
  payment_method_id number(20),
  name VARCHAR(50) NOT NULL,
  description VARCHAR(200) NOT NULL,
  primary key(payment_method_id)
);


CREATE TABLE payment177 (
  payment_id number(30),
  booking_id number(30),
  user_id number(20),
  payment_method_id number(20),
  amount DECIMAL(10,2) NOT NULL,
  payment_date VARCHAR(10),
  primary key(payment_id),
  FOREIGN KEY (booking_id) REFERENCES booking177 (booking_id),
  FOREIGN KEY (user_id) REFERENCES user177 (user_id),
  FOREIGN KEY (payment_method_id) REFERENCES payment_method177 (payment_method_id)
);


set pagesize 200
set linesize 300


alter table booking177 add location char(20);--Add column in the table
alter table booking177 modify location varchar(23);--Modify column definition in the table
alter table booking177 rename column location to location2;--Rename the column name
alter table booking177 drop column location2;--Drop the column from table