Create a Word or text log file - University of Arkansas



CSCE 4523 Database: Homework 3Due: Tuesday, March 9, 2021, 11:59pmAll solutions must be prepared individuallyObjectiveYou are to implement a simple database system for a coffee roasting company using MySQL on turing.?Program DescriptionCreate a database with the following schema:SUPPLIERIDint Primary KeyNAMEchar(50)NOT NULLPHONE_NUMBERchar(25)NOT NULLCOUNTRYchar(50)NOT NULLITEMIDint Primary KeyNAMEchar(50)NOT NULLPRICE_PER_LBNUMBER(5,2)NOT NULLROASTING_TYPEchar(1)EMPLOYEEIDint Primary KeyNAMEchar(50)NOT NULLOCCUPATIONchar(25)NOT NULLPHONE_NUMBERchar(25)NOT NULLADDRESSchar(50)NOT NULLSALESTRANS_IDintPrimary KeyITEM_IDintForeign key references ITEM(ID)DISCOUNTIntNOT_NULLPURCHASE_DATEdateNOT NULLEMPLOYEE_IDintForeign key references EMPLOYEE(ID) INVENTORY_MGMTITEM_IDintA composite primary key references ITEM(ID)SUPPLIER_IDintA composite primary key references SUPPLIER(ID)TOTAL_ITEM_SALES_3_MONTHSIntNOT_NULLTOTAL_AVAILABLEIntNOT_NULLImplementationBe sure to handle all entity integrity, referential integrity, and other constraints in your table definitions. Note: mysql does not enforce domain constraints, but include them in your definitions anyway so that, if you were to move to a different database or if they were to update their implementation, your database definition would be ready to go.?Define your constraints such that: If rows in any table are attempted to be deleted, that operation should be blocked/disallowed.Once the tables are created, insert records to match the data file linked on the homework web page. Hint: Type your queries into Notepad or Word or vim and cut and paste them at the mysql prompt, debug, and then save them. Or, put them in a shell script (see posted example).What to Turn In?To turn in your output, at the mysql prompt, type:tee a3.logThen execute the commands for tasks 0 through 10 to display the results on screen. When you are done, type:noteeUpload a3.log to Blackboard.Create a Word or text log file0. Create the tables and insert the records as above. 1. Show the tables that you have created. Use the command below: SHOW TABLES;2. Show the schema of each table. Use the command below, once per?tablename: DESC?tablename;3. Show how each table was created. Use the command below, once per?tablename: SHOW CREATE TABLE?tablename;4. Show the foreign keys of each table. Use the command (fill in your own db name):SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE?WHERE REFERENCED_COLUMN_NAME IS NOT NULL AND CONSTRAINT_SCHEMA =?'your_db'; 5. Show all the records within every table. For each table, use the command:SELECT * FROM?tablename;For each of the following, output the query and the results of running the query.6. Find the coffee that has the highest demand in the last three months; print supplier name, coffee name, roasting type, and origin.7. Find out the total sales for the day ‘Feb-12-2021’. Output the value of sales in $Hint: You need to calculate the price after discount using this formula PRICE_PER_LB-(PRICE_PER_LB*discount*0.01)8. Find which type of coffee roasting is the most popular (i.e., high in sales) during the month of February. Output the type of roasting and the total sales number as ‘COUNT’.9. Find the total sales of ‘ALWADI’ coffee per each employee, print the COUNT of the sold items and the employee name.10. Assume that item is in low stock if it is less 40% of its total inventory, find which items are in low stock, print the name of the item, supplier name, and supplier phone number.Hint1: Total Inventory = TOTAL_ITEM_SALES_3_MONTHS + TOTAL_AVAILABLEHint2: There are different approaches to develop a solution for this question, one is to create a temporary table from the result of the inventory and then do the required join with other tables.Turn off tee (notee) and append the output to your report with the CREATE TABLE code. Turn this one document in.Grading:?Creating the tables with the correct constraints is worth 35 points. Running the SQL queries to insert and retrieve is also worth 35 points. No report is necessary.Where to work:?This project must be done on turing.csce.uark.edu. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download