logo Use SA10RAM to get 10%* Discount.
Order Now logo

Ask This Question To Be Solved By Our ExpertsGet A+ Grade Solution Guaranteed

expert
Logan AndressonPsychology
(5/5)

875 Answers

Hire Me
expert
Rasmi SehgalSocial sciences
(5/5)

590 Answers

Hire Me
expert
Stefan WoodComputer science
(5/5)

899 Answers

Hire Me
expert
Ishrat KhanAccounting
(/5)

925 Answers

Hire Me
Others
(5/5)

Create one new table Products_xxxx. Your table names must follow the above format

INSTRUCTIONS TO CANDIDATES
ANSWER ALL QUESTIONS

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.

 

(5/5)
Attachments:

Related Questions

. The fundamental operations of create, read, update, and delete (CRUD) in either Python or Java

CS 340 Milestone One Guidelines and Rubric  Overview: For this assignment, you will implement the fundamental operations of create, read, update,

. Develop a program to emulate a purchase transaction at a retail store. This  program will have two classes, a LineItem class and a Transaction class

Retail Transaction Programming Project  Project Requirements:  Develop a program to emulate a purchase transaction at a retail store. This

. The following program contains five errors. Identify the errors and fix them

7COM1028   Secure Systems Programming   Referral Coursework: Secure

. Accepts the following from a user: Item Name Item Quantity Item Price Allows the user to create a file to store the sales receipt contents

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

. The final project will encompass developing a web service using a software stack and implementing an industry-standard interface. Regardless of whether you choose to pursue application development goals as a pure developer or as a software engineer

CS 340 Final Project Guidelines and Rubric  Overview The final project will encompass developing a web service using a software stack and impleme

Get Free Quote!

404 Experts Online