Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021
1. Preliminaries
Under Resources→Lab4 on Piazza, there are some files that are discussed in this document. Two
of the files are lab4_ create.sql script and lab4_data_loading.sql. The lab4_ create.sql script
creates all tables within the schema Lab4. The schema is similar to the one in our create.sql
solution to Lab1 (although it has two NOT NULL constraints on the Battles table, which are
described below). We included all the constraints that were in our Lab2 solution. Lab3’s new
General constraints and revised Referential Integrity constraints are not in this schema, but the
original Referential Integrity constraints are included.
lab4_data_loading.sql loads data into those tables, just as similar files did for previous Lab
Assignments. Alter your search path so that you can work with the tables without qualifying them
with the schema name:
ALTER ROLE SET SEARCH_PATH TO Lab4;
You must log out and log back in for this to take effect. To verify your search path, use:
SHOW SEARCH_PATH;
Note: It is important that you do not change the names of the tables. Otherwise, your application
may not pass our tests, and you will not get any points for this assignment.
Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021
2. Instructions for database access from C
An important file under Resources→Lab4 is runAdventureApplication.c. That file is not compilable
as is. You will have to complete it to make it compilable, including writing three C functions that
are described in Section 4 of this document. You will also have to write a Stored Function that is
used by one of those C functions; that Stored Function is described in Section 5 of this document.
As announced at the beginning of the quarter, we assume that CSE 180 students are familiar with C.
However, you will not have to use a Make file, since runAdventureApplication.c is the only file in
your C program.
Assuming that runAdventureApplication.c is in your current directory, you can compile it with the
following command (where the “>” character represents the Unix prompt):
> gcc -L/usr/include -lpq -o runAdventureApplication runAdventureApplication.c
When you execute runAdventureApplication, its arguments will be your userid and your password
for our PostgreSQL database. So after you have successfully compiled your
runAdventureApplication.c (and separately successfully created your Stored Function in the
database), then you’ll be able to execute your program by saying:
> runAdventureApplication
[Do not put your userid or your password in your program code, and do not include the < and >
symbols, just the userid and password. We will run your program as ourselves, not as you.]
How you develop your program is up to you, but we will run (and grade) your program using these
commands on unix.ucsc.edu. So you must ensure that your program works in that environment.
Don’t try to change your grade by telling us that your program failed in our environment, but
worked in your own environment; if you do, we’ll point you to this Lab4 comment.
Because Lab4 is mainly a database application programming assignment, rather than a C language
assignment, please don’t use a makefile or a header (.h) file in Lab4.
Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021
3. Goal
The fourth lab project puts the database you have created to practical use. You will implement part
of an application front-end to the database. As good programming practice, your functions should
catch erroneous parameters (and impossible situations). We describe erroneous parameters and
required handling below.
4. Description of the three C functions in the runAdventureApplication file that
interact with the database
The runAdventureApplication.c that you’ve been given contains skeletons for three C functions that
interact with the database using libpq.
These three C functions are described below. The first argument for all of these C function is your
connection to the database.
• printNumberOfThingsInRoom: The Things table has an attribute initialRoomID which
identifies the room where that thing is located when Adventure begins. However, a thing
could be owned by a character. If a thing is owned by a character, then the room where
that thing is currently located is the room that the character is in, not the thing’s
initialRoomID.
Besides the database connection, the printNumberOfThingsInRoom function has one
parameter, an integer, theRoomID.
o If there is no room in the Rooms table whose roomID is theRoomID, then
printNumberOfThingsInRoom shouldn’t print anything, and it should return the
value -1.
o If there is a room in Rooms table whose roomID is theRoomID, then
printNumberOfThingsInRoom should print the roomID, the description of that
room, and the number of number of things which are in that room, and it should
return the value 0. The format of the output should be:
Room , , has in it.
(Don’t worry about the exact number of spaces in your output.)
Big Hint: C functions can have more than one SQL statement in them. You’ll want to
use more than one SQL statement in your code for printNumberOfThingsInRoom. And
you’ll want those statement to be in a Serializable transaction.
Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021
• updateWasDefeated: The Lab4 create_lab4.sql file CREATE Battles statement specifies
NOT NULL for the characterBattlePoints nor monsterBattlePoints attributes.
A character lost a battle if their characterBattlePoints in that battle is less than the
monsterBattlePoints of the monster in that battle. And a monster lost a battle if their
monsterBattlePoints in that battle is less than the characterBattlePoints of the character in
that battle. If the character and monster in a battle had the same number of battlePoints in
that battle, then neither of them lost.
wasDefeated is an attribute in both the Characters and the Monsters table (which could be
NULL). If a character (or monster) lost a battle, then its wasDefeated attribute value
should be TRUE. And if a character (or monster) didn’t lose a battle, then its wasDefeated
attribute value should be FALSE. Some values of wasDefeated may be correct, but some
values of wasDefeated may be incorrect; the updateWasDefeated function will fix the
wasDefeated values (for either characters or monsters) that are incorrect in a Serializable
transaction.
Besides the database connection, updateWasDefeated has another parameter
doCharactersOrMonsters, which should have the value ‘C’ or ‘M’. updateWasDefeated
should fix incorrect values of wasDefeated for either characters or monster, depending on
the value of doCharactersOrMonsters). In either case, updateWasDefeated returns the
number of incorrect wasDefeated values that had to be fixed because they were incorrect.
o If doCharactersOrMonsters has the value ‘C’, updateWasDefeated should fix the
incorrect wasDefeated values for characters, and return the number of incorrect
wasDefeated values in Characters that had to be fixed.
o If doCharactersOrMonsters has the value ‘M’, updateWasDefeated should fix the
incorrect wasDefeated values for monsters, and return the number of incorrect
wasDefeated values in Monsters that had to be fixed.
If doCharactersOrMonsters has any value other than ‘C’ or ‘M’, that’s an error, and
updateWasDefeated should return the value -1. But if there are no characters (or monsters)
whose wasDefeated is incorrect, that’s not an error; in that case, updateWasDefeated
should return 0, since no wasDefeated values were updated.
Note that there are two reasons that the value of wasDefeated could be incorrect for a
character (or monster):
a) if wasDefeated is FALSE, but the character (or monster) lost at least one battle, or
b) if wasDefeated is TRUE, but the character (or monster) didn’t lose at least one
battle.
updateWasDefeated needs to fix both of these incorrect values for characters (or monsters).
• increaseSomeThingCosts: Besides the database connection, this function has one integer
parameter, maxTotalIncrease. increaseSomeThingCosts invokes a Stored Function,
increaseSomeThingCostsFunction, that you will need to implement and store in the
database according to the description in Section 5. The Stored Function
increaseSomeThingCostsFunction should have the same maxTotalIncrease parameter that
was supplied to increaseSomeThingCosts (but the Stored Function does not have the
Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021
database connection as a parameter).
The Things table has an attribute cost, indicating the cost of a thing.
increaseSomeThingCostsFunction will change the cost for some (but not necessarily all)
things in the Things table. Section 5 explains which things should have their cost values
changed, and also tells you how much you should increase these costs. The
increaseSomeThingCosts function should return the same integer result that the
increaseSomeThingCostsFunction Stored Function returns.
increaseSomeThingCostsFunction may return a negative value, signifying an erroneous
argument. increaseSomeThingCosts should just return that value to its invoker, which will
deal with that value as described in the Testing section below in Section 6.
The increaseSomeThingCosts function must only invoke the Stored Function
increaseSomeThingCostsFunction, which does all of the work for this part of the
assignment; increaseSomeThingCosts must not do the work itself.
Each of these three functions is annotated in the runAdventureApplication.c file we’ve given you,
with comments providing a description of what it is supposed to do (repeating just part of the above
descriptions). Your task is to implement functions that match those descriptions.
Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021
The following helpful libpq-related links appear in Lecture 11.
• Chapter 33. libpq - C Library in PostgreSQL docs, particularly:
• 31.1. Database Connection Control Functions
• 31.3. Command Execution Functions
• 33.21. libpq Example Programs
• PostgreSQL C tutorial from Zetcode
5. Stored Function
As Section 4 mentioned, you should write a Stored Function (not a Stored Procedure) called
increaseSomeThingCostsFunction which has one integer parameter, maxTotalIncrease. If
maxTotalIncrease is not positive, then increaseSomeThingCostsFunction should return -1,
signifying an error.
The attribute thingKind in the Things table distinguishes among different kinds of things, such as
scrolls (‘sc’), maps (‘ma’), statues (‘st’), swords (‘sw’) and shields (‘sh’). [Don’t worry about the
full words for these things; our Adventure database instances store two symbol abbreviations, but
it’s convenient to talk about scrolls, rather than things whose thingKind is ‘sc’.]
If a thing in Things has ownerMemberID and ownerRole that are NULL, then that thing is not
owned; if ownerMemberID and ownerRole for a thing aren’t NULL, then that thing is owned. (It’s
not possible for one of those attributes to be NULL unless the other attribute is also NULL.) There
might be 10 owned things that are scrolls, 3 owned things that are swords, and 1 owned thing that’s
a map.
[Note that there is no separate table of thingKind values. You determine which thingKind values
there are and how many things have each thingKind by looking at the Things table.]
We decide to increase the cost of popular things so that our Adventure game makes more money.
(Paying for things is not part of this assignment.) The cost increase will be the same for all things
which have the same thingKind (e.g., scrolls), even though different scrolls may have different
costs.
• If a thingKind is owned 5 or more times, then its cost increase should be 5.
• If a thingKind is owned 4 times, then its cost increase should be 4.
• If a thingKind is owned 3 times, then its cost increase should be 2.
• Otherwise, no cost increase.
But increaseSomeThingCostsFunction shouldn’t apply these cost increases. The total of the cost
increases must less than or equal to the value of the parameter maxTotalIncrease.
increaseSomeThingCostsFunction will return the total of the cost increases, which might equal
maxTotalIncrease, but also might be less than maxTotalIncrease.
Here’s how to determine which things should have their cost values increased:
Iterate through thingKind values based on the number of owners of that thingKind, in decreasing
order, so that the most popular thingKind values are considered first. For each thingKind you
consider, increase the cost of that thingKind by the cost increase describe above. However, the
total of your cost increases must not be more than maxTotalIncrease.
Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021
Note: You can declare variables as NUMERIC (e.g., NUMERIC(5,2)) in PL/pgSQL functions.
And it’s okay to add an INTEGER to a value that’s declared as NUMERIC.
Here are some examples of how this works. Assume that scrolls are owned by 20 characters
(cost increase is 5), swords are owned by 4 characters (cost increase is 4), maps are owned by 3
characters (cost increase is 2) and shields are owned by 3 characters (cost increase is also 2).
• What happens if maxTotalIncrease is 62? Then the costs of 12 scrolls are increased by 5,
the costs of the swords stay the same, and the cost of 1 of the maps (or shields) is increased
by 2. The value that is returned is 62 (which is 12*5 + 0*4 + 1*2).
[Any 12 of the scrolls, and any 1 of the maps (or shields) could have their costs increased,
doesn’t matter which. thingKind values which have the same popularity level are treated
the same way.]
• What happens if the maxTotalIncrease is 63? Then the costs of 12 scrolls are increased by
5, the costs of the swords stay the same, and the cost of 1 of the maps (or shields) is
increased by 2. The value that is returned is once again 62 (which is 12*5 + 0*4 + 1*2),
not 63.
• What happens if the maxTotalIncrease is 111? Then the costs of the 20 scrolls are
increased by 5, the costs of 2 swords are increased by 4, and the cost of 1 of the maps (or
shields) is increased by 2. The value that is returned is 110 (which is 20*5 + 2*4 + 1*2).
• What happens if maxTotalIncrease is 200? Then the costs of the 20 scrolls are increased
by 5, the costs of the 4 swords are increased by 4, and the cost of the 3 maps is increased by
2 and the costs of the 3 shields is increased by 2. The value that is returned is 128 (which
is 20*5 + 4*4 + 6*2).
• What happens if the maxTotalIncrease is 3? Then the costs of the scrolls and swords
remain the same, and the cost of 1 of the maps in increased by 2. The value that is returned
is 2 (which is 0*5 + 0*4 + 1*2).
• Finally, what happens if the maxTotalIncrease is 1? All the costs remain the same. The
value that is returned is 0.
Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021
Write the code to create the Stored Function, and save it to a text file named
increaseSomeThingCostsFunction.pgsql. After you’ve saved that file, you can create the Stored
Function increaseSomeThingCostsFunction by issuing the psql command:
\i increaseSomeThingCostsFunction.pgsql
at the server prompt. If the creation goes through successfully, then the server should respond with
the message “CREATE FUNCTION”. You will need to call the Stored Function from the
increaseSomeThingCosts function in your C program, as described in the previous section, so
you’ll need to create the Stored Function before you run your program. You should include the
increaseSomeThingCostsFunction.pgsql source file in the zip file of your submission, together with
your versions of the C source files runAdventureApplication.c that was described in Section 4; see
Section 7 for detailed instructions.
A guide for defining Stored Functions for PostgreSQL can be found here on the PostgreSQL site.
PostgreSQL Stored Functions have some syntactic differences from the PSM stored
procedures/functions that were described in Lecture. For Lab4, you should write a Stored Function
that has only IN parameters; that’s legal in both PSM and PostgreSQL.
We’ve given you some more hints on Piazza about writing PostgreSQL Stored Functions,
including:
• StoredFunction_pgsql_Info.pdf, which describes some differences between PSM and
PL/pgSQL,
• fireSomePlayersFunction.pgsql, an example of a PostgreSQL Stored Function, and
• What_Does_fireSomePlayersFunction_Do.pdf, an explanation of what that Stored Function
does. But we won’t provide the tables and load data for running that Stored Function.
6. Testing
Within main for runAdventureApplication.c, you should write several tests of the C functions
described in Section 4. You might also want to write your own tests, but the following tests must
be included in the runAdventureApplication.c file that you submit in your Lab4 solution.
• Write four tests of the printNumberOfThingsInRoom function.
o The first test should be for the room whose roomID is 1.
o The second test should be for the room whose roomID is 2.
o The third test should be for the room whose roomID is 3.
o The fourth test should be for the room whose roomID is 7.
Printing occurs within printNumberOfThingsInRoom, which should return the value 0 or
the value -1. If it returns 0, no additional action is required. But if it returns -1, then print
out:
No room exists whose id is
where roomID is the roomID that you supplied. Continue executing additional tests if this
occurs.
However, if printNumberOfThingsInRoom returns any other value besides 0 or -1, print out
an error message describing the bad value returned (format up to you, as long as it conveys
full information about the error) and exit using bad_exit.
Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021
• Write three tests for the updateWasDefeated function.
o The first test should be for doCharactersOrMonsters ‘C’.
o The second test should be for should be for doCharactersOrMonsters ‘M’.
o The third test should also be for doCharactersOrMonsters ‘C’. (What should the
value returned by that test always be?)
If updateWasDefeated returns a non-negative value, then print out:
wasDefeated values were fixed for
where is the value returned and is the
parameter that was sent to the function.
If updateWasDefeated returns -1 then print out:
Illegal value for doCharactersOrMonsters
If updateWasDefeated returns any other value, then print out an error message describing
the bad value returned (format up to you, as long as it conveys full information about the
error) and exit using bad_exit.
• Also write four tests for the increaseSomeThingCosts function.
o The first test should have maxTotalIncrease value 12.
o The second test should have maxTotalIncrease value 500.
o The third test should have maxTotalIncrease value 39.
o The fourth test should have maxTotalIncrease value 1.
Run these tests and print their results from main in runAdventureApplication. If
increaseSomeThingCosts returns a non-negative value, print out that result in the following
format:
Total increase for maxTotalIncrease is
But increaseSomeThingCosts can return a negative value, signifying an error. For these
negative values, print an error message (format of error message is up to you) that
describes the error and the erroneous parameter that resulted in the error and exit (using
bad_exit).
You must run all of these tests for all three of these functions in the specified order, starting with
the database provided by our create and load scripts. Some of these functions modify the database,
so be sure to use the load data that we’ve provided, executing the functions in the specified order.
Reload the original load data before you start, but you do not have to reload the data multiple times
in Lab4.
Hmmm, do these tests affect each other? What do you think?
Lab Assignment 4 CSE 180 - Fall 2022 Due: 11:59pm Tuesday, November 29, 2021
7. Submitting
1. Remember to add comments to your C code so that the intent is clear.
2. Place the C program runAdventureApplication.c and the stored procedure declaration code
increaseSomeThingCostsFunction.pgsql in your working directory at unix.ucsc.edu.
3. Zip the files to a single file with name Lab4_XXXXXXX.zip where XXXXXXX is your 7-
digit student ID, for example, if a student's ID is 1234567, then the file that this student
submits for Lab4 should be named Lab4_1234567.zip. To create the zip file, you can use the
Unix command:
zip Lab4_1234567 runAdventureApplication.c increaseSomeThingCostsFunction.pgsql
Please do not include any other files in your zip file, except perhaps for a view creation file
(described below) and an optional README file, if you want to include additional
information about your Lab4 submission.
4. Some students might want to use views to do Lab4. That’s not required. But if you do use
views, you must put the statements creating those views in a file called
createAdventureViews.sql which you include in your Lab4 zip file.
5. Lab4 is due on Canvas by 11:59pm on Tuesday, November 29, 2022. Late submissions will
not be accepted, and there will be no make-up Lab assignments.