submit cmps180-ark.w02 proj6 files...
Reminder: please do not submit any files containing large data sets or query results. All results should be truncated to a few lines. Large submissions will be rejected, either automatically or by a (not too happy) grader.
Programming work must be submitted electronically by midnight at the end of the day that it is due. Programming work submitted after the deadline but before the "late deadline" -- midnight 48 hours after the deadline -- will be accepted but penalized 50%, except for the first two late project parts handed in late and the first two homework assignments handed in late. No programming work will be accepted after the late deadline.
Please be sure to follow the explicitly specified file naming scheme and additional submission instructions below.
Triggers are discussed in the document Functions and Triggers and in the document Triggers. See also CREATE TRIGGER For more information on creating trigger functions see CREATE FUNCTION and PL/pgSQL - SQL Procedural Language. View definitions and constraint declarations largely follow the SQL2 standard although there are some restrictions; see the document Views and Constraints for more details.
In order to use plpgsql for your trigger functions, prior to starting psql use the command:
createlang -U postgres -L /usr/lib/pgsql plpgsql dbname
(a) Create two useful views on top of your PDA database schema. Submit a file views.sql containing the CREATE VIEW statements. Also submit a script file 6a.log showing the response of the system to the view definitions, and for each view, showing a query involving the view and the system response. (As usual you should truncate the response if more than a few tuples are produced.)
(b) You are to recreate your PDA schema, adding specifications for additional keys, referential integrity, and other constraints.
Submit a file cons.sql containing all your CREATE TABLE statements, along with a script file 6b1.log showing their successful execution in PostgreSQL.
Submit a file viols.sql containing all seven commands, along with a script file 6b2.log showing their unsuccessful execution in PostgreSQL. (c) Create at least two "interesting" triggers for your PDA. Submit a file trigs.sql containing the CREATE TRIGGER statements. Also submit a script file 6c.log showing the response of the system to the trigger definitions, and the execution of database modifications that illustrate the firing of each trigger and cases where neither trigger fires. Show in your script the results of queries demonstrating that the triggers had an effect when they fired and no effect when they didn't.
(d) Extra credit problem: If you do a good job on this problem you are eligible for up to an extra 30% of the total points for this programming assignment.
You are to do some sleuth work: Your task is to determine what criteria exactly PostgreSQL uses in deciding whether a view is updatable, i.e., whether it is possible to perform INSERT, DELETE, and/or UPDATE statements on the view. While your sleuth work could involve sifting through HELP pages or PostgreSQL books, we prefer that you do it experimentally. Write a series of views along with modification commands on the views to determine when PostgreSQL allows views to be updated and when it does not. As discussed in class and in the textbook, some SQL views are obviously updatable, some are obviously not updatable (due to ambiguities), and some are theoretically updatable but it is difficult for a system to determine the correct update translations. In your solution to this problem you should attempt to provide a concise characterization of those views that PostgreSQL allows to be updated, and you should support your claim by demonstrating:
If separate criteria apply for INSERT, DELETE, and UPDATE commands then these should be included in your solution. You may use your PDA schema and data for this problem if you like, or you may use a separate, simpler database.
If you attempt this problem, please submit a text file extra.txt specifying the view update criteria you believe PostgreSQL, and specifying clearly the contents of submitted .sql and/or .log files that support your findings.
README views.sql 6a.log cons.sql 6b1.log viols.sql 6b2.log trigs.sql 6c.logIf you attempt the extra credit problem (part (d)), then you must also submit extra.txt and some clearly specified additional files. If for some reason you feel you must submit files beyond those discussed here, please justify the files in a submitted file called README. Submissions that do not conform to these guidelines will not be graded.
Unless otherwise specified, the scripts you turn in for this assignment may show operations running over your small or your large database (or in the case of Part (d), another database altogether).
Whenever we insert a new tuple into Sells, remove white space and convert to upper case the beer name and then make sure the beer mentioned is also mentioned in Beers, and insert it (with a null manufacturer) if not.
Sells(bar, beer, price)
CREATE FUNCTION checkForBeer()
RETURNS opaque
AS 'BEGIN
IF length(trim(new.beer)) > 0
THEN
new.beer = upper(trim(new.beer));
END IF;
IF new.beer NOT IN
(SELECT name FROM Beers)
THEN
INSERT INTO Beers(name)
VALUES(new.beer);
END IF;
RETURN new;
END;'
LANGUAGE 'plpgsql';
CREATE TRIGGER BeerTrig
BEFORE INSERT OR UPDATE ON Sells
FOR EACH ROW
EXECUTE PROCEDURE checkForBeer();
CREATE TRIGGER trigger [ BEFORE | AFTER ] [ INSERT | DELETE | UPDATE [ OR ... ] ] ON relation FOR EACH [ ROW | STATEMENT ] EXECUTE PROCEDURE procedure (args);