Database tables to help:

CREATE TABLE Orders ( OrderID INT PRIMARY KEY IDENTITY, CustomerID INT REFERENCES Customers (CustomerID), OrderDate DATETIME NOT NULL, ShipAmount MONEY NOT NULL, TaxAmount MONEY NOT NULL, ShipDate DATETIME DEFAULT NULL, ShipAddressID INT NOT NULL, CardType VARCHAR(50) NOT NULL, CardNumber CHAR(16) NOT NULL, CardExpires CHAR(7) NOT NULL, BillingAddressID INT NOT NULL );

CREATE TABLE OrderItems ( ItemID INT PRIMARY KEY IDENTITY, OrderID INT REFERENCES Orders (OrderID), ProductID INT REFERENCES Products (ProductID), ItemPrice MONEY NOT NULL, DiscountAmount MONEY NOT NULL, Quantity INT NOT NULL );

CREATE TABLE Products ( ProductID INT PRIMARY KEY IDENTITY, CategoryID INT REFERENCES Categories (CategoryID), ProductCode VARCHAR(10) NOT NULL UNIQUE, ProductName VARCHAR(255) NOT NULL, Description TEXT NOT NULL, ListPrice MONEY NOT NULL, DiscountPercent MONEY NOT NULL DEFAULT 0.00, DateAdded DATETIME DEFAULT NULL );

CREATE TABLE InvoiceLineItems( InvoiceID int NOT NULL, InvoiceSequence smallint NOT NULL, AccountNo int NOT NULL, InvoiceLineItemAmount money NOT NULL, InvoiceLineItemDescription varchar(100) NOT NULL, CONSTRAINT PK_InvoiceLineItems PRIMARY KEY CLUSTERED ( InvoiceID ASC, InvoiceSequence ASC )

CREATE TABLE Invoices( InvoiceID int IDENTITY(1,1) NOT NULL, VendorID int NOT NULL, InvoiceNumber varchar(50) NOT NULL, InvoiceDate smalldatetime NOT NULL, InvoiceTotal money NOT NULL, PaymentTotal money NOT NULL, CreditTotal money NOT NULL, TermsID int NOT NULL, InvoiceDueDate smalldatetime NOT NULL, PaymentDate smalldatetime NULL, CONSTRAINT PK_Invoices PRIMARY KEY CLUSTERED ( InvoiceID ASC )

CREATE TABLE Vendors( VendorID int IDENTITY(1,1) NOT NULL, VendorName varchar(50) NOT NULL, VendorAddress1 varchar(50) NULL, VendorAddress2 varchar(50) NULL, VendorCity varchar(50) NOT NULL, VendorState char(2) NOT NULL, VendorZipCode varchar(20) NOT NULL, VendorPhone varchar(50) NULL, VendorContactLName varchar(50) NULL, VendorContactFName varchar(50) NULL, DefaultTermsID int NOT NULL, DefaultAccountNo int NOT NULL, CONSTRAINT PK_Vendors PRIMARY KEY CLUSTERED( VendorID ASC )

You have been given the responsibility to design a small database for a restaurant. The owner wants to keep track of the menu items that are being ordered. You have a menu at hand and the receipt from the restaurant. Follow the following steps to come up with the data to store and the relationships between the entities. Submission should include the two different Entity-Relationship (ER) Models discussed today. See slide#16 from WK9- Chap10-DBDesign.pptx for reference. Step 1: Identify the data elements Step 2: Subdivide each element into its smallest useful components Step 3: Identify the tables (entities) and assign columns Step 4: Identify the primary and foreign keys (relationships: one-to-many, many-to-many, one-to-one) As the designer, you can create any necessary ID, category etc. Use the menu and receipt as starters for the database design. You can look at the design of Vendors, Invoices and InvoiceLineltems in AP or Products, Orders and Orderlineltems in MyGuitarShop as guiding models for your design. Date: Apr 02, 2009 Time: 06:52PM Server: Ming B111: 0137 Table 42 1. Crab Meat with Fish Maw 13.99 Soup 41.90 17.99 9.99 1 1 10.5()5 1 House Special Variety Dried Se 1 Cold Chicken w Chinese Herb Sa HALF EGET 1 24ter 1 Chicken Chow Mein W Vegetable Change to 1 Yang Chow Fried Rice 7.99 9.99 APPETIZERS CHOP SUEY 01. Vegetable Roll sas. Vegetable Chop Suey 4.10 7.30 12. Roll Park & Shrimp.1.30 A Pork Chop Suey .4.50 8.00 R 03. Fried Chicken Wing .. 2. Chicken Chop Sony 4.50 8.00 Bred Sec... SAB 4 Beel Chop Suey 5.100.00 #06. Crab Rangoon (8)... ..4.30 Shrimp Chop Suey 5.10 3.80 BE06. Shrimp Toast... Res House Special Chop Suey5.10 3.80 #OT Fried Jumbo Shrimp.....64.30 112) 8.30 * Pred or Steamed Dumplings i...4.30 PORK # Filed Shrimp (15) 90 51. Roast Pork w. Shring Bean__.4.10 3.30 10. Pu Pu Planter for 21 #11 At 2 Roast Pork w. Mored Vegs....4.80 8.30 Boncom foarte be...()5.90 TL 3.50 # E$ 12. Pried Wonton (12 AS Roast Pork w. Broccol........4.80 8.30 EX 54 Roast Park w. Mushroom 4.80 3.30 ..4.30 13. Fried Crab Stick (6) SEZA 5. Roast Pork w. Snow Peas... 4.80 9.30 SOUPS POULTRY 14. Valabile Soup 1.80 3.30 REE 56. Chichew String Bean....... 5.00 8.50 15. Wonton Soup -1.90 3.30 57. Moo Goo Gal Pan Chicken... 5.00 8.50 16. Egg Dropdove 1.80 3.30 + Chicken w. Mlad Vegetable... 5.00 8.50 17. Wonton Egg Drop op...1.80 3.30 * Chichew. Brocool 5.000.50 18. Chicken Rice or Noodle Soup. 1.80 3.30 a Chicken w Cashew Nuts.5.00 2.50 RR19. Hot & Sour Soup 1.80 3.30 1.80 3.30 1. Chicken Black Bean Sauce.5.00 8.50 HRT 20. Roast Pork or Chicken Yat Got Mein...4.00 SAR Chichew. She Pes........5.00 8.50 #8 21. House special soup Curry Chicken Onion ...5.00 3.50 FRIED RICE BEEF Wire * newtatile Pried Rice 3.20 5.20 But Bering Beans..........5.20 9.30 HSR 23. Roast Porn Fried Rice -1.50 6.30 6. Popper Sleak Onions........5.209.30 * $24. Chicken Fried Rice 3.50 6.30 *** 5.20 9.30 2. Beedried Rich. # Bow Mixed Vegetable Beef Mixed Vegetable20 2.30 Simo Fried Rice BASR 27. Crab Meat Fried Rice 19 9. Beef Oyster Sauce Bar Tomato 9.30 .5.20 9.30 *81621 Combination Fried Rice........420 7.60 En Beef Mushroom.. .5.209.30 B7 Bulbrow Pe... 520 9.30 LO MEIN (PASTA) 12 Curry Boot woon Curry Beef w.Onion 09:30 Ne RSS 29. Vegetable Le Mein 3.70 7.10 SEAFOOD PE Ros Por La Mein.......... 780 FB 73. Shrim w Brown Lobster Sauce 5.20 3.30 #1. CNC Le Moi... 420 1.60 SERE 14. Shrimp w. Sering ears........520 9.30 2. Bref Lo Mein... 443190 5. Shrimp w. Lobster Sauce....... 5.20 9.30 33 Shrimp Lo Mein ...... 7 Shrimp w. Broccol... 5.209.30 BORN 34. Combination Le Mein...........450 8.10 77 Shrimp w. Midle... 5.209.30 MALFUN Ar Shrimp w. Popper & Tomato...5.20 9.30 E. Shrimp w. Black Bean Suce... 5.20 9.30 Thin die Nede SI Shrimp w. Snow Pas...509.30 35. Chicken Chew Mal Fun..... 7.30 #Shrimp w. Cashew Nuts XII 36. Roast Port Chow Malfun 7.80 Ewym w Onion ..5.209.30 37. Bal Chow Mil Fu... 8.50 +33 Salop. Lobster Sauce 5.20 9.30 * 38 Shrimp Chow Malu 8.50 9. House Special Chow Mail Fun 30 SWEET & SOURI 40. Singapore Chow Mal Puni 8.50 MIweet & Sour Park 4.30 780 Smulan 19 70 9.99 111.84 10.35 Subtotal Sales Tax Total 122.19