111 lines
2.5 KiB
SQL
111 lines
2.5 KiB
SQL
-- 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øík'),
|
|
(2, 'Martin', 'Svázaný'),
|
|
(3, 'Tomáš ', 'Plá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ání', 'PRG'),
|
|
(2, 'Vývoj aplikací', 'VAP'),
|
|
(3, 'Operaèní systémy', 'OPS'),
|
|
(4, 'Aplikaèní 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, 'èt', '1'),
|
|
(2, 2, 2, 5, 'út', '3'),
|
|
(3, 2, 2, 5, 'ú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, 'út', '5'),
|
|
(10, 3, 2, 5, 'ú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, 'út', '4'),
|
|
(17, 4, 3, 3, 'út', '5'),
|
|
(18, 4, 3, 3, 'st', '4'),
|
|
(19, 4, 3, 3, 'st', '5');
|