if exists (select * from sysobjects where id = object_id(N'[dbo].[Devices]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Devices]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[DeviceTypes]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[DeviceTypes]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Discs]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Discs]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Locations]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Locations]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[NeedToCheck]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[NeedToCheck]
GO

if exists (select * from sysobjects where id = object_id(N'[dbo].[Tracks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Tracks]
GO

CREATE TABLE [dbo].[Devices] (
	[DeviceID] [uniqueidentifier] NOT NULL ,
	[Name] [varchar] (1000) NULL ,
	[Type] [char] (1) NULL ,
	[Maker] [varchar] (1000) NULL ,
	[Port] [varchar] (50) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[DeviceTypes] (
	[Type] [char] (1) NOT NULL ,
	[Description] [varchar] (1000) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Discs] (
	[DiscID] [uniqueidentifier] NOT NULL ,
	[TOC] [varchar] (1024) NOT NULL ,
	[MediaID] [varchar] (50) NOT NULL ,
	[MuiID] [varchar] (50) NOT NULL ,
	[Title] [varchar] (1024) NOT NULL ,
	[Artist] [varchar] (1024) NULL ,
	[Year] [numeric](18, 0) NULL ,
	[Genre] [numeric](18, 0) NULL ,
	[Language] [numeric](18, 0) NULL ,
	[Label] [varchar] (1024) NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Locations] (
	[DeviceID] [uniqueidentifier] NOT NULL ,
	[DiscID] [uniqueidentifier] NOT NULL ,
	[ItemNumber] [numeric](18, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[NeedToCheck] (
	[DeviceID] [uniqueidentifier] NOT NULL ,
	[ItemNumber] [numeric](18, 0) NOT NULL ,
	[NeedToSend] [numeric](18, 0) NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Tracks] (
	[DiscID] [uniqueidentifier] NOT NULL ,
	[TrackNumber] [numeric](18, 0) NOT NULL ,
	[Title] [varchar] (1024) NOT NULL ,
	[Length] [numeric](18, 0) NULL 
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Devices] WITH NOCHECK ADD 
	CONSTRAINT [DF_Devices_DeviceID] DEFAULT (newid()) FOR [DeviceID],
	CONSTRAINT [PK_Devices] PRIMARY KEY  NONCLUSTERED 
	(
		[DeviceID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[DeviceTypes] WITH NOCHECK ADD 
	CONSTRAINT [PK_DeviceTypes] PRIMARY KEY  NONCLUSTERED 
	(
		[Type]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Discs] WITH NOCHECK ADD 
	CONSTRAINT [DF_Discs_DiscID] DEFAULT (newid()) FOR [DiscID],
	CONSTRAINT [PK_Discs] PRIMARY KEY  NONCLUSTERED 
	(
		[DiscID]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Locations] WITH NOCHECK ADD 
	CONSTRAINT [PK_Locations] PRIMARY KEY  NONCLUSTERED 
	(
		[DeviceID],
		[ItemNumber]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[NeedToCheck] WITH NOCHECK ADD 
	CONSTRAINT [PK_NeedToCheck] PRIMARY KEY  NONCLUSTERED 
	(
		[DeviceID],
		[ItemNumber]
	)  ON [PRIMARY] 
GO

ALTER TABLE [dbo].[Tracks] WITH NOCHECK ADD 
	CONSTRAINT [PK_Tracks] PRIMARY KEY  NONCLUSTERED 
	(
		[DiscID],
		[TrackNumber]
	)  ON [PRIMARY] 
GO