376a. Database Design

1
376a. Database Design

• Dept. of Computer Science
• Vassar College
• http//www.cs.vassar.edu/cs376
• Class 7 Domain Relational Calculus and
  beginning SQL

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)

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.DNAMEresearch and
  d.DNUMBERe.DNO)

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.

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, ..xnm
• xi range of domain of attribute Ai

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 ? , gt , ?, lt, ?, ?.
  xs are domain variables.
• 3. xi. op c where op ? , gt , ?, lt, ?, ? and
  xi is a domain variable.
• Normally use lowercase l-z for domain vars

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
  rB. and sSmith)
• Every attribute of EMPLOYEE is assigned a domain
  var. only U and V are free.

8
Another way

• q EMPLOYEE( John,B.,Smith,t, u, v, w, x,
  y, z)
• All variables are free.

9
Example

• Name and address of everyone in research
  department
• qsv (? z) (? l) (? m) (EMPLOYEE(qrstuvwxyz)
  and DEPT(lmno) and lresearch and mz)
• EMPLOYEE(FNAME, MINIT, LNAME, SSN, BDATE,
  ADDRESS, SEX, SALARY, SUPERSSN, DNO)
• DEPARTMENT(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE)

10
Try a few

• For every project in Stafford, list the
  controlling managers name and birthdate.
• EMPLOYEE(FNAME, MINIT, LNAME, SSN, BDATE,
  ADDRESS, SEX, SALARY, SUPERSSN, DNO)
• DEPARTMENT(DNAME, DNUMBER, MGRSSN, MGRSTARTDATE)
• PROJECT(PNAME,PNUMBER, PLOCATION,DNUM)

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,RELATIONS
  HIP)

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,RELATIONS
  HIP)

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.

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.

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.

16
DROP SCHEMA

• When youre done with the database.
• In MySQL use DROP DATABASE ltdatabasenamegt

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))

18
CREATE TABLE

• Better way
• CREATE TABLE MYCORPDB.EMPLOYEE
• (associate the table with the schema)

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 - DATETIME6digit fract sec
• (These are from p.387 of MySQL manual)

20
Constraints on variables

• NOT NULL - must have a value
• DEFAULT ltvaluegt - if no value is specified, use
  the following value.
• Table constraints include
• PRIMARY KEY (attribute list)
• FOREIGN KEY (attribute) REFERENCES ltforeign keygt
• UNIQUE - specifies alternate key
• Label constraints using
• CONSTRAINT ESSPK PRIMARY KEY (SSN)

21
Referential Integrity

• Through FOREIGN KEY.
• Referential triggered action (ON DELETE) (ON
  UPDATE)
• - SET NULL
• - CASCADE
• - SET DEFAULT

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.

23
Example

• EMPLOYEE( SUPERSSN CHAR (9),FOREIGN
  KEY(SUPERSSN) REFERENCES EMPLOYEE(SSN)ON UPDATE
  CASCADE
• If SSN of manager is corrected, results propagate
  to managers workers.

24
Use CASCADE

• For relationships like WORKS_ON, multi-valued
  attributes like DEPT_LOCATION and weak entities.

25
Remember

• Rows are not ordered.
• Attributes for a given relation have an implicit
  order.

26
DROP TABLE

• When youre finished with a table.
• VERY DANGEROUS. WILL destroy entire table.

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 cant you have a NOT NULL constraint on a new
  attribute?

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

29
SELECT statement

• No relation to sigma
• SQL tables are not sets (theyre multi-sets).
• Use DISTINCT to regain set-like quality.
• Basically
• SELECT
• ltattribute listgt
• FROM
• ltlist of tablesgt
• WHERE
• ltcondition listgt

30
Get the birthdate and address of employees with
the name John B. Smith

• R.A.
• SELECT
• BDATE, ADDRESS
• FROM
• EMPLOYEE
• WHERE
• FNAMEJOHN and MINITB and LNAMESMITH

31
Can use SELECT to do join operation too

• SELECT
• FNAME, ADDRESS
• FROM
• EMPLOYEE, DEPARTMENT
• WHERE
• DEPARTMENTRESEARCH AND DNUMBERDNO
• Print the firstname and address of all employees
  in the research department.

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

33
Can also create attribute aliases,

• EMPLOYEE AS E(FN,MI,LN,SSN,BD,ADDR,S,SAL,SSSN,DNO)

34
SELECT - FROM statements

• Unspecified where is .
• If multiple relations are specified in FROM then
  CROSSPRODUCT

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

36
UNION, EXCEPT and INTERSECT operations

• (SELECT ) UNION (SELECT )
• Sub sets should be union compatible, same
  attribute tuples, same ordering.

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

38
Operations on return values

• ,-,,/
• is string append,

39
SELECT FROM WHERE ORDER BY

• ORDER BY attribute ASCDESC, attribute ASCDESC
• By default it is in ascending order.
• Order on first attribute, then second,then third.

40
More complex queries.

• Nexted queries
• WHERE X IN takes (SELECT as argument)