FIT9132 Introduction to Databases Assignment 1 - Paris Arrow Transit

Sample 2 - an incomplete, future trip:

REMEMBER to keep up to date with the Ed Assignment 1 Logical forum, where further clarifications may be posted (this forum is to be treated as your client).

Please be careful not to 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. If you need to discuss your approach ensure you use ed private posts.

You can make assumptions if needed; however, they must align with the details here and in   the assignment forums and be clearly documented (see the required submission files). Other than surrogate keys, where appropriate, you must remember the design adage "All that is  required has been included, and all that has been included is required", i.e. you must not add features outside the requirements expressed in the brief.

Group Communication

Your group MUST use your private group channel in MS Teams for all group communication during this assignment, which is not face-to-face. Microsoft Teams provides facilities to support group interaction, including chat, group email, shared desktop, meetings, video/audio calling and shared files.

Activity in your private group channel is only visible to your group members and the teaching staff. It is important that you use Microsoft Teams for your group activities, as your marker may need to check the group members' contributions to the task and attendance at meetings—such a decision will be based on the activity in your private group channel ONLY.

Git Management

Ensure your group name is on every page of any document you submit. If a document is multipage (such as the normalisation), please include page numbers on every page.

GIT STORAGE

Your work for these tasks MUST be saved in your group's local working directory (repo) in the Ass1 Logical folder and regularly pushed to the FIT GitLab server to build a clear history of the development of your model. A minimum of nine pushes of your Oracle

Modeller model to the FIT Git Lab server is required. Please note nine pushes is a minimum, in practice, we would expect significantly more. This number of pushes must be evenly distributed amongst group members. All commits must include a meaningful commit    message that clearly describes what the particular commit is about and must be correctly assigned to a valid GitLab author.

Groups must regularly check that their pushes have been successful by logging in to the FIT

Git Lab server's web interface; you must not simply assume they are working. Before submission via Moodle, log in to the Git Lab server's web interface and ensure your   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 for all versions of a particular file.

Groups MUST NOT use REVERT or RESET when working on this assignment task.

Doing so could potentially cause serious errors in your remote repo. If you have problems pushing to the remote group repo, you should move your current local group repo out of the  way (to a new folder) and then reclone your group repo as discussed in the Applied 2 lesson (section A2-1.2).

Working on Oracle Data Modeler Models in your Group Repo

If multiple students work on a logical model simultaneously, merging these changes can be quite difficult since the files are not plain text files. For this reason, you must take a simple   approach to working on the model - lock the remote repo when making changes. Only one member of your group can and must work on the model at a particular time.

Whenever a particular student wishes to work on the model, they should go to the Git Server web interface and check if another group member has locked the Ass1 Logical folder.

If the folder is locked, you must not carry out any work on the assignment task.

If it has not been locked, you can proceed to lock the folder by selecting "Lock":

Ensure you are in the correct folder when this lock is applied.

You will know the items are locked as each will have a lock icon attached to it:

If you hover over the padlock icon, you can see who has the folder locked currently.

After locking the folder, you MUST do a pull (no changes must be made in your group local repo until the lock is in place AND this pull has been successfully completed). When you have completed your work and pushed it to Git, you should return to the Git web

interface and unlock the folder:

The model must not be completed by only one member of the group. In assessing your group's work, we will examine the commit log to ensure all group members have contributed to building the model.

Tasks to complete

1.   Perform normalisation to 3NF for the data depicted in the two supplied sample

documents. Remember there are only two documents to normalise (you have been given several samples of each to help your understanding of the likely content).

The approach you must use is shown in the normalisation applied class solutions.

The normalisation must be carried out form by form (i.e., one form at a time), beginning by representing the document you are working on as a single UNF relation and then moving through 1NF, 2NF, and 3NF. No marks will be awarded if you use a different approach.

During normalisation, you must:

○    Not add surrogate keys.

○    Include all attributes (you must not remove any attribute as derivable)

○    Clearly show UNF, 1NF, 2NF and 3NF.

○   Clearly show all candidate keys for each relation in 1NF.

○    Clearly identify the Primary Key in all relations by underlining the PK attribute/s.

○   Clearly 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 present and/or

No transitive dependencies present

○   Carry out attribute synthesis.

The relation and attribute names used throughout your normalisation and those on your subsequent logical model must be the same.

Your normalisation must be carried out in an MS Word document in your group’s

private MS Teams channel so that a full development history is available. All group   members must contribute to this normalisation (i.e. be visible as editors in the  document history). The document does not need to be pushed to Git Lab other than as listed in Submission Requirements.

2.   Prepare a logical level design for the Paris Arrow Transit database based on your

group's assignment 1 conceptual model, your marker's feedback, your reading of this case study, and the normalisations you carried out in step 1 above.

○   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. Your logical model must not show data types.

○   All relations depicted must be in 3NF. Candidate keys are possible natural

keys; you must ensure your model protects all candidate keys so that the business rules are maintained.

○   You must add at least one surrogate key to your design (you are free to

select the most appropriate relation to make this change in). You are required to explain the reason you added the surrogate key to your selected 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., they 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. Look-up tables do not require a unique index on their value since the

Database Administrator manages these tables

○   You MUST include the legend as part of your model. Please edit the legend panel to show your group name

○    Please carefully check slide 36 from the Topic 5 Workshop and ensure

you are following the steps listed

○   Your GIT repository must clearly indicate your development history with multiple commits/pushes as you work on your model.

To make your final pre-submission zipping of your project easier, please ensure you  start with the model inside a folder called pat_logical. You may call the model any suitable title.

3.   Generate the database schema in Oracle Data Modeler and use the schema to create the database in your Oracle account (this should be tested in your

individual Oracle accounts - a group Oracle account is unavailable).

The only edit you are permitted to carry out to the generated schema file is to add header comment/s containing your details (group/members names) and the

commands to spool/echo your run of the script. 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 pat_schema.sql.

4.   Maintain a Group Diary that records when the group met to discuss/work on the task, including the date, who was present and a brief statement of what occurred. This

Group Diary must be maintained in Microsoft Teams as a shared document in your private group channel and should be a continuation of the diary you started in  Assignment 1 conceptual. It does not need to be pushed to Git Lab other than as listed in Submission Requirements.

As part of your assignment submission, each group member will be required to provide confidential feedback on the group member's performance/interactions via FeedbackFruits.

Please note when working with your model, ensure that you NEVER select any export options from the Data Modeller menu:

such actions can fill your Oracle account space and render it unusable.