Skip to content

Instantly share code, notes, and snippets.

@d630
Last active July 13, 2018 04:02
Show Gist options
  • Save d630/f6b325dc734c57f112e41373fec3916c to your computer and use it in GitHub Desktop.
Save d630/f6b325dc734c57f112e41373fec3916c to your computer and use it in GitHub Desktop.
Immo
START TRANSACTION;
SET foreign_key_checks=0;
DROP DATABASE IF EXISTS immo;
CREATE DATABASE immo;
USE immo;
-- GRANT ALL ON immo.* TO 'user'@'localhost';
CREATE TABLE manager (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
company_name VARCHAR(40)
);
CREATE TABLE renter (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(20),
last_name VARCHAR(20)
);
CREATE TABLE property (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
sqm DOUBLE,
place VARCHAR(20),
postal_code VARCHAR(10),
location VARCHAR(40),
image VARCHAR(40)
);
CREATE TABLE building (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
sqm DOUBLE,
property_id INTEGER,
street VARCHAR(20),
house_num VARCHAR(5),
floor_total INTEGER,
has_garage BOOLEAN DEFAULT FALSE,
has_lift BOOLEAN DEFAULT FALSE,
image VARCHAR(40),
manager_id INTEGER,
FOREIGN KEY(property_id) REFERENCES property(id),
FOREIGN KEY (manager_id) REFERENCES manager(id)
);
CREATE TABLE apartment (
id INTEGER PRIMARY KEY AUTO_INCREMENT,
building_id INTEGER,
sqm DOUBLE,
floor_num INTEGER,
location VARCHAR(10),
room_total INTEGER,
has_balcony BOOLEAN DEFAULT FALSE,
bathroom_total INTEGER DEFAULT 1,
renter_id INTEGER,
FOREIGN KEY(building_id) REFERENCES building(id),
FOREIGN KEY (renter_id) REFERENCES renter(id)
);
INSERT INTO property
VALUES(NULL, 1674, "Berlin", "12345", "zwischen Muellerstr und Kanal", "p1.jpg"),
(NULL, 345.5, "Berlin", "54321", "Obststr.", "p2.jpg"),
(NULL, 897, "Berlin", "53262", "zwischen Bergstr. und Burgallee", "p3.jpg");
INSERT INTO building
VALUES(NULL, 654.56, 1, "Muellerstr", "6a", 3, FALSE, FALSE, "h1.jpg", 1),
(NULL, 243, 1, "Muellerstr", "6b", 1, TRUE, FALSE, "h2.jpg", 1),
(NULL, 1324, 1, "Muellerstr", "6c", 3, TRUE, TRUE, "h3.jpg", 1),
(NULL, 652, 1, "Maybachufer", "56", 3, TRUE, TRUE, "h4.jpg", 1),
(NULL, 845.5, 2, "Obststr", "89", 3, FALSE, FALSE, "h5.jpg", 1),
(NULL, 1764, 3, "Bergstr", "13", 5, FALSE, TRUE, "h6.png", 1),
(NULL, 764.45, 3, "Burgallee", "67", 3, FALSE, FALSE, "h7.jpg", 1);
INSERT INTO manager
VALUES(NULL, "Jens", "Mueller"),
(NULL, "Birgit", "Scholz"),
(NULL, "Ulrich", "Schmidt");
INSERT INTO renter
VALUES(NULL, "Hans", "Storck"),
(NULL, "Franz", "Ullmann"),
(NULL, "Hugo", "Hurtig"),
(NULL, "Mario", "Lustig"),
(NULL, "Anna", "Winter"),
(NULL, "Amea", "Miller"),
(NULL, "Anton", "Canale"),
(NULL, "Andy", "Alaef"),
(NULL, "Barbara", "Stoklosa"),
(NULL, "Susanne", "Sorge");
INSERT INTO apartment
VALUES (NULL, 1, 72.54, 1, "links", 2, TRUE, 1, 1),
(NULL, 1, 72.54, 1, "rechts", 2, TRUE, 1, 1),
(NULL, 1, 69.23, 3, "mitte", 2, TRUE, 1, 1),
(NULL, 1, 105.43, 4, "links", 2, TRUE, 1, 1),
(NULL, 1, 72.54, 1, "links", 2, TRUE, 1, 1),
(NULL, 1, 72.54, 1, "links", 2, TRUE, 1, 1),
(NULL, 2, 72.54, 1, "links", 2, TRUE, 1, 1),
(NULL, 2, 72.54, 5, "rechts", 2, TRUE, 1, 1),
(NULL, 3, 72.54, 1, "mitte", 2, TRUE, 1, 1),
(NULL, 3, 72.54, 1, "links", 2, TRUE, 1, 1),
(NULL, 3, 72.54, 1, "links", 2, TRUE, 1, 1),
(NULL, 3, 72.54, 1, "links", 2, TRUE, 1, 11),
(NULL, 4, 72.54, 1, "mitte", 2, TRUE, 1, 1),
(NULL, 4, 72.54, 1, "rechts", 2, TRUE, 1, 1);
COMMIT;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment