0dr4NtFq

1-- ---
2-- Globals
3-- ---
4
5-- SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
6-- SET FOREIGN_KEY_CHECKS=0;
7
8-- ---
9-- Table 'Exhibition'
10-- 
11-- ---
12
13DROP TABLE IF EXISTS `Exhibition`;
14		
15CREATE TABLE `Exhibition` (
16  `exhibitionID` INTEGER NOT NULL AUTO_INCREMENT,
17  `Exhibition_Date` DATE NOT NULL,
18  `Room_number` INTEGER NOT NULL,
19  PRIMARY KEY (`exhibitionID`)
20);
21
22-- ---
23-- Table 'showcases'
24-- 
25-- ---
26
27DROP TABLE IF EXISTS `showcases`;
28		
29CREATE TABLE `showcases` (
30  `showcaseID` INTEGER NOT NULL AUTO_INCREMENT,
31  `Exhibition` INTEGER NOT NULL,
32  `Painting` INTEGER NOT NULL,
33  PRIMARY KEY (`showcaseID`)
34);
35
36-- ---
37-- Table 'Painting'
38-- 
39-- ---
40
41DROP TABLE IF EXISTS `Painting`;
42		
43CREATE TABLE `Painting` (
44  `paintingID` INTEGER NOT NULL AUTO_INCREMENT,
45  `title` VARCHAR(100) NOT NULL,
46  `medium` VARCHAR NOT NULL,
47  `Price` DECIMAL NULL,
48  PRIMARY KEY (`paintingID`)
49);
50
51-- ---
52-- Table 'paints'
53-- 
54-- ---
55
56DROP TABLE IF EXISTS `paints`;
57		
58CREATE TABLE `paints` (
59  `paintsID` INTEGER NOT NULL AUTO_INCREMENT,
60  `Painting` INTEGER NOT NULL,
61  `Artist` INTEGER NOT NULL,
62  PRIMARY KEY (`paintsID`)
63);
64
65-- ---
66-- Table 'Artist'
67-- 
68-- ---
69
70DROP TABLE IF EXISTS `Artist`;
71		
72CREATE TABLE `Artist` (
73  `artistID` INTEGER NOT NULL AUTO_INCREMENT,
74  `name` VARCHAR(100) NOT NULL,
75  `SSN` INT NOT NULL,
76  PRIMARY KEY (`artistID`)
77);
78
79-- ---
80-- Foreign Keys 
81-- ---
82
83ALTER TABLE `showcases` ADD FOREIGN KEY (Exhibition) REFERENCES `Exhibition` (`exhibitionID`);
84ALTER TABLE `showcases` ADD FOREIGN KEY (Painting) REFERENCES `Painting` (`paintingID`);
85ALTER TABLE `paints` ADD FOREIGN KEY (Painting) REFERENCES `Painting` (`paintingID`);
86ALTER TABLE `paints` ADD FOREIGN KEY (Artist) REFERENCES `Artist` (`artistID`);
87
88-- ---
89-- Table Properties
90-- ---
91
92-- ALTER TABLE `Exhibition` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
93-- ALTER TABLE `showcases` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
94-- ALTER TABLE `Painting` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
95-- ALTER TABLE `paints` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
96-- ALTER TABLE `Artist` ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
97
98-- ---
99-- Test Data
100-- ---
101
102-- INSERT INTO `Exhibition` (`exhibitionID`,`Exhibition_Date`,`Room_number`) VALUES
103-- ('','','');
104-- INSERT INTO `showcases` (`showcaseID`,`Exhibition`,`Painting`) VALUES
105-- ('','','');
106-- INSERT INTO `Painting` (`paintingID`,`title`,`medium`,`Price`) VALUES
107-- ('','','','');
108-- INSERT INTO `paints` (`paintsID`,`Painting`,`Artist`) VALUES
109-- ('','','');
110-- INSERT INTO `Artist` (`artistID`,`name`,`SSN`) VALUES
111-- ('','','');