Lecture 9: Users, E
Download
Report
Transcript Lecture 9: Users, E
Lecture 9: Users, E-R
Diagrams, Advanced
Topics
TARIK BOOKER
CALIFORNIA STATE UNIVERSITY, LOS ANGELES
NOVEMBER 25, 2014
What we will cover…
User Information
E-R Diagrams
Advanced Topics
Users
mysql –u root
How we start up MySQL client.
What does it mean?
mysql
(client program)
-u
user
root
type of user
Super-user or Administrator
Can do everything with this account (full permissions)
Root is fine for us…
Users in a Professional Structure
In real life, very few people get root access
Many people may connect to your database
Command-line users
Applications
(Software, Websites, Phones)
You should restrict access to certain users
To prevent deletion
Improper (illegal) modification
Examples? (PayPal)
Creating a User Account
Let’s create a user account!
Must be logged in with an account that allows (grants permission to) you to create
users
Syntax:
CREATE USER ‘user’@’hostname’ IDENTIFIED BY ‘password’
CREATE USER ‘tarik’@localhost’ IDENTIFIED BY ‘hello122pass’
Localhost is your own (local) computer
Try this. Create your own user and log in
>mysql –u tarik –p
Prompt for password
If you don’t produce a password, you will not get any permissions
GRANT
If you log in with your new user, you won’t have access to any databases!
You must give the user the right to use a database!
You can give access to all databases, but it’s more common to give users
access to specific databases
CREATE USER ‘paddington’@localhost’ IDENTIFIED BY ‘hello122pass’
Let’s give user “paddington” access to the Lyric Database:
GRANT USAGE on lyric.* TO ‘paddington’@’localhost’;
Allow “paddington to use the SELECT query on Lyric:
GRANT SELECT ON lyric.* TO ‘paddington’@’localhost’;
GRANT (2)
>help grant;
Type at the prompt (in MySQL client) to get more info
If Paddington tries to do something that he has no permission to…
REVOKE
REVOKE removes permissions from previously granted users
REVOKE ALL ON *.* FROM ‘paddington’@’localhost’;
Removing a User
DROP USER
DROP USER ‘paddington’@’localhost’;
Removes the user and removes all permissions
Query Tools (Not Required)
Typically, SQL, DML, and DDL are generated, rather than specifically
written.
Other (Java, C++, etc.) Language creates the appropriate query
Let’s look at program generated SQL
Don’t worry if you don’t know Java
This is not required (won’t be on test)
Keep in mind: Objects and Data Structures are NOT LIKE TABLES in
relational databases!
Query Tools (2)
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 (from John Hurley):
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 (3)
Database access code from a Java Web app (CSNS). This uses Hibernate, which helps to map relational DBs to objects
(again, from John Hurley):
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 );
}
Advanced Topics
Indices
Stored Functions
Stored Procedures
Triggers
Indices
If you want to optimize your database, add an index
Indices stored in an optimized data structure
Trees, Hashes
Allows for fast searching
Automatically created on all primary and foreign keys with InnoDB storage
engine
Part of database schema
Syntax:
CREATE INDEX idx_Accounts
ON Accounts(Username);
Stored Functions
DELIMITER //
DROP FUNCTION IF EXISTS ComputeAge//
CREATE FUNCTION ComputeAge(bdate DATE)
RETURNS INT
Create your own functions!
BEGIN
Become part of Database schema
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
Note: DELIMITER temporarily changes the statement delimiter (terminating
RETURN ydiff;
character) to something other than semicolon.
We then change it back
END IF;
when we’re done!
IF DAY(cdate) < DAY(bdate) THEN RETURN
ydiff - 1;
END IF;
RETURN ydiff;
Stored Functions (2)
Usage:
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’;
Stored Procedures
You can automate (or standardize) use of the database, modify security
policies, etc.
DELIMITER //
Good for code reuse
DROP PROCEDURE IF EXISTS
CreateAccount//
Also can protect code from breaking
CREATE PROCEDURE CreateAccount(IN
Due to internal database changes
user VARCHAR(32),
IN pass VARCHAR(32),
IN name VARCHAR(32))
BEGIN
INSERT INTO Accounts VALUES(0, user,
pass, name, NULL);
END//
DELIMITER ;
Triggers
You can create SQL objects called when you INSERT, UPDATE, DELETE
information from a table
DELIMITER //
Become part of database schema
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
Making a database is not easy (to do right)!
A database is considered software
Software Engineering techniques required for Complicated Databases
Requirements
Design
Coding
Testing
Each is a phase in development
Database Design (2)
Requirements Phase
List all data the database MUST store
Design Phase
Analyze requirements and create an Entity-Relationship (ER) Diagram
Coding Phase
Typically a full document
Convert E-R Diagram into a Database Schema
Testing Phase
Run Database on a DBMS
Test, test, test…
Database Design (3)
We will discuss E-R Diagrams only peripherally
This is the beginning of CS422
Important Goals in Database Design
Important Goals:
Minimize data redundancy
Minimize data anomalies
Update
Insertion
Inappropriate dependencies prevent insertion of necessary data
Deletion
Redundant data not consistently updated in all places
Inappropriate dependencies prevent or force inappropriate deletions
Look at next example:
Database Design Example
Look at this table (Insertion):
StudentNum(PK) CourseNum
Student Name
Course
1
9201
Richards
CS120
2
9267
Watts
CS122
3
9267
Wyman
CS122
4
9201
Jagger
CS120
We would not be able to add a course that didn’t have any students
enrolled!
Database Design Example (2)
Deletion (example)
If members contained ArtistID as a NOT NULL field, we would have to delete any
members whose artists broke up!
Why?
How do we fix these problems?
Normalization, talked about in CS422
The Entity-Relationship Model
A way to formally describe our database relationships
Entities
Nouns, Distinct Physical Objects
A Person is an entity; people entity sets
Relationships
Verbs; Relationships that link several entities
A person acts in a movie
Attributes
Descriptive properties of entity and relationship sets
The Entity-Relationship Model (2)
We typically draw these models:
The Entity-Relationship Model (3)
Note: Underlined Attributes are Primary Keys!
Relationship Cardinalities
Cardinality - principle
One to one
One to one both ways (e.g. a person has a weight)
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 (date of death <-> person)
Many to many
One to many going both ways
Mapping Cardinalities
E-R Diagram Example
E-R 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
E-R Diagram Notation
More Entity Sets
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
E-R Diagram Notation
•
Relationship Sets
–
General
•
–
Weak Entity
•
–
Diamond shape with solid line surrounding set
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
E-R Diagram Notation
Attributes
Primary Keys
Discriminators
Underlined attribute names
underlined with dashes
Calculated attributes
dashed ovals
E-R Diagram Example
Converting an E-R Diagram to a
Database
As discussed earlier, we can convert an E-R Diagram to a Database
Create Database rules based on E-R Model Relationships
Look at cardinalities and analyze
Converting an E-R Diagram to a
Database
One-to-one Relationships Between Entity Sets (Two Options):
Option One: Merge one entity set’s attributes into the attributes of another
creating only one database table. Relationship set does not become a table
Ex: Products “have” an Inventory
Make this into one table, with an Inventory attribute
Option Two: Make each entity set a separate table. Relationship set does not
become a table
Ex: Products “have” an Inventory
Make two tables: Products and Inventory
This is actually better that option one (explained in CS 422)
Converting an E-R Diagram to a
Database
One-to-many Relationships Between Entity Sets
Use two tables, unless relationship is a self-relationship (like Employee DB).
Relationship set does not become a table
Ex: Artists “have” titles
Make two tables: Artists and Titles
Converting an E-R Diagram to a
Database
Many-to-many Relationships Between Entity Sets
Use three tables, unless relationship is a self-relationship. Relationship becomes
a table.
Often called a cross-reference table
Ex: People “act” in Movies
Three tables: People, Movies, XrefActorsMovies
Xref table called an associative (weak) entity
Converting an E-R Diagram to a
Database
Review:
One-to-one
One-to-Many / Many-to-one
Two tables
Many-to-Many
One or two tables, depending on form
Three tables
Most of this not required in this class, just know basics