1 / 9

Assignment 5 Q’s

Assignment 5 Q’s. CSCI 2141 W2013. Foreign Key issue. CONSTRAINT fk_model FOREIGN KEY (model) REFERENCES Desktop(Model ) Problem – can’t have it reference the model in two different tables My solution avoids that by collapsing 2 tables into devices

thomas-buckley
Download Presentation

Assignment 5 Q’s

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Assignment 5 Q’s CSCI 2141 W2013

  2. Foreign Key issue • CONSTRAINT fk_model FOREIGN KEY (model) REFERENCES Desktop(Model) • Problem – can’t have it reference the model in two different tables • My solution avoids that by collapsing 2 tables into devices • Problem: how to enforce constraint of a null value for screen if not a laptop? • Could do it semantically (screen is last attribute, trust those inserting data to not provide data if it is not a laptop) • Or need some kind of a constraint

  3. Solution from Aaron Hallink: • http://www.w3schools.com/sql/sql_check.asp • I found out it actually hasn't been implemented in MYsql. http://stackoverflow.com/questions/14247655/mysql-check-constraint • I had to create a trigger instead, to set the screen to null when the device is a desktop. DELIMITER $$ CREATE TRIGGER device_screen BEFORE INSERT ON Device FOR EACH ROW BEGIN IF (new.type ='DESKTOP') THEN SET new.screen = NULL; END IF; END$$ DELIMITER ;

  4. Tuple based constraints • Part 3 asks you to write constraints as tuple based constraints • Tuple based constraints are required instead of attribute-based constraints when you need to check more than 1 attribute in a tuple – they must be defined after the attributes are defined • CHECK (year > 1909 AND length <480) • Both attribute-based and tuple-based can refer to other tables through subqueries • So you can do a subquery to see if a studio name is in at least one Movies tuple • So that section actually doesn’t necessarily need tuple based constraints, just more complex constraints

  5. Attribute-Based Checks • Constraints on the value of a particular attribute. • Add CHECK(<condition>) to the declaration for the attribute. • The condition may use the name of the attribute, but any other relation or attribute name must be in a subquery.

  6. Example: Attribute-Based Check CREATE TABLE Sells ( canteen CHAR(20), product CHAR(20) CHECK ( product IN (SELECT name FROM products)), price REAL CHECK ( price <= 5.00 ) );

  7. Timing of Checks • Attribute-based checks are performed only when a value for that attribute is inserted or updated. • Example: CHECK (price <= 5.00) checks every new price and rejects the modification (for that tuple) if the price is more than $5. • Example: CHECK (product IN (SELECT name FROM products)) not checked if a product is deleted from products (unlike foreign-keys).

  8. Tuple-Based Checks • CHECK (<condition>) may be added as a relation-schema element. • The condition may refer to any attribute of the relation. • But other attributes or relations require a subquery. • Checked on insert or update only.

  9. Example: Tuple-Based Check • Only Joe’s canteen can sell product for more than $5: CREATE TABLE Sells ( canteen CHAR(20), product CHAR(20), price REAL, CHECK (canteen = ‘Joes canteen’OR price <= 5.00) );

More Related