Slide Template - University of Michigan

Download Report

Transcript Slide Template - University of Michigan

Chapter 7 Relational Algebra and SQL applications

Instructor: Dragomir R. Radev Fall 2005 Fundamentals, Design, and Implementation, 9/e

Review Relational Model Terminology

     

Relation

is a two-dimensional table

Attributes

– are single valued Each attribute belongs to a domain A

domain

is a physical and logical description of permittable values No two rows are identical Order is unimportant The row is called a

tuple

Relational Algebra



Relational algebra

defines a set of operators that may work on relations.

 Recall that relations are simply data sets. As such, relational algebra deals with set theory.

 The operators in relational algebra are very similar to traditional algebra except that they apply to sets. Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/3

Relational Algebra Operators

 Relational algebra provides several operators: – Union – – – – – – Difference Intersection Product Projection Selection Join Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/4

Union Operator

 The

union operator

adds tuples from one relation to another relation  A union operation will result in combined relation  This is similar to the logical operator ‘OR’ Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/5

Union Operator

JUNIOR and HONOR-STUDENT relations and their union:

(a) Example of JUNIOR relation (b) Example HONOR STUDENT relation (c) Union of JUNIOR and HONOR STUDENT relations Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/6

Difference Operator

 The

difference operator

produces a third relation that contains the tuples that appear in the first relation, but not the second  This is similar to a subtraction Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/7

Difference Operator

Intersection Operator

 An

intersection operation

will produce a third relation that contains the tuples that are common to the relations involved.

Intersection Operator

JUNIOR relation Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke HONOR STUDENT relation Intersection of JUNIOR and HONOR STUDENT relations Chapter 7/10

Product Operator

   A

product operator

is a concatenation of every tuple in one relation with every tuple in a second relation The resulting relation will have n x m tuples, where… n = the number of tuples in the first relation and m = the number of tuples in the second relation This is similar to multiplication Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/11

Projection Operator

 A

projection operation

produces a second relation that is a subset of the first.

 The subset is in terms of columns, not tuples  The resulting relation will contain a limited number of columns. However, every tuple will be listed.

Selection Operator

  However, the selection operator produces a subset of tuples, not columns.  The

selection operator

is similar to the projection operator. It produces a second relation that is a subset of the first.

The resulting relation contains all columns, but only contains a portion of the tuples.

Join Operator

 The

join operator

is a combination of the product, selection, and projection operators. There are several variations of the join operator… – – – Equijoin Natural join Outer join • • Left outer join Right outer join Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/14

Data for Join Examples

SID

123 158 271

Name

Jones Parks Smith

Major

History Math History

GradeLevel

JR GR JR 105 Anderson

StudentNumber

Management

ClassName

PositionNumber

Join Examples

Equijoin Natural Join Left Outer Join

Expressing Queries in Relational Algebra

1. What are the names of all students?

STUDENT [Name] 2. What are the student numbers of all students enrolled in a class?

Expressing Queries in Relational Algebra

3. What are the student numbers of all students not enrolled in a class?

STUDENT [SID] – ENROLLMENT [StudentNumber] 4. What are the numbers of students enrolled in the class ‘BD445’?

Expressing Queries in Relational Algebra

5. What are the names of the students enrolled in class ‘BD445’?

STUDENT JOIN (SID = StudentNumber) ENROLLMENT WHERE ClassName = ‘BD445’[STUDENT.Name] Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/19

Expressing Queries in Relational Algebra

6. What are the names and meeting times of ‘PARKS’ classes?

STUDENT WHERE Name = ‘PARKS’ JOIN (SID=StudentNumber) ENROLLMENT JOIN (ClassName = Name) CLASS [CLASS.Name, Time] Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/20

Expressing Queries in Relational Algebra

7. What are the grade levels and meeting rooms of all students, including students not enrolled in a class?

STUDENT LEFT OUTER JOIN (SID = StudentNumber) ENROLLMENT JOIN (ClassName = Name) CLASS [GradeLevel, Room] Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/21

Summary of Relational Algebra Operators

Using SQL in Applications

Instructor: Dragomir R. Radev Winter 2005 Fundamentals, Design, and Implementation, 9/e

View Ridge Gallery

 View Ridge Gallery is a small art gallery that has been in business for 30 years  It sells contemporary European and North American fine art  View Ridge has one owner, three salespeople, and two workers  View Ridge owns all of the art that it sells; it holds no items on a consignment basis Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/24

Application Requirements

 View Ridge application requirements – – – – Track customers and their artist interests Record gallery's purchases Record customers' art purchases List the artists and works that have appeared in the gallery – Report how fast an artist's works have sold and at what margin – Show current inventory in a Web page Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/25

View Ridge Data Model

 Problems: the keys for WORK and TRANSACTION are huge and the key for CUSTOMER is doubtful as many customers may not have an email address Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/27

Surrogate Key Database Design

Sample Values

CHECK CONSTRAINT

 CHECK CONSTRAINT defines limits for column values  Two common uses – – Specifying a range of allowed values Specifying an enumerated list  CHECK constraints may be used – – – To compare the value of one column to another To specify the format of column values With subqueries Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/34

SQL Views

    SQL view is a virtual table that is constructed from other tables or views It has no data of its own, but obtains data from tables or other views SELECT statements are used to define views – A view definition may not include an ORDER BY clause SQL views are a subset of the external views – They can be used only for external views that involve one multi-valued path through the schema Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/35

SQL Views

 Views may be used to – – – – Hide columns or rows Show the results of computed columns Hide complicated SQL statements Provide a level of indirection between application programs and tables – Assign different sets of processing permissions to tables – Assign different sets of triggers Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/36

Example: CREATE VIEW

CREATE VIEW CustomerNameView AS SELECT Name AS CustomerName FROM CUSTOMER; SELECT * FROM CustomerNameView ORDER BY CustomerName; Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/37

Updating Views

  Views may or may not be updatable Rules for updating views are both complicated and DBMS-specific Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/38

Updating Views

Embedding SQL In Program Code

     SQL can be embedded in triggers, stored procedures, and program code Problem: assigning SQL table columns with program variables Solution: object-oriented programming, PL/SQL Problem: paradigm mismatch between SQL and application programming language – SQL statements return sets of rows; an applications work on one row at a time Solution: process the SQL results as pseudo-files Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/40

Triggers

    A trigger is a stored program that is executed by the DBMS whenever a specified event occurs on a specified table or view Three trigger types: BEFORE , INSTEAD OF , and AFTER – – Each type can be declared for Insert, Update, and Delete Resulting in a total of nine trigger types Oracle supports all nine trigger types SQL Server supports six trigger types (only for INSTEAD OF and AFTER triggers) Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/41

Firing Triggers

   When a trigger is fired, the DBMS supplies – – – Old and new values for the update New values for inserts Old values for deletions The way the values are supplied depends on the DBMS product Trigger applications: – – – – Checking validity (Figure 7-14) Providing default values (Figure 7-15) Updating views (Figure 7-16) Enforcing referential integrity actions (Figure 7-17, 7-18) Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/42

Stored Procedures

   A stored procedure is a program that is stored within the database and is compiled when used – – In Oracle, it can be written in PL/SQL or Java In SQL Server, it can be written in TRANSACT-SQL Stored procedures can receive input parameters and they can return results Stored procedures can be called from – – – Programs written in standard languages, e.g., Java, C# Scripting languages, e.g., JavaScript, VBScript SQL command prompt, e.g., SQL Plus, Query Analyzer Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/43

Stored Procedure Advantages

   Greater security as store procedures are always stored on the database server Decreased network traffic SQL can be optimized by the DBMS compiler  Code sharing resulting in – – – Less work Standardized processing Specialization among developers Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/44

Using SQL In Application Code

 SQL can be embedded in application programs  Several SQL statements need to be executed to populate an external view  The application program causes the statements to be executed and then displays the results of the query in the form’s grid controls Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/45

Using SQL In Application Code (cont.)

  The application program also processes and coordinates user actions on a form, including – Populating a drop-down list box – Making the appropriate changes to foreign keys to create record relationships The particulars by which SQL code is inserted into applications depend on the language and data-manipulation methodology used Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/46

MySQL Chapters 1 and 3 Introduction to MySQL

Instructor: Dragomir R. Radev Winter 2005 Fundamentals, Design, and Implementation, 9/e

Overview

– TcX - Michael Widenius (MySQL) – Hughes - David Hughes (mSQL) – Features: • • • • Mostly ANSI SQL2 compliant Transactions Stored procedures Auto_increment fields Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/48

More features

   Cross-database joins Outer joins API: C/C++, Eiffel, Java, PHP, Perl, Python, TCL   Runs on Windows, UNIX, and Mac High performance Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/49

SQL syntax

   CREATE TABLE people (name CHAR(10)) INSERT INTO people VALUES (‘Joe’) SELECT name FROM people WHERE name like ‘J%’ Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/50

SQL commands

   SHOW DATABASES SHOW TABLES Data types: INT, REAL, CHAR(l), VARCHAR(l), TEXT(l), DATE, TIME   ALTER TABLE mytable MODIFY mycolumn TEXT(100) ENUM(‘cat’,’dog’,’rabbit’,’pig’) Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/51

SQL commands

  CREATE DATABASE dbname CREATE TABLE tname (id NOT NULL PRIMARY KEY AUTO_INCREMENT)   CREATE INDEX part_of_name ON customer (name(10)) INSERT INTO tname (c1, …, cn) values (v1, …, vn) Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/52

JOINs and ALIASing

 SELECT book.title, author.name

FROM author, book WHERE books.author = author.id

Loading text files

  Comma-separated files (*.csv) LOAD DATA LOCAL INFILE "whatever.csv" INTO TABLE tname Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/54

Aggregate queries

 SELECT position FROM people GROUP by position  SELECT position, AVG (salary) FROM people GROUP BY position HAVING AVG (salary) > 50000.00

Full text search

   CREATE TABLE WebCache ( url VARCHAR (255) NOT NULL PRIMARY KEY, ptext TEXT NOT NULL, FULLTEXT (ptext)); INSERT INTO WebCache (url, ptext) VALUES (‘index.html’, ‘Welcome to the University of Michigan’); SELECT url from WebCache WHERE MATCH (ptext) against (‘Michigan’); Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/56

Advanced features

Installing MySQL on Windows

 / http://www.mysql.com/products/mysql  http://www.webdevelopersnotes.com/t utorials/sql/index.php3

Useful pointers

    Small example: http://www.itl.nist.gov/div897/ctg/dm/sql_exa mples.htm

MySQL documentation: http://www.mysql.com/doc/en/index.html

(official) MySQL tutorial: http://www.mysql.com/doc/en/Tutorial.html

http://www.itl.nist.gov/div897/ctg/dm/sql_examples.htm

use test; CREATE TABLE STATION (ID INTEGER PRIMARY KEY, CITY CHAR(20), STATE CHAR(2), LAT_N REAL, LONG_W REAL); DESCRIBE STATION; INSERT INTO STATION VALUES (13, 'Phoenix', 'AZ', 33, 112); INSERT INTO STATION VALUES (44, 'Denver', 'CO', 40, 105); INSERT INTO STATION VALUES (66, 'Caribou', 'ME', 47, 68); SELECT * FROM STATION; SELECT * FROM STATION WHERE LAT_N > 39.7; Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/60

SELECT ID, CITY, STATE FROM STATION; ID CITY STATE ; SELECT ID, CITY, STATE FROM STATION WHERE LAT_N > 39.7; CREATE TABLE STATS (ID INTEGER REFERENCES STATION(ID), MONTH INTEGER CHECK (MONTH BETWEEN 1 AND 12), TEMP_F REAL CHECK (TEMP_F BETWEEN -80 AND 150), RAIN_I REAL CHECK (RAIN_I BETWEEN 0 AND 100), PRIMARY KEY (ID, MONTH)); INSERT INTO STATS VALUES (13, 1, 57.4, 0.31); INSERT INTO STATS VALUES (13, 7, 91.7, 5.15); INSERT INTO STATS VALUES (44, 1, 27.3, 0.18); INSERT INTO STATS VALUES (44, 7, 74.8, 2.11); INSERT INTO STATS VALUES (66, 1, 6.7, 2.10); INSERT INTO STATS VALUES (66, 7, 65.8, 4.52); SELECT * FROM STATS; Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/61

SELECT * FROM STATION, STATS WHERE STATION.ID = STATS.ID; SELECT MONTH, ID, RAIN_I, TEMP_F FROM STATS ORDER BY MONTH, RAIN_I DESC; SELECT LAT_N, CITY, TEMP_F FROM STATS, STATION WHERE MONTH = 7 AND STATS.ID = STATION.ID ORDER BY TEMP_F; SELECT MAX(TEMP_F), MIN(TEMP_F), AVG(RAIN_I), ID FROM STATS GROUP BY ID; SELECT * FROM STATION WHERE 50 < (SELECT AVG(TEMP_F) FROM STATS WHERE STATION.ID = STATS.ID); Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/62

CREATE VIEW METRIC_STATS (ID, MONTH, TEMP_C, RAIN_C) AS SELECT ID, MONTH, (TEMP_F - 32) * 5 /9, RAIN_I * 0.3937 FROM STATS; SELECT * FROM METRIC_STATS; SELECT * FROM METRIC_STATS WHERE TEMP_C < 0 AND MONTH = 1 ORDER BY RAIN_C; UPDATE STATS SET RAIN_I = RAIN_I + 0.01; SELECT * FROM STATS; UPDATE STATS SET TEMP_F = 74.9 WHERE ID = 44 AND MONTH = 7; Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/63

SELECT * FROM STATS; COMMIT WORK; UPDATE STATS SET RAIN_I = 4.50 WHERE ID = 44; SELECT * FROM STATS; ROLLBACK WORK; SELECT * FROM STATS; UPDATE STATS SET RAIN_I = 4.50 WHERE ID = 44 AND MONTH = 7; COMMIT WORK; SELECT * FROM STATS; Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/64

DELETE FROM STATS WHERE MONTH = 7 OR ID IN (SELECT ID FROM STATION WHERE LONG_W < 90); DELETE FROM STATION WHERE LONG_W < 90; COMMIT WORK; SELECT * FROM STATION; SELECT * FROM STATS; SELECT * FROM METRIC_STATS; Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/65

http://www.mysql.com/doc/en/Tutorial.html

CREATE TABLE animals ( id MEDIUMINT NOT NULL AUTO_INCREMENT, name CHAR(30) NOT NULL, PRIMARY KEY (id) ); INSERT INTO animals (name) VALUES ("dog"),("cat"),("penguin"), ("lax"),("whale"),("ostrich"); SELECT * FROM animals; CREATE TABLE shop ( article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, dealer CHAR(20) DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL, PRIMARY KEY(article, dealer)); INSERT INTO shop VALUES (1,'A',3.45),(1,'B',3.99),(2,'A',10.99),(3,'B',1.45),(3,'C',1.69), (3,'D',1.25),(4,'D',19.95); Copyright © 2004 SELECT * FROM shop; Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/66

CREATE TABLE articles ( id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), ); body TEXT, FULLTEXT (title,body) INSERT INTO articles VALUES (NULL,'MySQL Tutorial', 'DBMS stands for DataBase ...'), (NULL,'How To Use MySQL Efficiently', 'After you went through a ...'), (NULL,'Optimizing MySQL','In this tutorial we will show ...'), (NULL,'1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'), (NULL,'MySQL vs. YourSQL', 'In the following database comparison ...'), (NULL,'MySQL Security', 'When configured properly, MySQL ...'); SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database'); Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/67

# What's the highest item number?

SELECT MAX(article) AS article FROM shop; # Find number, dealer, and price of the most expensive article.

SELECT MAX(price) FROM shop; SELECT article, dealer, price FROM shop WHERE price=19.95; SELECT article, dealer, price FROM shop ORDER BY price DESC LIMIT 1; # What's the highest price per article? SELECT article, MAX(price) AS price FROM shop Copyright © 2004 GROUP BY article; Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/68

CREATE TEMPORARY TABLE tmp ( article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL); LOCK TABLES shop READ; INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article; SELECT shop.article, dealer, shop.price FROM shop, tmp WHERE shop.article=tmp.article AND shop.price=tmp.price; UNLOCK TABLES; DROP TABLE tmp; SELECT article, SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer, 0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price FROM shop GROUP BY article; Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/69

# find the articles with the highest and lowest price SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop; SELECT * FROM shop WHERE price=@min_price OR price=@max_price; # foreign keys CREATE TABLE person ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, name CHAR(60) NOT NULL, PRIMARY KEY (id) ); CREATE TABLE shirt ( id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT, style ENUM('t-shirt', 'polo', 'dress') NOT NULL, color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL, owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id), PRIMARY KEY (id) ); Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/70

INSERT INTO person VALUES (NULL, 'Antonio Paz'); INSERT INTO shirt VALUES (NULL, 'polo', 'blue', LAST_INSERT_ID()), (NULL, 'dress', 'white', LAST_INSERT_ID()), (NULL, 't-shirt', 'blue', LAST_INSERT_ID()); INSERT INTO person VALUES (NULL, 'Lilliana Angelovska'); INSERT INTO shirt VALUES (NULL, 'dress', 'orange', LAST_INSERT_ID()), (NULL, 'polo', 'red', LAST_INSERT_ID()), (NULL, 'dress', 'blue', LAST_INSERT_ID()), (NULL, 't-shirt', 'white', LAST_INSERT_ID()); SELECT * FROM person; SELECT * FROM shirt; Copyright © 2004 Database Processing: Fundamentals, Design, and Implementation, 9/e by David M. Kroenke Chapter 7/71

SELECT s.* FROM person p, shirt s WHERE p.name LIKE 'Lilliana%' AND s.owner = p.id

AND s.color <> 'white'; # unions select id, style from shirt where color = 'blue' union select id, style from shirt where color = 'orange' # visits per day CREATE TABLE t1 (year YEAR(4), month INT(2) UNSIGNED ZEROFILL, day INT(2) UNSIGNED ZEROFILL); INSERT INTO t1 VALUES(2000,1,1),(2000,1,20),(2000,1,30),(2000,2,2), (2000,2,23),(2000,2,23); SELECT year,month,BIT_COUNT(BIT_OR(1<

Slide Template - University of Michigan

Transcript Slide Template - University of Michigan

Review Relational Model Terminology

Relational Algebra

Relational Algebra Operators

Union Operator

Union Operator

Difference Operator

Difference Operator

Intersection Operator

Intersection Operator

Product Operator

Projection Operator

Selection Operator

Join Operator

Data for Join Examples

Join Examples

Expressing Queries in Relational Algebra

Expressing Queries in Relational Algebra

Expressing Queries in Relational Algebra

Expressing Queries in Relational Algebra

Expressing Queries in Relational Algebra

Summary of Relational Algebra Operators

Using SQL in Applications

View Ridge Gallery

Application Requirements

View Ridge Data Model

View Ridge Data Model

Surrogate Key Database Design

Sample Values

Sample Values

Sample Values

Sample Values

Sample Values

CHECK CONSTRAINT

SQL Views

SQL Views

Example: CREATE VIEW

Updating Views

Updating Views

Embedding SQL In Program Code

Triggers

Firing Triggers

Stored Procedures

Stored Procedure Advantages

Using SQL In Application Code

Using SQL In Application Code (cont.)

MySQL Chapters 1 and 3 Introduction to MySQL

Overview

More features

SQL syntax

SQL commands

SQL commands

JOINs and ALIASing

Loading text files

Aggregate queries

Full text search

Advanced features

Installing MySQL on Windows

Useful pointers

Directory