Description
This is a mini project for MSDS 7330, File Organization and Database Management. For this assignment, turn in a single pdf file containing all of your answers. The file should be named ¡yourLastName¿MiniProject-Number.pdf. For example, the file name for my mini project 1 would be ‘RafiqiMiniProject-1.pdf’. Insert your answer pages into this file with the answer for Question 1 inserted immediately after Question 1 and before Question 2, the answer for Question 2 inserted immediately after Question 2, and the answer for Question 3 inserted immediately after Question 3. You may insert a front page containing your name and date if you do not wish to or cannot electronically add that information to the first page of this homework sheet.
Collaboration is expected and encouraged; however, each student must hand in their own homework assignment. To the greatest extent possible, answers should not be copied but, instead, should be written in your own words. Copying answers from anywhere is plagiarism, this includes copying text directly from the textbook. Do not copy answers. Always use your own words and your own code. Directly under each question list all persons with whom you collaborated and list all resources used in arriving at your answer. Resources include but are not limited to the textbook used for this course, papers read on the topic, and Google search results. Don’t forget to place your name on the first page of the pdf document.
MySQL Database
Question 1: Use the Sales Order Database and answer the following queries using MySQL Workbench. Submit screenshots of queries along with screenshots of results. If results are longer than one page then simply provide a number of rows returned from the query. Answers for the following queries:
- Display the customers who have never ordered bikes or tires. 2. List the customers who have purchased a bike but not a helmet.
- Show me the customer orders that have a bike but do not have a helmet. Hint: This might seem to be the same as problem 2 above, but it is not. Solve it using EXISTS and NOT EXISTS.
- Display the customers and their orders that have a bike and a helmet in the same order. Hint: Solve this problem using EXISTS
- Show the vendors who sell accessories, car racks, and clothing. Hint: Solve this problem using IN.
Capture the sequence of queries and resulting output thereby demonstrating your database in operation. Turn in a pdf of your database in operation.
Question 2: Python – Write a Python Script that will connect to the Sales Order database and execute queries from question 1. The python script will connect to the MySQL database using MySQL connector and then you will execute the query using the cursor. To make it easier simply define the query at the beginning of the program.
Submit a complete python script.
Hint:
Import MySql connector
Define server name, user name, password
connect to the database initialize cursor, execute query
Resources:
———————
