CREATE TABLE Computer
(
	id			int				IDENTITY(1,1) UNIQUE NOT NULL,
	hname		nvarchar(30)	NOT NULL,
	location	nvarchar(5)		NOT NULL,
	PRIMARY KEY( id )
) ;

CREATE TABLE Manufacturer
(
	id			int				IDENTITY(1,1) UNIQUE NOT NULL,
	name		nvarchar( 30 )	NOT NULL,
	PRIMARY KEY( id )
) ;

CREATE TABLE DeviceType
(
	id			int				IDENTITY(1,1) UNIQUE NOT NULL,
	name		nvarchar( 30 )	NOT NULL,
    descr		nvarchar(1000),
    PRIMARY KEY( id )
) ;
 
CREATE TABLE Device
(
	id			int				IDENTITY(1,1) UNIQUE NOT NULL,
	type		int				NOT NULL,
	brand		int				NOT NULL,
    comp		int				NOT NULL,
	PRIMARY KEY( id ),
	FOREIGN KEY( type ) REFERENCES DeviceType( id ),
	FOREIGN KEY( brand ) REFERENCES Manufacturer( id ),
    FOREIGN KEY( comp ) REFERENCES Computer( id )
) ;

INSERT INTO Computer(hname,location) VALUES( 'cincinnati', 'st341' ) ;
INSERT INTO Computer(hname,location) VALUES( 'florence', 'fh109' ) ;
INSERT INTO Computer(hname,location) VALUES( 'independence', 'fh108' ) ;
INSERT INTO Computer(hname,location) VALUES( 'delhi', 'bp207' ) ;
INSERT INTO Computer(hname,location) VALUES( 'mtadams', 'su111' ) ;

INSERT INTO DeviceType(name,descr) VALUES( 'hard drive', 'it stores data!' ) ;
INSERT INTO DeviceType(name,descr) VALUES( 'cpu', 'brains, beautiful brains' ) ;
INSERT INTO DeviceType(name,descr) VALUES( 'motherboard', 'the heart perhaps? central nervous system?' ) ;
INSERT INTO DeviceType(name,descr) VALUES( 'gpu', 'like a processor... but not' ) ;
INSERT INTO DeviceType(name,descr) VALUES( 'nic', 'give me the internets!' ) ;

INSERT INTO Manufacturer(name) VALUES( 'western digital' ) ;
INSERT INTO Manufacturer(name) VALUES( 'seagate' ) ;
INSERT INTO Manufacturer(name) VALUES( 'intel' ) ;
INSERT INTO Manufacturer(name) VALUES( 'amd' ) ;
INSERT INTO Manufacturer(name) VALUES( 'nvidia' ) ;
INSERT INTO Manufacturer(name) VALUES( 'logitech' ) ;

INSERT INTO Device( type, brand, comp ) VALUES( 1, 2, 1 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 2, 3, 1 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 3, 5, 1 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 4, 4, 1 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 5, 3, 1 ) ;

INSERT INTO Device( type, brand, comp ) VALUES( 1, 2, 2 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 2, 3, 2 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 3, 5, 2 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 4, 4, 2 ) ;

INSERT INTO Device( type, brand, comp ) VALUES( 1, 3, 3 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 2, 3, 3 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 3, 3, 3 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 4, 3, 3 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 5, 3, 3 ) ;

INSERT INTO Device( type, brand, comp ) VALUES( 1, 2, 4 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 2, 4, 4 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 3, 5, 4 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 4, 5, 4 ) ;
INSERT INTO Device( type, brand, comp ) VALUES( 5, 5, 4 ) ;