【正文】 nal model and write the SQL mands to create the tables for both the entities as well as relationships.27. The following questions are based on a Sporting Goods database described below:customer (id: int, name: string, city: string, country: string, rating: string,sales_rep_id: int )dept(id: int, name: string, region_id: string)sales_rep(id: int, last_name: string, first_name: string, dept_id: int, salary: int)order(id: int, customer_id: int, date_ordered: date, total: int)Write SQL queries for each of the following subquestions.A. Display the name, city, country and rating of all customers whose number oforders exceeds the “average” number of orders for a customer.B. Display the name of all the departments that have at least one employee.C. Display the first name and last name of all sales representatives who do not have customers. D. Find the countries in which there are no sales representatives. If required, make any assumptions and state them.28. Consider the relation R, which has attributes that hold schedules of courses and sections at a university。, 1200) into the product table.D. Find the all customers who have ordered a WII.E. Change the price of 39。 project.B. Find the names of employees who are directly supervised by 39。 X(R)B. 213。G, GD174。 guestID, from.A. Depose the relation in smaller relations such that – each of the smaller relations is in BNCF with respect to the projection of the original dependencies。 guestID, toroomNo, to 174。E, AE174。 A,B+C174。ProductX39。Personal Computer39。 Each building has a manager. The manager has a name, telephone no. and salary.R. D, CD 174。 {Days_Hours, RoomNo, NoOfStudents, InstructorSSN} {RoomNo, Days_Hours, Semester, Year} 174。Lady Gaga has put.25. Consider The database of a online game pany has three relations: player, play, and game for storing information about players who plays which game. The database schema of the game pany is shown as follows:player (member no, name, level, phone, )play (member no, game id, date, time)game (game id, title, type)Please draw the ER diagram.26. Consider a HollyWood Enterprise that requires modeling information about the different type of peopleA. involved in the movie production.B. _ Each person should have person ID, name, phone, gender, and address.C. _ There are two main groups of persons: Movie professionals and celebrity. Each movie professionals work on someD. pany.E. _ A movie professionals can be either a director or a agent. Each director has her or his popularity and can direct aF. number of movies. Each agent has the agent fee.G. A celebrity can be a movie star, a model, or both. Each movie star has her or his movie style and play in someH. movies. Each model has her or his preferences.I. _ Each movie has the information about the title, released date, and language.J. Draw a EER diagram for the above HollyWood Enterprise.K. The owner of several apartment buildings is interested in a database to manage hisL. business.M. Research39。Database39。GH ABD174。Company(ame:string, city:string)。Part 3: Questions and Answers1. Consider the following relational schema:student (student no, _rst name, last name)book (isbn, title, authors, publisher, year)loan (student no, isbn, checkout date, duration)Use SQL to write the following queries:A. Create the table for the book table appropriate domain and required contraints.B. Change the data type of the checkout date attribute of the loan table to date.C. Add a constraint into the loan table to make sure the loan duration is no more than 180 days.D. Grant Arvil and Amy select and update authrorization on the book table.2. What are 6 basic operators of relational algebra?3. Explain how naturaljoin operation can be acplished by basic relational algebra operations?4. Explain how the division operation can be acplished by basic relational algebra operations?5. The database of a research center contains the following three tables about employees,projects, and the time spent by the employees on the projects.Employee(ssn: int, name: string, jobTitle: string)Project(pid: int, name: string, sponsor: string, startYear: int, endYear: int)WorkedOn(ssn: int, pid: int, year: int, month: int, noHours: int).The table Employee lists all the employees of the centre. The table Project lists all the projects of the centre with their sponsor and the start and end year of the project. The table WorkedOn records h