Download Understanding the Role & History of SQL in Database Management and more Study Guides, Projects, Research Introduction to Database Management Systems in PDF only on Docsity!
Announcements
ā¢^ Written Homework 1 due Nov 2ā See course web pageā Exercises 5.12, 5.15, 6.17, 6.20, 6.22 (a,c,f only).ā¢^ Todayā SQL (chapter 8)ā Instructional environment overviewā¢^ Where we are headingā SQL programming (chapter 9)ā Database design (ch 10), practical design (ch 12),
A brief history of SQL
ā¢^ In 1970s, team at IBM implemented System Rbased on the relational modelā¢^ SEQUEL (Structured English Query Language)was created to manipulate system R dataā¢^ In late 1970s Oracle V2 was first to marketā¢^ IBMs system 38 followedā¢^ Adopted as ANSI standard in ā86 and ISO in ā87ā āOfficialā pronunciation is āes-queue-elāā SQL3 (1999) last major update to standardā¢^ Currently vendors āself certifyā compliance ;-)
SQL, the reality
ā¢^ every vendor supports slightly different versionof SQLā¢^ Thus, oracle SQL for example, will not bedirectly portable to say Microsoft SQL
SQL has multiple roles
ā¢^ Data definition language (DDN)ā Eg, define relation schemasā¢^ Data control language (DCL)ā Eg, security and authorization controlsā¢^ Data manipulation language (DML)ā Query for tuplesā Insert, delete and modify tuplesā¢^ SQL supports constraints, transactions & viewsā¢^ SQL standard does not support indexes
SQL DDL statements
ā¢^ SQL has two key DDL commands^ ā^ CREATE SCHEMA
ā¢ Creates a database schema ā CREATE TABLE ā¢ Define a relation created in the context of somedatabase created with CREATE SCHEMA
ā¢^ Other DDL commands^ ā^ CREATE VIEW^ ā^ CREATE DOMAIN
ā¢ Not in mySQL.
CREATE
TABLE
CREATE
TABLE
<schema_name>.<table_name>( col1_name
col1_type, col2_name
col2_type, ...colM_name
colM_type, constraint1,...,constraintN
example with no constraints: CREATE
TABLE
dept_locations
dnumber
integer(4), dlocation
varchar(15)
if schema name isomitted, table createdin the default DBstatement ends withsemi-colon
Adding Constraints in
CREATE
TABLE
CREATE
TABLE
DEPT
DNAME
VARCHAR(10)
NOT^
NULL,
DNUMBER
INTEGER
NOT^ NULL,
MGRSSN
CHAR(9),
MGRSTARTDATE
CHAR(9),
PRIMARY
KEY^
(DNUMBER),
UNIQUE
(DNAME),
FOREIGN
KEY^
(MGRSSN)
REFERENCES
EMP^
other options:
DEFAULT,
CHECK,
UNIQUE
Dnumber
INT^
CHECK(Dnumber
>^0
AND^ Dnumber
<^ 21)
CREATE^
TABLE^ product
(category
INT^ NOT
NULL,^
id^ INT^
NOT^ NULL,
price^ DECIMAL,PRIMARY
KEY(category,
id))^ ENGINE=INNODB;
CREATE^
TABLE^ customer
(id^ INT
NOT^ NULL,PRIMARY^ KEY^ (id))
ENGINE=INNODB;
CREATE^
TABLE^ product_order
(no INT
NOT^ NULL
AUTO_INCREMENT, product_category
INT^ NOT
NULL,
product_id
INT^ NOT
NULL, customer_id
INT^ NOT
NULL,
PRIMARY
KEY(no), INDEX^ (product_category,
product_id),
FOREIGN
KEY^ (product_category,
product_id)
REFERENCES
product(category,
id)
ON^ UPDATE
CASCADE
ON^ DELETE
RESTRICT,
INDEX^ (customer_id),FOREIGN
KEY^ (customer_id)REFERENCES^ customer(id))
ENGINE=INNODB;
Referential Triggered Action(from mySQL āhelp constraintā)
defines āstorage engineā
Basic Data Types and Domains
ā¢^ Numericā INT, FLOAT, DECIMAL(precision,scale)ā¢^ Stringā char(n), varchar(n)ā¢^ Booleanā¢^ Dateā¢^ Timestampā¢^ BLOB (binary large object)ā tinyblob, mediumblob, largeblobā¢^ Enumā enum(āredā,āblueā,āgreenā);ā¢^ setā set( āredā, āblueā, āgreenā);
Retrieval Queries in SQL
ā¢^ SQL has one basic statement for retrieving informationfrom a database; the
SELECT
statement
not the same as
the SELECT operation of the relational
algebraā¢ Important distinction between SQL and the formalrelational model:ā SQL allows a table (relation) to have two or more tuples that areidentical in all their attribute valuesā Hence, an SQL relation (table) is a
multi-set
(sometimes called
a^ bag ) of tuples; it is
not^ a set of tuples
ā¢^ SQL relations can be constrained to be sets byspecifying PRIMARY KEY or UNIQUE attributes, or byusing the DISTINCT option in a query
Retrieval Queries in SQL (contd.)
ā¢^ A^ bag
or^ multi-set
is like a set, but an element
may appear more than once.ā Example: {A, B, C, A} is a bag. {A, B, C} is also a bagthat also is a set.ā Bags also resemble lists, but the order is irrelevant ina bag.ā¢ Example:ā {A, B, A} = {B, A, A} as bagsā However, [A, B, A] is not equal to [B, A, A] as lists
- Relational Database Schema--Figure 5.
