BqmeUWbV

1-- MySQL Workbench Forward Engineering
2
3SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
4SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
5SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION';
6
7-- -----------------------------------------------------
8-- Schema final_project_sadies_art_shop
9-- -----------------------------------------------------
10
11-- -----------------------------------------------------
12-- Schema final_project_sadies_art_shop
13-- -----------------------------------------------------
14CREATE SCHEMA IF NOT EXISTS `final_project_sadies_art_shop` DEFAULT CHARACTER SET utf8 ;
15USE `final_project_sadies_art_shop` ;
16
17-- -----------------------------------------------------
18-- Table `final_project_sadies_art_shop`.`customers`
19-- -----------------------------------------------------
20DROP TABLE IF EXISTS `final_project_sadies_art_shop`.`customers` ;
21
22CREATE TABLE IF NOT EXISTS `final_project_sadies_art_shop`.`customers` (
23  `customer_id` INT NOT NULL AUTO_INCREMENT,
24  `customer_name` VARCHAR(45) NOT NULL,
25  `customer_address1` VARCHAR(100) NOT NULL,
26  `customer_address2` VARCHAR(45) NULL,
27  `customer_city` VARCHAR(45) NOT NULL,
28  `customer_state` VARCHAR(45) NOT NULL,
29  `customer_zipcode` INT NOT NULL,
30  `customer_art_title` VARCHAR(250) NOT NULL,
31  PRIMARY KEY (`customer_id`))
32ENGINE = InnoDB;
33
34
35-- -----------------------------------------------------
36-- Table `final_project_sadies_art_shop`.`art_supplies`
37-- -----------------------------------------------------
38DROP TABLE IF EXISTS `final_project_sadies_art_shop`.`art_supplies` ;
39
40CREATE TABLE IF NOT EXISTS `final_project_sadies_art_shop`.`art_supplies` (
41  `art_supply_id` INT NOT NULL AUTO_INCREMENT,
42  `art_supply_type` VARCHAR(45) NOT NULL,
43  `art_supply_amount` INT NOT NULL,
44  `art_supply_date_restocked` DATE NOT NULL,
45  `art_supply_next_restock_date` DATE NOT NULL,
46  `art_supply_price_per_unit` DECIMAL(2) NOT NULL,
47  PRIMARY KEY (`art_supply_id`))
48ENGINE = InnoDB;
49
50
51-- -----------------------------------------------------
52-- Table `final_project_sadies_art_shop`.`art`
53-- -----------------------------------------------------
54DROP TABLE IF EXISTS `final_project_sadies_art_shop`.`art` ;
55
56CREATE TABLE IF NOT EXISTS `final_project_sadies_art_shop`.`art` (
57  `art_title` VARCHAR(250) NOT NULL,
58  `art_medium` VARCHAR(45) NOT NULL,
59  `art_price` DECIMAL(2) NOT NULL,
60  `art_description` VARCHAR(300) NULL,
61  `art_customer_id` INT NOT NULL,
62  PRIMARY KEY (`art_title`, `art_customer_id`),
63  UNIQUE INDEX `art_title_UNIQUE` (`art_title` ASC) VISIBLE,
64  INDEX `fk_art_customers_idx` (`art_customer_id` ASC) VISIBLE,
65  CONSTRAINT `fk_art_customers`
66    FOREIGN KEY (`art_customer_id`)
67    REFERENCES `final_project_sadies_art_shop`.`customers` (`customer_id`)
68    ON DELETE NO ACTION
69    ON UPDATE NO ACTION)
70ENGINE = InnoDB;
71
72
73-- -----------------------------------------------------
74-- Table `final_project_sadies_art_shop`.`advertisements`
75-- -----------------------------------------------------
76DROP TABLE IF EXISTS `final_project_sadies_art_shop`.`advertisements` ;
77
78CREATE TABLE IF NOT EXISTS `final_project_sadies_art_shop`.`advertisements` (
79  `advertisement_id` INT NOT NULL AUTO_INCREMENT,
80  `advertisement_company` VARCHAR(100) NOT NULL,
81  `advertisement_price` DECIMAL(2) NOT NULL,
82  `advertisement_art_title` VARCHAR(250) NOT NULL,
83  `advertisments_art_title` VARCHAR(250) NOT NULL,
84  PRIMARY KEY (`advertisement_id`, `advertisments_art_title`),
85  INDEX `fk_advertisements_art1_idx` (`advertisments_art_title` ASC) VISIBLE,
86  CONSTRAINT `fk_advertisements_art1`
87    FOREIGN KEY (`advertisments_art_title`)
88    REFERENCES `final_project_sadies_art_shop`.`art` (`art_title`)
89    ON DELETE NO ACTION
90    ON UPDATE NO ACTION)
91ENGINE = InnoDB;
92
93
94-- -----------------------------------------------------
95-- Table `final_project_sadies_art_shop`.`awards`
96-- -----------------------------------------------------
97DROP TABLE IF EXISTS `final_project_sadies_art_shop`.`awards` ;
98
99CREATE TABLE IF NOT EXISTS `final_project_sadies_art_shop`.`awards` (
100  `awards_id` INT NOT NULL AUTO_INCREMENT,
101  `awards_title` VARCHAR(45) NOT NULL,
102  `awards_date` DATE NOT NULL,
103  `awards_prize_money` DECIMAL(2) NULL,
104  `award_art_title` VARCHAR(250) NOT NULL,
105  `award_art_title` VARCHAR(250) NOT NULL,
106  PRIMARY KEY (`awards_id`, `award_art_title`),
107  INDEX `fk_awards_art1_idx` (`award_art_title` ASC) VISIBLE,
108  CONSTRAINT `fk_awards_art1`
109    FOREIGN KEY (`award_art_title`)
110    REFERENCES `final_project_sadies_art_shop`.`art` (`art_title`)
111    ON DELETE NO ACTION
112    ON UPDATE NO ACTION)
113ENGINE = InnoDB;
114
115
116SET SQL_MODE=@OLD_SQL_MODE;
117SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
118SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;