A tutoring company wants to set up their database. They are…
A tutoring company wants to set up their database. They are trying to keep track of the following: student ID, name, grade level, the subject they need tutoring in and their contact and payment information. They also want to keep track of the tutors’ information such as their ID, name, subject matter expertise and times available and rate. They will like to keep track of which student is assigned to which tutor and their meeting schedule as well. (5 points)*List all of the data that needs to be kept track of for the scenario above in a 1NF (Flat File) table (20 points)*Use the normalization technique and form the 3NF tables (25 points)*Write out the Create Table command in SQL for creating the database you have designed, make sure to use the right DATATYPES, CONSTRAINTS, and COSTRAINT REFERENCES. Create Table ##### ( ); (20 points) *Write one SQL Insert into Values ( ); command for each table in your database ____________________________________________________________________________________ A new veterinary clinic will like to set up their database. The clinic will like to keep record of their customers such as: pet name, pet type, pet age, pet weight, owner name, owner address, owner contact. Also the clinic will like to keep track of the veterinarian name, specialty and schedule. They will like to keep track of the customer appointment date, what the appointment is for, and how much it will cost. (5 points)*List all of the data that needs to be kept track of for the scenario above in a 1NF (Flat File) table (20 points)*Use the normalization technique and form the 3NF tables (25 points)*Write out the Create Table command in SQL for creating the database you have designed, make sure to use the right DATATYPES, CONSTRAINTS, and COSTRAINT REFERENCES. Create Table ##### ( ); (20 points) *Write one SQL Insert into Values ( ); command for each table in your database ___________________________________________________________________________ A hair salon will like to set up their database. They will like to keep track of their customers (you can decide on what information to keep track of for the customers). They will like to keep track of their stylists (you can decide what information to keep track of for the stylists). They will like to keep track of the schedule between the customers and the stylists. (Payment information is not needed). (5 points)*List all of the data that needs to be kept track of for the scenario above in a 1NF (Flat File) table (20 points)*Use the normalization technique and form the 3NF tables (25 points)*Write out the Create Table command in SQL for creating the database you have designed, make sure to use the right DATATYPES, CONSTRAINTS, and COSTRAINT REFERENCES. Create Table ##### ( ); (20 points) *Write one SQL Insert into Values ( ); command for each table in your database __________________________________________________________________________ For Questions 4 – 11: Please refer to the 3 tables below (DEPTARTMENTS, EMPLOYEES, SALARYGRADES) DEPTARTMENTS DEPTNO DNAME LOC 10 ACCOUNTING LAS ANGELES 20 RESEARCH HOUSTON 30 SALES NEW YORK 40 OPERATIONS WASHINGTON DC EMPLOYEES EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 WU CLERK 7902 12/17/1980 800 20 7499 MITCHELL SALESMAN 7698 2/20/1981 1600 300 30 7521 JOHARI SALESMAN 7698 2/22/1981 1250 500 30 7566 OBI MANAGER 7839 4/2/1981 2975 20 7654 AKBAR SALESMAN 7698 9/28/1981 1250 1400 30 7698 SMITH MANAGER 7839 5/1/1981 2850 30 7782 JACKSON MANAGER 7839 6/9/1981 2450 10 7788 DWIGHT ANALYST 7566 12/9/1982 3000 20 7839 XIO PRESIDENT 11/17/1981 5000 10 7844 DHIMAN SALESMAN 7698 9/8/1981 1500 0 30 7876 CHOPRA CLERK 7788 1/12/1983 1100 20 7900 RODRIGUEZ CLERK 7698 12/3/1981 950 30 7902 PEREZ ANALYST 7566 12/3/1981 3000 20 7934 MILLER CLERK 7782 1/23/1982 1300 10 9999 ABDUL CAPTAIN 4/14/2002 6000 50 SALARYGRADES GRADE LOSAL HISAL 1 900 1200 2 1201 1500 3 1501 2100 4 2101 4000 5 4001 9999 ________________________________________________________________ (5 points) Write the SQL query to list all employee information in department 30. (5 points) Write the SQL query to list employees name, job, and salary that is a manager and has a salary > $2,000 (5 points) Write the SQL Query to select all employees with an “H” as the second character of their name. Use a wildcard. (5 points) Write the SQL Query to show a list of different jobs. Eliminate repeating values. (5 points) Write the SQL Query to show employee names and salary that has not earned a commission yet. (5 points) Write the SQL Query to show the employee name with the maximum salary (5 points) Write the SQL Query to show what is the difference between the highest and lowest salary? (5 points) Write the SQL Query to select employee number, name for all employees with salaries above the average salary. Use a subquery. Upon completion of this exam, please follow these steps: Save your word doc to the desktop using the following naming convention: “ITD256_Midterm_yourname Click the “Browse” link at the bottom of this exam page in Canvas Choose your word doc from the desktop Click Submit