代码代写 158.337 Group Project Instructions: Part A

🍐 我们总结了代码代写中，新西兰CS代写的经典案例，如果你有任何作业代写的需求，可以随时联系我们，CoursePear™ From @2009。

158

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 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, 29th Aug 4.00 pm). Lateness:

There is a 10% penalty / per day and no submission allowed 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. Reminder 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 NZ Cover Staff Agency (NZCSA) Case Study given in the Appendix A. The case study is about an agency hiring temporary employees for company placements mainly during the peak seasons when the companies encounter employee shortfall.

For Part A, complete the three steps (Step 1 – Step 3).
PartA:Step1 (30 marks)

Create a logical (relational) data model (e.g., Database or ER diagram). Use J Developer or Visio* to create this diagram.

Note*: Use one of these two tools only. Do not extract 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 grade fairly). Use of other tools, extractions from tools such as SQL Developer will 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 the diagram (For example, if there are 10 relationships then there should be 10 pairs of these 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 diagram.

Examine each table’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 highest possible NF. State the normal form for each of the tables at every step in the process.

158337, S2, 2023 Page 1 of 8 Project Instructions

PartA:Step2 (20 marks)

Generate physical model based on the logical model created in the Step 1. To create a 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 the code 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) – (r) 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.

PartA:Step3 (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. However, no table should contain less than five rows. Make sure your test data appropriately and sufficiently reflects your design (this would usually imply 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 risk losing 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.

This assignment is also testing 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

158337, S2, 2023 Page 2 of 8 Project Instructions

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 order of the laid requirements.

There are three parts to this assignment submission:

• a project report (word/pdf – soft copy),
• scripts (2 code files – 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.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-non-identifying), participation (mandatory or optional)), etc. Make sure you have applied and shown 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. place all the constraints next to the attributes where possible. Basically, use only one single statement to create a table.

158337, S2, 2023 Page 3 of 8 Project Instructions

5. Test Data (well formatted, readable dump of all the contents of the tables) that can be refereed to with ease.

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 criss- crosses, 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 ensure that the ERD is presentable and readable or risk losing marks.

**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. A ZERO will also be awarded if any of the AI tools are used to do any part of the assignment.

Make sure all your work is complete before you submit. Graders will not be searching/chasing you for any work that is 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 assignments could be up to slightly over usual three weeks.

158337, S2, 2023 Page 4 of 8 Project Instructions

Appendix A

NZ Cover Staff Agency (NZCSA) Case Study: Agency Hiring Temporary Employees for Company Placements

Auckland Cover Staff Agency called ACSA is Auckland based hiring agency that places temporary workers in companies that are in and around Auckland (e.g., Auckland CBD, Silverdale, Orewa, etc.) that have a shortfall of employees (especially during their peak times). In the recent years, the agency has had great success and would now like to expand their business to other NZ regions. They would like to open four more offices in the other main NZ cities (Tauranga, Wellington, Christchurch, and Dunedin). To reflect this, they have recently applied to New Zealand Companies Office for change of agency name and would like to be called NZ Cover Staff Agency (NZCSA).

The Director of the new company NZCSA requires you to design a database to assist with the administration of their hiring and placement services. The requirements collection and analysis phase of the database design process has provided the following requirements for the database followed by examples of the query transactions that must all be supported by the database. Following is the description of how their main business operates:

•   NZCSA maintains a list of applicants who are willing to work on temporary basis to fill in temporary (temp) positions offered by companies to cover their employee shortfall especially during their peak business times. If an applicant has worked before with the agency, that applicant has that job history. Each time the applicant works, one additional job history record is created. Obviously if the applicant has never worked with the agency, no job history exists.
•   Agency requires to maintain applicant’s qualification information (for example description, minimum duration, date of accomplishing the qualification, etc.). Each applicant can have several qualifications. For example, an applicant may have done both BInfSc and Oracle Database Certification.
•   To generate additional income and to provide better service to both applicants and companies, NZCSA also offers courses to help applicants improve their qualifications. Every course develops one specific qualification; however, obviously NZCSA does not offer a course for every qualification (e.g., courses for the qualification BInfSc are not offered as this qualification is typically gained from a recognised university). Some qualifications have multiple courses that contribute towards that qualification. Courses that cover basic topics do not require any prerequisite qualifications. However, courses that cover advanced topics may need specific qualification(s) as prerequisites. A course can have several prerequisites and a qualification can be a prerequisite for more than one course.
•   Courses offered by the agency are taught during the scheduled sessions. A training session is the presentation of a single course. Over time, NZCSA offers many training sessions for each course; however, training sessions for the new courses may not be scheduled immediately. To attend a training

158337, S2, 2023 Page 5 of 8 Project Instructions

session, applicants need to enroll for all the course sessions and pay the required fee. Enrolment fee needs to be paid at least a week before the first training session start date. A training session can accommodate several applicants but for quality (e.g., trainer: applicant ratio, etc.), the session sizes are restricted, and the size can vary depending on the course requirements (e.g., practical courses may take up to 10 applicants and theoretical courses can take up to 20 applicants). A new training session initially will not have any registrations.

•   NZCSA obviously maintains a list of companies that request temporary employees. The company’s location will determine the agency’s branch (e.g., Wellington branch is better suited for Palmerston North companies) that will be responsible for helping them with potential openings and relevant requirements. Each time a company requests a temporary employee, NZCSA makes an entry in the openings file. NZCSA uses special codes (such as DBA- Oracle; SYS-1) to describe an applicant’s qualifications (Database Administrator, Oracle; Systems Analyst, Level 1) for an opening. This file holds all the necessary information about the sought opening such as company name, essential qualifications, a starting date, an expected ending date, hourly pay rate, etc. In addition, it can be assumed that each opening requires only one particular/main qualification. Many applicants can fill an opening, and an applicant can fill many openings.
•   When an applicant’s qualification matches, the job is assigned, and an entry is made in the placement record file. That file maintains information about the opening and the applicant and information such as total number of hours placed is logged. In addition, the applicant’s job history is updated (i.e., an entry is made in the applicant’s job history).

158337, S2, 2023 Page 6 of 8 Project Instructions

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 no need to write the SQL code for these queries). Use the following only to validate your database design using a dry run.

1. a)  Present a report listing employees (name, date of joining, contact details, etc.) that work as Branch Managers for NZCSA.
2. b)  Present a branch wise (Wellington, Auckland, etc.) list of all the companies registered with NZCSA.
3. c)  Present a report listing the applicants for the openings in the last three months (e.g., Apr 2023 to Jun 2023).
4. d)  Display all the courses offered by NZCSA along with their prerequisites (if any) for a specific qualification.
5. e)  Display all applicants aged over 40 years enrolled in a course’s training sessions.
6. f)  Display job history of all applicants with placement date between 1 May 2023 and 15 May 2023.
7. g)  Display all the qualifications that are not taught by any of the NZCSA courses.
8. h)  Display the details of all the applicants who have not yet paid a session’s enrolment fee for a course.
9. i)  Display all the applicants that have worked for over 240 hours in a particular opening placement.
10. j)  List all the applicants who acquired a particular qualification 20 years or earlier.
11. k)  Display all the courses for which the total session fee is more than the average.
12. l)  List all branch openings during a particular quarter for a particular qualification (Jan 2022 – Mar 2022 openings in Auckland area that required Oracle Database Certification as a qualification).
13. m)  List all course session enrolments starting from a particular date for a particular applicant.
14. n)  List all the qualifications that were attained by the applicants in the past five years (e.g., 2018-2022).
15. o)  Present a report listing NZCSA employees (name, date of joining, contact details, etc.) that are managed (by a specific Branch Manager).
16. p)  List all employees who have their birthday in the month of August.
17. q)  List all companies that fall under the same regional branch.
18. r)  Present a report of containing branch-wise employee salary total.

CoursePear™是一家服务全球留学生的专业代写。
—-我们专注提供高质靠谱的美国、加拿大、英国、澳洲、新西兰代写服务。
—-我们专注提供Essay、统计、金融、CS、经济、数学等覆盖100+专业的作业代写服务。

CoursePear™提供各类学术服务，Essay代写，Assignment代写，Exam / Quiz助攻，Dissertation / Thesis代写，Problem Set代做等。