PERSONNEL (PID, LName, FName, Phone, HireDate, Mgr#, PType) Ptype: housekeeping or activity guide Mgr#- employee's manag
Posted: Sat Nov 27, 2021 10:34 am
- Personnel Pid Lname Fname Phone Hiredate Mgr Ptype Ptype Housekeeping Or Activity Guide Mgr Employee S Manag 1 (75.28 KiB) Viewed 137 times
- Personnel Pid Lname Fname Phone Hiredate Mgr Ptype Ptype Housekeeping Or Activity Guide Mgr Employee S Manag 2 (166.01 KiB) Viewed 137 times
- Personnel Pid Lname Fname Phone Hiredate Mgr Ptype Ptype Housekeeping Or Activity Guide Mgr Employee S Manag 3 (96.49 KiB) Viewed 137 times
- Personnel Pid Lname Fname Phone Hiredate Mgr Ptype Ptype Housekeeping Or Activity Guide Mgr Employee S Manag 4 (95.68 KiB) Viewed 137 times
- Personnel Pid Lname Fname Phone Hiredate Mgr Ptype Ptype Housekeeping Or Activity Guide Mgr Employee S Manag 5 (19.7 KiB) Viewed 137 times
and two others) help just one SQL question below, thanks so
much.
- Personnel Pid Lname Fname Phone Hiredate Mgr Ptype Ptype Housekeeping Or Activity Guide Mgr Employee S Manag 6 (44.38 KiB) Viewed 137 times
PERSONNEL (PID, LName, FName, Phone, HireDate, Mgr#, PType) Ptype: housekeeping or activity guide Mgr#- employee's manager number HOUSEKEEPING (HKID, Assigned Condos) Assigned Condos: the condo numbers that have been assigned for cleaning. CLEANING (Condo#, DateCleaned, HKID) CONDOS (Condo#, Bldg#, UnitNum, SqrFt, Bdrms, Baths, WeeklyFee) Bdrms-number of bedrooms in unit Baths -number of baths in unit CONDOSTAYS (Condo# Guest#, StartDate, EndDate) StartDate-arrival EndDate - departure FAMILIES (Guest#, FName, Relationship, Birthdate) Guest's children: Relationship: son/daughter Rlname - last name of registered guest GUESTS (Guest#, RLName, RFname, City, State, Phone, SpouseName) Rename - first name of registered guest INVOICE (INV#, Condo#, Guest#) INVOICE DETAILS (INV#, RID, AID) RDate - date of reservation NumberinParty -number of people participating in the group RESERVATIONS (RID, Guest#, AID, GID, RDate, NumberinParty) ACTIVITIES (AID Description, Hrs, PPP, Distance, Type) Hrs-number hours for the activity PPP-price per person Distance -length in miles GUIDES (GID, CertDate, CertRenewDate)
Family Adventure Resorts PERSONNEL PID Name FName Phone Hiredate Mgr# Type HOUSEKEEPING HKID Assigned Condos (1,5) CLEANING Condo DateCleaned HKID CONDOS Condo# Bldg# UnitNum SarFt Bdrms Baths WeeklyFee FAMILIES Guest Fname Relationship Birthdate [Age] GUIDES GID CertDate CertRenewDate GUESTS Guest RLName # RFName City State Phone SpouseF Name CONDOSTAYS Condot Guest StartDate EndDate TotalFeePaid) RESERVATIONS RID Guest AID AID ACTIVITIES Description Hrs PPP Distance Type GID RDate NumberinParty (TotalCost) INVOICE INVE Condo Guest# IRL Name] (ArrivalDate] [Departure Dalej CondoFee] [Details Total [invoice Total [Sales Tax (TotalBilled] INVOICEDETAILS INVE RID AID [NumberinParty [Person Price [Activity Total [Details Total]
What the Model Does Tell You Arizona State University . . • the resort's guests are families, not individuals (the FAMILIES table holds the names of the quest's children) each employee (personnel) has one job • the resort is not tracking specific data for all of its employees (only the housekeeping staff for the condos and guides for the activities) There are 2 sides to the business: Guests staying at the condos Guests reserving activities that are available at the resort during their stay • several tables in the database require a calculation (derived data) • several tables in the database refer to data items in other tables without duplicating the data (Invoice, InvoiceDetails) • what else do you observe about the business from the EERD? .
What the Model Doesn't Tell You Arizona State University O . guests pay for a full week, even if they stay only a few days (condo weeklyfee) a one-week stay always starts on a Saturday (check-in) and ends the following Saturday (check-out) guests can be coming and going on the same day. guides must be certified to lead any activity guides must renew their certifications every 2 years; this allows them to continue to lead activities the database tracks each condo that is assigned to each housekeeper for cleaning (the AssignedCondos is not a 'total number of condos', rather the condo number) the resort is always adding new activities for their guests You may find out more about the business as you move forward into development . O
The following table aliases are recommended: activities = a cleaning = cl condos = 0 condostays = cs families = f guests = 9 guides = gu housekeeping = h invoice = i invoicedetails = id personnel =p reservations = 1 For column aliases and views, if you are asked to include them, they will be shown in parenthesis).
Question 1 10 pts Create a view june_activities to accomplish the following: Retrieve all activities that the guests participated in June, 2019. Your output should include: activity description (popular activities in june 2019), the activity type (activity type) and the number of people who participated in every activity (number_of_participants). Do not include duplicate records in the output. Display your results alphabetically by type. (Please leave the below text box as blank. Grading will be done based on your scripts file and screen shots file) Edit View Insert Format Tools Table 12pt v Paragraph v BI U A Tv р O words