Design process • Identify entities and attributes • What do you want to know? • Note that the question of whether something is an attribute or an entity may not be an easy one… • Identify relationships • Do they have their own attributes? • Think of specific data cases and walk through the business process with this in mind • Does your design still stand up? • Squish things around! • Don’t become attached to one way of thinking about things • Iterate • Until you’ve talked to everyone needed and had time to reflect on the design • More is better!
Normalization • Functional dependencies • Normal forms
Relational theory and SQL • Set theory • projection = select name, age, dept from student • selection = "retrieve students who are not studying LS and are not in their second year" • cartesian product = result of concatenating every tuple in r1 with every tuple of r2.
Student ID Name • Smith • Wilson Enrolled Student Course 1 1 1 2 2 1 Course • ID Name Descrip • 258 Database Systems • 101 Glass Fusing Cartesian product SELECT * FROM student, course, enrolled WHERE enrolled.student = student.ID;
Joins • Inner • Union • Union compatible sets: respective column datatypes are the same, column order is the same • necessary because datatypes of result set are determined by datatypes of source • Intersect • Minus or Difference • Outer
SQL Review • DDL – data definition language • Create, alter, drop • DML – data manipulation language • Select, update, insert, delete • Other useful commands • Describe, grant, revoke
Create Table CREATE table tablename ( colname datatype constraints, table-constraints); • common datatypes: CHAR(n), VARCHAR(n), int, float (n,n), date, time • common column constraints: primary key, not null, default value • common table-constraints: primary key, foreign key
Create Table Example (1) CREATE TABLE teacher ( PID CHAR(9) PRIMARY KEY, name VARCHAR(30) NOT NULL, salary FLOAT(8,2), bdate DATE, dept VARCHAR(10), FOREIGN KEY (dept) REFERENCES department (DNAME) ON DELETE CASCADE);
Create Table Example(2) CREATE TABLE teaches ( teacherID CHAR(9), coursenum CHAR(3), PRIMARY KEY (teacherID, coursenum), FOREIGN KEY (teacherID) references teacher(PID), FOREIGN KEY (coursenum) REFERENCES course (coursenum));
Create Index CREATE INDEX indexname ON table (col-1, col-2,..col-n); CREATE INDEX idx_teachername ON teacher (name);
Create View CREATE VIEW viewname AS SELECT col-1, col-2,…col-n FROM table-1, table-2,…table-n WHERE conditions;
Create View Example CREATE VIEW vw_teach_course AS SELECT PID, name, dept, coursenum, coursename FROM teacher, teaches, course WHERE teacher.PID = teaches.teacherID AND course.coursenum = teaches.coursenum;
Drop • DROP TABLE name; • DROP TABLE name RESTRICT; • DROP TABLE name CASCADE; • DROP INDEX name; • DROP VIEW name;
Alter Table • ALTER TABLE tablename ADD column datatype; • ALTER TABLE tablename DROP column; • and various other alterations, HIGHLY implementation specific as to availability • changing datatype is risky if data is already in DB
Select SELECT col-1, col-2,...col-n FROM table-1, table-2...table-3 WHERE conditions; • can also do aggregate functions, grouping, and ordering.
Select Example (1) SELECT PID, name, salary FROM teacher WHERE bdate < ‘1952-01-01’ ORDER BY salary;
Select Example (2a) SELECT teacher.name, course.cnum, course.cname FROM teacher, teaches, course WHERE teacher.PID = teaches.teacherID AND course.cnum = teaches.coursenum AND course.cname = ‘Glass Fusing 101’;
Select Example (2b) SELECT teacher.name, course.cnum, course.cname FROM teacher join (teaches join course on teaches.coursenum = course.cnum) on teaches.teacherID = teacher.PID WHERE course.cname = ‘Glass Fusing 101’;
Select Example (3) SELECT teacher.name FROM teacher WHERE teacher.PID in (SELECT teachID FROM teaches WHERE teaches.coursenum = ‘258’);
Select Example (4) SELECT dept, avg(salary) FROM teacher WHERE bdate < ‘1950-01-01’ GROUP BY dept ORDER BY dept;
Group by • A query with a group by clause is executed as follows: • Select all rows that satisfy conditions in the where clause • Form groups according to group by clause • Discard groups that don’t satisfy the having clause • Apply aggregate functions to each group • Retrieve values for aggregates and columns specified in the select clause
Insert INSERT INTO table VALUES (val-1, val-2,...val-3); INSERT INTO table (col-1, col-2,...col-n) VALUES (val-1, val-2,...val-3);
Insert Examples INSERT INTO teacher VALUES (‘123456789’, ‘Mary Brown’, 91253.83, ‘1949-03-21’, ‘English’); INSERT INTO teacher (PID, name, dept) VALUES (‘123456789’, ‘Mary Brown’, ‘English’);