[Solved]Background Database Tables Listed Belong Online Order Processing System Musical Instrument Q37112651
Background: The database tables listed below belong to an online order processing system for a musical instrument store. It contains 5 tables Clients (one record for every client) Orders (one record for every order) Products (one record for every product sold by the company) * OrderDetails (one record for every product purchased in each order – i.e., an order may contain many products) ClientComm (one record for every communication contact with each client – i.e., comments about every phone call, letter, e-mail, etc. from clients are recorded in this table) * Your task: (1) Use Microsoft Access to create a database that should include these five tables. For each table, you should specify the primary key if it is needed. For each field, please choose an appropriate data type (2) Specify the relationship between these tables (one-to-one or one-to many) (3) Enter these data into the database tables (4) Create the following queries Given an OrderNo (99001), retrieve the client name and the product name(s) Given a ClientID (carmel), retrieve all the orders (product names, Qty, and the order date) placed by this client Given a client name (Espinosa, Alberto), retrieve all the comments about this client . Clients ClientlD ClientName alberto Espinosa, AlbertoOld Albertown RoadBethesda carmel Carmel, Erran delone Delone, WilliamRockville Pike Address City State ZipCode |Telephone MD 20817 3018851958 200163013334444 207763012223333 By the River Road Washington DC Rockville MD Orders OrderDescription OrderNo ClientID OrderDate 990001alberto 6/7/2002 Computer System 990002carm 6/7/2002 Another System 990003delone 6/7/2002 More Systems OrderStatus DeliveryDueDate Top Priorit 6/10/2002 Medium Priority 6/11/2002 6/24/200.2 Low Priority Products ProdIDProdNameProdDescription comp keyb monit mou Price 1999.98 Computer Computer Box KeyboardErgonomic Keyboard 119.98 Computer Display 3 Button Mouse Monitor 374.85 44.85 Mouse OrderDetails OrderNo ProdIDQty 990001comp 10 990001 monit 1O 990001keyb 12 990001 mou 12 990002 comp 20 990002 monit 22 990003comp15 990003keyb 16 ClientComm ClientID Date alberto6/7/1999 This client acts like he has a lot of money, but he’s really broke alberto 6/8/1999 Client called to complain about nothing. Ignore him next time, needs Comments vacation carmel 6/10/1999 Called again to order music, but we only sell computers. He’s probably teaching too much Submission: upload your database through canvas. Also upload the SQL statement of the three queries Show transcribed image text Background: The database tables listed below belong to an online order processing system for a musical instrument store. It contains 5 tables Clients (one record for every client) Orders (one record for every order) Products (one record for every product sold by the company) * OrderDetails (one record for every product purchased in each order – i.e., an order may contain many products) ClientComm (one record for every communication contact with each client – i.e., comments about every phone call, letter, e-mail, etc. from clients are recorded in this table) * Your task: (1) Use Microsoft Access to create a database that should include these five tables. For each table, you should specify the primary key if it is needed. For each field, please choose an appropriate data type (2) Specify the relationship between these tables (one-to-one or one-to many) (3) Enter these data into the database tables (4) Create the following queries Given an OrderNo (99001), retrieve the client name and the product name(s) Given a ClientID (carmel), retrieve all the orders (product names, Qty, and the order date) placed by this client Given a client name (Espinosa, Alberto), retrieve all the comments about this client . Clients ClientlD ClientName alberto Espinosa, AlbertoOld Albertown RoadBethesda carmel Carmel, Erran delone Delone, WilliamRockville Pike Address City State ZipCode |Telephone MD 20817 3018851958 200163013334444 207763012223333 By the River Road Washington DC Rockville MD Orders OrderDescription OrderNo ClientID OrderDate 990001alberto 6/7/2002 Computer System 990002carm 6/7/2002 Another System 990003delone 6/7/2002 More Systems OrderStatus DeliveryDueDate Top Priorit 6/10/2002 Medium Priority 6/11/2002 6/24/200.2 Low Priority Products ProdIDProdNameProdDescription comp keyb monit mou Price 1999.98 Computer Computer Box KeyboardErgonomic Keyboard 119.98 Computer Display 3 Button Mouse Monitor 374.85 44.85 Mouse
OrderDetails OrderNo ProdIDQty 990001comp 10 990001 monit 1O 990001keyb 12 990001 mou 12 990002 comp 20 990002 monit 22 990003comp15 990003keyb 16 ClientComm ClientID Date alberto6/7/1999 This client acts like he has a lot of money, but he’s really broke alberto 6/8/1999 Client called to complain about nothing. Ignore him next time, needs Comments vacation carmel 6/10/1999 Called again to order music, but we only sell computers. He’s probably teaching too much Submission: upload your database through canvas. Also upload the SQL statement of the three queries
Expert Answer
Answer to Background: The database tables listed below belong to an online order processing system for a musical instrument store…. . . .
OR
