CC116/CB203程序写作、data课程程序写作

” CC116/CB203程序写作、data课程程序写作May Aug. 2020 Online Assessment CC116/CB203 DBMS/DB SystemsPage 1 of 4Question 1ScenarioUCSI Travel is a worldwide holiday company that maintains its own hotels in a numberof countries around the world. The company uses the following relational database tomanage its own business affairs. The relational database schema consists of relationssuch as COUNTRY, REGION, RESORT, HOTEL and FACILITY. These relations areshown in the descriptions below (please take not of the primary keys are underlined andforeign key are in emboldened italics): A relation about the countries in which the company operates holidays:COUNTRY ( CountryName string,Language string,Currency string,Continent string,TimeZone integer) A relation about the regions of different countries in which the holiday resorts arelocated:REGION ( RegionName string,Landtype string,Scenery string,CountryName string) A relation about the resorts in which hotels are located:RESORT ( ResortName string,BeachType string,NumOfBeaches integer,RegionName string) A relation about hotels in different resorts:HOTEL ( HotelId integer,HotelName string,Rating integer,TotalRooms integer,AvailRooms integer,WebPage string,ResortName string) A relation about the kinds of facilities in hotels and resorts:FACILITY ( FacId integer,Description string,FacType char,Category char) A relation about the facilities of each hotel:May Aug. 2020 Online Assessment CC116/CB203 DBMS/DB SystemsPage 2 of 4FACILITY_IN_ HOTEL ( HotelId integer,FacId integer)This relation represents a many-many relationship between FACILITY and HOTELCC116/CB203作业写作、data课程作业写作relations. A relation about the facilities of each resort:FACILITY_IN_ RESORT ( ResortName string,FacId integer)This relation represents a many-many relationship between FACILITY and RESORTrelations. There is a constraint that the type of facilities permitted for a resort must notbe related to accommodation i.e., the value of the FacType attribute of the facilitiesassociated with a resort must be O (meaning other facilities). A relation about bookings :BOOKING ( BookId integer,BookDate Date,ArrivDate date,DepartDate date,NumOfRooms integer,HotelId integerCost float,PayMethod string,BookGuestId integer,Status char) A relation about hotel guests :GUEST ( GuestId integer,GuestName string,Address string,BookId integer)The names of most attributes explain what kind of data it is and they hold. Here are afew explanatory notes for some (not all) attributes to supplement what is implied by theirnames.TimeZone The number of hours the countrys time is before or after USAtime. Assume the same difference in both summer and winter, asmost countries change between winter and summer time on almostthe same date.Landtype The type of the land associated with a region. Following are someof the land types:isle = Islandmain = MainlandRating The number of stars in a hotels rating.May Aug. 2020 Online Assessment CC116/CB203 DBMS/DB SystemsPage 3 of 4AvailRooms The number of rooms that are available for booking. Themaximum value of AvailRooms is equal to TotalRooms and theminimum value is zero.WebPage The URL of the web page of a hotel.FacType The facility type. This is either A for accommodation or O forother.Category Within each facility type, there are a number of categories offacility.For example, type A has the following categories:m = Mealsc = Childrenb = Bedrooma = AccommodationFor Example, type O has the following categories:s = Sporte = EntertainmentDescription This describes each facility. For example, a hotel might have twoO type facilities with category s described as swimmingpools and another described as horse riding.BookGuestId This identifies which of the guests made the booking on behalf of afamily or group.GuestId This identifies any guest.Status This is used by transactions and identifies the status of thebooking. It can be one of the following values:B = BookedA = ArrivedC = CancelledD = Departed/Checked-outP = PostponedTask: The management of UCSI Travel requires you to get the answers to thefollowing queries from their database. In each case, consult the relational databaseschema, write SQL statements that returns the required data.May Aug. 2020 Online Assessment CC116/CB203 DBMS/DB SystemsPage 4 of 4(a) Write Data Definition Language (DDL) statements to create all the tables in therelational database schema. (20 marks)(b) Write Data Manipulation Language (DML) statements to populate data. Verifythe creation of table (eg. describe COUNTRY) and insertion of data (eg.SELECT * FROM COUNTRY). You are required to display query results in thereport. (20 marks)(c) Write SQL statement to display names and beach types of the Spanish islandresorts. Display query results in the report. (10 marks)(d) Write SQL Statement to display names and identifiers of those hotels that havetype O facilities. Display query results in the report. (10 marks)[Total: 60 Marks]Question 2(a) Describe what database security is. (5 marks)(b) Describe FIVE (5) types of threats to database. (15 marks)(c) Discuss how to Rrevent ransomware infection to database.[Hint: you can includegood security practices in your answer] (20 marks)Note: Please ensure all answers for question 2 must be cited with references andreference list must also be provided. [Total: 40 Marks]END OF QUESTION PAPER如有需要，请加QQ：99515681 或邮箱：99515681@qq.com

“