pt edy

USE Intreprinderi;


CREATE TABLE ANGAJATI(
    IdAngajat INT PRIMARY KEY NOT NULL,
    Nume nvarchar(30),
    Preunume nvarchar(30),
    DataNasterii DATE,
    Adresa nvarchar(30),
    Functie nvarchar(30),
    Salariu FLOAT,
    IdSectie INT
);

CREATE TABLE SECTII(
    IdSectie INT PRIMARY KEY NOT NULL,
    Nume nvarchar(50)
);

ALTER TABLE ANGAJATI ADD CONSTRAINT FK_ANGAJATI_SECTII FOREIGN KEY(IdSectie) REFERENCES SECTII(IdSectie);

CREATE TABLE SECRETARE(
    IdAngajat INT PRIMARY KEY NOT NULL,
    VitezaRedactare FLOAT
)
;
ALTER TABLE ANGAJATI ADD CONSTRAINT FK_ANGAJATI_SECRETARE FOREIGN KEY(IdAngajat) REFERENCES SECRETARE(IdAngajat);

CREATE TABLE INGINERI(
    IdAngajat INT PRIMARY KEY NOT NULL,
    Specialitatea nvarchar(50)
)
;
ALTER TABLE ANGAJATI ADD CONSTRAINT FK_ANGAJATI_INGINERI FOREIGN KEY(IdAngajat) REFERENCES INGINERI(IdAngajat);
CREATE TABLE DEPENDENTI(
    IdAngajat INT PRIMARY KEY NOT NULL,
    Nume nvarchar(50) NOT NULL,
    Prenume nvarchar(50) NOT NULL,
    DataNasterii DATE,
    GradRudenie INT
)
;
ALTER TABLE ANGAJATI ADD CONSTRAINT FK_ANGAJATI_DEPENDENTI FOREIGN KEY(IdAngajat) REFERENCES DEPENDENTI(IdAngajat);

CREATE TABLE ACHIZITII(
    IdAchizitie INT PRIMARY KEY NOT NULL,
    IdFurnizor INT,
    IdComponenta INT,
    IdAchizitor INT,
    DATA DATE,
    NrComponente INT,
    PretUnitar FLOAT
)
;
ALTER TABLE ACHIZITII ADD CONSTRAINT FK_ACHIZITII_ANGAJATI FOREIGN KEY(IdAchizitor) REFERENCES ANGAJATI(IdAngajat);
CREATE TABLE VANZARI(
    IdVanzare INT PRIMARY KEY NOT NULL,
    IdProdus INT,
    IdClient INT,
    IdVanzator INT,
    DATA DATE,
    NrProduse INT
);

ALTER TABLE VANZARI ADD CONSTRAINT FK_VANZARI_ANGAJATI FOREIGN KEY(IdVanzator) REFERENCES ANGAJATI(IdAngajat);

CREATE TABLE ACTIVITATI(
    IdProiect INT PRIMARY KEY NOT NULL,
    IdAngajat INT NOT NULL,
    Ore INT
);

ALTER TABLE ACTIVITATI ADD CONSTRAINT FK_ACTIVITATI_ANGAJATI FOREIGN KEY(IdAngajat) REFERENCES ANGAJATI(IdAngajat);

CREATE TABLE FURNIZORI(
    IdFurnizor INT PRIMARY KEY NOT NULL,
    Nume nvarchar(50),
    Prenume nvarchar(50),
    Adresa nvarchar(30)
);

ALTER TABLE ACHIZITII ADD CONSTRAINT FK_ACHIZITII_FUNIZARI FOREIGN KEY(IdFurnizor) REFERENCES FURNIZORI(IdFurnizor);

CREATE TABLE COMPONENTE(
    IdComponenta INT PRIMARY KEY NOT NULL,
    Denumire nvarchar(50),
    Descriere nvarchar(500)
);

ALTER TABLE ACHIZITII ADD CONSTRAINT FK_ACHIZITII_COMPONENTE FOREIGN KEY(IdComponenta) REFERENCES COMPONENTE(IdComponenta);

CREATE TABLE COMPOZITII(
    IdComponenta INT PRIMARY KEY NOT NULL,
    IdProdus INT NOT NULL,
    NrComponete INT
);

ALTER TABLE COMPOZITII ADD CONSTRAINT FK_COMPOZITII_COMPONENTE FOREIGN KEY(IdComponenta) REFERENCES COMPONENTE(IdComponenta);

CREATE TABLE PRODUSE(
    IdProdus INT PRIMARY KEY NOT NULL,
    Denumire nvarchar(50),
    Descriere nvarchar(500)
);

ALTER TABLE COMPOZITII ADD CONSTRAINT FK_PRODUSE_COMPOZITII FOREIGN KEY(IdProdus) REFERENCES PRODUSE(IdProdus);

ALTER TABLE VANZARI ADD CONSTRAINT FK_PRODUSE_VANZARI FOREIGN KEY(IdProdus) REFERENCES Produse(IdProdus);

CREATE TABLE CLIENTI(
    IdClient INT PRIMARY KEY NOT NULL,
    Nume nvarchar(50),
    Prenume nvarchar(50),
    Adresa nvarchar(30)
);

ALTER TABLE VANZARI ADD CONSTRAINT FK_CLIENTI_PRODUSE FOREIGN KEY(IdClient) REFERENCES CLIENTI(IdClient);

CREATE TABLE PROIECTIE(
    IdProiect INT PRIMARY KEY NOT NULL,
    Denumire nvarchar(50),
    Termen DATE,
    Buget FLOAT
);

ALTER TABLE ACTIVITATI ADD CONSTRAINT FK_PROIECTIE_ACTIVITATI FOREIGN KEY(IdProiect) REFERENCES PROIECTIE(IdProiect);

INSERT INTO SECTII (IdSectie,Nume,Buget) VALUES (5, 'Tineret',700);
INSERT INTO SECRETARE (IdAngajat,VitezaRedactare) VALUES (5,99.9);
ALTER TABLE SECTII ADD Buget FLOAT;
INSERT INTO INGINERI VALUES(5,'Profesor');

INSERT INTO FURNIZORI VALUES(1,'Pop','Adrian','1 Decembrie');
INSERT INTO FURNIZORI VALUES(2,'Popescu','Adi','Badiza');
INSERT INTO FURNIZORI VALUES(3,'Nane','Stefan','Mihai Viteazu');
INSERT INTO FURNIZORI VALUES(4,'Mandea','Eduard','Stefan cel Mare');
INSERT INTO FURNIZORI VALUES(5,'Oprea','Ingrid','1 Aprilie');

INSERT INTO CLIENTI VALUES(1,'Release','Today','2 Decembrie');
INSERT INTO CLIENTI VALUES(2,'Popescu','Rena','2 Decembrie');
INSERT INTO CLIENTI VALUES(3,'Georgescu','Ion','35 Decebal');
INSERT INTO CLIENTI VALUES(4,'Rick','Morty','Impozitrag 25');
INSERT INTO CLIENTI VALUES(5,'Monte','Carla','Dumbrava 38');

INSERT INTO ANGAJATI (IdAngajat,Nume,Preunume,DataNasterii,IdSectie) VALUES (4,'Motanicu','Ricu','1988-06-06',1);

SELECT * FROM FURNIZORI
UNION
SELECT * FROM CLIENTI