1 / 40

376a. Database Design

Learn about Domain Relational Calculus, SQL basics, schema creation, & more in this Database Design class at Vassar College. Explore ER diagrams, tuples, constraints conversion, and relational data models.

edennis
Download Presentation

376a. Database Design

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. 376a. Database Design Dept. of Computer Science Vassar College http://www.cs.vassar.edu/~cs376 Class 7: Domain Relational Calculus and beginning SQL Prof. Billibon Yoshimi

  2. Housekeeping • Reminder: Homework for Oct 2nd. • 4.19, 7.20, 7.26, 9.11, 9.15, 9.20 (except h) • Midterm: October 9th (Wednesday) Prof. Billibon Yoshimi

  3. So far… • Should be able to draw ER and EER diagrams. • Need to work on constraints. • Relational data model. • How to convert ER and EER to Relational Model. • Understand tuples and tuple calculus {e.name, e.address | EMPLOYEE(e) and (d) (DEPARTMENT(d) and d.DNAME=“research” and d.DNUMBER=e.DNO) Prof. Billibon Yoshimi

  4. Safe expressions • Expressions should return finite number of results. • {t | not (EMPLOYEE(t)) } is not safe. • Only considered safe if the results are from the domain of the range relation (right side). • Not (EMPLOYEE(t)) has tuples from outside the EMPLOYEE(t) relation. Prof. Billibon Yoshimi

  5. One more calculus: Domain Relational Calculus • Domain relational calculus used in query by example. • Variables range of domains of attributes (instead of tuples.) • E.g. {x1, x2..xn| COND(x1, x2..xn, ..xn+m} xi range of domain of attribute Ai Prof. Billibon Yoshimi

  6. Atoms are different Atom may be… 1. R(x1, x2, … xn) where r is a relation with degree n and each xi is a domain variable. In short hand R(x1 x2 … xn) no commas 2. xi. op xj. where op  { =, > , , <, , }. x’s are domain variables. 3. xi. op c where op  { =, > , , <, , } and xi is a domain variable. *Normally use lowercase l-z for domain vars Prof. Billibon Yoshimi

  7. Example Get birthdate and address of person named “John B. Smith” { uv | ( q) ( r) ( s) ( t) ( w) ( x) ( y) ( z) (EMPLOYEE (qrstwxyz) and q = ‘John’ and r=‘B.’ and s=‘Smith’)} Every attribute of EMPLOYEE is assigned a domain var. only U and V are free. Prof. Billibon Yoshimi

  8. Another way {q | EMPLOYEE( ‘John’,’B.’,’Smith’,t, u, v, w, x, y, z) } All variables are free. Prof. Billibon Yoshimi

  9. Example Name and address of everyone in research department {qsv | ( z) ( l) ( m) (EMPLOYEE(qrstuvwxyz) and DEPT(lmno) and l=‘research’ and m=z) EMPLOYEE(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO) DEPARTMENT(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) Prof. Billibon Yoshimi

  10. Try a few For every project in Stafford, list the controlling manager’s name and birthdate. EMPLOYEE(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO) DEPARTMENT(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) PROJECT(PNAME,PNUMBER, PLOCATION,DNUM) Prof. Billibon Yoshimi

  11. Find employees with no dependents. EMPLOYEE(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO) DEPARTMENT(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) PROJECT(PNAME,PNUMBER, PLOCATION,DNUM) WORKS_ON(ESSN,PNO,HOURS) DEPT_LOCATIONS(DNUMBER,DLOCATION) DEPENDENT(ESSN, DEPENDENT_NAME,SEX,BDATE,RELATIONSHIP) Prof. Billibon Yoshimi

  12. List names of all managers with one dependent. EMPLOYEE(FNAME, MINIT, LNAME, SSN, BDATE, ADDRESS, SEX, SALARY, SUPERSSN, DNO) DEPARTMENT(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE) PROJECT(PNAME,PNUMBER, PLOCATION,DNUM) WORKS_ON(ESSN,PNO,HOURS) DEPT_LOCATIONS(DNUMBER,DLOCATION) DEPENDENT(ESSN, DEPENDENT_NAME,SEX,BDATE,RELATIONSHIP) Prof. Billibon Yoshimi

  13. SQL2 or SQL92 Related to relational algebra (types of operations) Used by most commercial RDBMS. Based on tuple calculus (declarative vs. procedural) Used to define data and manipulate data. Prof. Billibon Yoshimi

  14. Basic data types Table - relation Row - tuple Column - attribute Schema - name, tables, constraints, views, domains and authorization. Schemas allow multiple databases to exist on the same server. Prof. Billibon Yoshimi

  15. CREATE SCHEMA Used to create new schema E.g. CREATE SCHEMA MYCORPDB AUTHORIZATION YOSHIMI Creates a new table to hold relations (tables) for the schema MYCORPDB. The owner of the database is YOSHIMI. In MySQL use CREATE DATABASE no auth. Prof. Billibon Yoshimi

  16. DROP SCHEMA When you’re done with the database. In MySQL use DROP DATABASE <databasename> Prof. Billibon Yoshimi

  17. CREATE TABLE Used to create a new relation. CREATE TABLE EMPLOYEE ( FNAME VARCHAR(15) NOT NULL, MINIT CHAR, LNAME VARCHAR(15) NOT NULL, SSN CHAR(9) NOT NULL, BDATE DATE, ADDRESS VARCHAR(30), SEX CHAR, SALARY DECIMAL (10,2), SUPERSSN CHAR(9) DNO INT NOT NULL, PRIMARY KEY (SSN), FOREIGN KEY (SUPERSSN) REFERENCES EMPLOYEE(SSN), FOREIGN KEY (DNO) REFERENCES DEPARTMEnT (DNUMBER)); Prof. Billibon Yoshimi

  18. CREATE TABLE Better way CREATE TABLE MYCORPDB.EMPLOYEE (associate the table with the schema) Prof. Billibon Yoshimi

  19. Basic data types INTEGER, INT or SMALLINT FLOAT, REAL, DOUBLE PRECISION DECIMAL( i, j) - total and fractional CHAR, CHARACTER VARCHAR DATE - YEAR, MONTH, DAY TIME - HOUR, MINUTE, SECOND TIMESTAMP - DATE+TIME+6digit fract sec (These are from p.387 of MySQL manual) Prof. Billibon Yoshimi

  20. Constraints on variables NOT NULL - must have a value DEFAULT <value> - if no value is specified, use the following value. Table constraints include PRIMARY KEY (attribute list) FOREIGN KEY (attribute) REFERENCES <foreign key> UNIQUE - specifies alternate key Label constraints using CONSTRAINT ESSPK PRIMARY KEY (SSN) Prof. Billibon Yoshimi

  21. Referential Integrity Through FOREIGN KEY. Referential triggered action (ON DELETE) (ON UPDATE): - SET NULL - CASCADE - SET DEFAULT Prof. Billibon Yoshimi

  22. Example EMPLOYEE( … SUPERSSN CHAR (9), FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN) ON DELETE SET NULL When supervisor is deleted, set this field to NULL. Prof. Billibon Yoshimi

  23. Example EMPLOYEE( …SUPERSSN CHAR (9),FOREIGN KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN)ON UPDATE CASCADE If SSN of manager is corrected, results propagate to manager’s workers. Prof. Billibon Yoshimi

  24. Use CASCADE For relationships like WORKS_ON, multi-valued attributes like DEPT_LOCATION and weak entities. Prof. Billibon Yoshimi

  25. Remember… Rows are not ordered. Attributes for a given relation have an implicit order. Prof. Billibon Yoshimi

  26. DROP TABLE When you’re finished with a table. VERY DANGEROUS. WILL destroy entire table. Prof. Billibon Yoshimi

  27. ALTER TABLE Used to add/remove/modify attributes (domains). Used to add/remove/modify constraints ALTER TABLE MYCORPDB.EMPLOYEE ADD PAYSCALE CHAR(3); Why can’t you have a NOT NULL constraint on a new attribute? Prof. Billibon Yoshimi

  28. Other ALTER TABLE operations ALTER TABLE EMPLOYEE DROP PAYSCALE CASCADE; ALTER TABLE EMPLOYEE DROP PAYSCALE DEFAULT; cancel the default value ALTER TABLE EMPLOYEE SET DEFAULT ‘001’ ALTER TABLE EMPLOYEE DROP CONSTRAINT EMPPK CASCADE; Prof. Billibon Yoshimi

  29. SELECT statement No relation to sigma SQL tables are not sets (they’re multi-sets). Use DISTINCT to regain set-like quality. Basically: SELECT <attribute list> FROM <list of tables> WHERE <condition list> Prof. Billibon Yoshimi

  30. Get the birthdate and address of employees with the name “John B. Smith” R.A. SELECT BDATE, ADDRESS FROM EMPLOYEE WHERE FNAME=“JOHN” and MINIT=“B” and LNAME=“SMITH” Prof. Billibon Yoshimi

  31. Can use SELECT to do join operation too SELECT FNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT WHERE DEPARTMENT=“RESEARCH” AND DNUMBER=DNO Print the firstname and address of all employees in the research department. Prof. Billibon Yoshimi

  32. Resolving ambiguous names Relationships may have same named attributes. Use relation.attribute to disambiguate. When using multiple instances of a relation in a SELECT, use aliases.. SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE as E, EMPLOYEE as S WHERE E.SUPERSSN = S.SSN Prof. Billibon Yoshimi

  33. Can also create attribute aliases, EMPLOYEE AS E(FN,MI,LN,SSN,BD,ADDR,S,SAL,SSSN,DNO) Prof. Billibon Yoshimi

  34. SELECT - FROM statements Unspecified where is *. If multiple relations are specified in FROM then CROSSPRODUCT Prof. Billibon Yoshimi

  35. Other modifiers to SELECT SELECT * FROM - WHERE * - Selects all attributes SELECT ALL x FROM WHERE - get all values, including duplicates SELECT DISTINCT * FROM WHERE - removes duplicates *EXPENSIVE* Prof. Billibon Yoshimi

  36. UNION, EXCEPT and INTERSECT operations (SELECT *) UNION (SELECT *) Sub sets should be union compatible, same attribute tuples, same ordering. Prof. Billibon Yoshimi

  37. Comparing strings In WHERE statements use LIKE WHERE NAME LIKE “%ITH%” % - replaces arbitrary numbers of characters _ - replaces a single character “_____5_____” In MySQL, use REGEX too. ^ - match beginning of line $- match end of line [bB] - match any one char in bracket * - zero or one instances of preceding thing Match anywhere in the input, unlike LIKE Prof. Billibon Yoshimi

  38. Operations on return values +,-,*,/ || is string append, Prof. Billibon Yoshimi

  39. SELECT FROM WHERE ORDER BY ORDER BY attribute ASC|DESC, attribute ASC|DESC By default it is in ascending order. Order on first attribute, then second,then third. Prof. Billibon Yoshimi

  40. More complex queries. Nexted queries WHERE X IN takes (SELECT as argument) Prof. Billibon Yoshimi

More Related