CREATE DATABASE depozITfinal;

USE depozITfinal;


----------------------------------------------------
------------------Crearea tabelelor-----------------
----------------------------------------------------

CREATE TABLE produse
(
	Producator char(1) NOT NULL,
	Model int Primary key NOT NULL,
	Tip varchar(10) NOT NULL
)

CREATE TABLE pc_uri
(
	Cod int Primary key NOT NULL,
	Model int NOT NULL REFERENCES produse(Model),
	Viteza decimal(3,0) NOT NULL,
	Ram decimal(3,0) NOT NULL,
	Hd decimal(2,0) NOT NULL,
	Cd varchar(3) NOT NULL,
	Pret float NOT NULL
)

CREATE TABLE laptop_uri
(
	Cod int Primary key NOT NULL,
	Model int NOT NULL REFERENCES produse(Model),
	Viteza decimal(3,0) NOT NULL,
	Ram decimal(3,0) NOT NULL,
	Hd decimal(2,0) NOT NULL,
	Pret float NOT NULL,
	Ecran int NOT NULL
)

CREATE TABLE imprimante
(
	Cod int Primary key NOT NULL,
	Model int  NOT NULL REFERENCES produse(Model),
	Color char(2) NOT NULL,
	Tip varchar(6) NOT NULL,
	Pret float NOT NULL
)


----------------------------------------------------
--------------Definirea Cheilor Primare-------------
----------------------------------------------------

--ALTER TABLE produse ADD CONSTRAINT PK_Produs PRIMARY KEY(Model);
--ALTER TABLE pc_uri ADD CONSTRAINT PK_Pc PRIMARY KEY(Cod);
--ALTER TABLE laptop_uri ADD CONSTRAINT PK_Laptop PRIMARY KEY(Cod);
--ALTER TABLE imprimante ADD CONSTRAINT PK_Imprimanta PRIMARY KEY(Cod);


----------------------------------------------------
--------------Definirea Cheilor Externe-------------
----------------------------------------------------

--ALTER TABLE pc_uri ADD CONSTRAINT FK_Pc_Produse FOREIGN KEY(Model) REFERENCES produse(Model);
--ALTER TABLE laptop_uri ADD CONSTRAINT FK_Laptop_Produse FOREIGN KEY(Model) REFERENCES produse(Model);
--ALTER TABLE imprimante ADD CONSTRAINT FK_Imprimanta_Produse FOREIGN KEY(Model) REFERENCES produse(Model);


----------------------------------------------------
-----------------Popularea Tabelelor----------------
----------------------------------------------------

INSERT INTO produse(Producator,Model,Tip)
VALUES ('B','1121','PC');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('A','1232','PC');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('A','1233','PC');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('E','1260','PC');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('A','1276','Imprimante');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('D','1288','Imprimante');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('A','1298','Laptop_uri');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('C','1321','Laptop_uri');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('A','1401','Imprimante');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('A','1408','Imprimante');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('D','1433','Imprimante');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('E','1434','Imprimante');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('B','1750','Laptop_uri');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('A','1752','Laptop_uri');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('E','2111','PC');
INSERT INTO produse(Producator,Model,Tip)
VALUES ('E','2112','PC');



INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (1,'1232',500,64,5,'12x',600);
INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (2,'1121',750,128,14,'40x',850);
INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (3,'1233',500,64,5,'12x',600);
INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (4,'1121',600,128,14,'40x',850);
INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (5,'1121',600,128,8,'40x',850);
INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (6,'1233',750,128,20,'50x',950);
INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (7,'1232',500,32,10,'12x',400);
INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (8,'1232',450,64,8,'24x',350);
INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (9,'1232',450,32,10,'24x',350);
INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (10,'1260',500,32,10,'12x',350);
INSERT INTO pc_uri(Cod,Model,Viteza,Ram,Hd,Cd,Pret)
VALUES (11,'1233',900,128,40,'40x',980);


INSERT INTO laptop_uri(Cod,Model,Viteza,Ram,Hd,Pret,Ecran)
VALUES (1,'1298',350,32,4,700,11);
INSERT INTO laptop_uri(Cod,Model,Viteza,Ram,Hd,Pret,Ecran)
VALUES (2,'1321',500,64,8,970,12);
INSERT INTO laptop_uri(Cod,Model,Viteza,Ram,Hd,Pret,Ecran)
VALUES (3,'1750',750,128,12,1200,14);
INSERT INTO laptop_uri(Cod,Model,Viteza,Ram,Hd,Pret,Ecran)
VALUES (4,'1298',600,64,10,1050,15);
INSERT INTO laptop_uri(Cod,Model,Viteza,Ram,Hd,Pret,Ecran)
VALUES (5,'1752',750,128,10,1150,14);
INSERT INTO laptop_uri(Cod,Model,Viteza,Ram,Hd,Pret,Ecran)
VALUES (6,'1298',450,64,10,950,12);


INSERT INTO imprimante(Cod,Model,Color,Tip,Pret)
VALUES (1,'1276','nu','Laser',400);
INSERT INTO imprimante(Cod,Model,Color,Tip,Pret)
VALUES (2,'1433','da','Jet',270);
INSERT INTO imprimante(Cod,Model,Color,Tip,Pret)
VALUES (3,'1434','da','Jet',290);
INSERT INTO imprimante(Cod,Model,Color,Tip,Pret)
VALUES (4,'1401','nu','Matrix',150);
INSERT INTO imprimante(Cod,Model,Color,Tip,Pret)
VALUES (5,'1408','nu','Matrix',270);
INSERT INTO imprimante(Cod,Model,Color,Tip,Pret)
VALUES (6,'1288','nu','Laser',400);

exec sp_changedbowner 'sa'