Lecture 06 Data Modeling: E/R Diagrams

1. Lecture 06Data Modeling: E/R Diagrams Wednesday, January 18, 2006

2. Outline • Data Definition Language (6.6) • Views (6.7) • Constraints (Chapter 7) • We begin E/R diagrams (Chapter 2)

3. Data Definition in SQL • So far we have see the Data Manipulation Language, DML • Next: Data Definition Language (DDL) • Data types: • Defines the types. • Data definition: defining the schema. • Create tables • Delete tables • Modify table schema • Indexes: to improve performance

4. Creating Tables CREATE TABLE Person( name VARCHAR(30), social-security-number INT, age SHORTINT, city VARCHAR(30), gender BIT(1), Birthdate DATE );

5. Deleting or Modifying a Table Deleting: Exercise with care !! Example: DROP Person; Altering: (adding or removing an attribute). ALTER TABLE Person ADD phone CHAR(16); ALTER TABLE Person DROP age; Example: What happens when you make changes to the schema?

6. Default Values Specifying default values: CREATE TABLE Person( name VARCHAR(30), social-security-number INT, age SHORTINT DEFAULT 100, city VARCHAR(30) DEFAULT ‘Seattle’, gender CHAR(1) DEFAULT ‘?’, Birthdate DATE The default of defaults: NULL

7. Indexes REALLYimportant to speed up query processing time. Suppose we have a relation Person (name, age, city) Sequential scan of the file Person may take long SELECT * FROM Person WHERE name = “Smith”

8. Indexes • Create an index on name: B+ trees have fan-out of 100s: max 4 levels ! Will discuss in the second half of this course

9. Creating Indexes Syntax: CREATE INDEX nameIndex ON Person(name)

10. Creating Indexes Indexes can be useful in range queries too: B+ trees help in: Why not create indexes on everything? CREATE INDEX ageIndex ON Person (age) SELECT * FROM Person WHERE age > 25 AND age < 28

11. CREATE INDEX doubleindex ON Person (age, city) Example: SELECT * FROM Person WHERE age = 55 AND city = “Seattle” Helps in: SELECT * FROM Person WHERE age = 55 and even in: SELECT * FROM Person WHERE city = “Seattle” But not in: Creating Indexes Indexes can be created on more than one attribute:

12. The Index Selection Problem • Why not build an index on every attribute ? On every pair of attributes ? Etc. ? • The index selection problem is hard: balance the query cost v.s. the update cost, in a large application workload

13. Defining Views Views are relations, except that they are not physically stored. For presenting different information to different users Employee(ssn, name, department, project, salary) Payroll has access to Employee, others only to Developers CREATE VIEW Developers AS SELECT name, project FROM Employee WHERE department = “Development”

14. Example Person(name, city) Purchase(buyer, seller, product, store) Product(name, maker, price, category) CREATE VIEWSeattle-Purchase AS SELECT y.buyer, y.seller, y.product, y.store FROM Person x, Purchase y WHERE x.city = ‘Seattle’ AND x.name = y.buyer Seattle-Purchase(buyer, seller, product, store) “virtual table”

15. We can later use the view: SELECT v.name, u.store FROMSeattle-Purchase u, Product v WHEREu.product = v.name AND v.category = ‘shoes’

16. What Happens When We Query a View ? SELECT v.name, u.store FROMSeattle-Purchase u, Product v WHEREu.product = v.name AND v.category = ‘shoes’ SELECT v.name, y.store FROMPerson x, Purchase y, Product v WHEREx.city = ‘Seattle’ AND x.name = y.buyer AND y.product = v.name AND v.category = ‘shoes’

17. Types 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 data

18. Updating Views: Part 1 Purchase(buyer, seller, product, store) Product(name, maker, price, category) CREATE VIEWExpensive-Product AS SELECT name, maker FROM Product WHERE price > 100 INSERT INTOExpensive-ProductVALUES(‘Gizmo’, ‘Gadgets INC.’) INSERT INTO Product VALUES(‘Gizmo’, ‘Gadgets INC.’, NULL, NULL)

19. Updating Views: Part 2 Purchase(buyer, seller, product, store) Product(name, maker, price, category) CREATE VIEWToy-Product AS SELECT name, maker FROM Product WHERE category = ‘Toys’ INSERT INTOToy-ProductVALUES(‘Gizmo’, ‘Gadgets INC.’) Notethis INSERT INTO Product VALUES(‘Gizmo’, ‘Gadgets INC.’, NULL, NULL)

20. Updating Views: Part 3 Purchase(buyer, seller, product, store) Product(name, maker, price, category) CREATE VIEWBuyer-Maker AS SELECT x.buyer, y.maker FROM Purchase x, Product y WHERE x.product = y.name Non-updateableview INSERT INTOBuyer-MakerVALUES(‘John Smith’, ‘Gadgets INC.’) Most views arenon-updateable ? ? ? ? ?

21. Constraints 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 updates

22. Constraints in SQL Constraints in SQL: • Keys, foreign keys • Attribute-level constraints • Tuple-level constraints • Global constraints: assertions The more complex the constraint, the harder it is to check and to enforce simplest Mostcomplex

23. Keys CREATE TABLE Product ( name CHAR(30) PRIMARY KEY, category VARCHAR(20)) OR: Product(name, category) CREATE TABLE Product ( name CHAR(30), category VARCHAR(20) PRIMARY KEY (name))

24. CREATE TABLE Product ( name CHAR(30), category VARCHAR(20), price INT, PRIMARY KEY (name, category)) Keys with Multiple Attributes Product(name, category, price)

25. Other Keys CREATE TABLE Product ( productID CHAR(10), name CHAR(30), category VARCHAR(20), price INT,PRIMARYKEY (productID), UNIQUE (name, category)) There is at most one PRIMARY KEY;there can be many UNIQUE

26. Foreign Key Constraints Referentialintegrityconstraints CREATE TABLE Purchase ( prodName CHAR(30) REFERENCES Product(name), date DATETIME) May writejust Product(why ?) prodName is a foreign key to Product(name)name must be a key in Product

27. Product Purchase

28. Foreign Key Constraints • OR • (name, category) must be a PRIMARY KEY CREATE TABLE Purchase ( prodName CHAR(30), category VARCHAR(20), date DATETIME, FOREIGNKEY (prodName, category) REFERENCES Product(name, category)

29. What happens during updates ? Types of updates: • In Purchase: insert/update • In Product: delete/update Product Purchase

30. What 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 NULL READING ASSIGNEMNT: 7.1.5, 7.1.6

31. Constraints on Attributes and Tuples • Constraints on attributes: NOT NULL -- obvious meaning... CHECK condition -- any condition ! • Constraints on tuples CHECK condition

32. Whatis the difference fromForeign-Key ? CREATE TABLE Purchase ( prodName CHAR(30) CHECK (prodName INSELECT Product.nameFROM Product), date DATETIME NOT NULL)

33. General Assertions CREATE ASSERTION myAssert CHECKNOT EXISTS( SELECT Product.nameFROM Product, PurchaseWHERE Product.name = Purchase.prodNameGROUPBY Product.nameHAVING count(*) > 200)

34. Final Comments on Constraints • Can give them names, and alter later • Read in the book !!! • We need to understand exactly when they are checked • We need to understand exactly what actions are taken if they fail