Information Systems homework help

Semester Project

• Due 5/1/2020 before 11:59 pm
• Congratulations, you have been hired by a wine distribution company in Dallas. They have provided you with the information about the wine and the orders from their customers.  They are interested in answers to the questions, but as a good database developer you are as concerned with organizing and making the SQL code neat.
• The data and definitions are below. The questions might be adjusted per lectures. But no later than 2 weeks before the due date of the project.  (8 questions to be answered now,  potentially adding up to 2)

 
Project

• Build out the ERD diagrams for the entire solution based on the following questions (you must have at least 3 tables for full credit, there is no limit based on your simplification of the data. The simplification of the data should follow the in-class examples of data, with no duplication in tables)
• Provide

1. The MYSQL code (within the word document) to build the database and tables
2. The MYSQL code (within the word document) to answer each of the following questions
3. Provide screen shots answering each question along with any supporting text

 
 
Provided data
 
Questions to be answered

• What is the total revenue for the year?
• What is the gross profit for the year?
• How much revenue did each wine provide for the year?
• What is the Average revenue per month per location for the year?
• What is the final inventory of the basic Red wine?
• If  The retail price of the wine increases by 10%, what is the gross profit margin  ?
• How many bottles of the different wines were ordered in January ?
• What is the revenue per year per restaurant ?

 
SUbquery

• Select (Subquery) from (subquery) on (subquery);
• Think of it as an opportunity to narrow data as needed.
• Select username, how many times they logged into system from log_in_log;
• Select username, (select count (username) from log_in_log) as log_count from log_in_log;
• Simple examples can be done with select username, count(username) ….

 
subquery

• Use the subquery to ”filter” the data (more on this in chapter 12), but say you only want the team members from team 1 in the query ?
• Where statement can be a subquery to make a more complete where (select username where team=1 and team captain)
• This would then be the input to the regular query to provide information
• Select high, average, low scores from (subquery for getting the right user)
• There are multiple ways to implement question, subqueries, inner joins (outer, right, left. …) Keep your thought process simple, build out queries and test the data !

 
Find customers whose payments are greater than the average payment
 
Subquery in the from statement
 
Derived Table
 
Derived Table
 
SQL Configurations

• ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause
• and contains nonaggregated column ‘test.web_log.user_id’ which is not functionally
• dependent on columns in GROUP BY clause; this is incompatible
• with sql_mode=only_full_group_by

 
SQL_MOdes

• The oldest versions of MySQL got users accustomed to writing queries that were not semantically correct because it was designed to work in the “forgiving mode”. Users could write any kind of syntactically valid query regardless of SQL standard compliance or semantic rules. This was a bad habit that was corrected introducing the sql_mode to instruct MySQL to work in a more restrictive way for query validation.

 
Example
 
Rewrite the query
 
Use Aggregate functions
 
Any_Value
 
But certain data points not accessible

• It is possible to come up with creative solutions for all aspects.
• Big Rock concept – organizing data and basic formats
• So, if you get the error and cannot solve the work around, type following into your mysql> prompt
• Mysql> set session sql_mode=’’;
• This will allow solving of the error (more relaxed mode) in terms of commands