Database Homework 3- Basic SQL Solution

Problem 1: Database Keys
At the initial stage of designing a database for a fitness app we have identified the following data that we would like to store in a database. Please review the proposed data structure and answer the questions listed below.

Table name: TRAINING
ID INSERT_TIMEST AMP WORKOUT
_DATE WORKOU
T_TYPE WORKOU T_DURAT
ION WORKOU
T_DISTAN
CE_MILES WORKOUT_C
ITY WORK
OUT_S
TATE WORKOU
T_COMME
NT USER_EM
AIL USER_NA ME USER_PA YMENT
Simpson VISA
g 0:22:10 0.5 Boston MA Boring but
necessar
y wonka@y
ahoo.com Willie
Wonka PAYPAL
com Jane Doe MC
com Jane Doe MC
11:17:03 10/13/202
1 Running 1:58:12 26.2 Chicago IL Set my PR on
this one! jr@yahoo .com Jack Rabbit
Question 1.1: Which field(s) could be selected as Candidate Keys and why?
Field “ID” could be selected as Candidate key, for ID->INSERT_TIMESTAMP, WORKOUT_DATE,
WORKOUT_TYPE, WORKOUT_DURATION, WORKOUT_DISTANCE_MILES,
WORKOUT_CITY, WORKOUT_STATE, WORKOUT_COMMENT, USER_EMAIL,
USER_NAME, USER_PAYMENT

Fileds “USER_NAME”, “WORKOUT_DATE”, “WORKOUT_TYPE”, “INSERT_TIMESTAMP” could be selected as candidate key. For USER_EMAIL,WORKOUT_DATE, WORKOUT_TYPE, NSERT_TIMESTAMP -> ID,
WORKOUT_DURATION, WORKOUT_DISTANCE_MILES, WORKOUT_CITY,
WORKOUT_STATE, WORKOUT_COMMENT, USER_NAME, USER_PAYMENT
_____________________________________________________
Question 1.2: Among the keys you listed in your answer above, which would be considered Composite Keys?
_ USER_EMAIL,WORKOUT_DATE, WORKOUT_TYPE, NSERT_TIMESTAMP would be consider as composite key.
_____________________________________________________________________________

Question 1.3: Which one of the candidate keys would you select to be the Primary Key and why?
I will select “ID” to be the primary key of the training table. Because it identifies user’s every workout record. _______________________________________________________________________________________
Question 1.4: List all of the fields which could be stored in a separate table? Why would you store them in a separate table?
I think the fields could be stored in User table: USER_EMAIL, USER_NAME, USER_PAYMENT.
And the fields could be stored in Workout table: ID, INSERT_TIMESTAMP, WORKOUT_DATE,
WORKOUT_TYPE, WORKOUT_DURATION, WORKOUT_DISTANCE_MILES,
WORKOUT_CITY, WORKOUT_STATE, WORKOUT_COMMENT, USER_EMAIL
Because every user has a unique email. And every user can have many workouts. So separate user information from training, It can reduce the redundancy of user information
_______________________________________________________________________________________

Question 1.5: Suppose you were told that instead of storing the user's city and state in the same table, you need to reference the location stored in a separate locations table (example shown below). Describe how you would reference the locations table from your training table? Which fields would you drop and/or add from/to your workout table? What would be your foreign key?

LOCATION_ID CITY STATE ZIP_CODE
34561 Boston MA 02101
30999 New York NY 10001
_I would add the field “LOCATION_ID”_to training table and reference the locations table use attribute
“LOCATION_ID”. Then I would drop fields “WORKOUT_CITY “ and “WORKOUT_STATE” from training table. The field “LOCATION_ID” will be the foreign key of training table._
____________________________________________________________________________________

Problem 2: Database Types (Points: 10)
VARCHAR(10), etc…):

Field Name Field Type
ID INT
INSERT_TIMESTAMP TIMESTAMP
WORKOUT_TYPE VARCHAR(30)
WORKOUT_DURATION TIME
WORKOUT_DISTANCE_MILES FlOAT
WORKOUT_DISTANCE_MILES FlOAT
WORKOUT_CITY VARCHAR(30)
WORKOUT_STATE CHAR(2)
WORKOUT_COMMENT TEXT
USER_EMAIL VARCHAR(30)
USER_NAME VARCHAR(30)




Problem 3: Relational Database Design (Points: 20)
Redesign the database by breaking up the TRAINING table into two tables (USERS and WORKOUTS) in such a way that the WORKOUTS table would reference the USERS table using a foregin key. The payment information is the user's way to pay the monthly subscription fees for the use of the application. For simplicity we assume that each user can have only one payment method on file, and it can be described by a single word such as "VISA", "MASTERCARD", etc. Some users might not have payment information on file.

Draw the diagram of your new design below and provide a short verbal explanation of your design. Are there any constraints that you would impose on the values of any fields, and why?:




For every user has a unique email. And every user can have many workouts. And a workout record must belong to a user.

USERS(USER_EMAIL,USER_NAME, USER_PAYMENT)
WORKOUTS (ID, USER_EMAIL, INSERT_TIMESTAMP, WORKOUT_DATE, WORKOUT_TYPE,
WORKOUT_DURATION, WORKOUT_DISTANCE_MILES, WORKOUT_CITY, WORKOUT_STATE,
WORKOUT_COMMENT)

There is a foreign key constraint, the field USER_EMAIL of WORKOUTS references USERS(USER_EMAIL). ________________________________________________________________________________




Problem 4: Create Your Database (Points: 20)
1. Using valid SQL:
- Write a statement to create a new database called "training" according to the design you created in Problem 3.
- Write statements to create your two tables
Make sure the relationships between the two tables are properly defined with a foreign key.

Provide all SQL statements used:

create database training;
use training;

create table USERS(
USER_EMAIL varchar(30) primary key,
USER_NAME varchar(30),
USER_PAYMENT varchar(30)
);

create table WORKOUTS(
ID int,
USER_EMAIL varchar(30),
INSERT_TIMESTAMP timestamp,
WORKOUT_TYPE varchar(30),
WORKOUT_DURATION time,
WORKOUT_DISTANCE_MILES float,
WORKOUT_CITY varchar(30),
WORKOUT_STATE char(2), WORKOUT_COMMENT text,
foreign key (USER_EMAIL) references USERS(USER_EMAIL)
);
________________________________________________________________________________

2. Write a SQL statement or statements to Insert all of the sample data from the table used in Problem 1 into your tables. Make the necessary adjustments required to set up relationship between your two tables.

Paste one of the INSERT statements into USERS table:

insert into USERS values ('lisa@mymail.com', 'Lisa Simpson', 'VISA'); insert into USERS values ('wonka@yahoo.com', 'Willie Wonka', 'PAYPAL'); insert into USERS values ('jhn@work.org', 'John Doe', NULL); insert into USERS values ('jdoe@vz.com', 'Jane Doe', 'MC'); insert into USERS values ('jr@yahoo.com', 'Jack Rabbit', NULL);

________________________________________________________________________________

Paste one of the INSERT statements into WORKOUTS table:




6.48,null, null, 'Scenic views');



________________________________________________________________________________

Problem 5: SQL Queries (Points: 40):
Paste the SQL statement and the screenshot of the results for each of the following
1. DESCRIBE for each of the 2 tables:

describe USERS;

describe WORKOUTS;

________________________________________________________________________________

2. SELECT * from each of the tables (two separate selects, one for each table):

select * from USERS;

select * from WORKOUTS;

________________________________________________________________________________


3. From table USERS, list all users who do not have their payment information on file.

select * from USERS where USER_PAYMENT is null; ________________________________________________________________________________

4. Update the users with no payment information to set the the value to "FREE"

update USERS set USER_PAYMENT='FREE' where USER_PAYMENT is null;
________________________________________________________________________________





________________________________________________________________________________


6. Delete the workouts that do not have the location information.

delete from WORKOUTS where WORKOUT_CITY is null and WORKOUT_STATE is null;
________________________________________________________________________________




________________________________________________________________________________


8. Remove the field WORKOUT_COMMENT from the WORKOUTS table. Paste the ALTER statement that does that and the screenshot of the DESCRIBE statement executed for that table after you removed the field:

alter table WORKOUTS drop column WORKOUT_COMMENT; ________________________________________________________________________________








Bonus Problem: Download a sample database from the MySQL website and import it into your database instance running on the EC2 instance. Write and execute a few queries as described below. (Points: 10)

1. Read the description of the employees database on the MySQL website:
https://dev.mysql.com/doc/employee/en/

2. Connect to your EC2 instance where you have the MySQL DB installed and download the zipped database to your computer from GitHub (you don’t need a GitHub account to do that): https://github.com/datacharmer/test_db - use the wget command (typed on a single line):

wget --output-document test_db-master.zip https://github.com/datacharmer/test_db/archive/master.zip

3. On the EC2 instance, follow the Install instructions to import your DB into your MySQL instance:
https://dev.mysql.com/doc/employee/en/employees-installation.html Hint 1: In the second step of that instruction, the correct option is
set storage_engine = InnoDB;

Hint 2: modify the import command by adding "-uroot -p", like this - enter your MySql root's password when asked mysql -uroot -p -t < employees.sql

4. Validate that your installation succeeded running the following command:

time mysql -uroot -p -t < test_employees_sha.sql

Paste the screenshot of the output of the test below:


5. Write and execute the following queries (and paste your queries and screenshots with results):
Note: use the diagram at https://dev.mysql.com/doc/employee/en/sakila-structure.html to understand the structure of the data. Remember that you have to login to MySql as root user now.

6.1. Using the employees database and based on the data in the employees table figure out what is the percentage of male vs. female employees working in the company.

Male Employees: % Female Employees: %

select (
select count(*) as male_count
from employees where gender='M'
)/(select count(*) from employees) as male_percent,
(
select count(*) as female_count
from employees where gender='F'
)/(select count(*) from employees) as female_percent;
______________________________________________________________________________


6.2. What are the 3 highest salaries in the organization:

select * from salaries order by salary desc limit 3;

______________________________________________________________________________

6.3. What are the 3 lowest salaries in the organization:
select * from salaries order by salary limit 3; ______________________________________________________________________________