DQTjSsDV

1DROP SCHEMA IF EXISTS `Image`;
2
3CREATE SCHEMA IF NOT EXISTS `Image`;
4
5CREATE TABLE `Image`.`Photographer` (
6	`id` int(25) NOT NULL,
7    `name` varchar(45) NOT NULL,
8    PRIMARY KEY (`id`));
9    
10    
11CREATE TABLE `Image`.`Author` (
12	`id` int(25) NOT NULL,
13    `name` varchar(45) NOT NULL,
14    `gender` ENUM('F','M'),
15    `b_year` int(4) NOT NULL,
16    PRIMARY KEY (`id`))
17    ENGINE=InnoDB DEFAULT CHARSET=latin1;    
18
19CREATE TABLE `Image`.`Image` (
20  `id` int(25) NOT NULL,
21  `imagename` varchar(45) NOT NULL,
22  `imageurl` varchar(45) NOT NULL,
23  `added` DATETIME NOT NULL,
24  `caption` varchar(200) NOT NULL,
25  `imagecol` varchar(45) NOT NULL,
26  `modified` DATETIME DEFAULT NULL,
27  PRIMARY KEY (`id`),
28  FOREIGN KEY (`id`) REFERENCES `Image`.`Photographer` (`id`))
29  ENGINE=InnoDB DEFAULT CHARSET=latin1;
30
31CREATE TABLE `Image`.`Article` (
32  `id` int(25) NOT NULL,
33  `name` varchar(45) NOT NULL,
34  `caption` varchar(200) NOT NULL,
35  `text` TEXT NOT NULL,
36  `articlecol` varchar(45) NOT NULL,
37  PRIMARY KEY (`id`),
38	FOREIGN KEY (`id`) REFERENCES `Image`.`Image` (`id`))
39  ENGINE=InnoDB DEFAULT CHARSET=latin1;
40
41CREATE TABLE `Image`.`Tag` (
42	`id` int(25) NOT NULL,
43    `name` varchar(45) NOT NULL,
44    PRIMARY KEY (`id`))
45    ENGINE=InnoDB DEFAULT CHARSET=latin1;
46
47CREATE TABLE `Image`.`Image_has_Tag` (
48	`Image_id` int(25) NOT NULL,
49    `Tag_id` int(25) NOT NULL,
50	PRIMARY KEY (`Image_id`,`Tag_id`),
51	FOREIGN KEY (`Image_id`) REFERENCES `Image`.`Image`(`id`),
52    FOREIGN KEY (`Tag_id`) REFERENCES `Image`.`Tag` (`id`))
53	ENGINE=InnoDB DEFAULT CHARSET=latin1;
54
55CREATE TABLE `Image`.`Image_has_Article` (
56	`Image_id` int(25) NOT NULL,
57	`Article_id` int(25) NOT NULL,
58	PRIMARY KEY (`Image_id`, `Article_id`),
59    FOREIGN KEY (`Image_id`) REFERENCES `Image`.`Image`(`Image_id`), 
60    FOREIGN KEY (`Article_id`) REFERENCES `Image`.`Article`(`Article_id`))
61    ENGINE=InnoDB DEFAULT CHARSET=latin1;
62    
63CREATE TABLE `Image`.`Tag_has_Article` (
64	`Article_id` int(25) NOT NULL,
65    `Tag_id` int(25) NOT NULL,
66	PRIMARY KEY (`Tag_id`,`Article_id`),
67    FOREIGN KEY (`Tag_id`) REFERENCES `Image`.`Tag`(`Tag_id`),
68    FOREIGN KEY (`Article_id`) REFERENCES `Image`.`Article` (`Article_id`))
69    ENGINE=InnoDB DEFAULT CHARSET=latin1;
70    
71    
72INSERT INTO `Image` (`id`, `imagename`, `ìmageurl`,`added`,`caption`,`imagecol`) VALUES (1,`a`,`a`,`a`,`a`,`a`,`a`);
73INSERT INTO `Photographer` (`id`, `name`) VALUES (`a`,`a`);
74INSERT INTO `Article` (`id`,`name`,`caption`,`text`,`articlecol`) VALUES (`a`,`a`,`a`,`a`,`a`);
75INSERT INTO `Tag` (`id`,`name`) VALUES (`a`,`a`);
76INSERT INTO `Author` (`ìd`,`name`,`gender`,`b_year`) VALUES (`a`, `a`, 'M', 1991);