CREATE DATABASE HomeInventory

USE HomeInventory

CREATE TABLE Item
(
ItemID int CONSTRAINT pk1 PRIMARY KEY IDENTITY NOT NULL,
LocationID int CONSTRAINT fk1 FOREIGN KEY REFERENCES dbo.Location(LocationID) NOT NULL,
Description varchar(50) NOT NULL,
Quantity int,
PurchaseDate smalldatetime,
SerialNumber varchar(50),
Cost smallmoney CONSTRAINT ck1 CHECK Cost >= 0,
ItemNotes varchar(250)
)

CREATE TABLE Location
(
LocationID int CONSTRAINT pk2 PRIMARY KEY IDENTITY NOT NULL,
Location varchar(50) NOT NULL
)

CREATE TABLE Photo
(
PhotoID int CONSTRAINT pk3 PRIMARY KEY IDENTITY NOT NULL,
ItemID int CONSTRAINT fk2 FOREIGN KEY REFERENCES dbo.Item(ItemID),
PhotoLocation varchar(100) NOT NULL,
PhotoCaption varchar(50),
PhotoNotes varchar(250)
)

CREATE INDEX Item_Serial_Number_IX 
ON dbo.Item(SerialNumber ASC) INCLUDE (Description)