Alternatives to My SQL - Database Design - Lecture Slides, Slides of Database Management Systems (DBMS)

Download Alternatives to My SQL - Database Design - Lecture Slides and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

A little bit more about SQL

Alternatives to MySQL Views/Triggers

Alternatives to MySQL

• PostgreSQL – It has more features than many other DBMS including MySQL and is completely free thanks to its BSD license.
• SQLite – Small library, meant to be embedded inside an application without many features but surprisingly compliant to the SQL standard (source under the public domain!)

Differences (continued)

• Be aware when you use special features of one DBMS, you will be practically locked to that DBMS and it might be very hard to switch to another DBMS.
• Thankfully, most DBMS based on SQL do try very hard to adhere to the standard and the differences are almost always very small.

Views

• A view is a subset of a table. You can use it to retrieve and update data or even delete rows.
• You create a view from attributes/tuples of other tables and from there you can do almost everything that you can do with a table.

Deleting a view

• Very simple command
• DROP VIEW v;

Changing the view definition

• ALTER VIEW v AS SELECT qty, price FROM t;
• Same as CREATE VIEW. It actually DROPS the view and then creates it again. It is a shortcut.

• Did you see that?
• Our value attribute didn’t get updated when the price went up.
• Thankfully, triggers are at the rescue or are they?
• Actually they won’t help us here, but we’ll see why later!

Triggers

• A trigger is an object in a database that is associated with a table and is activated when a particular event occurs in the table.
• Unfortunately, triggers can only be associated with a permanent table and not with a temporary one or a view (at least in MySQL)

Triggers (continued)

• So there you go, we can now have our value attribute updated every single time that the table changes.
• The previous command did not look very efficient to me (what if you updated only one tuple.. You don’t need to go through all the rows).
• It appears that for most uses of a trigger you will need to go through all the rows though.

Triggers

• You can also remove a trigger.
• DROP TRIGGER updatevalue;
• Other DBMS like Oracle and Postgres seem to have a more advanced trigger implementation.