G64C3npy

1Bijlage 1: SQL scripts
2-- MySQL Workbench Forward Engineering
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='TRADITIONAL,ALLOW_INVALID_DATES';
6-- SET @SCHEMA_DATABASE_NAME = ‘YOUR_SCHEMA_NAME’;
7-- -----------------------------------------------------
8-- Schema
9-- -----------------------------------------------------
10-- DROP SCHEMA IF EXISTS ‘YOUR_SCHEMA_NAME’;
11-- -----------------------------------------------------
12-- Schema
13-- -----------------------------------------------------
14-- CREATE SCHEMA IF NOT EXISTS ‘YOUR_SCHEMA_NAME’ NAME DEFAULT CHARACTER SET utf8;
15-- USE ‘YOUR_SCHEMA_NAME’;
16-- -----------------------------------------------------
17-- Table `User`
18-- -----------------------------------------------------
19DROP TABLE IF EXISTS `User`;
20CREATE TABLE IF NOT EXISTS `User` (
21`id` INT NOT NULL AUTO_INCREMENT,
22`name` VARCHAR(10) NOT NULL,
23PRIMARY KEY (`id`))
24ENGINE = InnoDB;
25-- -----------------------------------------------------
26-- Table `Game`
27-- -----------------------------------------------------
28DROP TABLE IF EXISTS `Game`;
29CREATE TABLE IF NOT EXISTS `Game` (
30`id` INT NOT NULL AUTO_INCREMENT,
31`name` VARCHAR(20) NOT NULL,
32PRIMARY KEY (`id`))
33ENGINE = InnoDB;
34-- -----------------------------------------------------
35-- Table `Highscore`
36-- -----------------------------------------------------
37DROP TABLE IF EXISTS `Highscore`;
38CREATE TABLE IF NOT EXISTS `Highscore` (
39`User_id` INT NOT NULL,
40`Game_id` INT NOT NULL,
41`score` INT NOT NULL,
42PRIMARY KEY (`User_id`, `Game_id`),
43INDEX `fk_User_has_Game_Game1_idx` (`Game_id` ASC),
44INDEX `fk_User_has_Game_User_idx` (`User_id` ASC),
45CONSTRAINT `fk_User_has_Game_User`
46FOREIGN KEY (`User_id`)
47REFERENCES `User` (`id`)
48ON DELETE NO ACTION
49ON UPDATE NO ACTION,
50CONSTRAINT `fk_User_has_Game_Game1`
51FOREIGN KEY (`Game_id`)
52REFERENCES `Game` (`id`)
53ON DELETE NO ACTION
54ON UPDATE NO ACTION)
55ENGINE = InnoDB;
56SET SQL_MODE=@OLD_SQL_MODE;
57SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
58SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
59-- -----------------------------------------------------
60-- Data for table `User`
61-- -----------------------------------------------------
62START TRANSACTION;
63-- USE `ExampleGameDB`;
64INSERT INTO `User` (`id`, `name`) VALUES (1, 'Jack');
65INSERT INTO `User` (`id`, `name`) VALUES (2, 'Mike');
66INSERT INTO `User` (`id`, `name`) VALUES (3, 'Ben');
67INSERT INTO `User` (`id`, `name`) VALUES (4, 'Jur');
68INSERT INTO `User` (`id`, `name`) VALUES (5, 'Eric');
69COMMIT;
70-- -----------------------------------------------------
71-- Data for table `Game`
72-- -----------------------------------------------------
73START TRANSACTION;
74-- USE ‘zhofem’;
75INSERT INTO `Game` (`id`, `name`) VALUES (1, 'My FYS Game');
76INSERT INTO `Game` (`id`, `name`) VALUES (2, 'League of Luminary');
77INSERT INTO `Game` (`id`, `name`) VALUES (3, 'SunCraft');
78COMMIT;
79-- -----------------------------------------------------
80-- Data for table `Highscore`
81-- -----------------------------------------------------
82START TRANSACTION;
83-- USE ‘zhofem’;
84INSERT INTO `Highscore` (`User_id`, `Game_id`, `score`) VALUES (5, 1, 1000);
85INSERT INTO `Highscore` (`User_id`, `Game_id`, `score`) VALUES (4, 1, 1200);
86INSERT INTO `Highscore` (`User_id`, `Game_id`, `score`) VALUES (2, 1, 800);
87INSERT INTO `Highscore` (`User_id`, `Game_id`, `score`) VALUES (5, 3, 16000);
88INSERT INTO `Highscore` (`User_id`, `Game_id`, `score`) VALUES (2, 3, 4444);
89INSERT INTO `Highscore` (`User_id`, `Game_id`, `score`) VALUES (4, 2, 12);
90COMMIT;