Transcript Using Relational Databases and SQL

Using Relational Databases and SQL
Lecture 10:
Database Design
and
Advanced Topics
Steven Emory
Department of Computer Science
California State University, Los Angeles
Topics for Today
Final Exam
Final Exam Database
Advanced Topics (not on final)
Database Design (not on final)
Final Exam
Focus is on set functions (aggregates) and
subqueries
Problems
Functions and aggregates can be in any problem
#1 – 3 are single-table selections
#4 – 6 are joins (2 to 3 table joins)
#7 – 10 are subqueries (WHERE, HAVING, FROM
and SELECT)
Final Exam – Set Functions
Set Functions
Select what to aggregate (filter with WHERE).
Group the data (GROUP BY if necessary).
Aggregate (add your set functions).
Filter your results (filter with HAVING).
Final Exam – Subqueries
Know how to use IN, ALL and ANY
To solve a subquery problem:
Read the problem
If it is too hard to solve with just one query, break the
problem apart and try to solve what you can with
single queries
Now that you have the parts, using those parts, try to
solve the original problem
Advanced Topics
Good Books
Stored Functions
Stored Procedures
Triggers
Views
Indexes
Adv. Topics – Good Books
High Performance MySQL (2008) – Schwartz
Query optimization, security, and advanced SQL
features that we didn't have time to cover here in
class.
Expert MySQL (2008) – Bell
Very heavy on C++, focusing on extending MySQL
through rebuilding the database server.
Adv. Topics – Stored Functions
Create your own custom functions
Stored functions are part of database schema
DELIMITER //
DROP FUNCTION IF EXISTS ComputeAge//
CREATE FUNCTION ComputeAge(bdate DATE)
RETURNS INT
BEGIN
DECLARE cdate DATE;
DECLARE ydiff INT;
SET cdate = CURDATE();
SET ydiff = YEAR(cdate) - YEAR(bdate);
IF MONTH(cdate) < MONTH(bdate) THEN RETURN ydiff - 1;
END IF;
IF MONTH(cdate) > MONTH(bdate) THEN RETURN ydiff;
END IF;
IF DAY(cdate) < DAY(bdate) THEN RETURN ydiff - 1;
END IF;
RETURN ydiff;
END//
DELIMITER ;
Adv. Topics – Stored Procedures
Like a stored function, but does not return a value
Typically used to execute many DML commands
DELIMITER //
DROP PROCEDURE IF EXISTS CreateAccount//
CREATE PROCEDURE CreateAccount(IN user VARCHAR(32),
IN pass VARCHAR(32),
IN name VARCHAR(32))
BEGIN
INSERT INTO Accounts VALUES(0, user, pass, name, NULL);
END//
DELIMITER ;
Adv. Topics – Triggers
Triggers are SQL objects that are called when you
INSERT, UPDATE, or DELETE records from a
table
Triggers are part of the database schema
DELIMITER //
CREATE TRIGGER trg_AccountDelete
BEFORE DELETE ON Accounts
FOR EACH ROW
BEGIN
INSERT INTO AccountHistory
VALUES(OLD.UserID, OLD.Username, OLD.Password, OLD.FirstName,
OLD.LastLogin, CURDATE());
END//
DELIMITER ;
Adv. Topics – Views
Views are stored queries
Views are part of the database schema
Use views as though they were actual tables
Adv. Topics – Views
Example:
-- create a spouses view
CREATE VIEW vw_spouses AS
SELECT
H.PersonID AS H_ID, H.FirstName AS H_FirstName, H.MiddleName AS H_MiddleName,
H.LastName AS H_LastName, H.BirthDate AS H_BirthDate, H.BirthCity AS H_BirthCity,
H.BirthStateProvince AS H_BirthStateProvince, H.BirthCountry AS H_BirthCountry,
H.Gender AS H_Gender, H.Height AS H_Height,
W.PersonID AS W_ID, W.FirstName AS W_FirstName, W.MiddleName AS W_MiddleName,
W.LastName AS W_LastName, W.BirthDate AS W_BirthDate, W.BirthCity AS W_BirthCity,
W.BirthStateProvince AS W_BirthStateProvince, W.BirthCountry AS W_BirthCountry,
W.Gender AS W_Gender, W.Height AS W_Height,
S.StartDate, S.EndDate, S.Status, S.Children
FROM Spouses S
INNER JOIN People H ON H.PersonID = S.HusbandID
INNER JOIN People W ON W.PersonID = S.WifeID;
-- now select the view
SELECT *
FROM vw_spouses;
Adv. Topics – Indexes
With an indexed attribute, all values are stored in
some type optimized data structure such as a B–
tree or a hash table for fast searching
Indexes are automatic on all primary and foreign
keys in InnoDB storage engine
Indexes are part of the database schema
CREATE INDEX idx_Accounts
ON Accounts(Username);
Database Design
A database is software
Creating a database is no trivial task
When developing a complicated database, we
oftentimes follow the rules of software
engineering
Requirements
Design
Coding
Testing
Database Design
In the requirements phase, we list all the data that
our database MUST store
In the design phase, we typically analyze the
requirements and then create a diagram called an
Entity-Relationship (ER) Diagram
In the coding phase, the entity-relationship
diagram is converted into a database schema
In the testing phase, the database schema is run
on a DBMS and the database is tested
Database Design
The movie archive database is so simple, we
really don’t need to think much about these
software engineering issues
But when the data requirements become large (for
companies such as the real IMDB, amazon.com,
airlines, and other large organizations), a
thorough, documented analysis of our engineering
methods is required
In other words: Planning is everything!
Two Important Goals
Minimize data redundancy
Minimize data anomalies
How?
Take CS 422 to learn about normalization
The Entity-Relationship Model
Entity Sets
Nouns, distinct physical objects
A person is an entity; people are an entity set
Relationship Sets
Verbs, relationships that link several entities
A person acts in movies
A set of people who act in movies is a relationship set
Attributes
Descriptive properties of entity and relationship sets
Entity Sets and Attributes
What becomes an entity?
What becomes an attribute?
How do we tell?
Movie and MPAA are nouns
Is MPAA an attribute or an entity set?
No easy way to tell (CS 422)
MPAA can be an entity set if we are concerned about data
integrity (like the Genres table)
Mapping Cardinalities
One-to-One = one-to-one both ways
One-to-Many = one-to-many going left-to-right,
one-to-one going right to left
Many-to-One = one-to-one going left-to-right,
one-to-many going right to left
Many-to-Many = one-to-many going both ways
Mapping Cardinalities
The Entity-Relationship Model
The Entity-Relationship Model
ER Diagram Notation
Entity Sets
Strong Entity
Box, with a single solid line surrounding entity set
Means entity is independent of other entities
Strong entities have an independent primary key (unlike
weak entities which have one or more foreign keys as a part
of their primary key)
Examples: Companies, if you delete everything in the
database but the Companies table, the Companies table still
has a physical meaning
ER Diagram Notation
Entity Sets (cont.)
Weak Entity
Box, with a double solid line surrounding entity set
Means entity is dependent on a strong entity to exist
Weak entity primary key consists of a foreign key and a
discriminator
Examples: Taglines
Example: Delete a movie, all taglines have to go with it too
ER Diagram Notation
Relationship Sets
General
Diamond shape with solid line surrounding set
Weak Entity
Diamond shape with double solid lines surrounding set
Weak entity primary key is foreign key + discriminator
Mapping Cardinalities
Many different graphical choices
I prefer using 1 and m near the entity sets
Other options are Crow’s feet notation, UML notation, etc.
ER Diagram Notation
Attributes
Primary Keys
Underlined attribute names
Discriminators
Discriminating attribute names are underlined with dashes
Entity-Relationship Diagrams
When we draw all our entity sets, relationship
sets, attributes and mapping cardinalities in one
diagram, we have an entity-relationship diagram
ER Diagram for short
ER Diagrams are then translated into a database
schema in the coding phase
ER Diagram to Database
One-to-One Relationships Between Entity Sets
Option #1: Merge one entity set’s attributes into
attributes of the other, creating only one database
table. Relationship set does not become a table.
Products “have an” Inventory
One table: Products, with an inventory attribute
Option #2: Make each entity set a separate table.
Relationship set does not become a table.
Products “have an” Inventory
Two tables: Products, Inventory
This (3NF) is a better choice than option #1 (not 3NF)
ER Diagram to Database
One-to-Many Relationships Between Entity Sets
Use two tables, unless relationship is a selfrelationship (employee-manager). Relationship set
does not become a table.
Movies “have” Taglines
Two tables: Movies and Taglines
ER Diagram to Database
Many-to-Many Relationships Between Entity Sets
Use three tables, unless relationship is a selfrelationship (person-married-person). Relationship
becomes a table.
Relationship table oftentimes called a cross-reference table
People “act in” Movies
Three tables: People, Movies, and XRefActorsMovies
You can give the relationship table any name you want, but
Actors is kind of a misleading name
Also called an associative (weak) entity
ER Diagram to Database
Summary
One-to-One
One or two tables, depending on normal forms
One-to-Many/Many-to-One
Two tables
Many-to-Many
Three tables