158337S2 Logical and Physical

158337, S2, 2024 Page 1 of 9 Project Instructions IN 
CONFIDENCE 
158.337 Group Project Instructions: Part A 
(Course mark - 17.5%) 
 
You will work in groups of 3-4 members for this assignment. Register yourself on the 
Group registration link (under the Assignment section on the Stream site). Use the 
following set of steps to guide you in completing the assignment project. You should 
turn the work in for grading by the due date (Tuesday, 27th Aug 4.00 pm). Lateness: 
There is a 10% penalty / per day, and no submission is permitted after 3 days. 
 
Note: Make sure you have altered the password of your allocated group Oracle a/c 
from the given default (GROUP01, GROUP02,…case sensitive). You do not want 
other people accessing your account. Remember it is your responsibility to protect 
your work. For the assignment, it is the joint responsibility of all the group members. 
 
 Logical and Physical Database Design 
 
Read Te Ohanga Village - Massey University Student Accommodation Office 
Case Study given in the Appendix A. 
 
For Part A, complete the three steps (Step 1 – Step 3). 
 
Part A: Step 1 (30 marks) 
 
Create a logical (relational) data model (e.g., Database or ER diagram). Use J 
Developer or Visio* to create this diagram. Use one of these two tools only. 
 
Note*: Do not extract your diagram from SQL Developer after creating tables or 
DataModeler (notation is different and some required modelling information to fully 
assess will be missing so cannot be graded fairly). Use of other tools (e.g. Generative 
AI tools), extractions from tools such as SQL Developer will all lead to a straight 
Zero. 
 
Use appropriate naming conventions while naming tables, attributes, relationships, etc. 
in your diagram. Also, use appropriate attribute datatypes, data sizes, etc. in the 
diagram and ensure to flag PKs, FKs, etc. 
 
State the business rules very clearly (no ambiguity), consistently under a separate 
section (in your report). There should be two business rules for each relationship 
shown on your diagram (For example, if there are 10 relationships then there should 
be 10 pairs of rule statements). Similarly, state your reasonable assumptions (this 
helps us to evaluate your design better) under a separate section. The work presented 
in these two sections should be consistent with your ER diagram. 
 
Examine each table’s (entity's) attributes for dependencies and apply the rules of 
normalisation discussed in Chapter 6 of the textbook. You are required to show the 
detailed normalisation process {e.g., how you move from lower normal form (e.g., 
1NF) to the next (e.g., 2NF)} in the submitted work. Use dependency diagrams for 
depicting all the relationships among a table’s attributes. Normalise tables up to the 158337, S2, 2024 Page 2 of 9 Project Instructions IN 
CONFIDENCE 
highest NF (up to 4th
 NF should be fine). State the normal form for each of the tables 
at every step in the process. 
 
Part A: Step 2 (20 marks) 
 
Generate the physical model based on the logical model created in the Step 1. To 
create the required set of tables, relationships, constraints, etc. write SQL scripts using 
a text editor (e.g., Notepad++). Use SQL Developer for initial checking / debugging 
and running of the code. Keep improving your code until you are satisfied with the 
design. Please ensure that it is consistent with your ERD (you may have to revise your 
ERD if you come up with better design during this stage). 
 
Check the DDL code in your script file and make sure that all the attribute definitions 
(e.g., names, data types, data sizes, whether required, domain values including 
defaults, etc.), table constraints (e.g., entity integrity, referential integrity, etc.), etc. 
are specified adequately and correctly. 
 
Validate your work by ensuring that query transactions {(a) - (v) listed towards the 
end of the case study} are supported by your design. Make sure that the physical 
design is robust i.e., meets the requirements of a good design. Remember 
normalisation alone does not produce a good design. In the end, ensure that both 
logical and physical designs are consistent with each other. 
 
Run your final scripts to generate the database (i.e., physical model). Use your Group 
Oracle a/c to implement the final DDL code. 
 
Part A: Step 3 (10 marks) 
 
Finally, create appropriate test data to populate the tables you created in Step2. Use 
SQL Developer to run a script file that contains SQL INSERT statements. 
 
There should be sufficient rows of the test data across all the tables. No table should 
contain less than five rows. Make sure your test data appropriately and sufficiently 
reflects your design (this would usually mean adding more than 5 rows in some 
tables) and does not violate any of the constraints declared while creating the 
database design in Step1 and Step2 (i.e., create table scripts and the ERD). Set up all 
the tables and their constraints in the correct order before you load any data into 
the database. In summary, the grader should be able to verify your design 
convincingly, comprehensively and with ease. 
 
Notes: 
Apply good naming conventions, which are also self-explanatory (e.g., name create 
table script file as createTables.sql, name your tables, attributes appropriately, etc.). 
Additionally, annotate your code (both DDL and DML) with appropriate and enough 
comments to enhance understandability. Consistency between work done in all the 
steps is very important otherwise you will unnecessarily risk losing a good number of 
marks for this reason. 
Check your project work and make sure that all the scripts run without any errors. 
Also, ensure that appropriate names have been given to all files. 158337, S2, 2024 Page 3 of 9 Project Instructions IN 
CONFIDENCE 
This assignment is also testing only your basic SQL knowledge, so write code for all 
the DDL and DML statements. Do not give us automated statements that are derived 
from Oracle tools (i.e., SQL Developer or JDeveloper). No marks will be allocated 
for DDL/DML scripts generated by Oracle tools. The code should NOT be tied to the 
schema, and we should be able to run the code from any Oracle account. 
 
Assignment Submission 
 
Organise your project report (printer-friendly, in case we need to print – grader’s 
preference) to include all the assignment requirements. Make sure the report contents 
are also in the same order as the laid requirements. 
 
There are three parts to this assignment submission: 
• a project report (word/pdf – soft copy), 
• scripts (2 code files – sets of create and insert statements), 
• database (tables, etc under your Group account). 
 
Note: 
Enter your names, student ids on the project marking sheet (Appendix B). 
Remember to provide the (Group) Oracle username in your report. The 
Password not needed. 
Checklist for project report (word/pdf) contents 
 
 1. A logical relational model (database or ER diagram) that is laid out clearly 
and legibly for grading. The text font size should not be very small (otherwise 
risk getting a straight zero)*. The diagram should show all the entities, 
attributes (with appropriate data types, sizes, etc.), primary keys, foreign keys, 
and relationship details (appropriate association names, cardinality, strength 
(identifying or non-identifying), participation (mandatory or optional)), etc. 
Make sure you have applied and shown the normalisation process to arrive at 
the final model. Pay special attention to the readability of the ER diagram so 
that one could print and assess with ease (as this is the core of all the work). 
 
2. A discussion of the reasonable assumptions you make about the ambiguous 
aspects and / or reasonable extensions to the given case study. We anticipate 
that each group's design will differ** from those of other groups based on 
the underlying assumptions. 
 
3. A list of business rules for each relationship on your diagram (clearly state 
two unambiguous rules for each relationship as these are bi-directional). 
 
4. DDL and DML code that is taken from the command files used for creating 
(create statements) and populating (Insert statements) your Oracle tables. 
 
This should NOT be auto generated code and/or linked to your schema 
account. Remember to include declarative constraints, etc in Create table code. 
Do not provide Alter statement code separate to the Create statements i.e. 158337, S2, 2024 Page 4 of 9 Project Instructions IN 
CONFIDENCE 
place all the constraints next to the attributes where possible. Basically, use 
only one single statement to create a table. 
 
5. Test Data (well formatted, readable dump of contents for all the tables) that 
can be referred to with ease. This helps in evaluating the design. 
 
Checklist for submission under your Group Oracle a/c 
 
All the tables with test data must be available via your Group Oracle a/c. 
Checklist for submission on Stream 
Compressed Folder – containing Report (a word/pdf file) and coding files (2 .sql 
files). Include your IDs in the name of your compressed file. 
Submit on the Assignment (Part A) link. Only one single submission per group. 
Plagiarism and other project guidelines: 
* If your diagram elements are too small then a ZERO mark would be awarded. In 
addition, ensure that the layout of your diagram is good (e.g., avoid many crisscrosses,
etc.) and the font size of the entities, attributes etc. is at least Arial 11 (or 
similar size if using a different font family, style). Make your diagram easy to read for 
the grader. You might want to use print settings for ERD with multiple (A4) pages or 
format diagram as per A3 size. Please put in effort to make it presentable and readable 
or risk losing marks. 
Use of any AI tools is NOT Permitted for the course assessments. You can find 
more information about the Student Academic Integrity Policy here. This will lead 
to a ZERO and may lead to an academic investigation by the Academic lead. 
We suggest you keep a record of the intermediate work (work in progress) leading up 
to your assignment submission as you may be asked to establish that this is your work 
and is done over a period of time. 
 
**A ZERO mark will be given to ALL the collaborating parties (no discussion on 
who did the original work and who copied). Any partial copying will also be awarded 
a straight ZERO. 
Make sure all your work is complete before you submit. Graders will not be 
searching/chasing you for any not easily traceable/missing assignment components. 
Not adhering to any of the assignment requirements, may also be given a straight 
ZERO. 
Note: Turnaround time for assignment may be slightly over the usual three weeks. 158337, S2, 2024 Page 5 of 9 Project Instructions IN 
CONFIDENCE 
Appendix A 
Te Ohanga Village, Albany - Massey University Student 
Accommodation Office Case Study 
The Director of Te Ohanga Village, Albany - Massey University Student 
Accommodation Office requires you to design a database to assist with the 
administration of their accommodation office. After involving various stakeholders, 
the requirements collection and analysis phase of the database design process has 
provided the following requirements for the Student Accommodation Office database. 
These requirements are followed by examples of the query transactions that must all 
be supported by the database design. 
1. The data stored on each full-time student must include student details such as the 
identification number, name, home address, age, gender, category of student (e.g., 
undergraduate, postgraduate), nationality, smoker/non-smoker, special needs, any 
additional comments, status (e.g., placed/waiting), and what degree course the 
student is studying towards, etc. Accommodation is available for students with 
accessibility requirements, but these must be recorded in the system. 
 
The student information stored relates to those currently renting some form of 
accommodation and those on the waiting list. Students may rent a room in a hall 
of residence or a student flat (4-5 bedrooms apartments). Post-graduate, mature 
students may in addition opt for renting studio units. 
When a student joins the university, he or she is assigned to a staff member who 
acts as his or her Advisor (of studies). The Advisor is responsible for monitoring 
the student's welfare and overall academic progress. The data held for a student's 
Advisor includes their name, position, name of department, internal telephone 
number, room number, etc. 
2. Each hall of residence has a name (e.g., Tui, Pukeko, Weka), address, telephone 
number, and a hall manager who supervises the operations of the hall. The halls 
provide only single rooms, which have a room number, place number, and 
monthly rent rate. These have shared, private bathrooms and communal areas on 
each floor. Currently, each of the three halls can accommodate up to 70 students. 
Halls of residence offer fully catered services (2 meals per day) but can 
accommodate to various dietary requirements. 
The place number uniquely identifies each room in all the halls controlled by the 
student accommodation office and is used when renting a room to a student. 
3. The accommodation office also offers student flats. These flats are fully furnished 
and provide single room accommodation for groups of four to five students. They 
have common areas that flat members share. The information held on student flats 
includes a flat number, address, and the number of single bedrooms available in 
each flat. The flat number uniquely identifies each flat. Flats are self-catered. 
Currently there are 70 flats. 158337, S2, 2024 Page 6 of 9 Project Instructions IN 
CONFIDENCE 
Each bedroom in a flat has a monthly rent rate (this can occasionally very based 
on the room area), a room number, and a place number. The place number 
uniquely identifies each room available in all student flats and is used when 
renting a room to a student. 
4. The studio units offered by the accommodation office allow only for single 
occupancy (i.e., cannot be shared by the students). Mature students with small 
families usually rent these units (e.g., they can have a partner and/or a child living 
with them). The number of members residing in a unit needs to be recorded along 
with some essential details about each of the members (name, age, etc). 
Each studio unit has a monthly rent rate, and a unique studio unit number. In 
addition, studio units have the fully or partly furnished options but are selfcatered.
Currently there are 12 units. 
5. A student may rent a room (in a hall or student flat) or a studio unit for various 
periods. They need to sign a rent lease agreement before they can move in. 
New lease agreements are negotiated at the start of each academic year with a 
minimum rental period of one year, which includes semesters 1, 2 and the summer 
semester. Each individual lease agreement between a student and the 
accommodation office is required to be unique. 
The data stored on each lease includes the duration of the lease (given as 
semesters), student details, place number, room number, address details of the hall 
/ student flat / studio, the date the student wishes to enter the room, and the date 
the student wishes to leave the room (if known), etc. 
6. At the start of each semester, each student is sent an invoice for the ensuing rental 
period. Each invoice has a unique invoice number. 
The data stored on each invoice includes the invoice number, lease number, 
semester, payment due, student’s ID number and full name, place number, room 
number, and the address of the hall or flat. Additional data is also held on the 
payment of the invoice and includes the date the invoice was paid, the method of 
payment (e.g., cheque, cash, etc.), and the date the first or second payment 
reminder is sent (if necessary), etc. 
7. Staff on a regular basis inspect student flats and studio units to ensure that the 
accommodation is well maintained. The information recorded for each inspection 
is the name of the member of staff who carried out the inspection, the date of 
inspection, an indication of whether the property was found to be in a satisfactory 
condition or not, any additional comments, etc. 
 
8. Some information is also held on members of staff of the accommodation office 
and includes the staff number, home address, sex, position (e.g., Hall Manager, 
Administrative Assistant, Cleaner, etc.), location (e.g., accommodation office, 
Hall), etc. 
 158337, S2, 2024 Page 7 of 9 Project Instructions IN 
CONFIDENCE 
9. The accommodation office also stores a limited information on the degree courses 
run by the University such as degree title, degree director name, internal telephone 
number, and room number, department name, etc. Each student is associated with 
a single degree course. 
 
10. Information on a student's next-of-kin is stored which includes the name, 
relationship, contact details, etc. This information disclosure is not mandatory. 
 
 158337, S2, 2024 Page 8 of 9 Project Instructions IN 
CONFIDENCE 
Query Transactions 
 
The following transactions must be supported by your database design. However, you 
are NOT required to implement these transactions (in Part B you will write code so 
there is no need to write the SQL code for these queries). Use the following only to 
validate your database design using a dry run. All the attributes are spelled out in the 
description above but may be part of the transactions listed below. 
a) Create a suburb-wise staff list (based on their residential address). 
b) Create a list of students and staff who have their birthday next month. 
c) Create a list of post-graduate students who live in studio units. 
d) Create a report listing halls of residence Manager’s name and telephone 
number. 
e) Create a report listing students’ names, ID numbers and the details of their rent 
lease agreements. 
f) Display the details of lease agreements that include the Semester 1 and 2. 
g) Display the students who are paying over $1500 monthly for their room in a 
flat. 
h) Create a report on students that have not paid their invoices by a given date. 
i) Display the details of flat and studio inspections where the property was found 
to be in an unsatisfactory condition along with the comments. 
j) Create a report of the names and ID numbers of students with their room 
number and place number in a hall of residence. 
k) Create a report listing the details of all students currently on the waiting list for 
accommodation. 
l) Display the total number of students in each student category. 
m) Create a report listing the names and ID numbers for all students who have not 
supplied details of their next-of-kin. 
n) Display the minimum, maximum, and average monthly rent for rooms in halls 
of residence. 
o) Create a report listing students who have opted for Vegetarian or Vegan as 
their catering option. In the report include their hall of residence as well. 
p) Display the total number of rooms currently occupied and total number of 
places available in each hall of residence. 
q) Display the staff number, name, age, and current location of the staff who 
have been working 5 years or more at the accommodation village. 
r) Create a staff telephone directory in the order of family name. 
s) Create a report listing names and internal telephone numbers of the Advisors 
along-with their student names. 
t) List students who have any accessibility requirements along with the special 
requirement(s). 
u) List details of all the members who are residing with students in their studio 
units. 
v) List the number of flats or studio units that are not rented.158337, S2, 2024 Page 9 of 9 Project Instructions IN 
CONFIDENCE 
Appendix B 
158.337 Project Marking Sheet (Part A – 17.5% course mark) 
 (Attach this to your project report BEFORE you turn it in.) 
(Please make sure you provide all the necessary details) 
Oracle Account: Group___ 
Group Member 1 (Who Submits) - ID number, Name 
Group Member 2 - ID number, Name 
Group Member 3 - ID number, Name 
Group Member 4 - ID number, Name 
 
(Grader’s section, please do not write below this) 
PART A: _____/60 marks 
 
Logical Database Design (Step 1): /30 marks 
 
 
Physical Database Design (Step 2 and 3): /30 marks 
  
Part A: 17.5%, Part B: 17.5% (Total Course Percentage for the project – 35%)