115 lines
2.6 KiB
SQL
115 lines
2.6 KiB
SQL
SET ANSI_NULLS ON;
|
|
|
|
SET QUOTED_IDENTIFIER ON;
|
|
|
|
-- Tabulka vyucujici
|
|
CREATE TABLE vyucujici(
|
|
id int NOT NULL primary key,
|
|
jmeno nvarchar(100) NOT NULL,
|
|
prijmeni nvarchar(100) NOT NULL);
|
|
|
|
INSERT vyucujici (id, jmeno, prijmeni)
|
|
VALUES
|
|
(1, N'Jan', N'Rychtařík'),
|
|
(2, N'Martin', N'Svázaný'),
|
|
(3, N'Tomáš ', N'Plácal'),
|
|
(4, N'Milan', N'Chrastil');
|
|
|
|
-- Tabulka ucebny
|
|
CREATE TABLE ucebny(
|
|
id int NOT NULL primary key,
|
|
oznaceni nvarchar(5) NOT NULL,
|
|
patro numeric(18, 0) NOT NULL,
|
|
id_vyucujici_zodpovedny int NULL);
|
|
|
|
INSERT ucebny
|
|
(id, oznaceni, patro, id_vyucujici_zodpovedny)
|
|
VALUES
|
|
(1, N'ICT1', 2, 1),
|
|
(2, N'ICT2', 2, 1),
|
|
(3, N'ICT3', 2, 4),
|
|
(4, N'ICT4', 2, NULL),
|
|
(5, N'ICT5', 2, 2);
|
|
|
|
-- Tabulka vybaveni_uceben
|
|
CREATE TABLE vybaveni_uceben(
|
|
id int NOT NULL primary key,
|
|
id_ucebny int NOT NULL,
|
|
nazev nvarchar(100) NOT NULL,
|
|
evidencni_kod nvarchar(10) NOT NULL);
|
|
|
|
-- Tabulka vybaveni_uceben
|
|
INSERT vybaveni_uceben
|
|
(id, id_ucebny , nazev, evidencni_kod )
|
|
VALUES
|
|
(1, 1, N'projektor', N'p42'),
|
|
(2, 1, N'tabule', N't58'),
|
|
(3, 2, N'televizor', N'tv15'),
|
|
(4, 2, N'televizor', N'tv17'),
|
|
(5, 3, N'projektor', N'p12'),
|
|
(6, 3, N'tabule', N't57'),
|
|
(7, 4, N'projektor', N'p38'),
|
|
(8, 4, N'tabule', N't51'),
|
|
(9, 5, N'projektor', N'p89'),
|
|
(10, 5, N'tabule', N't46');
|
|
|
|
-- Tabulka predmety
|
|
CREATE TABLE predmety(
|
|
id int NOT NULL primary key,
|
|
nazev nvarchar(100) NOT NULL,
|
|
zkratka nchar(3) NOT NULL);
|
|
|
|
INSERT predmety
|
|
(id, nazev, zkratka)
|
|
VALUES
|
|
(1, N'Programování', N'PRG'),
|
|
(2, N'Vývoj aplikací', N'VAP'),
|
|
(3, N'Operační systémy', N'OPS'),
|
|
(4, N'Aplikační software', N'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 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 nvarchar(2) NULL);
|
|
|
|
INSERT rozvrh (id, id_vyucujici, id_predmety, id_ucebny, den, hodina)
|
|
VALUES
|
|
(1, 1, 4, 5, N'čt', N'1'),
|
|
(2, 2, 2, 5, N'út', N'3'),
|
|
(3, 2, 2, 5, N'út', N'4'),
|
|
(4, 2, 2, 5, N'st', N'2'),
|
|
(5, 2, 2, 5, N'st', N'5'),
|
|
(6, 2, 2, 5, N'st', N'7'),
|
|
(7, 2, 2, 5, N'st', N'8'),
|
|
(8, 2, 2, 5, N'st', N'9'),
|
|
(9, 3, 2, 5, N'út', N'5'),
|
|
(10, 3, 2, 5, N'út', N'6b'),
|
|
(11, 3, 2, 5, N'st', N'1'),
|
|
(12, 3, 1, 3, N'st', N'2'),
|
|
(13, 3, 1, 3, N'st', N'3'),
|
|
(14, 3, 1, 2, N'st', N'4'),
|
|
(15, 3, 2, 5, N'st', N'6a'),
|
|
(16, 4, 3, 3, N'út', N'4'),
|
|
(17, 4, 3, 3, N'út', N'5'),
|
|
(18, 4, 3, 3, N'st', N'4'),
|
|
(19, 4, 3, 3, N'st', N'5');
|