讲解：FIT2094、Database Design、SQL、S

FIT2094 - FIT3171 DatabasesS1 / 2019Assignment 1 - Database Design - AirTnT1.0. Scenario.Note: items marked with a are deliberately omitted and will be discussedin Section 3.1.After graduating, you are approached by a startup company which wants to competewith AirBnB in the accommodation and tourism sector. This startup, AirTnT -- short forAirBnB, tourism and technology -- intends to provide a more personalised experiencecompared to AirBnB, while having lower cost than traditional hotels and travel agencies.In the concept of the gig economy, AirTnT seeks to engage freelance tour guides andhouse-sharers.AirTnT has customers who are identified by a unique customer id. When a customerchecks in to an AirTnT facility (i.e. house-share), AirTnT records the customers first andlast names, address, date of birth, current mobile number, one for identity verification^>, one , andemergency contact number (if they are not already on the system). They also record thedate and time of check in. The system needs to maintain a record of all check ins for aparticular customer. When a customer checks out, the date and time of their departurefor this holiday is recorded.While in AirTnT, customers are located in a house-share. The house-share is identifiedby a house-share code and name. AirTnT wishes to record the total number of rooms ineach house-share and the number of currently available (empty) rooms. Rooms locatedin a house-share are assigned a room number within that house - thus, for example,each house has a room number 1. The rooms phone number (e.g. if a complimentaryVoIP phone is provided by the houses owner) and room type are also recorded. Roomsare classified (their room type) as either fixed or open-plan. Not all rooms are suppliedwith a complimentary phone.During a customers check-in, sometimes they may need to be moved from one room to FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 2another, possibly in a different house-share (e.g. if the room is damaged). If this occurs,the date and time the customer is assigned to the new room and/or house-share arerecorded (a history of all such assignments during check-in is required). While in AirTnTeach customer is assigned one tour guide (identified by a tour guide id) as their maintour guide. A customers main tour guide may be in charge of many customers. TheAirTnT system records each tour guides first and last names, phone number, andInstagram handle (e.g. @tour_guide). A tour guide may have one or morespecialisations (e.g. foodie tours, nature tours, bicycle tours, etc), but not all tour guideswho work for AirTnT have a specialisation.During their check-in, customers are prescribed with activities as part of their tourexperience by tour guides. Activities consist of things such as Pub Visits, BungeeJumping etc.; they also include activities which might be compulsory such as AirTnTBicycle Safety Induction or Buy AirTnT Travel Insurance. A customer may haveactivities prescribed by their main tour guide or any other tour guide working for AirTnT.An activity is identified by an activity code. Each activity has a name (such as Nevis134m Bungee Jumping) and includes a description of what the activity involves, thetime required for the activity and the current standard cost for this activity. When aparticular activity is conducted during a customers check-in, the date and time when theactivity is carried out is also recorded. A particular activity is completed before anyfurther activities are run (two of them cannot occur simultaneously). Some activities,such as Bungee Jumping are carried out by other Tourism Providers, morepersonalised activities may require a tour guide such as AirTnT Safety Induction.If a tour guide carries out the activity, the tour guide who completes the activity isrecorded (the tour guide who completes the activity may be different from the tour guidewho prescribes it). Even if a team of tour guides is involved in the activity (e.g. safetyinduction), only one tour guide (the most senior tour guide in charge) is recorded ascompleting the activity.If an activity is performed by a Tourism Provider (e.g. extreme sports facilities), AirTnTdoes not record the details of the Tourism Provider who completed the activity.Not all check-ins require an activity to be carried out (e.g. someone just wants to have astaycation and not go anywhere).Activities may require extra items such as stationery, food, USB sticks (for photos), orname badges. Each item held in stock is assigned an item code by AirTnTheadquarters. The item description, current stock and price are recorded. Foraccounting purposes, each item is assigned to a unique cost centre, such as FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 3Food/Beverage, Photography, or Stationery. A cost centre is identified by a cost centrecode and has a recorded cost centre title and admin team members name (this is aperson at AirTnT headquarters who is in charge of buying stock!). The quantity of eachitem used in a particular activity is recorded.Customers are billed for the cost for the activity itself and also any extra items whichare used as part of an activity. (e.g. Bike Ride and Picnic consists of the activity costitself and food items). The billed charge is based on the activity/item cost at the dateand time of the activity.AirTnT also records details of its housekeeping staff and their allocation to work in thehouse-share. At any point in time a housekeeping staff member can only work in (beassigned to) one house-share. A housekeeper is identified by a unique numerichousekeeper id. AirTnT also records the staff members first and last name, as well asan alphanumeric Police Check Certificate code (if available). The initial date ahousekeeper is assigned to work in a house-share is recorded. Housekeepers may bemoved between house-shares as staffing requirements change. When a housekeeperfinishes an allocation with a particular house-share the date they finished is alsorecorded. Within these changes, a house-keeper may return to a house-share theypreviously worked in; if they do so, a new allocation is recorded. FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 42.0. Sample Documents / ArtifactsFIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 5FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 63.0. Tasks.3.1. Compulsory RequirementsYour Details: Please ENSURE your ID, name, Authcate and unit code(e.g. 12345678 - Jack Ma - jma1001 - FIT2094) are shown on every page of anydocument you submit. If a document is a multipage document, such as for thenormalisation, please also make sure you include page numbers on every page.Version Control: All working files, as you work on this assignment task, must be storedin your personal Google Drive under a folder named with your student ID, Authcate,full name, and unit code (as above) and must show a clear history of development.Teaching staff require access to your Google folder to track and audit yourdevelopment progress and investigate academic integrity issues.: Note that in the original specifications (Section1.0), two items for the customer, “one verification^>” and “one ” weredeliberately omitted! Why? We want you, as future database analysts, to be able tocritically think of (and justify!) certain unknown details when faced with a real scenario.As a compulsory requirement - you are required to research what attributes (one forX, one for Y) can be used in the case study above. Hence - in Assignment 1B - foreach marked^ item, you need to supply the attribute name in your model. Moreimportantly, you must justify your decision for each of X and Y with a clear oneparagraphdescription/explanation of what they are! For each item, the researchand decision process (why you chose it as an attribute and why it is a goodchoice) should be carefully documented with at 2 references per marked^ item.These write-ups form a part of Item #7 in Assignment 1B (refer Section 4.2).EXAMPLE: let’s use a bad example which you cannot use in your own submission (youwill get no marks if you do use this example). You think that storing a 12 digit credit cardnumber is a good idea for “”. Therefore, inAssignment 1B, you will include the attribute name ‘credit_card_no’ in your models (conceptualand logical). Then, you will write a one-paragraph of why storing someone’s credit card is agood idea (e.g. ‘we need to store credit card numbers so that AirTnT can easily debit/credit theuser… this was also used in products such as blah blah… advantages of storing credit cards asopposed to cheques... etc’) as well as 2 references (e.g. Coronel & Morris Textbook CaseStudies, real-world business cases, research/journal papers etc) each.FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 73.2. Moodle Part A Submission: [5 marks]1. Using LucidChart, prepare an INITIAL conceptual model (Entity RelationshipDiagram) for AirTnT.○ For this initial conceptual model, include what you see as identifiers (keys)for each entity only (other attributes are not required) and allrelationships.○ Surrogate keys must not be added to this model. Connectivity andParticipation for all relationships must be shown on the diagram.Participation must show both minimum and maximum values (using asingle line for 1:1 is not acceptable for this unit).This initial conceptual model must be submitted to Moodle as Assignment1 Part A by the date as stipulated in Section 4.0.If this submission is not made by this date you will not be able to submitAssignment 1 Part B.Your tutor will provide feedback and guidance based on your submitted initialmodel which should be integrated into your continuing work in Part B.3.3. Moodle Part B Submission: [100 marks]2. Perform normalisation to 3NF for the data depicted in the sample AirTnTActivity Sheet and AirTnT Housekeeper Assignment Details.During normalisation, you must:○ Not add surrogate keys to the normalisation.○ You must include all attributes (you must not remove any attribute asderivable)○ Clearly show UNF, 1NF, 2NF and 3NF.○ Clearly identify the Primary Key in all relations.○ Clearly identify the partial and transitive dependencies (if they exist) in all1NF relations. You may use a dependency diagram or alternative notation(see the normalisation tutorial sample solution for a possible alternativerepresentation).Your attribute names as used in your normalisation and those on yourconceptual/logical models must be consistent i.e. the same name used on eachfor the same property.FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 83. Using LucidChart, prepare a FULL conceptual model (Entity RelationshipDiagram) for AirTnT.○ For this FULL conceptual model, include what you see as identifiers (keys)for each entity, all required attri代写FIT2094作业、代做Database Design作业、SQL语言作业代写、代做SQL编程设计作业 调试Matlbutes and all relationships. This fullmodel will be based on your feedback from your Part A submission, thenormalisation above and further reading of the case study. It may benecessary to revise/update this model while developing your logical modelin part 4 below.○ Surrogate keys must not be added to this model. Participation andconnectivity for all relationships must be shown on the diagram.4. Based on your final full version of your conceptual model, prepare a logicallevel design for the AirTnT database.○ The logical model must be drawn using the Oracle Data Modeler. Theinformation engineering or Crow’s foot notation must be used in drawingthe model.○ All entities depicted must be in 3NF○ All attributes must be commented in the database.○ Sequences must be used to generate numeric primary keys and checkclauses must be applied to attributes where appropriate.○ Be sure to include the legend as part of your model.○ Note that your Google Drive folder (discussed in S3.1) must clearlyindicate your development history with multiple updates as you workon your model. If your model is added to Google Drive without aclear development history - i.e. we cannot trace the actual progressof your development - IT WILL NOT BE MARKED.5. Generate the schema for the database in Oracle Data Modeler and use theschema to create the database in your Oracle account. The only edit you arepermitted to carry out to the generated schema file is to add header comment/scontaining your details (remember: ID, name, Authcate and unit code), dropsequence commands and the commands to spool/echo your run of thescript.○ Capture the output of the schema statements using the spool command.○ Ensure your script includes drop table and sequence statements at thestart of the script.○ Name the schema file as airtnt_schema.sql.FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 94.0. Submission Requirements4.1. Assignment 1A:DUE DATE PER UNIT GUIDE:Due date: Week 6, Monday 9 amAll times are Melbourne time - i.e. Australian Eastern Daylight TimeThe following files are to be submitted:● A single page pdf file containing your initial version of your conceptual model.Name the file airtnt_initial_conceptual.pdf.● This file must be created via File - Download As - PDF from LucidChart (do notuse screen capture) and must be able to be accessed with a developmenthistory via Google Drive. You can create this development history by uploadingsuccessive versions to Google Drive as you work on your model.4.2. Assignment 1B:DUE DATE PER UNIT GUIDE:Due date: Week 8, Monday 9 amAll times are Melbourne time - i.e. Australian Eastern Daylight TimeThe following files are to be submitted:1. A single page pdf file containing your final version of your conceptual model.Name the file airtnt_conceptual.pdf. This file must be created via File -Download As - PDF from LucidChart (do not use screen capture), again must beable to be accessed with a development history via Google Drive.2. A pdf document showing your full normalisation of documents A, B and Cshowing all normal forms (UNF, 1NF, 2NF and 3NF). Name the fileairtnt_normalisation.pdf3. A single page pdf file containing the final logical Model you created in OracleData Modeller. Name the file airtnt_logical.pdf. This pdf must be created viaFile - Data Modeler - Print Diagram - To PDF File from within SQL Developer, donot use screen capture.4. A zip file containing your Oracle data modeler project (in zipping these files besure you include the .dmd file and the folder of the same name). Name the fileairtnt_oraclemodel.zip. FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 10○ This model must be able to be opened by your marker and contain your fullmodel otherwise your task 4 will not be marked. For this reason, youshould carefully check that your model is complete - you should take yoursubmission archive, copy it to a new temporary folder, extract yoursubmission parts, extract your model and ensure it opens correctly beforesubmission.○ If your marker cannot open your file, as above, task 4 will be zero.5. A schema file (CREATE TABLE statements) generated by Oracle Data Modeller.Name the file airtnt_schema.sql6. The output from SQL Developer spool command showing the tables have beencreated. Name the file airtnt_schema_output.txt7. A pdf document containing any assumptions you have made in developing themodel or comments your marker should be aware of. Name the fileairtnt_assumptions.pdfNote that there are seven required files as itemised above. These files must bezipped into a single zip file named:a1combined----.zipe.g., a1combined-12345678-JackMa-jma1001-FIT2094.zip… before the assignment due date/time. ID, name, Authcate and unit codeSubmit the a1combined… .zip file to Moodle before the due date.Late submission will incur penalties as outlined in the unit guide.FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 11AppendixAppendix I: Marking Rubric Part AOutstanding (Range D - HD) Adequate (Range P - C) Not Adequate (N)Identified therequired Entities(1)● All/most entitiesidentified.● All/most keys arecorrectly identified.● Majority of entitiesidentified.● Majority of keys arecorrectly identified.● None or few of entitiesidentified.● None or few of keysare correctly identifiedIdentified therequiredRelationships (1)● All/most requiredrelationships identified.● Majority of requiredrelationships identified.● None/few requiredrelationships identified..Identified correctConnectivity andParticipation (1)● All/Most of depictedrelationshipsConnectivity andParticipation correctlyidentified.● Majority of depictedrelationshipsConnectivity andParticipation correctlyidentified.● None/few of depictedrelationshipsConnectivity andParticipation correctlyidentified.Able to correctlyuse the requirednotationconvention andbe consistent inits usage. (2)All notations in the model areconsistent and follow theunit’s ERD standards.Most notations in the modelare consistent and followthe unit’s ERD standards.Few notations in the modelare consistent or follow theunit’s ERD standards.FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 12Appendix II: Marking Rubric Part BOutstanding (Range D - HD) Adequate (Range P - C) Not Adequate (N)Identify the datarequirements tosupport anorganisationsoperations from thesupplied case studyand expressesthese via adatabaseconceptual model.(50)All AirTnT operations aresupported.● Required number ofentities are present● All/most requiredattributes and keyshave been captured● Surrogate keys havenot been added● All/most requiredrelationships havebeen captured● All/most requiredcardinality andparticipation constraintshave been captured● Good choice ofattribute for marked^ones in business case;as well as clearjustification andresearch.Some AirTnT operations arenot supported.● Majority of requiredentities are present● Majority of requiredattributes and keyshave been captured● Surrogate keyshave not beenadded● Majority of requiredrelationships havebeen captured● Majority of requiredcardinality andparticipationconstraints havebeen captured● Acceptable choiceof attribute for themarked^ ones inbusiness case withsome research.Many of the AirTnToperations are notsupported.● None or few of therequired entities arepresent● None or few of therequired attributesand keys have beencaptured● Surrogate keys havebeen added● None or few of therequiredrelationships havebeen captured● None or few of therequired cardinalityand participationconstraints havebeen captured● None or poorlyjustified attributesfor marked^ ones.Understand andfollow a databasedesignmethodology. (25)All/majority of the designprocesses have been correctlyfollowed:● All/most Normalisationprocesses are correct● Dependency diagramshave been provided andmatch normalisation.● ER diagram mapped tological model with onlyminor errors/omissions.● SQL Developer Relationalmodel correctly generatedfrom the logical model● Sequences have beencreated to provide numericprimary keys whererequiredSome of the designprocesses have beencorrectly followed:● Majority ofNormalisationprocesses are correct● Dependency diagramshave been provided andmatch normalisation inthe majority ofsituations.● ER diagram mapped tological model with only asmall number oferrors/omissions.● SQL DeveloperRelational modelcorrectly generated fromthe logical model● Sequences have beencreated to providenumeric primary keyswhere required in themajority of situationsFew of the design processeshave been correctlyfollowed:● Significant errors duringthe Normalisationprocesses● Dependency diagramsnot provided or havemajor errors● ER diagram mapped tological model witherrors/omissions.● SQL DeveloperRelational model notcorrectly generated fromthe logical model● Sequences have notbeen created to providenumeric primary keyswhere required FIT2094 - FIT3171 Databases S1 / 2019. ? Monash University. Page 13Appendix II: Marking Rubric Part B (cont’d)Outstanding (Range D - HD) Adequate (Range P - C) Not Adequate (N)Understand andapply the relationalmodel principlesinto practice. (15)All relational modelprinciples have beenfollowed:● All/most entities are inthird normal form.● All/most Primary andForeign keys arecorrectly identified.● All/most data integrityrequirements (Entity,Referential, Domain)have been correctlyidentified.Most relational modelprinciples have beenfollowed:● Majority of entities arein third normal form.● Majority of Primaryand Foreign keys arecorrectly identified.● Majority of dataintegrity requirements(Entity, Referential,Domain) have beencorrectly identified.Few of the relationalmodel principles havebeen followed:● None or few of theentities are in thirdnormal form.● None or few of thePrimary and Foreignkeys are correctlyidentified.● None or few of thedata integrityrequirements (Entity,Referential, Domain)have been correctlyidentified.Able to generateand modify aschema given alogical model inSQL Developer. (5)The DDL script wasexecuted without errors.The DDL script wasexecuted with errors.Able to correctlyuse the requirednotation conventionand be consistent inits usage. (5)All notations in the modelare consistent.Some notations in themodel are consistent.Few notations in themodel are consistent.转自：http://www.7daixie.com/2019043020189181.html