Execute the following script in Query Editor to create the Order and OrderDetail tables. OrderID is the Primary Key for the Order table. The OrderDetail table has many to one relationship to the Order table. The OrderDetail table has a composite Primary Key: OrderID and LineNumber. Each product ordered has a record in OrderDetail.

CREATE TABLE AdventureWorks.[dbo].[Order]

(

[OrderID] int identity(1,1) PRIMARY KEY,

[PurchaseOrderID] [int] NOT NULL,

[EmployeeID] [int] NULL,

[VendorID] [int] NULL,

[TaxAmount] [smallmoney] NULL,

[Freight] [smallmoney] NULL,

[SubTotal] [money] NULL,

[TotalDue] [money] NULL,

[Status] [tinyint] NOT NULL,

[ShipMethodID] tinyint NULL,

[ShipDate] [smalldatetime] NOT NULL,

[OrderDate] [smalldatetime] NULL,

[RevisionNumber] [tinyint] NULL,

[ModifiedDate] [smalldatetime] NULL,

CONSTRAINT constrOrdersRangeYear

CHECK ([OrderDate] >= '20001001'

AND [OrderDate] <= getdate()),

[ModifiedBy] char(10) NULL

)

GO

CREATE TABLE AdventureWorks.[dbo].[OrderDetail](

[OrderID] [int] REFERENCES [Order](OrderID),

[LineNumber] [smallint] ,

[ProductID] [int] NULL,

[UnitPrice] [smallmoney] NULL,

[OrderQty] [smallint] NULL,

[ReceivedQty] [float] NULL,

[RejectedQty] [float] NULL,

[OrderDate] [smalldatetime] NOT NULL

CONSTRAINT OrderDetailsRangeYearCK

CHECK ([OrderDate] >= '20000101'

AND [OrderDate] <= getdate()),

[DueDate] [datetime] NULL,

[LineTotal] AS (([UnitPrice]*[OrderQty])),

[StockedQty] AS (([ReceivedQty]-[RejectedQty])),

[ModifiedDate] [datetime] NOT NULL

CONSTRAINT [dfltOrderDetailsModifiedDate]

DEFAULT (getdate()),

[ModifiedBy] char(10) NULL,

PRIMARY KEY CLUSTERED

(

      [OrderID] ASC,

      [LineNumber] ASC

) ON [PRIMARY]

)

GO