Hello, if you have any need, please feel free to consult us, this is my wechat: wx91due

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:

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:

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 (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:

Section A (SQL Queries) (20 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)

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.

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)

Section B (PL SQL) (26 marks)

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)

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 the MIS 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).

Section C (LINQ Queries) (6 marks)

Section D (NoSQL) (8 marks)

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

There are three parts to this assignment submission:

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

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.

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

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

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.

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.