CREATE DATABASE GrafikUczelniZmiana;

use GrafikUczelniZmiana;

CREATE TABLE Budynki (
	KodBudynku nvarchar (3) NOT NULL ,
	NazwaBudynku nvarchar (25) NULL ,
	LiczbaKondygnacji smallint NULL ,
	Winda bit NOT NULL DEFAULT 0 ,
	Parking bit NOT NULL DEFAULT 0 
);

CREATE TABLE Dziedziny (
	IDDziedziny nvarchar (10) NOT NULL ,
	OpisDziedziny nvarchar (75) NULL ,
	IDWydzialu int NULL DEFAULT 0 
);

CREATE TABLE Sale (
	IDSali int NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	KodBudynku nvarchar (3) NULL ,
	DostTelefonu bit NOT NULL DEFAULT 0 
);

CREATE TABLE Zajecia (
	IDZajec int NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	IDPrzedmiotu int NULL DEFAULT 0 ,
	IDSali int NULL DEFAULT 0 ,
	PunktyEdukacyjne smallint NULL DEFAULT 0 ,
    DataRozpoczecia date NULL ,
	GodzinaRozpoczecia time NULL ,
	CzasTrwania smallint NULL DEFAULT 0 ,
	PlanNaPoniedzialek bit NOT NULL DEFAULT 0 ,
	PlanNaWtorek bit NOT NULL DEFAULT 0 ,
	PlanNaSrode bit NOT NULL DEFAULT 0 ,
	PlanNaCzwartek bit NOT NULL DEFAULT 0 ,
	PlanNaPiatek bit NOT NULL DEFAULT 0 ,
	PlanNaSobote bit NOT NULL DEFAULT 0
);

CREATE TABLE Wydzialy (
	IDWydzialu int NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	NazwaWydzialu nvarchar (50) NULL ,
	KierownikWydzialu int NULL DEFAULT 0 
);

CREATE TABLE Wykladowcy (
	IDPracownika int NOT NULL DEFAULT 0 ,
	Tytul nvarchar (50) NULL ,
	Status nvarchar (12) NULL ,
	Etat bit NOT NULL DEFAULT 0 
);

CREATE TABLE Dziedziny_wykladowcow (
	IDPracownika int NOT NULL ,
	IDDziedziny nvarchar (10) NOT NULL DEFAULT 'KSI' 
);

CREATE TABLE Zajecia_wykladowcow (
	IDZajec int NOT NULL ,
	IDPracownika int NOT NULL 
);

CREATE TABLE Przedmioty_wykladowcow (
	IDPracownika int NOT NULL DEFAULT 0 ,
	IDPrzedmiotu int NOT NULL DEFAULT 0 ,
	OcenaBieglosci real NULL DEFAULT 0 
);

CREATE TABLE Kierunki (
        IDKierunku int NOT NULL ,
        Kierunek nvarchar (20) NULL 
        );

CREATE TABLE Pracownicy (
	IDPracownika int NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	ImiePracownika nvarchar (25) NULL ,
	NazwiskoPracownika nvarchar (25) NULL ,
	AdresPracownika nvarchar (50) NULL ,
	MiastoPracownika nvarchar (30) NULL ,
	StanZamPracownika nvarchar (2) NULL ,
	KodPocztowyPracownika nvarchar (5) NULL ,
	NumKierPracownika nvarchar (5) NULL ,
	TelefonPracownika nvarchar (8) NULL ,
	Wynagrodzenie decimal (15,2) NULL ,
	DataZatrudnienia date NULL ,
	Stanowisko nvarchar (50) NULL 
);

CREATE TABLE Status_zajec_studenta (
	StatusZajec int NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	OpisStatusuZajec nvarchar (50) NULL 
);

CREATE TABLE Grafik_studenta (
	IDStudenta int NOT NULL ,
	IDZajec int NOT NULL ,
	StatusZajec int NULL DEFAULT 0 ,
	Ocena float NULL DEFAULT 0 
);

CREATE TABLE Studenci (
	IDStudenta int NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	ImieStudenta nvarchar (25) NULL ,
	NazwiskoStudenta nvarchar (25) NULL ,
	AdresStudenta nvarchar (50) NULL ,
	MiastoStudenta nvarchar (30) NULL ,
	StanZamStudenta nvarchar (2) NULL ,
	KodPocztowyStudenta nvarchar (5) NULL ,
	NumKierStudenta nvarchar (5) NULL ,
	TelefonStudenta nvarchar (8) NULL ,
	SredniaOcenStudenta float NULL DEFAULT 0 ,
    KierunekStudiow int NULL 
);

CREATE TABLE Przedmioty (
	IDPrzedmiotu int NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	IDDziedziny nvarchar (10) NULL ,
	KodPrzedmiotu nvarchar (8) NULL ,
	NazwaPrzedmiotu nvarchar (50) NULL ,
        Wymagania nvarchar (8) NULL DEFAULT NULL ,
	OpisPrzedmiotu text NULL
);

ALTER TABLE Budynki 
	ADD CONSTRAINT Budynki_PK PRIMARY KEY 
	(
		KodBudynku
	);

CREATE  INDEX LiczbaKondygnacji ON Budynki(LiczbaKondygnacji);

ALTER TABLE Dziedziny 
	ADD CONSTRAINT Dziedziny_PK PRIMARY KEY 
	(
		IDDziedziny
	);

CREATE  INDEX IDWydzialu ON Dziedziny(IDWydzialu);

CREATE  INDEX BuildingNumber ON Sale(KodBudynku);

CREATE  INDEX ZajeciaIDDziedziny ON Zajecia(IDPrzedmiotu);

CREATE  INDEX IDSali ON Zajecia(IDSali);

CREATE  INDEX PracownicyWydzialy ON Wydzialy(KierownikWydzialu);

ALTER TABLE Wykladowcy 
	ADD CONSTRAINT Wykladowcy_PK PRIMARY KEY 
	(
		IDPracownika
	);

ALTER TABLE Dziedziny_wykladowcow 
	ADD CONSTRAINT Wykladowcy_Dziedziny_PK PRIMARY KEY 
	(
		IDPracownika, 
		IDDziedziny
	);

CREATE  INDEX IDDziedzinyWykladowcy_Dziedziny ON Dziedziny_wykladowcow(IDDziedziny);

CREATE  INDEX PracownicyIDWykladowcy_Dziedziny ON Dziedziny_wykladowcow(IDPracownika);

ALTER TABLE Zajecia_wykladowcow 
	ADD CONSTRAINT Wykladowcy_Zajecia_PK PRIMARY KEY 
	(
		IDZajec, 
		IDPracownika
	);

CREATE  INDEX ZajeciaWykladowcy_Zajecia ON Zajecia_wykladowcow(IDZajec);

CREATE  INDEX PracownicyWykladowcy_Zajecia ON Zajecia_wykladowcow(IDPracownika);

ALTER TABLE Przedmioty_wykladowcow 
	ADD CONSTRAINT Wykladowcy_Przedmioty_PK PRIMARY KEY 
	(
		IDPracownika, 
		IDPrzedmiotu
	);

CREATE  INDEX PracownicyIDWykladowcy_Przedmioty ON Przedmioty_wykladowcow(IDPracownika);

CREATE  INDEX PrzedmiotyWykladowcy_Przedmioty ON Przedmioty_wykladowcow(IDPrzedmiotu);

CREATE  INDEX PracownicyKodPocztowy ON Pracownicy(KodPocztowyPracownika);

CREATE  INDEX NumKierStudenta ON Pracownicy(NumKierPracownika);

ALTER TABLE Kierunki 
        ADD CONSTRAINT Kierunki_PK PRIMARY KEY
        (
               IDKierunku
        );

ALTER TABLE Grafik_studenta 
	ADD CONSTRAINT Grafik_studenta_PK PRIMARY KEY 
	(
		IDStudenta, 
		IDZajec
	);

CREATE  INDEX ZajeciaGrafik_studenta ON Grafik_studenta(IDZajec);

CREATE  INDEX Status_zajec_studentaGrafik_studenta ON Grafik_studenta(StatusZajec);

CREATE  INDEX StudenciGrafik_studenta ON Grafik_studenta(IDStudenta);

CREATE  INDEX NumKierStudenta ON Studenci(NumKierStudenta);

CREATE  INDEX KodPocztowyStudenta ON Studenci(KodPocztowyStudenta);

CREATE  INDEX KierunekStudiow ON Studenci(KierunekStudiow);

CREATE  INDEX DziedzinyPrzedmioty ON Przedmioty(IDDziedziny);

CREATE  UNIQUE  INDEX KodPrzedmiotu ON Przedmioty(KodPrzedmiotu);

CREATE  INDEX WymPrzedm ON Przedmioty(Wymagania);

ALTER TABLE Dziedziny 
        ADD CONSTRAINT Dziedziny_FK00 FOREIGN KEY 
        (
                IDWydzialu
        ) REFERENCES Wydzialy (
                IDWydzialu
        );

ALTER TABLE Sale 
        ADD CONSTRAINT Sale_FK00 FOREIGN KEY 
        (
                KodBudynku
        ) REFERENCES Budynki (
                KodBudynku
        );

ALTER TABLE Zajecia 
        ADD CONSTRAINT Zajecia_FK00 FOREIGN KEY 
        (
                IDSali
        ) REFERENCES Sale (
                IDSali
        ),
        ADD CONSTRAINT Zajecia_FK01 FOREIGN KEY 
        (
                IDPrzedmiotu
        ) REFERENCES Przedmioty (
                IDPrzedmiotu
        );

ALTER TABLE Wydzialy 
        ADD CONSTRAINT Wydzialy_FK00 FOREIGN KEY 
        (
                KierownikWydzialu
        ) REFERENCES Pracownicy (
                IDPracownika
        );

ALTER TABLE Wykladowcy 
        ADD CONSTRAINT Wykladowcy_FK00 FOREIGN KEY 
        (
                IDPracownika
        ) REFERENCES Pracownicy (
                IDPracownika
        );

ALTER TABLE Dziedziny_wykladowcow 
        ADD CONSTRAINT Wykladowcy_Dziedziny_FK00 FOREIGN KEY 
        (
                IDDziedziny
        ) REFERENCES Dziedziny (
                IDDziedziny
        ),
        ADD CONSTRAINT Wykladowcy_Dziedziny_FK01 FOREIGN KEY 
        (
                IDPracownika
        ) REFERENCES Wykladowcy (
                IDPracownika
        );

ALTER TABLE Zajecia_wykladowcow 
        ADD CONSTRAINT Wykladowcy_Zajecia_FK00 FOREIGN KEY 
        (
                IDZajec
        ) REFERENCES Zajecia (
                IDZajec
        ),
        ADD CONSTRAINT Wykladowcy_Zajecia_FK01 FOREIGN KEY 
        (
                IDPracownika
        ) REFERENCES Pracownicy (
                IDPracownika
	),
        ADD CONSTRAINT Wykladowcy_Zajecia_FK02 FOREIGN KEY
	(
		IDPracownika
	) REFERENCES Wykladowcy (
		IDPracownika
        );

ALTER TABLE Przedmioty_wykladowcow 
        ADD CONSTRAINT Wykladowcy_Przedmioty_FK00 FOREIGN KEY 
        (
                IDPracownika
        ) REFERENCES Wykladowcy (
                IDPracownika
        ),
        ADD CONSTRAINT Wykladowcy_Przedmioty_FK01 FOREIGN KEY 
        (
                IDPrzedmiotu
        ) REFERENCES Przedmioty (
                IDPrzedmiotu
        );

ALTER TABLE Studenci 
        ADD CONSTRAINT Studenci_FK00 FOREIGN KEY
        (
                KierunekStudiow
        ) REFERENCES Kierunki (
                IDKierunku
        );

ALTER TABLE Grafik_studenta 
        ADD CONSTRAINT Grafik_studenta_FK00 FOREIGN KEY 
        (
                IDZajec
        ) REFERENCES Zajecia (
                IDZajec
        ),
        ADD CONSTRAINT Grafik_studenta_FK01 FOREIGN KEY 
        (
                StatusZajec
        ) REFERENCES Status_zajec_studenta (
                StatusZajec
        ),
        ADD CONSTRAINT Grafik_studenta_FK02 FOREIGN KEY 
        (
                IDStudenta
        ) REFERENCES Studenci (
                IDStudenta
        );

ALTER TABLE Przedmioty 
        ADD CONSTRAINT Przedmioty_FK00 FOREIGN KEY 
        (
                IDDziedziny
        ) REFERENCES Dziedziny (
                IDDziedziny
        ),
        ADD CONSTRAINT Przedmioty_FK01 FOREIGN KEY
	(
		Wymagania
	) REFERENCES Przedmioty (
		KodPrzedmiotu
	);