Assume the following relational model representing the design for BurgerBay database and use it to answer questions
1.
Using BurgerBay relational model, write the SQL statements to create the tables
Supply,
Schedule
and
Employees.
Use the following information to define your attributes.
SupplierID :
integer ; Each supplier must provide supplies
RestaurantLicense :
integer ; Each restaurant must have supplies ;
Each restaurant must have an employee schedule
ScheduleID:
integer
EmployeeID:
integer
Day:
date format ; required value
StartTime:
integer; required value
EndTime:
integer; required value
EmployeeName:
50 characters maximum; required value
NbrYearEmployed:
integer; optional value
Employee_Type: 1 character; required value; only allowed values are ‘C’ (for Cook) and ‘M’ (for Manager)
2.
Using BurgerBay relational model, write the SQL statements answering the following queries :
a. Display the name and city of all suppliers.
SELECT SupplyName, City
FROM Suppliers;
b. Display the EmployeeID, employee name and number of years employed of all the Managers employed for more than 5 years. Display your results from the most recent to the most experienced managers.
c. Count the number of restaurants located in each of the following states : California (CA) , Oregon (OR) and Nevada (NV).
Assume that states are stored in the database using two characters code, meaning CA, OR and NV.
Make sure your query displays the number of restaurants for each state; NOT the number of restaurants for all states combined.
d. Using Subqueries, display the name and contract fee of the advertisement agencies working with restaurants located in the state of California.
e.
Using Join queries, display the name and contract fee of the advertisement agencies working with restaurants located in the state of California.
f. Using the method of your choice, display the EmployeeID, employee name , schedule day ,start time and end time of the employees who work for a restaurant located in the city of San Francisco.
Extracted text: Specialty SpecialtyID SpecialtyName Suppliers SupplierlD SupplierName City AdvertisementAgency Supply AdAgencylD AgencyName City State ZipCode ContractFee SupplierID Restaurantlicense Restaurant Restaurantlicense Size Street City State ZipCode Drive Thru Dineln AdAgencylD Express ERestaurantlicense DTOpenTime DTCloseTIme Dine-In DRestaurantLicense Max_Capacity Schedule SchedulelD EmployeelD RestaurantLicense Day StartTime EndTime Employees EmployeelD EmployeeName NbrYearEmployed Employee_Type Manager MEmployeelD OfficePhone Cook CEmployeelD CookingStation Cooking Station StationName CEmployeelD