158.337 Queries (SQL/LINQ), Triggers

158.337 Group Project Instructions: Part B

(Course mark - 17.5%)

You will continue to work in groups* for this assignment. You do not need to register again but in case you change your group membership please let us know via emailing Indu ([email protected]).  Make   sure  you  formally  inform. your  Assignment  Part  A partner(s) as well. You will lose significant marks if you do not work in a group without a very strong reason and permission. You can use the group forum for this should you need to but do this early on; in fact, sort this first. Any other submission irregularities will also have a penalty.

Use the following instructions to guide you in the Four Sections (Section A – Section D) of the project. You should turn the work in for grading by the due date (Tuesday, 15th Oct 4.00 pm).

Notes:

•    *Working in a group does not mean you completely split the work or probably work only on one section. You should all work on each section and then compare your work i.e. have discussions around any differences (e.g., different results, style. of coding, getting stuck, etc.). Subsequently you collate put your best work in the report. We suggest you start working on assignment in parallel (this will also prepare you for the coding test which is required to be done individually).

•   Make sure you have altered the password of your (Group) Oracle a/c and keep it safe within your group. You do not want other people accessing your account and code. We have seen this in past that occasionally students have been able to access other group accounts,  if  the  default  passwords  have  not  been   changed.  Remember  it  is  your responsibility to protect your work.

•   Do not post any  assignment queries on the  Stream’s  discussion  forum. Everyone is expected to work within their group and such posts either give away the answer or may pose some confusion to other groups who may be writing scripts differently.

PART B

Queries (SQL/LINQ), Triggers, Procedures, Function, Cursor (PL SQL) & MongoDB (NoSQL)

Download  following  two  files  provided  in  a  compressed  folder  (PartB.zip)  under  the Assignment heading from the Stream site:

University database file (UniversityDatabase.sql) - for Sections A, B and C.

The file provides code to create the required database in your account. Seven tables created are – LOCATION, FACULTY, STUDENT, TERM, COURSE, COURSE_SECTION, and ENROLLMENT.

Collection file (NoSQL.txt) - for Section D.

Use script. files (use some text editor e.g., Notepad++) for writing your solution code. Name your files appropriately (e.g., SectionA.sql). Include query number (e.g., a) along with the given query question (e.g., Write a query that will list...) as part of the comment before writing the solution code (SQL / PL/SQL / LINQ) in these files. Annotate your code with suitable comments wherever necessary.

Use a Word file for your project report (this can be later converted into a pdf should you prefer to submit a pdf report). As mainly the report will be used for grading so you will need to transfer your final working code from the script. files to this report as well. Here you also need to include the outputs of your code (e.g., query results, etc.). The outputs presented in the report should be well formatted (properly aligned, appropriate column names, etc.) for readability purposes. The script. files will be used wherever required (i.e., we may not the run code for every question but may for the some to confirm that the code works correctly in differently simulated situations).

For PL/SQL (triggers, procedures, etc.) besides the created code, the output produced from running the code 代 写158.337 Queries (SQL/LINQ), Triggers(e.g., procedure successfully created, etc.) you also need to provide the testing examples using some quality test data to prove that procedure/trigger works the way it is intended to work. Once again make it easy for grading by being easy to read but also make it easy should the grader choose to run the code.

All (SQL/LINQ) queries must only be based on the information provided in the assignment question itself (e.g., Write a query that will list...). Do not use a different criterion to arrive at the expected equivalent query result set. Also, do not manipulate the query results by using ROWID, ROWNUM, With Ties, etc. to sort and get the first ranked /last record, etc. Some SQL may require use of subqueries, etc.

Ensure that the query results display the information that is asked including the columns that are necessary to easily evaluate your query results. That does not mean use “*” to display everything – use your judgement and fulfil the query requirements. Your code should work correctly even when the database is modified (e.g., added more records, deleted  some, modified  some values,  etc.). At times, you may  consider making  some  changes to the database itself to see your code works fine for the changed database (works as expected).

Notes:

•   For all exercises asking you to display names, list first name and last name as a single column instead of separate columns.

•   Remember to include all three - the asked question (as comment), the code (SQL /PL SQL /LINQ/ MapReduce) and the output (including any test cases, etc.) in your report. Some questions may have other additional requirements, so meet those as well.

Section A (SQL Queries)            (20 marks)

Note: For each of the questions (a)-(h) in  Section A, write code using a  SINGLE statement ONLY (i.e., you cannot write two separate select statements to arrive at an answer; however, select clause can be used more than once in a statement. There should only be one semi-colon in your code). Use the given criteria only. We will either give full or zero mark for most of the questions in this section so make sure your answer is fully correct.

a.   Write a query that will list the faculty members (along with the building code and room number) who are (located) in the (BUS)iness building.            (1.5 marks)

b.   Write a query that will list students who are enrolled in the courses offered in the Fall term of 2024 or 2025 or Spring term of 2025. Do not display the duplicate student names in the output.          (1.5 marks)

c.   Write a query that will list the total building capacity of various buildings. The rooms with a capacity of less than five must be excluded when generating building’s total capacity. The final query result should list the buildings with a total building capacity of 150 or over. Present this in the increasing order of the total capacity.       (2 mark)

d.   Write a query that will list faculty supervisors and their respective students. Each supervisor’s students should appear in a single row (i.e., list of values instead of separate rows) i.e., if a faculty supervisor supervises more than one student then all the supervised students’ names should appear in a single row (next to each other separated by a delimiter). Present your final output in the order of faculty supervisor's id and include only those results where the number of students against a supervisor is more than 1.

Hint: May have to google for Oracle’s special aggregate function.               (3 mark)

e.   Write a query that will list students enrolled with a total of 12 or more course credit points. List your results in decreasing order of total credit points. Do not assume or hard code the value of the course credits (e.g., 3, 6, etc.).                              (3 mark)

f.   Write a query that lists the courses (with their course names) and the course sections that are offered either on all the three days (M)onday, (W)ednesday and (F)riday or at least four times a week. Also, display the course section days (e.g. MWF), the course section time (e.g. 2 pm), number of the days that the courses are offered (e.g., 5 days). List your results in the increasing order of number of days.

Hint:  The  attribute  c_sec_day  lists  weekdays, where the  first  letter represents  a weekday (e.g. M-Monday,…...,F-Friday; for Thursday R is used). The course section weekdays are listed in the order M(onday) to (F)riday i.e., Monday being the first. (3 marks)

g.   Write a query listing the details of the faculty member(s) who supervise(s) the highest number of students. The result should also display the number of students. The query should also work in situations when more than one supervisor has highest students (e.g., 2 supervisors each having 5 students and 5 being highest number).     (3 marks)

h.   Write a query that will list student(s) enrolled with the highest total course credit points. The result should also display the number of courses that a student is enrolled for, the average credit points, the highest credit points along with the total credit points. (3 would be highest if all enrolled courses are worth 3 otherwise 6 if enrolled with a course worth 6).                                                                                    (3 marks)

Section B (PL SQL)                     (26 marks)

Comprehensive testing examples needed for all PL/SQL (triggers, procedures, cursor, and function). You need to capture and show that you tested your code using good test cases. Include some exception handling as deemed necessary.

a.   Think of a useful business rule or situation (based on the provided database) where it would be appropriate for a trigger to fire. However, do not write trigger to do something that  could  be  done  using  some  database  design  constraints  (e.g.,  simple  referential integrity checking, assign default values to attributes, or simply saying record is being inserted, or an attribute has a null value, etc.). Provide a sensible and useful trigger and do not use the already provided or similar triggers (including tutorial triggers) for this question. Failing to meet these will result in a zero mark. There is also a discretionary mark for the degree of difficulty/relevance, etc. (1 out of the total 7). But do not confirm the trigger quality with staff before submission.

Start first by clearly explaining the context and purpose (what it will do using business language)  of  your  trigger.  Then  provide  the  PL/SQL  code  and  the  results  (using appropriate testing scenarios).

The trigger should be based on the tables already provided. Do not unnecessarily create too many and/or similar tables. Adding one or two tables may be fine – but justification is needed. Altering a table (adding a field) is fine.

Write one trigger (tell the trigger type). Display the successful creation and the results of running the trigger. Ensure that you also display the relevant tables before and after (results of the trigger) the trigger is fired. Remember to provide the purpose of your trigger (as stated in question b below), as this helps us to evaluate your work against the stated trigger requirements.                         (7 marks)

b.   Write a trigger that does not allow more than two 'Full' ranked professors as part of the faculty (For example, trigger should fire if a new (third) Full professor is added or the rank  of  one  of  the   existing  Associate  professors  is  promoted  to  Full).  Provide comprehensive test data and test results to confirm that the trigger works.         (4 marks)

c.   Write a trigger to check that when salary is updated for an existing faculty the raise is not over 5.5%.              (4 marks)

d.   Write a procedure to insert a new faculty record. The procedure should also automatically calculate the faculty salary value. This calculated salary should be  10% less than the average salary of the existing faculty members. Use the rest of the attribute values as input parameters. Execute your procedure to insert at least one faculty record.            (4 marks)

e.   Use a cursor to list course sections for all theMIS courses (along with their courses names and credits). Present the report in the order of course section id and course number. In the end, also display some meaningful summary statistics as part of the outcome.  (4 marks)

f.   Write a function, which can be used to format faculty member’s salary using appropriate format (e.g., $80,000.00). Do not hard code the exact salary datatype (i.e., your function should work even for some minor changes that may be made to the salary data size).

Call this function using a SQL statement for displaying a faculty member’s salary.  (3 marks)

Section C (LINQ Queries)                            (6 marks)

For (a) – (e) below, write queries using LINQ.

a.   List faculty members who earn 80,000 or over.                                        (1 mark)

b.   List courses that have MIS in their course number.                                  (1 mark)

c.   List faculty members and their location details.                                        (1 mark)

d.   Display the total number of rooms in each building.                                (1.5 marks)

e.   Display total number of students supervised by each faculty in the order of faculty last name.                        (1.5 marks)

Section D (NoSQL)                             (8 marks)

Use the code provided in NoSQL.txt to create a collection of 15 rows called dragons.

Now write MapReduce code to generate a report based on:

•   the gender-wise average weight.

Rewrite the above MapReduce code using Aggregation pipeline operators.

Include both the code and the report generated in your assignment report. Place the code in a script. file as well.

Assignment Submission

Organise your final report (print friendly – but soft copy) to include all assignment sections’ work. Make sure the report contents are also in the order of the laid assignment requirements (e.g., section-wise work).

There are three parts to this assignment submission:

•   a report (word/pdf)

•   scripts (code files – one for each section, clearly labelled) and

•   database objects (in your Group’s Oracle account)

You must put your final report and code file(s) into a “single” compressed folder and submit via the Stream Assignment link.

Make sure there are not too many separate/unnecessary duplicated code files (e.g., all section A queries in a single file and not separate files). No duplicate group submissions (ONLY one per group).

Checklist for final report:

Readable format of SQL, PL/SQL, LINQ commands, and Map Reduce code. Remember to include the related outputs along with the code (not separately in an appendix). For some questions, there are additional specific requirements (e.g., purpose of trigger, etc.).

Checklist for submission under your Group Oracle a/c: All objects - database tables, triggers, procedures, etc.

Checklist for Stream submission:

Check your project work and make sure that all the scripts run without any errors. Also, check that appropriate names have been given to all file(s). Upload your report and code on Stream as a single compressed file on the Assignment link.

Notes:

Enter your names, student ids on the project marking sheet (Appendix B). The project report should include this marking sheet (at the front i.e., 1st  page). Make sure to provide the correct Oracle username in your report. Remember to include the script file(s). Make sure your work is well presented (i.e., easy to grade).

Plagiarism and other project guidelines:

Use of any AI tools is NOT Permitted for the course assessments. You can find more information about the Student Academic IntegrityPolicy 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 component(s).

Not adhering to any of the assignment requirements, may also be given a straight ZERO.

We expect each group member to genuinely and independently work on all the sections. Any reported non-contributors within  a group may be given a ZERO.

Note: Turnaround time for assignment may be slightly over the usual three weeks.