Whenever I want to work with someone brainstorming or co-creating a project I like to encourage them to watch this three part video on the IDEO deep dive.
For now I want to spend some time (up front today) sketching out some plan of attack for at least some of our four squirrel games (descriptions here).
I have set up four posts and folders on our Piazza site. For the game that you picked last time go ahead and reply to that post with some of your thoughts for the next 10-15 minutes. Then find the other people who thought about your game. Then collaborate (using the edit button and maybe a master post) to take the best of both ideas.
Feel free to use the boards for this.
This is an advanced SQL feature so it is different for each different vendor. When we spend some time hacking at the guts of SQLite later in the semester we'll start by making our own functions there.
For now let's fire up our old username2 mysql tables on orioles1. SSH into the right spot and use a command like mysql -h localhost -u andynovo -povonydna andynovo2.
Documentation for when something goes wrong...
OK so the basic syntax is
CREATE FUNCTION funcname(inputvarname type, ...)
RETURNS type DETERMINISTIC
BEGIN
DECLARE tempvariables type;
do stuff here
RETURN something;
END
Since these are multi-line we can change the end-of-line character with DELIMITER // or whichever characters you want. After the END line put in your new delimiter, then probably reset it to ;
A sample:
DELIMITER $$
CREATE FUNCTION numCourses(sID int)
RETURNS int DETERMINISTIC
BEGIN
DECLARE courseCount int;
select count(*) into courseCount from takes where ID=sID;
RETURN courseCount;
END$$
DELIMITER ;
select numCourses(60688);
You might also want to DROP FUNCTION numCourses; when something goes wrong, then you can redefine it.
TASK 1 Create a simple function to multiply two integers.
TASK 2 Create a select statement to find which rooms in a building are unused in a given semester, year, and time_slot.
TASK 3 Turn this into a function.
A trigger is basically an event listener in SQL. It can do something to a table either before or after an insert, update, or delete.
You can use triggers to help maintain the consistency of your database, or to do big-brother stuff. It's up to your imagination really.
These work both in SQLite and MySQL with subtle syntax differences. I prefer to sandbox in SQLite for this class so let's start there. Here is the SQLite documentation on triggers.
create trigger destroyStud
after delete on student
begin
delete from advisor where s_ID=OLD.ID;
delete from takes where ID=OLD.ID;
end;
That trigger will remove a student from both advisor and takes when it is deleted from the student table. (We will make a better way to do this later in this lecture.) Note that OLD and NEW are special tables made for the trigger that will have column values that are being altered.
TASK A create a simple enrollment table and populate with the current number of students.
TASK B create a trigger which decrements the enrollment when a student is deleted.
TASK C create an incrementer for when a student is added.
Test your triggers.
Not a hard concept but when you have a table which references another table you can have columns that will be a key on the other table. For instance all of your relationship tables are made of such keys, one for member A one for member B. This is called a foreign key.
You can declare your foreign keys to SQL as a type of constraint on your database. This will cause your database to reject inserts and updates that would create an invalid reference.
SQLite prefers to have foreign keys added at the table creation.
One choice often made with foreign keys is what you will do when a row which is referenced gets deleted. You can either set references to that row to null, take no action, or cascade the delete to the referencing tables, or prevent the delete.
In general foreign keys are ways of making your database more secure and therefore are additional hoops for you to jump through as the database user/developer/admin. It is a healthy thing to do, but I want my project to be in a somewhat mature state before I add them (personally), because I will cost myself hours keeping tables consistent early on.
TASK create a foreign key in your MySQL database from student to takes that cascades. Then delete a student.
Earlier in the course we introduced natural join and the cartesian product of tables. I wanted to spend a little time making sure you know the various JOIN options and just what they will do. The logically distinct joins are any combination of a join type:
(Inner join, left outer join, right outer join, full outer join)
and a column condition
(Natural ..., ... ON (condition), ... USING (c1, c2, c3, ...))
Altogether that makes 12 joins, and many of them have aliases. For instance inner join and join are the same thing, but left join will be the same as left outer join.
The join types are all about how to handle references that don't exist. If I join table A to table B on a column that they share, then a reference in table A which isn't in table is the primary issue. In an inner join only rows that exist in both tables show up. In an outer join every possible combination (except for null, null) shows up. In a left join the left table has every row and the right side might have null values, and the right join does the opposite.
WARNING, in your WHERE clause you must say column IS NULL or column IS NOT NULL. Don't use = or &lft;> for NULL values.
The other part of a join is the column specification. Natural tries to match on any columns with the same name, USING lets you specify which columns with the same name you want to honor, and ON (my favorite) lets you specify a left column name and a right column name which you want to honor.
TASK J1 do a LEFT OUTER JOIN between two chinook tables using NATURAL, ON, and USING.
TASK J2 count the number of missing rows from the right table.
We can declare a CHECK constraint into our tables at declaration. Check out this link and create an arithmetic contraint in a table. (This works in sqlite not mysql.) Check it out.
When you have two users trying to write the same value to the same place how do you decide who wins? The database will have some locking going on and might deal with it for you, but in your application you might actually want to solve this a little bit yourself. Here are some solutions I have seen working with various pros and cons:
These are helpful things to hear a couple of times in your professional life. So your task is to think about a system that might benefit from one version more than another.