Immo

immo.sql

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;