· 8 years ago · Dec 07, 2017, 02:36 PM
1-- MySQL Script generated by MySQL Workbench
2-- Thu Dec 7 14:01:08 2017
3-- Model: New Model Version: 1.0
4-- MySQL Workbench Forward Engineering
5
6SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
7SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
8SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';
9
10-- -----------------------------------------------------
11-- Schema game_rental
12-- -----------------------------------------------------
13DROP SCHEMA IF EXISTS `game_rental` ;
14
15-- -----------------------------------------------------
16-- Schema game_rental
17-- -----------------------------------------------------
18CREATE SCHEMA IF NOT EXISTS `game_rental` ;
19USE `game_rental` ;
20
21-- -----------------------------------------------------
22-- Table `game_rental`.`platform`
23-- -----------------------------------------------------
24DROP TABLE IF EXISTS `game_rental`.`platform` ;
25
26CREATE TABLE IF NOT EXISTS `game_rental`.`platform` (
27 `platform_id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,
28 `name` VARCHAR(25) NOT NULL,
29 PRIMARY KEY (`platform_id`))
30ENGINE = InnoDB
31DEFAULT CHARACTER SET = utf8;
32
33
34-- -----------------------------------------------------
35-- Table `game_rental`.`member`
36-- -----------------------------------------------------
37DROP TABLE IF EXISTS `game_rental`.`member` ;
38
39CREATE TABLE IF NOT EXISTS `game_rental`.`member` (
40 `member_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
41 `first_name` VARCHAR(45) NOT NULL,
42 `last_name` VARCHAR(45) NOT NULL,
43 `email` VARCHAR(50) NOT NULL,
44 PRIMARY KEY (`member_id`))
45ENGINE = InnoDB
46DEFAULT CHARACTER SET = utf8;
47
48
49-- -----------------------------------------------------
50-- Table `game_rental`.`game`
51-- -----------------------------------------------------
52DROP TABLE IF EXISTS `game_rental`.`game` ;
53
54CREATE TABLE IF NOT EXISTS `game_rental`.`game` (
55 `game_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
56 `title` VARCHAR(255) NOT NULL,
57 `publisher` VARCHAR(45) NOT NULL,
58 `rental_duration` TINYINT UNSIGNED NOT NULL DEFAULT 3,
59 `rental_rate` DECIMAL(4,2) NOT NULL DEFAULT 5.00,
60 `replacement_cost` DECIMAL(5,2) NOT NULL DEFAULT 50.00,
61 PRIMARY KEY (`game_id`))
62ENGINE = InnoDB
63DEFAULT CHARACTER SET = utf8;
64
65
66-- -----------------------------------------------------
67-- Table `game_rental`.`game_platform`
68-- -----------------------------------------------------
69DROP TABLE IF EXISTS `game_rental`.`game_platform` ;
70
71CREATE TABLE IF NOT EXISTS `game_rental`.`game_platform` (
72 `game_id` SMALLINT UNSIGNED NOT NULL,
73 `platform_id` TINYINT UNSIGNED NOT NULL,
74 PRIMARY KEY (`game_id`, `platform_id`),
75 CONSTRAINT `fk_game_id_plt`
76 FOREIGN KEY (`game_id`)
77 REFERENCES `game_rental`.`game` (`game_id`)
78 ON DELETE RESTRICT
79 ON UPDATE CASCADE,
80 CONSTRAINT `fk_platform_id`
81 FOREIGN KEY (`platform_id`)
82 REFERENCES `game_rental`.`platform` (`platform_id`)
83 ON DELETE RESTRICT
84 ON UPDATE CASCADE)
85ENGINE = InnoDB
86DEFAULT CHARACTER SET = utf8;
87
88CREATE INDEX `fk_platform_id` ON `game_rental`.`game_platform` (`platform_id` ASC);
89
90
91-- -----------------------------------------------------
92-- Table `game_rental`.`inventory`
93-- -----------------------------------------------------
94DROP TABLE IF EXISTS `game_rental`.`inventory` ;
95
96CREATE TABLE IF NOT EXISTS `game_rental`.`inventory` (
97 `inventory_id` MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
98 `game_id` SMALLINT UNSIGNED NOT NULL,
99 PRIMARY KEY (`inventory_id`),
100 CONSTRAINT `fk_game_id_inv`
101 FOREIGN KEY (`game_id`)
102 REFERENCES `game_rental`.`game` (`game_id`)
103 ON DELETE RESTRICT
104 ON UPDATE CASCADE)
105ENGINE = InnoDB
106DEFAULT CHARACTER SET = utf8;
107
108CREATE INDEX `idx_fk_game_id` ON `game_rental`.`inventory` (`game_id` ASC);
109
110
111-- -----------------------------------------------------
112-- Table `game_rental`.`rental`
113-- -----------------------------------------------------
114DROP TABLE IF EXISTS `game_rental`.`rental` ;
115
116CREATE TABLE IF NOT EXISTS `game_rental`.`rental` (
117 `rental_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
118 `rental_date` DATETIME NOT NULL,
119 `inventory_id` MEDIUMINT UNSIGNED NOT NULL,
120 `member_id` SMALLINT UNSIGNED NOT NULL,
121 `return_date` DATETIME NULL DEFAULT NULL,
122 `staff_id` TINYINT UNSIGNED NOT NULL,
123 PRIMARY KEY (`rental_id`),
124 CONSTRAINT `fk_inventory_id`
125 FOREIGN KEY (`inventory_id`)
126 REFERENCES `game_rental`.`inventory` (`inventory_id`)
127 ON DELETE RESTRICT
128 ON UPDATE CASCADE,
129 CONSTRAINT `fk_member_id`
130 FOREIGN KEY (`member_id`)
131 REFERENCES `game_rental`.`member` (`member_id`)
132 ON DELETE RESTRICT
133 ON UPDATE CASCADE)
134ENGINE = InnoDB
135DEFAULT CHARACTER SET = utf8;
136
137CREATE INDEX `idx_fk_inventory_id` ON `game_rental`.`rental` (`inventory_id` ASC);
138
139CREATE INDEX `idx_fk_member_id_rent` ON `game_rental`.`rental` (`member_id` ASC);
140
141
142-- -----------------------------------------------------
143-- Table `game_rental`.`payment`
144-- -----------------------------------------------------
145DROP TABLE IF EXISTS `game_rental`.`payment` ;
146
147CREATE TABLE IF NOT EXISTS `game_rental`.`payment` (
148 `payment_id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
149 `member_id` SMALLINT UNSIGNED NOT NULL,
150 `rental_id` INT UNSIGNED NOT NULL,
151 `amount` DECIMAL(5,2) NOT NULL,
152 `payment_date` DATETIME NOT NULL,
153 PRIMARY KEY (`payment_id`),
154 CONSTRAINT `fk_rental_id`
155 FOREIGN KEY (`rental_id`)
156 REFERENCES `game_rental`.`rental` (`rental_id`)
157 ON DELETE SET NULL
158 ON UPDATE CASCADE,
159 CONSTRAINT `fk_member_id`
160 FOREIGN KEY (`member_id`)
161 REFERENCES `game_rental`.`member` (`member_id`)
162 ON DELETE RESTRICT
163 ON UPDATE CASCADE)
164ENGINE = InnoDB
165DEFAULT CHARACTER SET = utf8;
166
167CREATE INDEX `idx_fk_member_id_pay` ON `game_rental`.`payment` (`member_id` ASC);
168
169CREATE INDEX `fk_rental_id` ON `game_rental`.`payment` (`rental_id` ASC);
170
171
172SET SQL_MODE=@OLD_SQL_MODE;
173SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
174SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;