Great Deal! Get Instant $25 FREE in Account on First Order + 10% Cashback on Every Order Order Now

First, download the datacode of the exam and create the ERD using "Reverse Engineering", then answer the questions below based on the ERD. (100 points total, 10 point each question). Show first name,...

1 answer below »

First, download the datacode of the exam and create the ERD using "Reverse Engineering", then answer the questions below based on the ERD. (100 points total, 10 point each question).

  1. Show first name, last name, phone number, and full address of customers (return total 4 columns)
  2. Find customers who like Standards but not Jazz.
  3. Show the entertainers who have no booking.
  4. Show agents who have never booked a Country or Country Rock group.
  5. List the entertainers who played engagement for either customers Berg or Hallmark.
  6. Delete musical styles that aren’t played by any entertainer.
  7. Add five percent to the salary of agents.
  8. Show the entertainers who have more than two overlapped bookings.
  9. List customers who have booked entertainers who play country or country rock or classical.
  10. Show all entertainers and the number (count) of each entertainer’s engagement.




Instructions

Download theinstruction (BUSA526-Porject-V2.Pdf) and two Sqlfiles.

Note that for Question 2, if the person is repeated twice (or more) in the dataset is considered a duplicate row whether the email is the same or not. For instance, Row 9,14, and 15 belong to an individual person, and these rows are considered duplicated rows.

ProjectQ2

Start Date
Jan 18, 2022 1:00 AM
Due Date
Mar 12, XXXXXXXXXX:59 PM
Attachments
Download All Files

Submit Assignment

Files to submit
(0) file(s) to submit
After uploading, you must click Submit to complete the submission.
Comments
ParagraphLato (Recommended)19px (Default)
SubmitCancel
Answered 3 days AfterMar 08, 2022

Solution

Nandini answered on Mar 11 2022
72 Votes
Solution No. 1
ERD to Normalized Relational Model Conversion
Employee Table:
    EmployeeID
    EmployeeFirstName
    EmployeeLastName
    SuperviseID
Course Table:
    CourseID
    CourseTitle
    EmployeeID
MySql Query:
Create Database Employee_Info;
use EMPLOYEE_INFO;
CREATE TABLE Employee
(
EmployeeID int primary key not null,
EmployeeFirstName varchar(255),
EmployeeLastName varchar(255),
SuperviseID int
);
CREATE TABLE Course
(
CourseID int primary key not null,
CourseTitle varchar(255),
EmployeeID int,
FOREIGN KEY (EmployeeID) REFERENCES Employee (EmployeeID)
);
use EMPLOYEE_INFO;
insert into employee values(1,'John','Mathew', 1);
insert into employee values(2,'Jade','Mathew', 2);
insert into employee values(3,'Tommy','Parker', 2);
insert into employee values(4,'Peter','Hope', 1);
insert into employee values(5,'Donas','James', 1);
insert into Course values(1,'Data Structure', 1);
insert into Course values(2,'DataBase', 1);
insert into Course values(3,'Artificial Intelligence', 2);
insert into Course values(4,'C Programming', 3);
insert into Course values(5,'Java', 4);
Solution No. 2
Solution No. 3
-- a) the most populated city in each country.
SELECT country.Name, city.Name, MAX(city.population) FROM city
LEFT JOIN country
ON city.CountryCode = country.Code...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here