CREATE TABLE [dbo].[Catalog](
            	[item_id] [int] NOT NULL,
            	[item_name] [varchar](128) NULL,
             CONSTRAINT [PK_Catalog] PRIMARY KEY CLUSTERED ( [item_id] ASC)
            )

            CREATE TABLE [dbo].[Customers](
            	[customer_id] [int] NOT NULL,
            	[first_name] [varchar](128) NULL,
            	[last_name] [varchar](128) NULL,
             CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED ( [customer_id] ASC)
            )

            CREATE TABLE [dbo].[Orders](
            	[order_id] [int] NOT NULL,
            	[customer_id] [int] NULL,
            	[order_date] [date] NULL,
             CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ( [order_id] ASC)
            )

            CREATE TABLE [dbo].[OrderDetail](
                [order_id] [int] NOT NULL,
                [detail_num] [int] NOT NULL,
                [item_id] [int] NULL,
                [quantity] [int] NULL,
                [unit_price] [decimal](18, 2) NULL,
             CONSTRAINT [PK_OrderDetail] PRIMARY KEY CLUSTERED ( [order_id] ASC, [detail_num] ASC)
          )



	ALTER TABLE [dbo].[Orders]  
		WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([customer_id])
		REFERENCES [dbo].[Customers] ([customer_id]) ON UPDATE CASCADE               

	ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]
            

	ALTER TABLE [dbo].[OrderDetail]  
		WITH CHECK ADD  CONSTRAINT [FK_OrderDetail_Orders] FOREIGN KEY([order_id])            
		REFERENCES [dbo].[Orders] ([order_id]);            
	ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_Orders]


	ALTER TABLE [dbo].[OrderDetail]  
		WITH CHECK ADD  CONSTRAINT [FK_OrderDetail_Catalog] FOREIGN KEY([item_id])
		REFERENCES [dbo].[Catalog] ([item_id]);
	ALTER TABLE [dbo].[OrderDetail] CHECK CONSTRAINT [FK_OrderDetail_Catalog]
            

INSERT Catalog ([item_id], [item_name]) 
	VALUES (1, 'Widget'), 
		(2, 'Fidget'), 
		(3, 'Blodge');

INSERT Customers ([customer_id], [first_name], [last_name])  
	VALUES (1, 'Abby', 'Adams'), 
		(2, 'Bob', 'Barkley'), 
		(3, 'Chris', 'Corwin');

INSERT Orders ([order_id], [customer_id], [order_date]) 
	VALUES (1, 1, GETDATE());

INSERT OrderDetail ([order_id], [detail_num], [item_id], [quantity], [unit_price]) 
	VALUES (1, 1, 1, 1, 1.0);