E-commerce Database Design

Create a database for a small e-commerce web site for a company that makes dog houses. As a minimum, it should include the following entities as a starting point:

Customer – typical customer info
ProductCatalog – description, size, quantity on hand, unit price, etc.
Order – date, status, payment info, etc.
OrderDetail – typical shopping cart line items
RawMaterial – item, supplier, description, unit of measure, quantity on hand (like plywood, paint, etc)
BOM (Bill of Materials) – listing of material (and the quantity) required for each dog house
Supplier – typical vendor info plus MainPhone, SalesPhone, ShippingPhone, and BillingPhone
StateFees – list of 50 states and the sales tax and shipping costs
Notes:

Include a check constraint on the Product table's Size column to restrict it to "S", "M", "L", or "XL"
ProductCatalog and RawMaterial start out as a N:M, so use the BOM to fix that
There is a multi-column issue that needs to be fixed
Include an "archive" feature for the unit price in the OrderDetails table
The fee info is: 1) Charge 6.25% tax in Texas 2) Charge 9.25% tax in California, 3) All other states have 0% tax.... and 4) Charge $4 to ship to TX, LA, OK, NM, 5) Charge $9 to ship to AK and HI, and 6) Charge $6 shipping to all other states.
Real databases often provide an alternative to deleting records, so please account for this in your table design
Use the database diagram to ensure that there are no "orphan" tables (or groups of tables)
Use roles and permissions to avoid unauthorized access or modification of data
Just to make things easier, let's assume that a part in RawMaterials is just provided by 1 Supplier (so, that means there is no N:M between Supplier and RawMaterials)
As a minimum, you must create Views, Table Functions, or Scalar Functions for the following:

query to calculate the extended price (a single value) for a given Order and a given Line Item
query to show all order activity details for a given Customer
query to calculate the line-item subtotal (sum of the extended prices as a single value) for a given Order
query to show the tax and shipping for a given Subtotal and a given State
query to show the total sales (in $) for each month
query to show the total sales (in $) for each size of dog house
create a view to demonstrate the use of each of the functions above
You must provide the data for the tables:

Have fun... you can use cartoon characters, etc. as customers
You need to have enough sample data in your database to be able to demonstrate that each query works. For example one of the queries will require multiple months of sales data


Expectations: A single "detached" Microsoft SQL Server database file (an *.mdf file)