Lab4
pdf
keyboard_arrow_up
School
University of Toronto *
*We aren’t endorsed by this school
Course
350
Subject
Information Systems
Date
Dec 6, 2023
Type
Pages
3
Uploaded by MagistrateWorldChinchilla30
MIE350F Lab #4
SQL and Many-To-One in CMS
Fall 2023
Objectives:
1.
Implement a new CMS feature based on a modifying SQL query
2.
Implement a Many-To-One relation in CMS
Before You Begin:
1.
Open your CMS project in IntelliJ IDEA. Run your project and make sure the log shows
“
APPLICATION IS RUNNING
”
(Instruction on running IntelliJ and CMS can be found in Lab 1)
Important: The database is reset each time you run the application. At any time, you
can stop the application and re-start it to re-initialize the database to its original state.
2.
Run the Insomnia REST client you have downloaded in lab 2.
Recall (lab 3): an example for Many-To-One relation in CMS
In Course.Java you will notice that the variable professor is annotated as:
@ManyToOne
@JoinColumn(name="professorId")
private Professor professor;
This indicates the professor details are not stored directly in the table Courses but there is a
foreign key
pointing to the table Professors in a column named professorID (defined in the
@
JoinColumn
annotation) representing a many-to-one relationship between courses and
professors.
Questions to Answer:
Reminder: if your code is running but is not working as expected – check if an error message
has been printed to the program output in the “Run” terminal window.
1.
We would like to implement a new feature in CMS that increases all the grades in a
course by 5 points using a SQL query.
a.
Which type of SQL query would be the most appropriate for this?
b.
To do so, please add the following function to CourseMarkRepository and
replace
???
with a SQL query that increases all the grades in course identified by
the course code provided as parameter by 5 points.
Note that the decorator
@Modifying
and
@Transactional
are used since
this is an SQL query that modifies the database
c.
Extend CourseMarkController to include a new function that accepts POST
requests with the URL “/marks/increaseFive/{code}” that:
(1) Calls the increaseFive function you just implemented in
CourseMarkRepository.
(2) Returns the list of all marks (similar to retrieveAllMarks) after the update.
d.
Check your code using the Insomnia client and make sure your new function
works.
2.
In a previous lab, you created a new entity called Classroom. We would like to connect
Classrooms to Courses such that each course will be associated with the classroom it
takes place in. To do so:
a.
Extend the entity
Course
to include a field for the classroom.
b.
Make sure you define a many-to-one relation between classrooms and courses.
c.
Edit data.sql such that:
i.
There at least 2 classrooms (you should have done that as part of lab 2)
@Modifying
@Transactional
@Query(value = "
???
", nativeQuery = true)
void increaseFive(@Param("code") String code);
ii.
Each course is now associated with one of the classrooms
d.
Using Insomnia get the new list of courses and make sure the classroom details
(including capacity) are now retrieved for each course.
3.
Homework – further SQL practice (not graded, but important to complete at home as
preparation for project and lab quizzes):
Extend the query in Question (1) such that it never increases a grade beyond 100.
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help