Database Systems COMP3311 26T1

Prac 04, 05 Videos Out

Posted by Jerry He Monday 16 March 2026, 10:56:50 AM.

Hi all,

Prac 04 and Prac 05 videos are out:

https://www.youtube.com/watch?v=8F-gkSwiYbE&list=PLRalM05yyRqUQu81nFdV2JY4sfqa-gkfm&index=2

https://www.youtube.com/watch?v=VDUheECb_xU&list=PLRalM05yyRqUQu81nFdV2JY4sfqa-gkfm&index=1

Hope these help you.

3311 Team

Some Clarifications from Week 04 Lectures

Posted by Jerry He Friday 13 March 2026, 11:16:41 PM.

Hi everyone,

Please note that Week 4 Monday and Thursday demonstration scripts are located in:

/import/adams/A/cs3311/web/26T1/lectures/week04-monday/sql/

/import/adams/A/cs3311/web/26T1/lectures/week04-thursday/sql/

We got some interesting questions from lectures this week:

1. Why RETURN NEW in an AFTER trigger?

Even though an AFTER INSERT trigger cannot modify the row being inserted (because the operation is already complete), PostgreSQL still requires a RETURN statement.

• In PL/pgSQL, the trigger function must return a record to satisfy the system's requirements.
• Returning NEW is essentially a standard "handshake" to the database to indicate that the trigger has executed successfully without blocking the operation. It is syntax plumbing , not an indication that you are modifying the data being inserted.

2. Dropping overloaded functions

If you have multiple functions named seq() with different signatures (parameters), PostgreSQL treats each as a unique entity.

• Will it drop all of them? No.
• How to drop a specific one: You must specify the signature. If you only type DROP FUNCTION seq();, the database will error because it doesn't know which version you mean.
• To delete a specific version, include the argument types, for example:
  • DROP FUNCTION seq(int);
  • DROP FUNCTION seq(int, int);

3. Why our user defined mycount(*) fail.

Consider the following user defined aggregate:

create or replace function mycount_trans(state integer, val anyelement)

returns integer

as $$

begin

return state + 1;

end;

$$ language plpgsql;

create aggregate mycount(anyelement) (

sfunc = mycount_trans,

stype = integer,

initcond = '0'

);

You will notice that the following code works:

SELECT bar, mycount(bar) FROM sells GROUP BY bar;

However, the following code does not work.

SELECT bar, mycount(*) FROM sells GROUP BY bar;

This is because, the * symbol is a special token that represents "all columns of the table. The built-in count(*) is a hardcoded exception in the database engine that knows how to count rows without needing a specific column. Our user defined aggregate my count did not implement this exception.

Have a good weekend! Let's have more discussions like this in class.

COMP3311 Team

Assignments 1 autotest release

Posted by Wenqian Zhang Friday 13 March 2026, 05:01:34 PM.

The autotest for Assignment 1 has been released. You can use the following command to test your work: 3311 autotest ass1