create table Actors
(
ActorID int not null,
ActorName varchar(255) null,
ActorNotes varchar(255) null,
primary key (ActorID)
);
create table Clues
(
ClueID int not null,
Description varchar(255) null,
primary key (ClueID)
);
create table Events
(
EventID int auto_increment
primary key,
EventName varchar(255) null,
EventTimeStart time null,
EventTimeEnd time null
)
auto_increment = 4;
create table Rooms
(
RoomID int not null,
RoomName varchar(255) null,
primary key (RoomID)
);
create table ActorRoomTime
(
ARTID int auto_increment
primary key,
ActorID int null,
RoomID int null,
TimeStart time null,
TimeEnd time null,
constraint ActorRoomTime_ibfk_1
foreign key (ActorID) references Actors (ActorID),
constraint ActorRoomTime_ibfk_2
foreign key (RoomID) references Rooms (RoomID)
)
auto_increment = 13;
create index ActorID
on ActorRoomTime (ActorID);
create index RoomID
on ActorRoomTime (RoomID);
create table RoomClue
(
RCID int auto_increment
primary key,
RoomID int null,
ClueID int null,
constraint RoomClue_ibfk_1
foreign key (RoomID) references Rooms (RoomID),
constraint RoomClue_ibfk_2
foreign key (ClueID) references Clues (ClueID)
)
auto_increment = 9;
create index ClueID
on RoomClue (ClueID);
create index RoomID
on RoomClue (RoomID);
create table Sorten
(
SortID int not null,
Name varchar(255) null,
primary key (SortID)
);
create table Eisbecher
(
BecherID int not null,
SortenID int null,
AnzKugeln int null,
primary key (BecherID),
constraint Eisbecher_ibfk_1
foreign key (SortenID) references Sorten (SortID)
);
create index SortenID
on Eisbecher (SortenID);
create table Weapons
(
WeaponID int not null,
WeaponName varchar(255) null,
primary key (WeaponID)
);
create table RoomWeapons
(
RWID int auto_increment
primary key,
WeaponID int null,
RoomID int null,
constraint RoomWeapons_ibfk_1
foreign key (WeaponID) references Weapons (WeaponID),
constraint RoomWeapons_ibfk_2
foreign key (RoomID) references Rooms (RoomID)
)
auto_increment = 6;
INSERT INTO Weapons (WeaponID, WeaponName) VALUES (1, 'Küchenmesser');
INSERT INTO Weapons (WeaponID, WeaponName) VALUES (2, 'Vase');
INSERT INTO Weapons (WeaponID, WeaponName) VALUES (3, 'Schührhaken');
INSERT INTO Weapons (WeaponID, WeaponName) VALUES (4, 'Mistgabel');
INSERT INTO Weapons (WeaponID, WeaponName) VALUES (5, 'Antiker Dolch');
INSERT INTO Rooms (RoomID, RoomName) VALUES (1, 'Esszimmer');
INSERT INTO Rooms (RoomID, RoomName) VALUES (2, 'Kaminzimmer');
INSERT INTO Rooms (RoomID, RoomName) VALUES (3, 'Garten');
INSERT INTO Rooms (RoomID, RoomName) VALUES (4, 'Herrenzimmer');
INSERT INTO Rooms (RoomID, RoomName) VALUES (5, 'Schalfzimmer');
INSERT INTO Rooms (RoomID, RoomName) VALUES (6, 'Küche');
INSERT INTO Rooms (RoomID, RoomName) VALUES (7, 'Vorratskeller');
INSERT INTO Rooms (RoomID, RoomName) VALUES (8, 'Familiengruft');
INSERT INTO Clues (ClueID, Description) VALUES (1, 'Eine Gestalt mit Hut kam nach dem Essen aus dem Herrenzimmer');
INSERT INTO Clues (ClueID, Description) VALUES (2, 'Fußspuhren von flachen schuhen');
INSERT INTO Clues (ClueID, Description) VALUES (3, 'Auf der Artbeitsplatte sind Blutspuhren');
INSERT INTO Clues (ClueID, Description) VALUES (4, 'Unter dem Bett liegt etwas ... mit Blutspuren');
INSERT INTO Clues (ClueID, Description) VALUES (5, 'Zwischen 18 und 18.30 waren seltsame geräusche zu hören');
INSERT INTO Clues (ClueID, Description) VALUES (6, 'Der Raum wurde nach dem Essen nicht mehr betreten');
INSERT INTO Clues (ClueID, Description) VALUES (7, 'Eine Vase wurde umgeschmissen');
INSERT INTO Clues (ClueID, Description) VALUES (8, 'Nachts war ein heulen zu hören');
INSERT INTO Actors (ActorID, ActorName, ActorNotes) VALUES (1, 'Herman Glubsch', 'Trägt einen Hut');
INSERT INTO Actors (ActorID, ActorName, ActorNotes) VALUES (2, 'Doctor Peterson', 'Trägt einen Hut');
INSERT INTO Actors (ActorID, ActorName, ActorNotes) VALUES (3, 'Mary Ploppins', 'Kindermädchen, Trägt hohe Schuhe');
INSERT INTO Actors (ActorID, ActorName, ActorNotes) VALUES (4, 'Sir Walter Muffin', 'RIP');
INSERT INTO Actors (ActorID, ActorName, ActorNotes) VALUES (5, 'Willy William McDougal', 'Gärtner');
INSERT INTO Actors (ActorID, ActorName, ActorNotes) VALUES (6, 'Agatha Christian', 'Trägt einen Hut, , Trägt hohe Schuhe');
INSERT INTO Actors (ActorID, ActorName, ActorNotes) VALUES (7, 'James Blonde', 'Trägt keine Uhr');
INSERT INTO Actors (ActorID, ActorName, ActorNotes) VALUES (8, 'Florina Rey', 'Sammelt Antiquitäten, Trägt einen Hut');
INSERT INTO Actors (ActorID, ActorName, ActorNotes) VALUES (9, 'Hubert Klein', 'Trägt hohe Schuhe, Trägt keine Uhr');
INSERT INTO Actors (ActorID, ActorName, ActorNotes) VALUES (10, 'Mecky Messer', 'Trägt einen Hut');
INSERT INTO RoomWeapons (RWID, WeaponID, RoomID) VALUES (1, 1, 1);
INSERT INTO RoomWeapons (RWID, WeaponID, RoomID) VALUES (2, 2, 5);
INSERT INTO RoomWeapons (RWID, WeaponID, RoomID) VALUES (3, 3, 2);
INSERT INTO RoomWeapons (RWID, WeaponID, RoomID) VALUES (4, 4, 3);
INSERT INTO RoomWeapons (RWID, WeaponID, RoomID) VALUES (5, 5, 4);
INSERT INTO RoomClue (RCID, RoomID, ClueID) VALUES (1, 2, 2);
INSERT INTO RoomClue (RCID, RoomID, ClueID) VALUES (2, 4, 1);
INSERT INTO RoomClue (RCID, RoomID, ClueID) VALUES (3, 3, 8);
INSERT INTO RoomClue (RCID, RoomID, ClueID) VALUES (4, 6, 3);
INSERT INTO RoomClue (RCID, RoomID, ClueID) VALUES (5, 8, 6);
INSERT INTO RoomClue (RCID, RoomID, ClueID) VALUES (6, 1, 4);
INSERT INTO RoomClue (RCID, RoomID, ClueID) VALUES (7, 7, 5);
INSERT INTO RoomClue (RCID, RoomID, ClueID) VALUES (8, 5, 7);
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (1, 1, 1, '17:50:00', '19:00:00');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (2, 2, 1, '18:00:00', '18:45:00');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (3, 6, 1, '18:05:00', '18:35:00');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (4, 8, 1, '18:00:00', '19:30:00');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (5, 9, 1, '18:10:00', '18:20:00');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (6, 10, 1, '18:20:00', '19:45:00');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (7, 3, 7, '17:55:00', '18:25:00');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (8, 7, 7, '17:55:00', '18:25:00');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (9, 9, 4, '18:25:00', '18:30:00');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (10, 5, 3, '14:00:00', '23:00:00');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (11, 4, 8, '07:00:00', '23:59:59');
INSERT INTO ActorRoomTime (ARTID, ActorID, RoomID, TimeStart, TimeEnd) VALUES (12, 4, 2, '00:00:01', '06:59:00');
INSERT INTO Events (EventID, EventName, EventTimeStart, EventTimeEnd) VALUES (1, 'Mittagsschalaf', '12:00:00', '12:30:00');
INSERT INTO Events (EventID, EventName, EventTimeStart, EventTimeEnd) VALUES (2, 'Abendessen', '18:00:00', '18:30:00');
INSERT INTO Events (EventID, EventName, EventTimeStart, EventTimeEnd) VALUES (3, 'Empfang', '19:30:00', '22:00:00');
select * from Rooms;
Telegram: DB developers channel