Part I: Please evaluate, analyze and answer the following questions.
1. (15 pts) Create one new table Products_xxxx. Your table names must follow the above format (table name is case sensitive) with following fields in correct data types. This table will be used in your project.
• p_id: auto_increment integer, primary key (2 pt)
• name: varchar(200), unique, NOT NULL (2 pt)
• description: varchar(200), NOT NULL (1 pt)
• type: varchar(50), NOT NULL (1 pt)
• sell_price: float, NOT NULL (1 pt)
• cost: float, NOT NULL (1 pt)
• quantity: int, NOT NULL (1 pt)
• e_id: int, NOT NULL, a foreign key to the employee_id field in TECH3740.EMPLOYEE table. (3 pts)
• v_id: int, NOT NULL, a foreign key to the vendor_id field in TECH3740.VENDOR table. (3 pts)
2. (10 pts) insert 5 records into your Products_xxxx table. The vendor names should be different.
3. (5 pts) Write a view named vHW_3_XXXX to show the total room number for each hotel that has 3 or more rooms. The output columns should be hotelname, number_of_rooms and it should be sorted based on the total room number from high to low. You should use the Hotel and Room tables in dreamhome database. Here is a sample output.
+ + +
| hotelname | number_of_rooms |
+ + +
| Latham | 3 |
| Grosvenor | 3 |
+ + +
4. (5 pts) Write a view named vHW_4_XXXX to list the hotel name, total rooms in the hotel, total revenue if all rooms were booked for the hotel, and the revenue is higher than 85, also the hotels are in London. Your view header names should be: hotelname, count, revenue. You should use Hotel and Room tables in dreamhome database. Below is a sample of the output.
+ + + +
| hotelname | count | revenue |
+ + + +
| Omni Shoreham | 2 | 89.98 |
| Grosvenor | 3 | 87.00 |
+ + + +
5. (5 pts) Create a view vHW_5_xxxx to list the project name, staff name and the staff’s branch city for the same staff who working on the same project more than one time. You should use Project, Staff2, Branch, and Working tables. The output column header should be (Project_name, Staff_name, Branch_city). The staff_name should combine fname and lname with a space in between. Below is a sample output:
+ + + +
| Project_name | Staff_name | Branch_city |
+ + + +
| Kean University | New Engineer | London |
| Citi Bank | Susan Brand | Glasgow |
| British Museum | Super User | London |
+ + + +
6. (10 pts) Create a view vHW_6_xxxx to show the employee who has the lowest salary for each gender. You must use UNION operation and Staff2 table in the dreamhome database. The output column name should be (gender, name, age, salary). The name should be combined fname and lname, with a space in between. The output should be female first and then male. Below is a sample output:
+ + + + +
| sex | name | age | salary |
+ + + + +
| F | Mary Howe | 51 | 9000.00 |
| F | Julie Lee | 56 | 9000.00 |
| M | New Tester | 31 | 8000.00 |
+ + + + +
7. (10 points) Create a view vHW_7_xxxx listing staff’s names, salary, ages, and their supervisors’ names, salary, and ages for all staff whose supervisor’s age is younger than the staff’s age and their supervisor’s salary is higher than the staff’s salary. You should use Staff2 table. The output header needs to distinguish between staff and manger: (E_name, E_position, E_salary, E_age, M_name, M_position, M_salary, M_age). Below is a sample output.
+ + + + + + +
| E_fName | E_salary | E_age | M_fName | M_salary | M_age |
+ + + + + + +
| Susan | 24000.00 | 81 | John | 30000.00 | 75 |
+ + + + + + +
Part II: Write Stored Routines. Grading criteria is based on the test cases for each question. xxxx is your database login ID. The email ID of the testing statements is demo.
8. (20 points) Please write a stored procedure named pHW_8_xxxx( city ) which will display the student’s transcript by a student’s first_name and last_name. The transcript should have the student’s first_name, last_name, all courses ID, course names and grades at which year/semester. Please refer to Students, Courses, Students_Courses tables.
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
