158.337编程代写、代做Java/Python程序语言

日期：2024-08-12 09:14

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%)