1Lecture 05Views, ConstraintsFriday, October 6, 20062Outline• Data Definition Language (6.6)• Views (6.7)• Constraints (Chapter 7)3Defining ViewsViews are relations, except that they are not physically stored.For presenting different information to different usersEmployee(ssn, name, department, project, salary)Payroll has access to Employee, others only to DevelopersCREATE VIEW Developers ASSELECT name, projectFROM EmployeeWHERE department = “Development”CREATE VIEW Developers ASSELECT name, projectFROM EmployeeWHERE department = “Development”4CREATE VIEW CustomerPrice ASSELECT x.customer, y.priceFROM Purchase x, Product yWHERE x.product = y.pnameCREATE VIEW CustomerPrice ASSELECT x.customer, y.priceFROM Purchase x, Product yWHERE x.product = y.pnameExamplePurchase(customer, product, store)Product(pname, price)CustomerPrice(customer, price)“virtual table”5SELECT u.customer, v.storeFROM CustomerPrice u, Purchase vWHERE u.customer = v.customer ANDu.price > 100SELECT u.customer, v.storeFROM CustomerPrice u, Purchase vWHERE u.customer = v.customer ANDu.price > 100We can later use the view:Purchase(customer, product, store)Product(pname, price)CustomerPrice(customer, price)6What Happens When We Query a View ?SELECT u.customer, v.storeFROM CustomerPrice u, Purchase vWHERE u.customer = v.customer ANDu.price > 100SELECT u.customer, v.storeFROM CustomerPrice u, Purchase vWHERE u.customer = v.customer ANDu.price > 100SELECT x.customer, v.storeFROM Purchase x, Product y, Purchase v, WHERE x.customer = v.customer ANDy.price > 100 ANDx.product = y.pnameSELECT x.customer, v.storeFROM Purchase x, Product y, Purchase v, WHERE x.customer = v.customer ANDy.price > 100 ANDx.product = y.pname7Types of Views• Virtual views:– Used in databases– Computed only on-demand – slow at runtime– Always up to date• Materialized views– Used in data warehouses– Pre-computed offline – fast at runtime– May have stale data8CREATE VIEW Expensive-Product ASSELECT pnameFROM ProductWHERE price > 100CREATE VIEW Expensive-Product ASSELECT pnameFROM ProductWHERE price > 100Updating Views: Part 1INSERT INTO Expensive-ProductVALUES(‘Gizmo’)INSERT INTO Expensive-ProductVALUES(‘Gizmo’)INSERT INTO Product VALUES(‘Gizmo’, NULL)INSERT INTO Product VALUES(‘Gizmo’, NULL)Purchase(customer, product, store)Product(pname, price)Updateableview9CREATE VIEW AcmePurchase ASSELECT customer, productFROM PurchaseWHERE store = ‘AcmeStore’CREATE VIEW AcmePurchase ASSELECT customer, productFROM PurchaseWHERE store = ‘AcmeStore’Updating Views: Part 2INSERT INTO Toy-ProductVALUES(‘Joe’, ‘Gizmo’)INSERT INTO Toy-ProductVALUES(‘Joe’, ‘Gizmo’)INSERT INTO Product VALUES(‘Joe’,’Gizmo’,NULL)INSERT INTO Product VALUES(‘Joe’,’Gizmo’,NULL)NotethisPurchase(customer, product, store)Product(pname, price)Updateableview10Updating Views: Part 3INSERT INTO CustomerPriceVALUES(‘Joe’, 200)INSERT INTO CustomerPriceVALUES(‘Joe’, 200)? ? ? ? ?? ? ? ? ?Non-updateableviewMost views arenon-updateableCREATE VIEW CustomerPrice ASSELECT x.customer, y.priceFROM Purchase x, Product yWHERE x.product = y.pnameCREATE VIEW CustomerPrice ASSELECT x.customer, y.priceFROM Purchase x, Product yWHERE x.product = y.pnamePurchase(customer, product, store)Product(pname, price)11Constraints in SQL• A constraint = a property that we’d like our database to hold• The system will enforce the constraint by taking some actions:– forbid an update– or perform compensating updates12Constraints in SQLConstraints in SQL:• Keys, foreign keys• Attribute-level constraints• Tuple-level constraints• Global constraints: assertionsThe more complex the constraint, the harder it is to check and to enforcesimplestMostcomplex13KeysOR:CREATE TABLE Product (name CHAR(30) PRIMARY KEY,category VARCHAR(20))CREATE TABLE Product (name CHAR(30) PRIMARY KEY,category VARCHAR(20))CREATE TABLE Product (name CHAR(30),category VARCHAR(20)PRIMARY KEY (name))CREATE TABLE Product (name CHAR(30),category VARCHAR(20)PRIMARY KEY (name))Product(name, category)14Keys with Multiple AttributesCREATE TABLE Product (name CHAR(30),category VARCHAR(20),price INT,PRIMARY KEY (name, category))CREATE TABLE Product (name CHAR(30),category VARCHAR(20),price INT,PRIMARY KEY (name, category))40GadgetGizmo302010PricePhotoGizmoPhotoCameraGadgetGizmoCategoryNameProduct(name, category, price)15Other KeysCREATE TABLE Product (productID CHAR(10),name CHAR(30),category VARCHAR(20),price INT,PRIMARY KEY (productID),UNIQUE (name, category))CREATE TABLE Product (productID CHAR(10),name CHAR(30),category VARCHAR(20),price INT,PRIMARY KEY (productID),UNIQUE (name, category))There is at most one PRIMARY KEY;there can be many UNIQUE16Foreign Key ConstraintsCREATE TABLE Purchase (prodName CHAR(30)REFERENCES Product(name),date DATETIME)CREATE TABLE Purchase (prodName CHAR(30)REFERENCES Product(name),date DATETIME)prodName is a foreign key to Product(name)name must be a key in ProductReferentialintegrityconstraintsMay writejust Product(why ?)17PhotoOneClickPhotoCameragadgetGizmoCategoryNameWizCameraRitzCameraWizGizmoStoreProdNameProduct Purchase18Foreign Key Constraints• OR• (name, category) must be a PRIMARY KEYCREATE TABLE Purchase (prodName CHAR(30),category VARCHAR(20),date DATETIME,FOREIGN KEY (prodName, category) REFERENCES Product(name, category) CREATE TABLE Purchase (prodName CHAR(30),category VARCHAR(20),date DATETIME,FOREIGN KEY (prodName, category) REFERENCES Product(name, category)19PhotoOneClickPhotoCameragadgetGizmoCategoryNameWizCameraRitzCameraWizGizmoStoreProdNameProduct PurchaseWhat happens during updates ?Types of updates:• In Purchase: insert/update• In Product: delete/update20What happens during updates ?• SQL has three policies for maintaining referential integrity:• Reject violating modifications (default)• Cascade: after a delete/update do a delete/update• Set-null set foreign-key field to NULLREADING ASSIGNEMNT: 7.1.5, 7.1.621Constraints on Attributes and Tuples• Constraints on attributes:NOT NULL -- obvious meaning...CHECK condition -- any condition !• Constraints on tuplesCHECK condition22CREATE TABLE Purchase (prodName CHAR(30)CHECK (prodName INSELECT Product.nameFROM Product),date DATETIME NOT NULL)CREATE TABLE Purchase (prodName CHAR(30)CHECK (prodName INSELECT Product.nameFROM Product),date DATETIME NOT NULL)Whatis the difference fromForeign-Key ?23General AssertionsCREATE ASSERTION
View Full Document
Unlocking...