-
Notifications
You must be signed in to change notification settings - Fork 0
/
SQL-Queries.sql
64 lines (43 loc) · 2.11 KB
/
SQL-Queries.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 IF NOT EXISTS Art_Gallery;
USE Art_Gallery;
CREATE table Artist(Artist_ID int AUTO_INCREMENT not null primary key,
FirstName varchar(20) not null,
MiddleName varchar(20) not null,
LastName varchar(20) not null,
Address varchar(100) not null,
Phone int not null);
CREATE table Gallery(Gallery_ID int AUTO_INCREMENT not null primary key,
Gallery_Name varchar(40) not null,
URL varchar(40) not null,
Location varchar(100) not null );
CREATE table Exhibition(Ex_ID int AUTO_INCREMENT not null primary key,
Ex_Name varchar(40) not null,
Ex_Location varchar(100) not null,
Ex_type varchar(40) not null,
Start_Date date not null,
End_Date date not null);
CREATE table Customer(C_ID int AUTO_INCREMENT not null primary key,
C_Email varchar(40) not null unique,
C_Name varchar(40) not null,
Address varchar(100) not null,
Phone int not null);
CREATE table Painting(Painting_ID int AUTO_INCREMENT not null primary key,
Title varchar(40) not null,
URL varchar(40) not null,
Year year(4) not null,
Type varchar(40) not null,
Cost int not null,
Likes int not null);
CREATE table Display(Ex_ID int not null,
Painting_ID int not null,
primary key (Ex_ID,Painting_ID));
ALTER table Gallery add column Artist_ID int not null;
ALTER table Gallery add foreign key(Artist_ID) references Artist(Artist_ID) on delete cascade;
ALTER table Painting add column Artist_ID int not null;
ALTER table Painting add foreign key (Artist_ID) references Artist(Artist_ID) on delete cascade;
ALTER table Painting add column Gallery_ID int not null;
ALTER table Painting add foreign key (Gallery_ID) references Gallery(Gallery_ID) on delete cascade;
ALTER table Painting add column C_ID int not null;
ALTER table Painting add foreign key (C_ID) references Customer(C_ID) on delete cascade;
ALTER table Display add foreign key (Ex_ID) references Exhibition(Ex_ID) on delete cascade;
ALTER table Display add foreign key (Painting_ID) references Painting(Painting_ID) on delete cascade;