DROP TABLE dependenti;
CREATE TABLE dependenti(
IdAngajat int NOT NULL,
Nume varchar(20) NOT NULL,
Prenume varchar(20) NOT NULL,
DataN datetime,
GradRudenie int NOT NULL
);

DROP TABLE ingineri;
CREATE TABLE ingineri(
IdAngajat int NOT NULL PRIMARY KEY,
Specialitatea varchar(30) NOT NULL
);

DROP TABLE secretare;
CREATE TABLE secretare(
IdAngajat int NOT NULL PRIMARY KEY,
VitezaRedactare int NOT NULL
);

DROP TABLE sectii;
CREATE TABLE sectii(
IdSectie int NOT NULL PRIMARY KEY,
Nume varchar(30) NOT NULL,
Buget decimal
);

DROP TABLE angajati;
create table angajati(
IDAngajat int NOT NULL PRIMARY KEY,
Nume varchar(20) NOT NULL,
Prenume varchar(20) NOT NULL,
DataN datetime,
Adresa varchar(50),
Functie varchar(50),
Salariu decimal DEFAULT 2800,
IdSectie int
);

DROP TABLE achizitii;
CREATE TABLE achizitii(
IdAchizitie int NOT NULL PRIMARY KEY,
IdFurnizor int NOT NULL,
IdComponenta int NOT NULL,
IdAchizitor int NOT NULL,
Data datetime,
NrComponente int NOT NULL,
PretUnitar float NOT NULL
);

DROP TABLE compozitii;
CREATE TABLE compozitii(
IdComponenta int NOT NULL PRIMARY KEY,
IdProdus int NOT NULL,
NrComponente int NOT NULL
);

DROP TABLE vanzari;
CREATE TABLE vanzari(
IdVanzare int NOT NULL PRIMARY KEY,
IdProdus int NOT NULL,
IdClient int NOT NULL,
IdVanzator int NOT NULL,
Data datetime,
NrProduse int NOT NULL
);

DROP TABLE activitati;
CREATE TABLE activitati(
IdProiect int NOT NULL PRIMARY KEY,
IdAngajat int NOT NULL,
Ore int NOT NULL
);

DROP TABLE furnizori;
CREATE TABLE furnizori(
IdFurnizor int NOT NULL PRIMARY KEY,
Nume varchar(30) NOT NULL,
Prenume varchar(30) NOT NULL,
Adresa varchar(30) NOT NULL
);

DROP TABLE componente;
CREATE TABLE componente(
IDComponenta int NOT NULL PRIMARY KEY,
Denumire varchar(30) NOT NULL,
Descriere varchar(30) NOT NULL
);

DROP TABLE produse;
CREATE TABLE produse(
IdProdus int NOT NULL PRIMARY KEY,
Denumire varchar(30) NOT NULL,
Descriere varchar(30) NOT NULL
);

DROP TABLE clienti;
CREATE TABLE clienti(
IdClient int NOT NULL PRIMARY KEY,
Nume varchar(30) NOT NULL,
Prenume varchar(30) NOT NULL,
Adresa varchar(30) NOT NULL
);

DROP TABLE proiecte;
CREATE TABLE proiecte(
IdProduse int NOT NULL PRIMARY KEY,
Denumire varchar(30) NOT NULL,
Termen datetime,
Buget decimal
);


ALTER TABLE dependenti ADD CONSTRAINT fk_angajati FOREIGN KEY(IdAngajat) REFERENCES angajati(IdAngajat);
ALTER TABLE ingineri ADD CONSTRAINT fk_ingineri FOREIGN KEY(IdAngajat) REFERENCES ingineri(IdAngajat);
ALTER TABLE secretare ADD CONSTRAINT fk_secretare FOREIGN KEY(IdAngajat)REFERENCES angajati(IdAngajat);
ALTER TABLE angajati ADD CONSTRAINT fk_angajati FOREIGN KEY(IdSectie) REFERENCES sectii(IdSectie);
ALTER TABLE achizitii ADD CONSTRAINT fk_achizitii FOREIGN KEY(IdAchizitor) REFERENCES angajati(IdAngajat);
ALTER TABLE vanzari ADD CONSTRAINT fk_vanzari FOREIGN KEY(IdVanzator) REFERENCES angajati(IdAngajat);
ALTER TABLE activitati ADD CONSTRAINT fk_activitati FOREIGN KEY(IdAngajat) REFERENCES angajati(IdAngajat);
ALTER TABLE activitati ADD CONSTRAINT fk_activitati FOREIGN KEY(IdProiect) REFERENCES proiecte(IdProiect);
ALTER TABLE vanzari ADD CONSTRAINT fk_vanzari FOREIGN KEY(IdVanzare) REFERENCES clienti(IdClient);
ALTER TABLE vanzare ADD CONSTRAINT fk_vancare FOREIGN KEY(IdVanzare)REFERENCES produse(IdProdus);
ALTER TABLE compozitii ADD CONSTRAINT fk_compozitii FOREIGN KEY(IdProdus) REFERENCES produse(IdProdus);
ALTER TABLE compozitii ADD CONSTRAINT fk_compozitii FOREIGN KEY(IdComponenta) REFERENCES componente(IdComponenta);
ALTER TABLE achizitii ADD CONSTRAINT fk_achizitii FOREIGN KEY(IdFurnizor) REFERENCES furnizori(IdFurnizor);
ALTER TABLE achizitii ADD CONSTRAINT fk_achizitii FOREIGN KEY(IdComponenta) REFERENCES componente(IdComponenta);


SELECT *FROM dependenti;
SELECT *FROM ingineri;
SELECT *FROM secretare;
SELECT *FROM angajati;
SELECT *FROM sectii;
SELECT *FROM achizitii;
SELECT *FROM compozitii;
SELECT *FROM vanzari;
SELECT *FROM activitati;
SELECT *FROM furnizori;
SELECT *FROM componente;
SELECT *FROM produse;
SELECT *FROM clienti;
SELECT *FROM proiecte;


INSERT INTO sectii VALUES(1,'Vanzari',1241);
INSERT INTO sectii VALUES(2,'Contabilitate',43242);
INSERT INTO sectii VALUES(3,'IT',99999);

INSERT INTO angajati VALUES(1,'Dan','George','05/12/1998','Slatina',5000,3);
INSERT INTO angajati VALUES(2,'Ion','Alex','04/01/1974','Craiova',7000,2);
INSERT INTO angajati VALUES(3,'Popescu','George','07/27/1985','Slatina',2000,1);

INSERT INTO secretare VALUES(1,55);

INSERT INTO ingineri VALUES(2,'Mecanica');

INSERT INTO dependenti VALUES(1,'Dan','Mihai','12/28/1987',2);