FIT2094 – FIT3171 Databases S1 / 2019
Assignment 1 – Database Design – AirTnT 1.0. Scenario.
Note: items marked with a
After graduating, you are approached by a startup company which wants to compete with AirBnB in the accommodation and tourism sector. This startup, AirTnT — short for “AirBnB, tourism and technology” — intends to provide a more personalised experience compared 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 and house-sharers.
AirTnT has customers who are identified by a unique customer id. When a customer checks in to an AirTnT facility (i.e. house-share), AirTnT records the customer’s first and last names, address, date of birth, current mobile number, one
for identity verification^>, one , and emergency contact number (if they are not already on the system). They also record the date and time of check in. The system needs to maintain a record of all check ins for a particular customer. When a customer checks out, the date and time of their departure for this holiday is recorded.
While in AirTnT, customers are located in a house-share. The house-share is identified by a house-share code and name. AirTnT wishes to record the total number of rooms in each house-share and the number of currently available (empty) rooms. Rooms located in a house-share are assigned a room number within that house – thus, for example, each house has a room number 1. The room’s phone number (e.g. if a complimentary VoIP phone is provided by the house’s owner) and room type are also recorded. Rooms are classified (their room type) as either fixed or open-plan. Not all rooms are supplied with a complimentary phone.
During a customer’s check-in, sometimes they may need to be moved from one room to
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 1
another, 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 are recorded (a history of all such assignments during check-in is required). While in AirTnT each customer is assigned one tour guide (identified by a tour guide id) as their main tour guide. A customer’s main tour guide may be in charge of many customers. The AirTnT system records each tour guide’s first and last names, phone number, and Instagram handle (e.g. @tour_guide). A tour guide may have one or more specialisations (e.g. foodie tours, nature tours, bicycle tours, etc), but not all tour guides who work for AirTnT have a specialisation.
During their check-in, customers are prescribed with activities as part of their tour experience by tour guides. Activities consist of things such as Pub Visits, Bungee Jumping etc.; they also include activities which might be compulsory such as “AirTnT Bicycle Safety Induction” or “Buy AirTnT Travel Insurance”. A customer may have activities 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 “Nevis 134m Bungee Jumping”) and includes a description of what the activity involves, the time required for the activity and the current standard cost for this activity. When a particular activity is conducted during a customer’s check-in, the date and time when the activity is carried out is also recorded. A particular activity is completed before any further activities are run (two of them cannot occur simultaneously). Some activities, such as Bungee Jumping are carried out by other Tourism Providers, more personalised 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 is recorded (the tour guide who completes the activity may be different from the tour guide who prescribes it). Even if a team of tour guides is involved in the activity (e.g. safety induction), only one tour guide (the most senior tour guide in charge) is recorded as completing the activity.
If an activity is performed by a Tourism Provider (e.g. extreme sports facilities), AirTnT does 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 a ‘staycation’ and not go anywhere).
Activities may require “extra” items such as stationery, food, USB sticks (for photos), or name badges. Each item held in stock is assigned an item code by AirTnT headquarters. The item description, current stock and price are recorded. For accounting purposes, each item is assigned to a unique cost centre, such as
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 2
Food/Beverage, Photography, or Stationery. A cost centre is identified by a cost centre code and has a recorded cost centre title and admin team member’s name (this is a person at AirTnT headquarters who is in charge of buying stock!). The quantity of each item used in a particular activity is recorded.
Customers are billed for the cost for the activity itself and also any “extra” items which are used as part of an activity. (e.g. “Bike Ride and Picnic” consists of the activity cost itself and food items). The billed charge is based on the activity/item cost at the date and time of the activity.
AirTnT also records details of its housekeeping staff and their allocation to work in the house-share. At any point in time a housekeeping staff member can only work in (be assigned to) one house-share. A housekeeper is identified by a unique numeric housekeeper id. AirTnT also records the staff member’s first and last name, as well as an alphanumeric Police Check Certificate code (if available). The initial date a housekeeper is assigned to work in a house-share is recorded. Housekeepers may be moved between house-shares as staffing requirements change. When a housekeeper finishes an allocation with a particular house-share the date they finished is also recorded. Within these changes, a house-keeper may return to a house-share they previously worked in; if they do so, a new allocation is recorded.
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 3
2.0. Sample Documents / Artifacts
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 4
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 5
3.0. Tasks.
3.1. Compulsory Requirements
Your Details: Please ENSURE your ID, name, Authcate and unit code
(e.g. 12345678 – Jack Ma – jma1001 – FIT2094) are shown on every page of any document you submit. If a document is a multipage document, such as for the normalisation, 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 stored in 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.
: Note that in the original specifications (Section
1.0), two items for the customer, ¡°one
verification^>¡± and ¡°one ¡± were deliberately omitted! Why? We want you, as future database analysts, to be able to critically 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 for X, one for Y) can be used in the case study above. Hence – in Assignment 1B – for each marked^ item, you need to supply the attribute name in your model. More importantly, you must justify your decision for each of X and Y with a clear one- paragraph description/explanation of what they are! For each item, the research and decision process (why you chose it as an attribute and why it is a good choice) 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 (you will get no marks if you do use this example). You think that storing a 12 digit credit card number is a good idea for ¡°
Teaching staff require access to your Google folder to track and audit your
development progress and investigate academic integrity issues.
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 6
3.2. Moodle Part A Submission: [5 marks]
1. Using LucidChart, prepare an INITIAL conceptual model (Entity Relationship Diagram) for AirTnT.
¡ð For this initial conceptual model, include what you see as identifiers (keys) for each entity only (other attributes are not required) and all relationships.
¡ð Surrogate keys must not be added to this model. Connectivity and Participation for all relationships must be shown on the diagram. Participation must show both minimum and maximum values (using a single line for 1:1 is not acceptable for this unit).
This initial conceptual model must be submitted to Moodle as Assignment 1 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 submit Assignment 1 Part B.
Your tutor will provide feedback and guidance based on your submitted initial model 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 AirTnT Activity 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 as
derivable)
¡ð 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 all 1NF relations. You may use a dependency diagram or alternative notation (see the normalisation tutorial sample solution for a possible alternative representation).
Your attribute names as used in your normalisation and those on your conceptual/logical models must be consistent i.e. the same name used on each for the same property.
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 7
3. Using LucidChart, prepare a FULL conceptual model (Entity Relationship Diagram) for AirTnT.
¡ð For this FULL conceptual model, include what you see as identifiers (keys) for each entity, all required attributes and all relationships. This full model will be based on your feedback from your Part A submission, the normalisation above and further reading of the case study. It may be necessary to revise/update this model while developing your logical model in part 4 below.
¡ð Surrogate keys must not be added to this model. Participation and connectivity for all relationships must be shown on the diagram.
4. Based on your final full version of your conceptual model, prepare a logical level design for the AirTnT database.
¡ð The logical model must be drawn using the Oracle Data Modeler. The information engineering or Crow¡¯s foot notation must be used in drawing the 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 check
clauses 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 clearly
indicate your development history with multiple updates as you work on your model.
5. Generate the schema for the database in Oracle Data Modeler and use the schema to create the database in your Oracle account. The only edit you are permitted to carry out to the generated schema file is to add header comment/s containing your details (remember: ID, name, Authcate and unit code), drop sequence commands and the commands to spool/echo your run of the script.
¡ð Capture the output of the schema statements using the spool command.
¡ð Ensure your script includes drop table and sequence statements at the
start of the script.
¡ð Name the schema file as airtnt_schema.sql.
If your model is added to Google Drive without a
clear development history – i.e. we cannot trace the actual progress
of your development – IT WILL NOT BE MARKED.
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 8
4.0. Submission Requirements
4.1. Assignment 1A:
The 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 not use screen capture) and must be able to be accessed with a development history via Google Drive. You can create this development history by uploading successive versions to Google Drive as you work on your model.
4.2. Assignment 1B:
The 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 be able to be accessed with a development history via Google Drive.
2. A pdf document showing your full normalisation of documents A, B and C showing all normal forms (UNF, 1NF, 2NF and 3NF). Name the file airtnt_normalisation.pdf
3. A single page pdf file containing the final logical Model you created in Oracle Data Modeller. Name the file airtnt_logical.pdf. This pdf must be created via File – Data Modeler – Print Diagram – To PDF File from within SQL Developer, do not use screen capture.
4. A zip file containing your Oracle data modeler project (in zipping these files be sure you include the .dmd file and the folder of the same name). Name the file airtnt_oraclemodel.zip.
DUE DATE PER UNIT GUIDE:
Due date: Week 6, Monday 9 am
All times are Melbourne time – i.e. Australian Eastern Daylight Time
DUE DATE PER UNIT GUIDE:
Due date: Week 8, Monday 9 am
All times are Melbourne time – i.e. Australian Eastern Daylight Time
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 9
¡ð This model must be able to be opened by your marker and contain your full model otherwise your task 4 will not be marked. For this reason, you should carefully check that your model is complete – you should take your submission archive, copy it to a new temporary folder, extract your submission parts, extract your model and ensure it opens correctly before submission.
¡ð 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.sql
6. The output from SQL Developer spool command showing the tables have been
created. Name the file airtnt_schema_output.txt
7. A pdf document containing any assumptions you have made in developing the model or comments your marker should be aware of. Name the file airtnt_assumptions.pdf
Note that there are seven required files as itemised above. These files must be zipped into a single zip file named: a1combined-
e.g., a1combined-12345678-JackMa-jma1001-FIT2094.zip
… before the assignment due date/time. ID, name, Authcate and unit code Submit 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 10
Appendix
Appendix I: Marking Rubric Part A
Outstanding (Range D – HD)
Adequate (Range P – C)
Not Adequate (N)
Identified the required Entities (1)
¡ñ All/most entities identified.
¡ñ All/most keys are correctly identified.
¡ñ Majority of entities identified.
¡ñ Majority of keys are correctly identified.
¡ñ None or few of entities identified.
¡ñ None or few of keys are correctly identified
Identified the required Relationships (1)
¡ñ All/most required relationships identified.
¡ñ Majority of required relationships identified.
¡ñ None/few required relationships identified.
.
Identified correct Connectivity and Participation (1)
¡ñ All/Most of depicted relationships
Connectivity and Participation correctly identified.
¡ñ Majority of depicted relationships
Connectivity and Participation correctly identified.
¡ñ None/few of depicted relationships
Connectivity and Participation correctly identified.
Able to correctly use the required notation convention and be consistent in its usage. (2)
All notations in the model are consistent and follow the unit¡¯s ERD standards.
Most notations in the model are consistent and follow the unit¡¯s ERD standards.
Few notations in the model are consistent or follow the unit¡¯s ERD standards.
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 11
Appendix II: Marking Rubric Part B
Outstanding (Range D – HD)
Adequate (Range P – C)
Not Adequate (N)
Identify the data requirements to support an organisations operations from the supplied case study and expresses these via a database conceptual model. (50)
All AirTnT operations are supported.
¡ñ Required number of entities are present
¡ñ All/most required attributes and keys have been captured
¡ñ Surrogate keys have not been added
¡ñ All/most required relationships have been captured
¡ñ All/most required cardinality and
participation constraints
have been captured
¡ñ Good choice of
attribute for marked^ ones in business case; as well as clear justification and research.
Some AirTnT operations are not supported.
¡ñ Majority of required entities are present
¡ñ Majority of required
attributes and keys
have been captured
¡ñ Surrogate keys
have not been
added
¡ñ Majority of required
relationships have
been captured
¡ñ Majority of required cardinality and
participation constraints have been captured
¡ñ Acceptable choice of attribute for the marked^ ones in business case with some research.
Many of the AirTnT operations are not supported.
¡ñ None or few of the required entities are present
¡ñ None or few of the required attributes and keys have been captured
¡ñ Surrogate keys have been added
¡ñ None or few of the required
relationships have
been captured
¡ñ None or few of the
required cardinality and participation constraints have been captured
¡ñ None or poorly justified attributes for marked^ ones.
Understand and follow a database design methodology. (25)
All/majority of the design processes have been correctly followed:
¡ñ All/most Normalisation
processes are correct
¡ñ Dependency diagrams
have been provided and
match normalisation.
¡ñ ER diagram mapped to
logical model with only
minor errors/omissions.
¡ñ SQL Developer Relational model correctly generated
from the logical model
¡ñ Sequences have been
created to provide numeric primary keys where required
Some of the design processes have been correctly followed:
¡ñ Majority of
Normalisation
processes are correct
¡ñ Dependency diagrams
have been provided and match normalisation in the majority of situations.
¡ñ ER diagram mapped to logical model with only a small number of errors/omissions.
¡ñ SQL Developer Relational model correctly generated from the logical model
¡ñ Sequences have been created to provide numeric primary keys where required in the majority of situations
Few of the design processes have been correctly followed:
¡ñ Significant errors during
the Normalisation
processes
¡ñ Dependency diagrams
not provided or have
major errors
¡ñ ER diagram mapped to
logical model with
errors/omissions.
¡ñ SQL Developer
Relational model not correctly generated from the logical model
¡ñ Sequences have not been created to provide numeric primary keys where required
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 12
Appendix II: Marking Rubric Part B (cont¡¯d)
Outstanding (Range D – HD)
Adequate (Range P – C)
Not Adequate (N)
Understand and apply the relational model principles into practice. (15)
All relational model principles have been followed:
¡ñ All/most entities are in
third normal form.
¡ñ All/most Primary and Foreign keys are
correctly identified.
¡ñ All/most data integrity
requirements (Entity, Referential, Domain) have been correctly identified.
Most relational model principles have been followed:
¡ñ Majority of entities are
in third normal form.
¡ñ Majority of Primary
and Foreign keys are
correctly identified.
¡ñ Majority of data
integrity requirements (Entity, Referential, Domain) have been correctly identified.
Few of the relational model principles have been followed:
¡ñ None or few of the
entities are in third
normal form.
¡ñ None or few of the
Primary and Foreign keys are correctly identified.
¡ñ None or few of the data integrity
requirements (Entity, Referential, Domain) have been correctly identified.
Able to generate and modify a schema given a logical model in SQL Developer. (5)
The DDL script was executed without errors.
The DDL script was executed with errors.
Able to correctly use the required notation convention and be consistent in its usage. (5)
All notations in the model are consistent.
Some notations in the model are consistent.
Few notations in the model are consistent.
FIT2094 – FIT3171 Databases S1 / 2019. ý Monash University. Page 13