112 lines
2.5 KiB
MySQL
112 lines
2.5 KiB
MySQL
|
-- Tabulka vyucujici
|
|||
|
|
CREATE TABLE vyucujici(
|
|||
|
|
id int NOT NULL primary key,
|
|||
|
|
jmeno varchar(100) NOT NULL,
|
|||
|
|
prijmeni varchar(100) NOT NULL);
|
|||
|
|
|
|||
|
|
INSERT INTO vyucujici (id, jmeno, prijmeni)
|
|||
|
|
VALUES
|
|||
|
|
(1, 'Jan', 'Rychta<EFBFBD><EFBFBD>k'),
|
|||
|
|
(2, 'Martin', 'Sv<EFBFBD>zan<EFBFBD>'),
|
|||
|
|
(3, 'Tom<EFBFBD><EFBFBD> ', 'Pl<EFBFBD>cal'),
|
|||
|
|
(4, 'Milan', 'Chrastil');
|
|||
|
|
|
|||
|
|
-- Tabulka ucebny
|
|||
|
|
CREATE TABLE ucebny(
|
|||
|
|
id int NOT NULL primary key,
|
|||
|
|
oznaceni varchar(5) NOT NULL,
|
|||
|
|
patro numeric(18, 0) NOT NULL,
|
|||
|
|
id_vyucujici_zodpovedny int NULL);
|
|||
|
|
|
|||
|
|
INSERT INTO ucebny
|
|||
|
|
(id, oznaceni, patro, id_vyucujici_zodpovedny)
|
|||
|
|
VALUES
|
|||
|
|
(1, 'ICT1', 2, 1),
|
|||
|
|
(2, 'ICT2', 2, 1),
|
|||
|
|
(3, 'ICT3', 2, 4),
|
|||
|
|
(4, 'ICT4', 2, NULL),
|
|||
|
|
(5, 'ICT5', 2, 2);
|
|||
|
|
|
|||
|
|
-- Tabulka vybaveni_uceben
|
|||
|
|
CREATE TABLE vybaveni_uceben(
|
|||
|
|
id int NOT NULL primary key,
|
|||
|
|
id_ucebny int NOT NULL,
|
|||
|
|
nazev varchar(100) NOT NULL,
|
|||
|
|
evidencni_kod varchar(10) NOT NULL);
|
|||
|
|
|
|||
|
|
-- Tabulka vybaveni_uceben
|
|||
|
|
INSERT INTO vybaveni_uceben
|
|||
|
|
(id, id_ucebny , nazev, evidencni_kod )
|
|||
|
|
VALUES
|
|||
|
|
(1, 1, 'projektor', 'p42'),
|
|||
|
|
(2, 1, 'tabule', 't58'),
|
|||
|
|
(3, 2, 'televizor', 'tv15'),
|
|||
|
|
(4, 2, 'televizor', 'tv17'),
|
|||
|
|
(5, 3, 'projektor', 'p12'),
|
|||
|
|
(6, 3, 'tabule', 't57'),
|
|||
|
|
(7, 4, 'projektor', 'p38'),
|
|||
|
|
(8, 4, 'tabule', 't51'),
|
|||
|
|
(9, 5, 'projektor', 'p89'),
|
|||
|
|
(10, 5, 'tabule', 't46');
|
|||
|
|
|
|||
|
|
-- Tabulka predmety
|
|||
|
|
CREATE TABLE predmety(
|
|||
|
|
id int NOT NULL primary key,
|
|||
|
|
nazev varchar(100) NOT NULL,
|
|||
|
|
zkratka nchar(3) NOT NULL);
|
|||
|
|
|
|||
|
|
INSERT INTO predmety
|
|||
|
|
(id, nazev, zkratka)
|
|||
|
|
VALUES
|
|||
|
|
(1, 'Programov<EFBFBD>n<EFBFBD>', 'PRG'),
|
|||
|
|
(2, 'V<EFBFBD>voj aplikac<61>', 'VAP'),
|
|||
|
|
(3, 'Opera<EFBFBD>n<EFBFBD> syst<73>my', 'OPS'),
|
|||
|
|
(4, 'Aplika<EFBFBD>n<EFBFBD> software', 'ASW');
|
|||
|
|
|
|||
|
|
-- Tabulka vyucujici_predmety
|
|||
|
|
CREATE TABLE vyucujici_predmety(
|
|||
|
|
id int NOT NULL primary key,
|
|||
|
|
id_predmety int NOT NULL,
|
|||
|
|
id_vyucujici int NOT NULL);
|
|||
|
|
|
|||
|
|
INSERT INTO vyucujici_predmety
|
|||
|
|
(id, id_predmety, id_vyucujici)
|
|||
|
|
VALUES
|
|||
|
|
(1, 1, 4),
|
|||
|
|
(2, 2, 1),
|
|||
|
|
(3, 2, 2),
|
|||
|
|
(4, 3, 1),
|
|||
|
|
(5, 3, 2),
|
|||
|
|
(6, 4, 3);
|
|||
|
|
|
|||
|
|
-- Tabulka rozvrh
|
|||
|
|
CREATE TABLE rozvrh(
|
|||
|
|
id int NOT NULL primary key,
|
|||
|
|
id_vyucujici int NOT NULL,
|
|||
|
|
id_predmety int NOT NULL,
|
|||
|
|
id_ucebny int NOT NULL,
|
|||
|
|
den nchar(2) NULL,
|
|||
|
|
hodina varchar(2) NULL);
|
|||
|
|
|
|||
|
|
INSERT INTO rozvrh (id, id_vyucujici, id_predmety, id_ucebny, den, hodina)
|
|||
|
|
VALUES
|
|||
|
|
(1, 1, 4, 5, '<EFBFBD>t', '1'),
|
|||
|
|
(2, 2, 2, 5, '<EFBFBD>t', '3'),
|
|||
|
|
(3, 2, 2, 5, '<EFBFBD>t', '4'),
|
|||
|
|
(4, 2, 2, 5, 'st', '2'),
|
|||
|
|
(5, 2, 2, 5, 'st', '5'),
|
|||
|
|
(6, 2, 2, 5, 'st', '7'),
|
|||
|
|
(7, 2, 2, 5, 'st', '8'),
|
|||
|
|
(8, 2, 2, 5, 'st', '9'),
|
|||
|
|
(9, 3, 2, 5, '<EFBFBD>t', '5'),
|
|||
|
|
(10, 3, 2, 5, '<EFBFBD>t', '6b'),
|
|||
|
|
(11, 3, 2, 5, 'st', '1'),
|
|||
|
|
(12, 3, 1, 3, 'st', '2'),
|
|||
|
|
(13, 3, 1, 3, 'st', '3'),
|
|||
|
|
(14, 3, 1, 2, 'st', '4'),
|
|||
|
|
(15, 3, 2, 5, 'st', '6a'),
|
|||
|
|
(16, 4, 3, 3, '<EFBFBD>t', '4'),
|
|||
|
|
(17, 4, 3, 3, '<EFBFBD>t', '5'),
|
|||
|
|
(18, 4, 3, 3, 'st', '4'),
|
|||
|
|
(19, 4, 3, 3, 'st', '5');
|