FIT9132 ****Introduction to ****D atabases
Assignment ****1 - ****Database ****Design
Ocean Odyssey
| Purpose | Given the ****provided case study, students will ****be asked to transform. the ****information ****provided ****into a sound database design and ****implement ****it ****in Oracle. ****This ****taskcovers ******learning outcomes:**1. Apply the theories of t he ****relational ****database ******model.**2. ****Develop a sound ******relational database design.**3. ****Implement a ****relational datab ase ****based on a sound database ****design. |
|---|---|
| Your task | This ****is an open-book, ****individual tas k . The output for this task will ****be ****an ****initial ****conceptual ****model as a ****PDF document and a ****logical ****model ****imple mented ****in the ****Oracle RDBMS |
| Value | 40 % of your total marks for the unit |
| Due ****Date | Wed, 30 April 2025 a t 4:30 ****pm |
| Submission | ● Via Moodle Assignment Submission.● FIT GitLab check-ins will be ****used ****to ****assess ****the ****history ****of ****development |
| Assessment ****Criteria | ● Using the supplied case study description prepare a conceptual model identifying the required entities, attributes and relationships.● Normalise the supplied case study forms/s and integrate the resultant relations into a logical model derived from the identified conceptual model.● Depict the data requirements expressed in the case study via a relational database logical model.● Generate a schema that meets the case study data requirements from the logical model produced● Consistent use of industry-standard notation and convention |
| Late ****Penalties | ● 5% of the marks available for the task (-5 marks) deduction per calendar day or part thereof for up to one week● Submissions over 7 calendar days after the due date will receive a mark of zero(0), and no assessment feedback will be provided. |
| Support ****Resources | See Moodle Assessment page |
| Feedback | Feedback will be provided on student work via:● general cohort performance● specific student feedback fifteen working days post-submission (approved by ADE)● a sample solution |
Case Scenario
Ocean Odyssey (OO) is a worldwide travel company. The company books passengers on ships that operate cruises departing from various ports worldwide. Each ship is operated by a particular company known as the operator. Each operator is assigned an operator ID as an identifier, and the company's name and the Chief Executive Officer's name are recorded. A given operator operates one or more ships. For each ship, Ocean Odyssey records a ship code to identify the ship, the ship's name, the date the ship was commissioned, its tonnage, its maximum guest capacity and the name of the country where the ship is registered.
The cabins on a gi代写FIT9132 Introduction to Databasesven ship are identified by a cabin number (such numbers may be reused across ships, e.g. many ships may have a cabin D1). Ocean Odyssey records a particular cabin's sleeping capacity and the cabin's class for a given ship (this class classifies the quality of the experience and services available).
A cruise uses a particular ship (a cruise only uses one ship) and departs on a particular date and at a particular time. A cruise ID identifies each such cruise. Ocean Odyssey records the name of the cruise and a brief description of the cruise.
Passengers register with Ocean Odyssey when they make their first cruise booking. Each passenger is assigned a unique ID. The passenger's first and last name is recorded. Ocean Odyssey also records the passenger's gender and date of birth. If the passenger is a minor (i.e. under 18 years of age), another registered passenger must be designated as a guardian. The guardian must be able to be identified by the system. This data is used during booking to ensure minors are accompanied by their guardian.
Each passenger's address is recorded as a street (including street number), town, postcode, and country. When the members of a particular family book on a cruise, they often all have the same address.
Ocean Odyssey maintains a manifest (list of booked passengers) for all cruises they manage. This manifest records the cabin that has been allocated for each passenger for each cruise (this allocation is carried out when the passenger is booked on the cruise). For each passenger taking part in a cruise, OO also records the date and time when they first boarded the ship.
REMEMBER to keep up to date with the Moodle Ed Assignment 1 forum, where further clarifications may be posted (this forum is to be treated as your client).
To view Assignment 1 only posts, select the Assignment and then the Assignment 1 forum from the Categories list in the left panel.
Once selected, you can Filter the posts via the Filter option at the top of the list of posts:
Please ****be careful to ensure you do ****not ****publicly ****post anything that ****includes your ****reasoning, logic, or any ****part of your work to this forum. ****Doing so violates ****Monash ****plagiarism/ collusion rules and carries significant academic ****penalties. ****Use ****private ****posts to ****raise questions that ****may ****reveal ****part of your reasoning or solution.
You are free to make assumptions if needed; however, they must align with the details here and in the assignment forums and must be clearly documented (see the required submission files). Normally, such assumptions would only relate to minimum ****cardinality, which was ****not ****expressed ****in ****the ****case study.
GIT STORAGE
Your work for these tasks MUST be saved in the provided ******Assignment/Ass1 folder of your local repository and regularly ******pushed to ******the FIT GitLab s erver ******to build a clear history of the development of your model.
TASKS to ****be Completed
TASK ****1 Ocean Odyssey Conceptu al ****Model ****[15 ****Marks]
Based on the case scenario on page 2 of this document, prepare a CONCEPTUAL ****model for Ocean Odyssey. In preparing this model, you must only use ******the description provided on page 2 of ******this ******document. Your model must be saved in a file named oo_conceptual.pdf
Your development history, as pushed to Git Lab, must clearly show the steps you have been taught:
● Step 1: entities and keys
● Step 2: relationships, and
● Step 3: non-key attributes
The ****PDF file of your mode l must ****have at ****least three ****pushes ****(remember all ****pushes ****must ****be ****of ****a ****file with the same ****name - oo_conceptual.pdf) . Please note that three pushes are a minimum; you are free to make more (and we would expect more, in which case you will have more than one commit/push for each step). You must regularly check that your pushes have been successful by logging in to the FIT Git Lab server's web interface; you must not simply assume they are working. Do ****not forget to check that your GitLab author details are ****correct ****for ****every ****push. Before submission via Moodle, you must log in to the Git Lab server's web interface and ensure your final submission files are present.
GIT automatically maintains a history of all files pushed to the server. You do not need to, and MUST not, add a version name to your various versions. Please ensure you use the same name (oo_conceptual.pdf) for all saved versions of your solution.
The steps to complete th is task:
Using LucidChart, prepare a FULL conceptual model (Entity Relationship Diagram) using crow’s foot notation for Ocean Odyssey (OO) as described above.
● For this FULL conceptual model (ERD), include:
○ Identifiers (key s) for each entity
○ all required attributes and
○ all relationships. Cardinality (min and max) and connectivity for all relationships must be shown on the diagram.
● Surrogate ****keys must ****not ****be added to this ****mode l.
Your model must conform. to the ****unit ****ERD standards ****listed ****in the ****“Conceptual ****Modelling” ****Applied ****lesson "Unit ****Ent ity ****Relationship ****Diagram Standards" on ****Ed. Your name ****must ****be ****shown on your diagram, and ****it must be ****exported ****as ****an ****A4 ****p ortrait ****page .
TASK 2 Ocean Odyssey ****Normalisation ****[15 marks]
The image below shows two sample cruise itineraries:
Note that a cruise may "loop" around its origination port, i.e. depart from the origination port, return to the originating report and then depart again, all as part of the same cruise.
Perform. normalisation to 3NF for the data depicted in the supplied sample documents (note **there are two samples here; you onl y need to normalise one **document/itinerary). This normalisation must be based only on the depicted form. content - you must not introduce attributes not shown on the document.
The approach you must ****use ****is shown in the “Normalisation” Applied class solutions. You must begin by representing the document you are working on as a single UNF relation and then move through 1NF, 2NF, and 3NF. No ****marks will ****be awarded ****if you ****use a different ****approach.
During normalisation, you must:
○ Do ****not add surroga te ****keys.
○ Include all attributes shown on the form ****(you must not ****remove any attribute as derivable)
○ Clearly show ****UNF, ****1NF, 2N F and ****3NF.
○ Clearly show all candidate ****keys for each relation in 1NF.
○ Identify the ****Primary ****Key ****in all ****relations ****by ****underlining the ****PK attribute/s.
○ Identify all dependencies at the various normalisation stages (Partial at 1NF, Transitive at 2NF and Full at 3NF). You should use the same notation as depicted in the normalisation sample solutions, for example:
attr1 -> attr2, attr3
If none exist, you must note this by stating:
No partial dependencies pr esent and/or
No transitive dependencies pr esent
○ Carry out attribute synthesis ****if r equired.
The relation and attribu te ****names ****used throughout your normalisation ****and those ****on your ****subsequent ****logical model must ****be the same.
Your normalisation must be completed in an MS Word, Apple Pages, or Google document with a filename of oo_normalis ation.
If using MS Word or Pages, place the source document inside your local Assignment 1 Git Lab repo (Assignments/Ass1). The source document must be regularly saved and pushed to Git Lab as you develop your normalisation.
If you are using a Google document, you must regularly download the normalisation as a file called oo_normalisation.pdf and push it to Git Lab. You must maintain the source Google document and make it available to your marker on request.
Your normalisation must ****have at ****least three ****pushes (remember ****all ****pushes ****must ****be ****of a ****file with the same ****name - oo_normalisation ) t o GitLab. ****The file ****extension ****for ****oo_normalisation ****will depend on which software you choose to ****use. ****Ensure that your ****name ****is shown ****on ****ev ery ****page of the ****normalisation.
TASK 3 Ocean Odyssey ****Lo gical ****Model ****[55 ****marks]
Ocean Odyssey has supplied some further information to guide your modelling:
● The company records each passenger's contact phone number; for minors, no contact
number will be recorded (the contact for their guardian will be used). The phone number should be recorded as a simple attribute. A new entity should not be created to hold the phone number.
● Cabins across the various ships are assigned a cabin class as one of the following:
○ Interior
○ Ocean view
○ Balcony, or
○ Suite
These classes are fixed and will not be modified.
- Prepare a logical level design for the Ocean Odyssey database based on your Task 1 Conceptual model, the normalisations you carried out in Task 2 above and further details supplied here in
Task 3.
● The logical model must be drawn using the Oracle Data Modeler. Information engineering or Crow’s foot notation must be used to draw the model. Your logical model must not show data types. You must create a ****new em pty ****folder ****in ****your ****local ****repo, ****in ****the Ass1 ****folder, called oo_model, and then ****place your model ****inside this folder, ****naming the saved model as ****oo_logical.
● All relations depicted must be in 3NF. Candidate keys are possible natural keys; you ****must ensure your model ****protects all candidate ****keys to maintain the business rules.
● You must add at ****least on e surrogate ****key to ****your ****design ****(you are free to select the
most appropriate relation to make this change in). You must explai n why you ****added ****the ****surrogate ****key to your chosen ****relation as ****part of your assumptions. We have a unit rule about requiring a surrogate key if the relation has a composite key with more than two attributes, but this is not the only reason you might add a surrogate. You may add surrogate keys to multiple relations if you wish.
● All attributes must ****be commented ****in ******the ******database ******(i.e., the comments must be part of the table structure, not simply comments in the schema file).
● Check clauses/look-up tables must be applied to attributes where appropriate.
● You MUST ****include the legend in your model. Please edit the legend panel to show your name and ID number.
● Please carefully check the ****slide ****"Overall ****Design ****Process ****- ****ch ecklist" ****from ****the “Logical ****Modelling” Workshop and ensure you follow the steps ****listed.
● Your GIT repository must indicate your development history with multiple commits/pushes as you work on your model. A minimum of six ****pushes ****is ****required for your ****logical model as it is developed ******to show this ****history. You are free to ****make ****more pushes/commits and are encouraged to do ****so.
2. Generate the database schema ****in Oracle ****Data ****Modeler and ****use the schem a 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 and the commands to spool/echo your run of the script (as illustrated in "Logical Modelling" Applied Stage 3 on Ed ). In generating your schema file, ensure you:
● Capture the output of the run of your schema statements using the spool command.
● Ensure your script. includes drop table statements at the start of the script.
● Name the schema file as oo_schema.sql.
Please note when working with your model, ensure that you ****NEVER select ****any ****export ****options ****from the ****Data ****Modeler menu:
such actions can fill your Oracle account space and render it unusable.
Tasks ****1, 2 and 3 - ****Use of Modelin g ****Standards/Meeting ****Submission ****Requirements and Git ****usage ****[15 marks]
See the Marking Guide section of this document for further details.
Use of Generative AI tools
In this assessment, you can only use generative artificial intelligence (AI) to assist with design decisions. Any ****use of generative AI must be appropriately acknowledged ****(see Learn HQ)
Requirements
The following seven files are to be submitted and must ****also exist ****in your FITGit Lab server repo:
● A single-page ****pdf file ****containing your full final conceptual model. Name the file
oo_conceptual.pdf. This file must be created via File - Export (or Download As) - PDF from LucidChart (do ****not ****use screen capture) and must be able to be accessed with a development history via GIT. You can create this development history by downloading your
PDFs (don't forget to ****use the sam e ****name, oo_conceptual.pdf - ****DO ****NOT ****use version ****1, ****etc) and committing/pushing to GIT as you work on your model. In exporting from LucidChart, please select a ****page size of A4 with ****portrait mode.
● A PDF document showing your full normalisation of the sample cruise itineraries showing all normal forms (UNF, 1NF, 2NF and 3NF). Name the file oo_normalisation.pdf
● A single-page PDF file containing the final logical Model you created in Oracle Data Modeler. Name the file oo_logical.pdf. This pdf must be created via File - Data Modeler - Print Diagram - To PDF File from within Data Modeler, do not use screen capture.
● A zip file containing your Oracle Data Modeler project (when zipping these files, be sure to include the .dmd file and the folder of the same name). Name the zip file oo_model.zip.
Part of the assessment of your submission will involve your marker extracting your model from this zip, opening it in Data Modeller, and engineering to a new Relational model. From this, your marker will generate a schema, which will then be compared with your submitted schema (they must ****be the same for your schema to ****be accepted). For this reason, your model must ****be able to ****be opened ****by your marker and contain your ****complete ****model ****(i.e. ****both your logical and relational mode ls); ****otherwise, your ******submission ******will not be able ******to ******be fully marked, resulting in a significant loss of ******marks. You MUST carefully check that your model is complete - ensure you 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. Please view the video on Ed under the lesson "A6 Oracle Data Modeler Support Videos", which demonstrates this process.
● A schema file (CREATE TABLE statements) generated by Oracle Data Modeler. Name the file oo_schema.sql
● The output from the Oracle spool command showing the tables have been created. Name the file oo_schema_outpu t.txt
● ****A PDF document containing any assumptions you wish to make your marker aware of. Name the file oo_assumpt ions.pdf. If you have made no assumptions, submit the document with a single statement saying, "No assumptions made".
Your assignment MUST show a status of "Submitted for grading" before it will be marked.
If your submission shows a status of "Draft (not submitted)", it will not be assessed and will inc ur late ******penalties after the due ******date/time.
WX：codinghelp
