CREATE TABLE Customer
(
  id int IDENTITY(1,1) NOT NULL UNIQUE, 
  name nvarchar(25) NOT NULL,
  address nvarchar(25) NOT NULL,
  state nvarchar(2) NOT NULL,
  city nvarchar(2) NOT NULL,
  zip int NOT NULL,
  email nvarchar(35) NOT NULL,
  phone nvarchar(10) NOT NULL,
  optout bit NOT NULL DEFAULT 0,
  PRIMARY KEY( id )
) ;

CREATE TABLE Employee
(
  id int IDENTITY(1,1) NOT NULL UNIQUE,
  name nvarchar(25) NOT NULL,
  address nvarchar(25) NOT NULL,
  state nvarchar(2) NOT NULL,
  city nvarchar(2) NOT NULL,
  zip int NOT NULL,
  email nvarchar(35) NOT NULL,
  phone nvarchar(10) NOT NULL,
  ss int NOT NULL,
  PRIMARY KEY( id ) 
) ;

CREATE TABLE Orders
(
  id int IDENTITY(1,1) NOT NULL UNIQUE,
  cust_id int NOT NULL,
  emp_id int NOT NULL,
  orddate datetime,
  PRIMARY KEY( id ),
  FOREIGN KEY( cust_id ) REFERENCES Customer(id),
  FOREIGN KEY( emp_id ) REFERENCES Employee(id)
) ;

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

CREATE TABLE Album
(
  id int IDENTITY(1,1) NOT NULL UNIQUE,
  artist_id int NOT NULL,
  PRIMARY KEY( id ),
  FOREIGN KEY( artist_id ) REFERENCES Artist(id)
);

CREATE TABLE Track
(
  track_num int NOT NULL UNIQUE,
  album_id int NOT NULL,
  title nvarchar(25),
  PRIMARY KEY( track_num ),
  FOREIGN KEY( album_id ) REFERENCES Album(id)
);
CREATE TABLE Item
(
  id int IDENTITY(1,1) NOT NULL UNIQUE,
  album_id int NOT NULL,
  media_type nvarchar(3) NOT NULL,
  condition nvarchar(1) NOT NULL,
  price decimal(9,2) NOT NULL,
  quantity int NOT NULL,
  PRIMARY KEY( id ),
  FOREIGN KEY( album_id ) REFERENCES Album(id)
);

CREATE TABLE Order_Line
(
  line int NOT NULL UNIQUE,
  order_id int NOT NULL,
  item_id int NOT NULL,
  quantity int,
  price decimal(9,2),
  PRIMARY KEY( line, order_id ),
  FOREIGN KEY( order_id ) REFERENCES Orders(id),
  FOREIGN KEY( item_id ) REFERENCES Item(id)
);