USE [master]
GO

/****** Object:  Database [Katedra]    Script Date: 25.06.2021 14:19:10 ******/

IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Katedra')
  BEGIN
     -- Close connections to the Katedra database 
    ALTER DATABASE [Katedra] SET  SINGLE_USER WITH ROLLBACK IMMEDIATE
    DROP DATABASE [Katedra]
  END
GO

CREATE DATABASE [Katedra]
 CONTAINMENT = NONE
 ON  PRIMARY 
( NAME = N'Katedra', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Katedra.mdf' , SIZE = 8192KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON 
( NAME = N'Katedra_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\Katedra_log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Katedra].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO

ALTER DATABASE [Katedra] SET ANSI_NULL_DEFAULT OFF 
GO

ALTER DATABASE [Katedra] SET ANSI_NULLS OFF 
GO

ALTER DATABASE [Katedra] SET ANSI_PADDING OFF 
GO

ALTER DATABASE [Katedra] SET ANSI_WARNINGS OFF 
GO

ALTER DATABASE [Katedra] SET ARITHABORT OFF 
GO

ALTER DATABASE [Katedra] SET AUTO_CLOSE OFF 
GO

ALTER DATABASE [Katedra] SET AUTO_SHRINK OFF 
GO

ALTER DATABASE [Katedra] SET AUTO_UPDATE_STATISTICS ON 
GO

ALTER DATABASE [Katedra] SET CURSOR_CLOSE_ON_COMMIT OFF 
GO

ALTER DATABASE [Katedra] SET CURSOR_DEFAULT  GLOBAL 
GO

ALTER DATABASE [Katedra] SET CONCAT_NULL_YIELDS_NULL OFF 
GO

ALTER DATABASE [Katedra] SET NUMERIC_ROUNDABORT OFF 
GO

ALTER DATABASE [Katedra] SET QUOTED_IDENTIFIER OFF 
GO

ALTER DATABASE [Katedra] SET RECURSIVE_TRIGGERS OFF 
GO

ALTER DATABASE [Katedra] SET  DISABLE_BROKER 
GO

ALTER DATABASE [Katedra] SET AUTO_UPDATE_STATISTICS_ASYNC OFF 
GO

ALTER DATABASE [Katedra] SET DATE_CORRELATION_OPTIMIZATION OFF 
GO

ALTER DATABASE [Katedra] SET TRUSTWORTHY OFF 
GO

ALTER DATABASE [Katedra] SET ALLOW_SNAPSHOT_ISOLATION OFF 
GO

ALTER DATABASE [Katedra] SET PARAMETERIZATION SIMPLE 
GO

ALTER DATABASE [Katedra] SET READ_COMMITTED_SNAPSHOT OFF 
GO

ALTER DATABASE [Katedra] SET HONOR_BROKER_PRIORITY OFF 
GO

ALTER DATABASE [Katedra] SET RECOVERY SIMPLE 
GO

ALTER DATABASE [Katedra] SET  MULTI_USER 
GO

ALTER DATABASE [Katedra] SET PAGE_VERIFY CHECKSUM  
GO

ALTER DATABASE [Katedra] SET DB_CHAINING OFF 
GO

ALTER DATABASE [Katedra] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 
GO

ALTER DATABASE [Katedra] SET TARGET_RECOVERY_TIME = 60 SECONDS 
GO

ALTER DATABASE [Katedra] SET DELAYED_DURABILITY = DISABLED 
GO

ALTER DATABASE [Katedra] SET QUERY_STORE = OFF
GO

ALTER DATABASE [Katedra] SET  READ_WRITE 
GO

USE Katedra
GO

DROP TABLE IF EXISTS pracownicy;
DROP TABLE IF EXISTS zespoły;
DROP TABLE IF EXISTS etaty;

CREATE TABLE zespoły (
   id_zesp smallint NOT NULL PRIMARY KEY,
   nazwa nvarchar(20),
   adres nvarchar(20)
);

CREATE TABLE etaty (
   nazwa nvarchar(20) NOT NULL PRIMARY KEY,
   płaca_min money NOT NULL CHECK(płaca_min>500),
   płaca_max money NOT NULL CHECK(płaca_max<=10000)
);

CREATE TABLE pracownicy (
   numer smallint NOT NULL PRIMARY KEY,
   nazwisko nvarchar(20),
   etat nvarchar(20),
   szef smallint,
   pracuje_od DATE,
   płaca_pod NUMERIC(6,2),
   płaca_dod NUMERIC(6,2),
   id_zesp smallint,
   FOREIGN KEY (etat) REFERENCES etaty (nazwa) ON DELETE CASCADE,
   FOREIGN KEY (id_zesp) REFERENCES zespoły (id_zesp) ON DELETE CASCADE 
);

GO

USE Katedra
GO

INSERT INTO zespoły VALUES (10,'Administracja','Piotrowo 3a');
INSERT INTO zespoły VALUES (20,'Bazy danych','Wieżowa 75');
INSERT INTO zespoły VALUES (30,'Sieci komputerowe','Garbary 3');
INSERT INTO zespoły VALUES (40,'Systemy operacyjne','Piotrowo 3a');
INSERT INTO zespoły VALUES (50,'Translatory','Mansfelda 4');

INSERT INTO etaty VALUES ('Stażysta',800,1000);
INSERT INTO etaty VALUES ('Sekretarka',900,1200);
INSERT INTO etaty VALUES ('Asystent',1000,1600);
INSERT INTO etaty VALUES ('Adiunkt',1600,2000);
INSERT INTO etaty VALUES ('Profesor',2000,2500);
INSERT INTO etaty VALUES ('Dyrektor',2500,3200);

INSERT INTO pracownicy VALUES (1000,'Lech','Dyrektor',NULL,'1971-01-01',3160,570,10);
INSERT INTO pracownicy VALUES (1080,'Koliberek','Sekretarka',1000,'1983-02-20',1150,NULL,10);
INSERT INTO pracownicy VALUES (1010,'Podgajny','Profesor',1000,'1975-05-01',2180,420,20);
INSERT INTO pracownicy VALUES (1040,'Rus','Adiunkt',1010,'1979-09-15',1750,NULL,20);
INSERT INTO pracownicy VALUES (1070,'Muszyński','Adiunkt',1010,'1985-05-01',1600,NULL,20);
INSERT INTO pracownicy VALUES (1060,'Misiecki','Asystent',1010,'1985-03-01',1400,NULL,20);
INSERT INTO pracownicy VALUES (1090,'Palusz','Asystent',1040,'1989-09-15',1200,NULL,20);
INSERT INTO pracownicy VALUES (1020,'Delcki','Profesor',1000,'1977-09-01',2050,270,30);
INSERT INTO pracownicy VALUES (1030,'Maleja','Adiunkt',1020,'1968-07-01',1750,NULL,30);
INSERT INTO pracownicy VALUES (1100,'Warski','Asystent',1030,'1987-07-15',1350,NULL,30);
INSERT INTO pracownicy VALUES (1110,'Rajski','Stażysta',1030,'1990-07-01',900,NULL,30);
INSERT INTO pracownicy VALUES (1050,'Lubicz','Adiunkt',1000,'1983-09-01',1780,NULL,40);
INSERT INTO pracownicy VALUES (1120,'Orka','Asystent',1050,'1988-04-01',1350,NULL,40);
INSERT INTO pracownicy VALUES (1130,'Kolski','Stażysta',1050,'1991-09-01',900,NULL,40);

GO