CREATE DATABASE SYSTEMINFO
GO
USE SYSTEMINFO
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE Users(
	login varchar(20) NOT NULL PRIMARY KEY,
	passwd nvarchar(50) NOT NULL,
)
GO

CREATE TABLE DiskDriveInfo (
	login varchar(20) NOT NULL,
	id int IDENTITY(1,1) UNIQUE NOT NULL,
	Manufacturer varchar(50) NULL,
	Model varchar(50) NULL,
	Name varchar(50) NULL,
	InterfaceName varchar(50) NULL,
	InterfaceType varchar(50) NULL,
	MediaType varchar(50) NULL,
	BytesPerSector int NULL,
	TotalCylinders bigint NULL,
	TotalHeads int NULL,
	TotalSectors bigint NULL,
	TotalTracks bigint NULL,
	Partitions int NULL,
	PRIMARY KEY (login, id),
	FOREIGN KEY (login) REFERENCES Users
		ON UPDATE CASCADE
		ON DELETE CASCADE
)
GO

CREATE TABLE BusInfo(
	login varchar(20) NOT NULL,
	id int IDENTITY(1,1) NOT NULL,
	BusNum int NULL,
	BusType int NULL,
	DeviceId varchar(50) NULL,
	PRIMARY KEY (login, id),
	FOREIGN KEY (login) REFERENCES Users
		ON UPDATE CASCADE
		ON DELETE CASCADE
)

CREATE TABLE CacheInfo(
	login varchar(20) NOT NULL,
	id int IDENTITY(1,1) NOT NULL,
	Access int NULL,
	Lvl int NULL,
	BlockSize bigint NULL,
	CacheSpeed int NULL,
	CacheType int NULL,
	MaxCacheSize int NULL,
	PRIMARY KEY (login, id),
	FOREIGN KEY (login) REFERENCES Users
		ON UPDATE CASCADE
		ON DELETE CASCADE
)
GO

CREATE TABLE DiskPartitionInfo(
	login varchar(20) NOT NULL,
	id int IDENTITY(1,1) NOT NULL,
	DeviceId varchar(50) NULL,
	BlockSize bigint NULL,
	Bootable bit NULL,
	BootPartition bit NULL,
	DiskIndex int NULL,
	NumberOfBlocks bigint NULL,
	PrimaryPartition bit NULL,
	Size bigint NULL,
	Type varchar(50) NULL,
	PRIMARY KEY (login, id),
	FOREIGN KEY (login) REFERENCES Users
		ON UPDATE CASCADE
		ON DELETE CASCADE
)

CREATE TABLE BiosInfo(
	login varchar(20) NOT NULL,
	id int IDENTITY(1,1) NOT NULL,
	SerialNumber varchar(50) NULL,
	Manufacturer varchar(50) NULL,
	IdentificationCode varchar(50) NULL,
	Version varchar(50) NULL,
	InstallDate datetime NULL,
	PRIMARY KEY (login, id),
	FOREIGN KEY (login) REFERENCES Users
		ON UPDATE CASCADE
		ON DELETE CASCADE
)
GO

CREATE TABLE PhysicalMemoryInfo(
	login varchar(20) NOT NULL,
	id int IDENTITY(1,1) NOT NULL,
	BankLabel varchar(50) NULL,
	Capacity bigint NULL,
	DataWidth int NULL,
	TotalWidth int NULL,
	Manufacturer varchar(50) NULL,
	Model varchar(50) NULL,
	MemoryType int NULL,
	Speed int NULL,
	PRIMARY KEY (login, id),
	FOREIGN KEY (login) REFERENCES Users
		ON UPDATE CASCADE
		ON DELETE CASCADE
)
GO

CREATE TABLE ProcessorInfo(
	login varchar(20) NOT NULL,
	id int IDENTITY(1,1) NOT NULL,
	DeviceId varchar(50) NULL,
	Manufacturer varchar(50) NULL,
	Arquitecture int NULL,
	AddressWidth int NULL,
	DataWidth int NULL,
	CurrentClockSpeed int NULL,
	MaxClockSpeed int NULL,
	CurrentVoltage int NULL,
	ProcessorType int NULL,
	Family int NULL,
	PRIMARY KEY (login, id),
	FOREIGN KEY (login) REFERENCES Users
		ON UPDATE CASCADE
		ON DELETE CASCADE
)
GO

CREATE TABLE VideoInfo(
	login varchar(20) NOT NULL,
	id int IDENTITY(1,1) NOT NULL,
	DeviceId varchar(50) NULL,
	AdapterRAM int NULL,
	VideoModeDescription varchar(50) NULL,
	VideoProcessor varchar(50) NULL,
	CurrentRefreshRate int NULL,
	AdapterDACType varchar(50) NULL,
	PRIMARY KEY (login, id),
	FOREIGN KEY (login) REFERENCES Users
		ON UPDATE CASCADE
		ON DELETE CASCADE
)
GO

CREATE TABLE LogicalDiskInfo(
	login varchar(20) NOT NULL,
	id int IDENTITY(1,1) NOT NULL,
	BlockSize bigint NULL,
	DeviceId varchar(50) NULL,
	DriveType int NULL,
	FileSystem varchar(50) NULL,
	FreeSpace bigint NULL,
	NumberOfBlocks bigint NULL,
	Size bigint NULL,
	VolumeSerialNumber varchar(50) NULL,
	PRIMARY KEY (login, id),
	FOREIGN KEY (login) REFERENCES Users
		ON UPDATE CASCADE
		ON DELETE CASCADE
)
GO
