Transcript Lecture 10 - California State University, Los Angeles

Using Relational Databases and SQL
Lecture 10:
Department of Computer Science
California State University, Los Angeles
Evaluations
Users
• So far, we have always started the MySQL client as
the “root” user.
• “Root” is a administrative or super-user who has
rights to do anything at all with the DBMS.
• In a production system, neither command line users
nor applications normally receive such rights. They
use accounts with more limited rights
CREATE USER
• Creates a user account
• In order to run this, you must be logged in with an
account that has permission to create users.
• Syntax:
• CREATE USER 'godzilla'@'localhost' IDENTIFIED BY
'ieattokyo';
• Instead of starting the client with mysql –u root,
godzilla starts it with mysql –u john –p
• He is then asked for his password
• Can enter the client without the password, but then does
not get any permissions.
GRANT
• Gives particular rights to a given user
• It is possible to grant privileges on all databases to a
particular user, but it is more common to grant users
permissions for specific databases.
• Let godzilla use the lyric database:
• GRANT USAGE on lyric.* TO 'godzilla'@'localhost';
• Let godzilla run select queries on the lyric database:
• GRANT SELECT ON lyric.* TO 'godzilla'@'localhost';
• Type “help grant” in MySQL to see other available
permissions
GRANT
• Here is what happens when the user tried to do
something he does not have permission to do:
mysql> DELETE FROM members;
ERROR 1142 (42000): DELETE command denied to user
'john'@'localhost' for table 'members'
REVOKE
• Takes away permissions previously granted
• REVOKE ALL ON *.* FROM 'john'@'localhost';
Query Tools
• Application programmers often use various tools to
generate SQL/DML/DDL, rather than writing it
directly
• But you have to understand the languages to be able to
use the automated tools
• If you don’t have application programming experience,
you won’t understand the next couple of slides. That’s
OK.
• Key issue: objects and data structures in OO apps
are very different from tables in relational
databases!
Query Tools
• Typical database access code from an Android app.
Generate a query, run it, and use the data from the
result to create an object of class Item:
String matchString = "\'" + id.toString() + "\'";
Cursor cursor = database.query(TABLE_NAME, allColumns, "_id = "
+ matchString, null, null, null, null);
cursor.moveToFirst();
Item item = cursorToItem(cursor);
Query Tools
• Typical database access code from a Java web app
(namely CSNS). This uses Hibernate, which helps to
map relational DBs to objects:
public File getTopLevelFolder( User user, String name )
{
String query = "from File where owner = ? and parent is null "
+ "and folder = true and name = ?";
Object params[] = { user, name };
List results = getHibernateTemplate().find( query, params );
return results.size() == 0 ? null : (File) results.get( 0 );
}
Adv. Topics – Indices
With an indexed attribute, values are stored in an
optimized data structure such as a tree or a hash
table for fast searching
Indices are automatic on all primary and foreign
keys in InnoDB storage engine
Indices are part of the database schema
CREATE INDEX idx_Accounts
ON Accounts(Username);
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 ;
DELIMITER temporarily sets statement delimiter to something other than
the semicolon, so that we can use semicolons in the function without
running each line when we write the function. Note that we set it back
to ; at the end!
Adv. Topics – Stored Functions
Examples:
SELECT ComputeAge("1965-11-12");
SELECT ComputeAge(m.birthday)
from members m;
SELECT ComputeAge(m.birthday)
from members m where LastName =
“Sanders” and FirstName =
“Bryce”;
Adv. Topics – Stored Procedures
Allows specialist DB manager to standardize use
of the DB, implement security policies, etc.
Allows more code reuse
Protect code from breaking due to internal DB
changes
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 ;
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
Two Important Goals
• Minimize data redundancy
• Minimize data anomalies
• update : redundant data is not consistently updated in all places
• insertion : inappropriate dependencies prevent insertion of necessary
data. In this table, we would not be able to add a course which did not
yet have any students enrolled:
StudentNum(PK)
1
2
3
4
CourseNum Student Name
9201
Richards
9267
Watts
9267
Wyman
9201
Jagger
Course
CS120
CS122
CS122
CS120
• deletion : inappropriate dependencies prevent or force
inappropriate deletions
• if members contained artistID as a not null field, we would have to delete any
members whose artists broke up
• How?
– Normalization; one of the key topics of CS 422
The Entity-Relationship Model
• Entities
– Nouns, distinct physical objects
– A person is an entity; people are an entity set
• Relationships
– Verbs, relationships that link several entities
– A person acts in movies
• Attributes
– Descriptive properties of entity and relationship sets
The Entity-Relationship Model
The Entity-Relationship Model
underlined attribute = primary key
Relationship 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
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: Members; if you delete everything else in the
database, Members is still meaningful
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 contains a foreign key
Examples: Dependents in an employee database
Example: Delete the employee, all dependents 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
– Mapping Cardinalities
• Many different graphical choices
• Most common is to place numbers or variables near the
entities
ER Diagram Notation
Attributes
Primary Keys
Underlined attribute names
Discriminators
underlined with dashes
Calculated attributes
dashed ovals
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.
Artists “have” Titles
Two tables: Artists and Titles
ER Diagram to Database
• Many-to-Many Relationships Between Entity Sets
– Use three tables, unless relationship is a selfrelationship. Relationship becomes a table.
•
•
•
•
Relationship table oftentimes called a cross-reference table
People “act in” Movies
Three tables: People, Movies, and XRefActorsMovies
Xref table 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
Final Exam
Will Cover
basic facts about databases, RDBMS, and SQL
multitable selects with joins and subqueries
string and aggregate functions
DDL and DML
Format will be similar to the midterm
Last lab will be an (ungraded) practice final
What was this class good for?
If you are an undergraduate, you might not use
this material in your programming assignments
until late in the program, but in the wild almost
every application uses a DB.
If you are a graduate student, you’ll use this
material soon.
When you eventually work as a software
engineer, you will certainly need to know this
material.
Next Steps
Next course in DB sequence is 422, Principles of
Database Systems
Very challenging but highly recommended!
Requires CS312 as well as 122. May also require
some or all of the Java sequence
Focuses on how to design DBs to efficiently represent
real-world problems
Most of you will work as application programmers,
but you will still need to do some DB design at some
point
In-Class Problem
Write DDL and DML commands to do the tasks
shown in the next slide
There will be a problem similar to this on the
final!
The tasks are sequential, and the first one is
necessarily the most difficult. It is important to
do these carefully and in order, checking each
step to make sure it is right. If you mess up one
step, everything after it will probably be wrong.
In-Class Problem
Set up a table to track information on vehicles. Include the following
fields: make (eg Mazda), model (eg Miata), license plate number, state
of registration. Also include an autoincrement field and make it the
primary key.
Enter information on three real or fictional vehicles. Use the following
for one of the license plate numbers: 1ABC123
Change the state of registration to MI for the vehicle with plate number
1ABC123
Alter the table to include an additional field with the color of the vehicle
Set the color of the vehicle with plate 1ABC123 to ‘maroon’
Add a constraint that requires the plate number to be unique
In-Class Problem
1) define the table:
CREATE TABLE vehicles(
vehicleId int primary key auto_increment,
make varchar(20),
model varchar(20),
plateNum varchar(12),
state char(2)
);
SELECT * from vehicles
In-Class Problem
2) insert data:
INSERT INTO vehicles VALUES(0, 'Mazda', 'Miata',
'1ABC123', 'CA');
INSERT INTO vehicles VALUES(0, 'Mercury', 'Grand
Marquis', '2BCD234', 'NJ');
INSERT INTO vehicles VALUES(0, 'Ford', 'Pinto', '3CDE345',
'OH');
SELECT * FROM vehicles;
In-Class Problem
3) Change the state of registration to MI for the vehicle with
plate number 1ABC123
UPDATE vehicles SET state = 'MI' WHERE plateNum =
'1ABC123';
SELECT * FROM vehicles
In-Class Problem
4) Alter the table to include an additional field with
the color of the vehicle
ALTER TABLE vehicles ADD COLUMN color varchar(20);
SELECT * FROM vehicles;
In-Class Problem
5) Set the color of the vehicle with plate 1ABC123 to
‘maroon’
UPDATE vehicles SET color = 'maroon' WHERE plateNum
= '1ABC123';
SELECT * FROM vehicles;
In-Class Problem
6) Add a constraint that requires the plate number to
be unique
ALTER TABLE vehicles ADD CONSTRAINT
uniqueplate UNIQUE(plateNum)