Assignment 1: Setting up Databases (15%) This assignment relates to the following Cowrse Learning Requirements: CLR 1 - Plan, Prepare, Install, Configure, and Use a market-leading Database Management System, Data Modeling Engineering Tools, and Open Source Software. CLR 2 - Develop Advanced Database Design and Normalization CLR 3 - Develop advanced subjects and techniques of using the SQL database language Background You are a new employee at a startup company Van Whinkle. The company just purchased several mom and pop hotels that they want to incorporate under their Van Whinkle brand name. Because the company is new and the hotels were previously owned by small business owners, they do not have any electronic databases. Most have their data on paper. Only a few were a little bit more organized and had spreadsheets and QuickBooks. Instructions Execute the following steps. Please submit your work only where the instructions specify to do so. If you do not follow instructions, your submission will be rejected.. For this assignment use the tools specified in Activity 1. 2 Naming Standards The cummon naming standard is having UPPER. CASE table names and ProperCase ficld names. This naming standard ONLY applies to your Latabase design and the SQL your run in Oeicle ft done' 1 apply to the the actual obiect names created in Oracle. Underseores are conly used in naming when they are abselutely neteded to meparale woeds for readability. For exarple, SKI_ID is casier to read than SKITD. Nive: Thir tasuing satrilarf apgties ay all your diagraser and whew ywu mrite SELFCT Rann:hame FROM EMFLLAVEF. SELECT "Finaliame" FROM EMPLDYFE: fotabuares as is Beriawe ef these druble-pasales. 3 Add FEE table to Database Design You wilt create a one table dntabane design. This diagram is callod a Latabase design and WOT an I-R diagrant flecase uf the poefusion it crates the tern E-R tiayram will not be unot dariag this zieune 1. Creafe a new Database Desien diagram ii. Add a FHE table to your diagram. Name it FEE_NN where NN are the initals of your first and last natnie Stadcai Boob Clark wald name the table fFF HC ii. Add a primary key ID collomn to the FFF gable that has a matimum of 15 digits. Afply your changes. iv. Rename the priemary key constraint te Fecirk. Afply your changes. v. Add the following fields then save your changek. 4 Add SERVICE and GUEST tables The eormpany wants so track what seniees were chagged for which fets: To de this, a service table is created which will be a purcent of the fec able. Alis the cortipany wamts to erack whal racets w me clurgot which fees. So yoa croate a guest table that will be awsther purcet of the fere able. You da not add ptacit information so yoer FEE tallic. The FEE 2. Following the steps you ased so create the FEE, create a SERVICE rable and a GUEST table. Name them SERVICE_NN and GUEST_NN where NN is your initiaks. Save your changes each time you complete creating a able. 3. Create a noe-niandanory foreign key relationship line betwees the SERVICE (pareni) table and the FEE (child) table. Rename the forcign key constraint and foreigs key column to follow the ProperCase naming ptandard. Save your changes. ia. One guest is charged multiple fees. Create a non-emandatory foreign key relatienchip line berween the FEF table and the GUEST table. Make sure you pick the correst parent and the correct child in the relationship. Rename the foreign key consiraint and foreign key column to follere the ProperCase raming standard. Save your changes. Submit Database Design Add below a sereen shot of your Database Design dagram. To receive full marks your diagram mest includes your hbles, name, and student number with the cuinent date Exclude menasi toolkan, and objes brewsers cpaste your sereenshot heres 6 Forward Engineering Script - Generate a SQL. Seript for your Database Design Diagram ii. Remove all lines that prefix with lyyhens (these are columents and ane nut necoledil ai. Add a comment ce the first line that inclades your name, student ruuber, and current date. For the stadcm Bob Clark tbe comment could look like... - Bob Clark, 1234567x, 2022\%7.01 v. You should ONIY have the SQL statements starting off with: DROP. CREATE TABLE, ADD CONSTRAINT statements remaining v. Rewrite this script so table names are UPPER_CASE and columns are PreperCave this allows you to netse the serimt io nin on diflereas tatakanes) vi. Remove CASCADE CONSIRAINIS from the DROP saacment by making the statement look like the following vi. Morge the ALTER TABL.E statement with the CREATE TABL.E following the below pattenss, (this imgeoves the readability and inderstarslability of the scripin) - Plak? CREATE TARLE ITOLESAME F. JablevamelD, Zasifinh, CONSTR IINT JalleVawdPK PRLMARY KEY \& TaMeNameiD) (2) - CHICD CREATE TAREE TAEUE NAME I TakeNan:1D, Lasifieht, CONSTRAINT JaWleVamePN PALMARY KEY \& Jahe VamedD d. CONSTRAINT Construint,Vawe FORENGN KEY ( ParedLD) ) REFERENCES ChidTDWeNane (ChidTobde) viai. To avoid compile errors, verify you have your commas, berikets, and semi-colons in the correct places. a. Reoeder the DROP statenvents so the ehild table (FEE) is dropped befeee the parent tables are (SEKVICE and GUEST). Paents cannos be isceciafully droppod before their children x. Reorder the CREATE stacenenss so the child able (FEE) is created after the parent tables are (SERVICE and GUEST). Children cannot be usceeufully ereated befoce their parente. xi. Verify you have a senvicolon after the closing bracket for cach CREATE TABL.E statement xis, Save your \$QL Script as a file xiai. Run your Forward Eecgincering Script to create your SERVICE. GUEST, and FEE tables. 7 Submit Forward Engineering Script Add below a sereen shot of ONLY your DOS wndow. To receive full marks your sereenshot must inclades alt SOL statements with its results along with yoer name, student number, and curcen date. Maximiene your DOS windon before taking the screcnohot. cpasle your serecnshot here? Submit Table Designer Screen Shot Add below a sereen shot of the FEE table design proving that it was successfully ereated in the database. To receive marks your serecashot rrast includes your table name and AL.L. of its columns, data types, and attributes. cpasic your screenshot here:- Add Data to the GUEST, SERVICE, and FEE. tables You kecp all protat nands and infonmation in the GUEST table. Yos do not duplicate this informarion in any other table. The more duplicates of information, the thore precessing power and netwotz bandwidta you heed to use to cinsare all duplicakcs ane exactly the samos. For each (avere one sow for the deposis charged. The Feelifestription is Deposir Chargell. Her noem tale per day was $50 from Feb 3 ind to Feb toth. (sesec 8 ress of room raics charged. The FerlDeseripeicin is Room Kage Charged.) And her roota rate was 545 from Feb 11 th to Fed 17th. (store 7 rows of the room nat charged. The Feebeseription is Rewem Rate Charged. h. She was a smoker wo she was charged $25 for smoke darrage and 530 for the carpet boms on Fcb IT. (The Fecthescripetion is Smoke toanage for one shanet and Carpet Bums for another charge.) Hsilly ERliof was charged last year oe Jan 20hh. He is a stadent at Queces University and paid the student lower rate of $30 per day. Because he is a loyal guest he didn'y need to pay a deposit on Jan 20b. (thas means yon need to seore a tecoral showing the guest was charesd a dogosat of 0.000 fustin lisekman paid last year a disequnt rate of 5.55 per day asd a deposit of $20 bocause he is a smoker. He was charged the room rate for Feb 2nd, 8th, 17th, and 28 Decna Donor was charged last year on the I" of every month from January through to Apeil. Besase of her position, ahe is not charged a deposit or rooms rate. (ilas means yoe niced to recond the rooes rate being 0.00 and the deposit being 0.00 for cach 12 of the month) Submit GUEST, SERVICE, and FEE Data Screen Shots Add below a serten shot of ONLY your DOS window. To teceive full marks your screensbot must inclades all SQL statements with its results along with yeer name, student number, and cureen dase, Each row of data for cach table must take a single row. 5 rows in the table should take 3 rows in the sereen shot, Include ALL. columns, Maximize your DOS window before taking the sereenahes. -ipasie your serecmihots heres 11 Generate INSERT Statements 2. Generate your itsent sabements seript for your FEE, SERVICE, and GUEST tables Save your seript file 12 INSERT Statements Screen Shot hid below a sereen shot of ONLY your DOS window. To receive full warks your screensbot must inelodes all SQL satements with its resalts along witt your name, student number, and current date. Maximine your DOS window before taking the sereenchot. epaste your screcnshot here? 13 Add STAY, EMPLOYEE, VENUE tables A suy for a guest at a botel is determined not by when they are charged put by weien they formally check-iti and check-xad, Each lime a guest chocks-in, the datakase generates a new stay for the gueut and associates ail fees charged 50 that siay. 3 Add STAY, EMPLOYEE, VENUE tables A stay for a paest at a botel is desernined not by when they are diurged but by when they formally check-in and check-sut. Eachh time a paced checks-it, the datahase foencrates a new stay for the pued and anoseats all fect charged to that stay. Einpliyees charge guest fess. Unlike the otier tables sa far, the employec table uses a recursive relaticonship. A rocursise relationship is Where a table nefternces itself They arc ened ho show hicrirclass ef projecti, teams, or people. For employees, each enployec spperta to a redoee tables and simplify queraes since you doe't need sepuree belles for employees, managers, and their manayers. A vesac is an oryanization where a faes cen go and roceive services. fir can be a deficrent hedel, casino, spa, or golf sovene. L. Crate a STAY, EMPLOYEE, and VENUE table. Name them STAY_NN, EMPLOYEE_NN, and VENUE_NN whare NX is your initials. Save your changes each time you ceaplete creating a thble. ii. One stay can involve maltiple fees. Create a non-trandatory forengr key relationship line between the FIE table and the STAY uble Make wure you poick the correct porent and the correct child in the relarionship Rename the foreign key constraint and the foreign keyy solame to follow the ProperCase naming standard, Save your changes. iii. One cmployee can involve multiple stays. Create a non-mandatery forcign key relationship line between the STAY table and the EMPL,OYEE table. Make sure you pick the ceerect parent and the correct child in the relationship. Rename the forcign key constraint and the foreign key eolumin to follow the ProperCase narning stasdard. Save your changes, iv, One employee can have multiple errployses reporting to then. Create a non-mandatory forcign key relationship line between the EMTPLOYEE table and leep it back to the EMPLOYEE table. Name the foreige kiy coluena ManageriD. Rename the forcign key constraint to follos the ProperCase naming standard. Sove your changes. v. One venue can involve multiple emplayecs. Creale a not-musdatery foreign key telationship line between the EMPLOYEE table and the VENUE table. Make sure you pick the coerect parent and the cenect child in the relationship. Rename the forcign key constraint and the foreign key columa do follonw the ProperCase namiag standard. Swe your changes. vi. A guest can be an cmployee. Create a nop-laandatory foreign hey vi. A gaest can be an empleyec. Crene a foe-mandatiry foreiga key relationship line between the EMPLOYEE (garent) table ard the GUESI (child) table. Rename the foecign hey coestrint and foreign key column to follow the ProperCase naming standard. Save your champes. 14 Forward Engineer and Re-Insert Data Yea will sow recrate your datatune fiven the diagram resultiog in you Aeleting all of the tabies and their date yew alealy cmitered in. Make sure you have your INSER.T script you cmeated frum the provious sop iaved so you can te-phin it. 1. Ferward engineer youe datahase denga inemabber to drup all ablen before tesrrating themil ii. Execuete the INSFRT serist to ne-add yeer GUEST, FEF, and SERVICE data iii. Execute conmit: to chvure you cin vies your inserfed data. 15 Submit Forward Engineering and Insert Script Add below multiple screen shoes of ONLY your DOS window To roceive marks your scroenchots mast inclade all SQL salements with its results along with your natne, stobert number, add curnent date. Maximixe your Dos window befice biking the screen shots. paste your screwnichots here? 16 Add EMPLOYEE and Stay Data Yoe will nove emier in data for the teit of your tables 1. Add and conmin the following data is FMPLOYFE euch with their vun 1D. Once done, assaciate each anployee to the correct manager then cotrenit yoer updates. Boh smich is the manager of the hetel with 20 years' experience. He makes 580,000 per year and lives in Windwor Ontario. His manager is Deena Donor. Aeb Smist is the desk clerk with enly 5 years of experience at the hotel. He makes 515 per beer and lives in Totento Ontario. His manager is the other Bob Smith. Iamya Dencan docs maindenanoe and has been with the hotel for 10 years, She makes $5,000 per moeth and lives in London Ontario, Her manaper is Bob Simith. Decen Drer is the operatioe munager for the botel. ii. Add and commit the followine data no STAY cach with its eran StaylD. Associate this stay to the oumest mployes, gacst and foe tows. Kristoff Kurn checked in last year by the dejk elerk Bob Smith on Feb 3wi and checked out an Fob 17 17 Submit EMPLOYEE, GUEST, STAY, FEE Data Screen Shots Ada below a sereen thot of 0s2.Y yeur DOS windew. To receive full marks your seroenshot must inclabs all SOL satemats with is revalls along with your name, student tiemer, and current dite. Each row of bate for each table mast take a rinele tos. 5 rowo in the table should take 5 rows in the screen shot. Include ALI oolanss Matimine your DOS uindow before taking the kerecnibe. 7 Submit EMPLOYEE, GUEST. STAY, FEE Data Sereen Shots AdA below a sereen sbot of ONLY yeor DOS window. To reqetie fall marks your sereenshor must inclades all 50O stiecoents with its results along with your name, student number, and pareat dine. Each now of late for each table must take a single roes. 5 roms in the bable should take 5 roas in the screen shot Include ALL colarnse. Mrximize your DOS window before taking the sareenhe. paste your screenshots heres? 18 Any Blanks Query data from different nouces you will asod fill is the missing data. To talke's nocoed is blank. A blant in a dabluse is aet an empty itring, it is - NULL. A commot enor pognanmen male is aning the equat opernitor ( ) when chscking for blanks. A blant is nouline no value. So you cat' we the equal eptrater for the chock. Yoe me tie IS opsrator initent. 4. Use the follosing pattern to chack loe hlaeks in your GUEST table SEL.ECT fnll, Fnl? - Fink FROU RERE NAME MPERE Finldi IS ,Nt Le de findet as NtLt of FindeV is Null. ii. Add an ORDER, BY clause to son foum 2 so Aee last names aed from A to Z on first names. iii. Confinm the query nass soccecudelly Submit IS NULL Screen Shot Add below a screen ahot of ORL.Y your DOS window. To recenve fill marks your sereenchet must inclades all 50O, ictenents with its reults aliong with your nume, student number, and cancor dite. Fxch row of data for each query row it mast take a vingle tos. 5 nows in the resulta. sbould take 5 rows in the sereen shoe Matimue yoer DOS wiribes before taking the screcesbot. cpaste scroensons belows Equal NULL 1. Clange each is operator to (equab) ii. Confinm the query nuss muccesstally 21 Submit Equal NUL.L. Screen Shot hdd below a sereen shot of ONLY your DOS window. To receive full maiks your screenhet must incladss all 90C, satenents with its reulls along with your narne, abdent number, and curnet date. Fach now of data for each query row it must tales a sople now. 5 nows in the recults thould take 5 rows in the screen ahe. Maxinite yeer D0S wieders before taking the screcnchut. -paste strensibe belows 22 Fee Sum Query 22 Fee Sum Query Management wints multiple tepots thas shew the calculations of foes. Yoa will ctease a reublle query shat cabedtates the beal fees for the pom rate, dansege, and nomm service service mines i. Create a SELECT eutement that retiras the GoeatD froen the GUEST rable 7. Add the tables SERVICE and FEE. 10N them in your query. ii. Use WHERE and the IN cperaker to fiher the service aumes foom rate, damage, and room service. v. Add the GROUP BY so that agg 4gete finctioss are calculated for each GuesilD 2. Add the returning colume TotalfecClurged which is the agaregited SUM of the ForChargod for cach GaenD ii. Confirm the query runs successfully 23 Submit Fee Sum Query Screen Shot Ada below a sereen shot of 0NiY yoer DOS windew. To receive fall marks your screenshot mast includes all SQL itatenerss with its results aloeg with your nane, stident aumber, and curent date. Each row of data for cach query row it must take a single now. 5 rows in the results boald take 5 rows in the seroen she. Maxinine your DOS windiow before uking the kreenshot. cpaste screenshes belowo: 24 Including all Fees Query Manugeinent wants a teport that conponss bos asch gocsts and pending. 1. Copy the "Foe Sum query" 7. JOIN the Fee Sum qoery by maling it a subquery for the following top-level query SELECT F FROM FEE ii. JOIN GUEST and SERVICE within the query v. Use WHERE wo filier the resulteng query asing the same filher used in the subquery v. Change the gaery to reham OWIY the gaest's fint name, last name, date chargod, the fec description, the fice dlatgod, and the toeal charged. vi. Add an ORDER BY at the end of the kop level query to sort the botals calculaled frem highest to lowest. vi. Confirm the query runs succesalilly Submit Including all Fees Screen Shot Aid below a serees shot of ONIV your DOS window. To receive fall marks your sereenshot mast includes all 50L, asemests with its reiults along with yeur nune. student eurrher, and canent date. Exeh row of tata for cach query row it must take a single nos 5 rows in the results. bhould take 5 rows in the sereen she. Mrvizine yeur DOS window before tuking the screensht. Submit Including all Fees Screen Shot Add below a sereen shot of ONLY your DOS window. To receive fall marks your screenshot must inclades all 90L statesents with its resulds along with yeer name, sudent number, and cureet dite. Each row of data for cach qucty row it must take a single row. 5 now in the roels. bould abe 5 pows in the screen shat. Maximive your DOS wiadow before tuking the scroenshot. cpaste serecmahot below? 26 Excluding Fees Query ppocific ienioc names (foe topel) cash time it'in nam. Marugcmicnt mell to this by modifying the data in a REFrovt_Fil Tiek uble - Create a REPORT FHLTER tahle with the oclumn Fectype VARCHAR (20), Do not use forwand enginceriat wereate this nule 3i. Copy the "Includieg all Fees query" iii. Add NOT IN conditions to each WHERE chause to amit any foe fype in the REPORT_FILTER vable iv. Confirm the query runs saccessfully 27 Submit Excluding Fees Screen Shot Wud below a sereen shot of ONLY your DOS window. To recene flett marks your screenshot must inclades all SQL statcmets pith its renelta along with your name, student number, and cartent date. Fach now of Lata foe each query row it nuea take a single row, 5 nows is the tecelts hhould take 5 tows in the sereen shat. Masimire yoer DXOS =isdow befere taking the screcmshet. Cpaste screcnshot below? 28 Manager and Subordinate Query Managerisent wants a list of cmplogees and the name of the monaper each employee topocts 10. You will create a query dent spes each cmployer's find tame, last name, city and their manapre i fint name ast name and city: - Create a seloct that returns for firt name, last name, and city from the EMPLOYEE table 7. Add and L.EF JOMN a second EMPLOYEE tahle by guing the cecond LMPLOYYE table the atias MANAGER zai, Use WHERE to ecturn ryws when the lase nume that starts widh the letter B or K usiag the L.1KE operator x. Confirm your query runs correctly. Submit Manager and Subordinate Sereen Shot Wda below a sereen shot of ONLY yeur DOS w indors. Te qextive fall marks your screenshot must inclabes all SQL statonints with its reselts along with your name, student mumber, and curreat date, Each roe of lata for each query row it meal take a single row. 5 nows in the rnals phould take 5 rows in the screen shot. Masimine your DOS mindon tefere taking the screctsboe, Cpaste sereenshot below? 31 Submit Employee and Guest Screen Shot Add below a screen shot of ONLY yoer DOS window. To receive full marks your sereenshot must includes alf SOL staterents with its results along with your name, student number, ad current date. Each row of data for each query row it mast take a kingle row. 5 rows in the results should take 5 rows is the sereen shot. Mxvimire your DOS window before taking the screenshot. pasle serecenshot below> Extract Query Management wants to create a dita cxitict contriaing all the data in the tatshase. 1. Walk through your database design to see how each table is joined. Visualize a line you can draw through your diagram that will go through cvery table but will never cross itseif. ii. Based on that line, create a single query that joins all tables together where each JOIN will not result in a CARTESIAN PRODUCT iii. Confirm the query compiles 33 Submit Extract Screen Shot Add below a sereen shot of ONLY your DOS window: To receive full marks your sereenshot must includes all SQL statements with its results. along with your name, student number, and eurrent date. Maximixe your DOS window before taking the screenaboe. paste sereenshor below