INF 510辅导、讲解SQL、辅导SQL编程语言、讲解Systems Engineering 解析Haskell程序|辅导Python程序
- 首页 >> 其他 INF 510
Principles of Programming for Informatics
Senior Director for Administrative Affairs
USC/Information Sciences Institute
Research Professor
Department of Industrial & Systems Engineering Acknowledgments
Many of the slides l use were created by Dr. Chuck Severance,
the author of our book.
You’ll recognize them by their black backgrounds.
The slides, and more material can be found on his
website.
They are Copyright 2010–2017 Charles R. Severance and
are made available under a Creative Commons Attribution
4.0 License.
Some of the slides I use were created by Dr. Jeremy Abramson
of USC/ISI. He simply gave them to me to use in this class.
Many of the slides I use were created by me.
I hope you’re thinking about what you might
do with your data!
You’ll use the data sources you specify/
specified in HW 4
HW 5 will be due in 2 weeks
Will need to access the data in the
sources you identified
These are part of the “Project” grade
3Make-Up Lab Is Next Week
4Relational Databases
and SQL, Continued
And We Have Three Videos TodayAlbum
Naming FK artist_id is a
convention
Remember: Don’t Replicate Data, Use IDs & Foreign Keys!Using Join Across Tables
http://en.wikipedia.org/wiki/Join_(SQL)Relational Power
By removing the replicated data and replacing it with references to
a single copy of each bit of data we build a “web” of information
that the relational database can read through very quickly - even
for very large amounts of data
Often when you want some data it comes from a number of tables
linked by these foreign keysThe JOIN Operation
The JOIN operation links across several tables as part of a select
operation
You must tell the JOIN how to use the keys that make the
connection between the tables using an ON clauseselect Album.title, Artist.name from Album join Artist on Album.artist_id = Artist.id
What we want
to see
The tables that
hold the data
How the tables
are linked
Artist
Albumselect Album.title, Album.artist_id, Artist.id,Artist.name
from Album join Artist on Album.artist_id = Artist.idSELECT Track.title,
Track.genre_id,
Genre.id, Genre.name
FROM Track JOIN Genre
Joining two tables without an
ON clause gives all possible
combinations of rows.select Track.title, Genre.name from Track join Genre on Track.genre_id = Genre.id
What we want
to see
The tables that
hold the data
How the tables
are linkedselect Track.title, Artist.name, Album.title, Genre.name
from Track join Genre join Album join Artist on
Track.genre_id = Genre.id and Track.album_id =
Album.id and Album.artist_id = Artist.id
What we want to see
The tables which hold
the data
How the tables are
linkedMany-To-Many Relationships
https://en.wikipedia.org/wiki/Many-to-many_(data_model)Album
belongs-to
Review:
One to Many
One ManyMany
One One Many
https://en.wikipedia.org/wiki/One-to-many_(data_model)Many to Many
Sometimes we need to model a
relationship that is many-to-many
We need to add a "connection"
table with two foreign keys
There is usually no separate
Database
CREATE TABLE User (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
name TEXT UNIQUE,
email TEXT
)
CREATE TABLE Course (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
title TEXT UNIQUE
)
CREATE TABLE Member (
user_id INTEGER,
course_id INTEGER,
role INTEGER,
PRIMARY KEY (user_id, course_id)
)
Student (=0) or Teacher (=1)Insert Users and Courses
INSERT INTO User (name, email) VALUES ('Jane', 'jane@tsugi.org');
INSERT INTO User (name, email) VALUES ('Ed', 'ed@tsugi.org');
INSERT INTO User (name, email) VALUES ('Sue', 'sue@tsugi.org');
INSERT INTO Course (title) VALUES ('Python');
INSERT INTO Course (title) VALUES ('SQL');
INSERT INTO Course (title) VALUES ('PHP');INSERT INTO Member (user_id, course_id, role) VALUES (1, 1, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 1, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 1, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (1, 2, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 2, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 3, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 3, 0);SELECT User.name, Member.role, Course.title
FROM User JOIN Member JOIN Course
ON Member.user_id = User.id AND
Member.course_id = Course.id
ORDER BY Course.title, Member.role DESC, User.name Complexity Enables Speed
? Complexity makes speed possible and allows you to get very fast
results as the data size grows
? By normalizing the data and linking it with integer keys, the overall
amount of data which the relational database must scan is far
lower than if the data were simply flattened out
It might seem like a tradeoff - spend some time designing your
database so it continues to be fast when your application is a
successAdditional SQL Topics to Read About
Indexes improve access performance for things like string fields
Constraints on data - (cannot be NULL, etc..)
Transactions - allow SQL operations to be grouped and done as a
unitSummary
Relational databases allow us to scale to very large amounts of
data
The key is to have one copy of any data element and use relations
and joins to link the data to multiple places
This greatly reduces the amount of data which much be scanned
when doing complex operations across large amounts of data
Database and SQL design is a bit of an art formAcknowledgements / Contributions
These slides are Copyright 2010- Charles R. Severance (www.drchuck.com)
of the University of Michigan School of Information and
open.umich.edu and made available under a Creative Commons
Attribution 4.0 License. Please maintain this last slide in all copies of
the document to comply with the attribution requirements of the license.
If you make a change, feel free to add your name and organization to
the list of contributors on this page as you republish the materials.
Initial Development: Charles Severance, University of Michigan School
of Information
...
Principles of Programming for Informatics
Senior Director for Administrative Affairs
USC/Information Sciences Institute
Research Professor
Department of Industrial & Systems Engineering Acknowledgments
Many of the slides l use were created by Dr. Chuck Severance,
the author of our book.
You’ll recognize them by their black backgrounds.
The slides, and more material can be found on his
website.
They are Copyright 2010–2017 Charles R. Severance and
are made available under a Creative Commons Attribution
4.0 License.
Some of the slides I use were created by Dr. Jeremy Abramson
of USC/ISI. He simply gave them to me to use in this class.
Many of the slides I use were created by me.
I hope you’re thinking about what you might
do with your data!
You’ll use the data sources you specify/
specified in HW 4
HW 5 will be due in 2 weeks
Will need to access the data in the
sources you identified
These are part of the “Project” grade
3Make-Up Lab Is Next Week
4Relational Databases
and SQL, Continued
And We Have Three Videos TodayAlbum
Naming FK artist_id is a
convention
Remember: Don’t Replicate Data, Use IDs & Foreign Keys!Using Join Across Tables
http://en.wikipedia.org/wiki/Join_(SQL)Relational Power
By removing the replicated data and replacing it with references to
a single copy of each bit of data we build a “web” of information
that the relational database can read through very quickly - even
for very large amounts of data
Often when you want some data it comes from a number of tables
linked by these foreign keysThe JOIN Operation
The JOIN operation links across several tables as part of a select
operation
You must tell the JOIN how to use the keys that make the
connection between the tables using an ON clauseselect Album.title, Artist.name from Album join Artist on Album.artist_id = Artist.id
What we want
to see
The tables that
hold the data
How the tables
are linked
Artist
Albumselect Album.title, Album.artist_id, Artist.id,Artist.name
from Album join Artist on Album.artist_id = Artist.idSELECT Track.title,
Track.genre_id,
Genre.id, Genre.name
FROM Track JOIN Genre
Joining two tables without an
ON clause gives all possible
combinations of rows.select Track.title, Genre.name from Track join Genre on Track.genre_id = Genre.id
What we want
to see
The tables that
hold the data
How the tables
are linkedselect Track.title, Artist.name, Album.title, Genre.name
from Track join Genre join Album join Artist on
Track.genre_id = Genre.id and Track.album_id =
Album.id and Album.artist_id = Artist.id
What we want to see
The tables which hold
the data
How the tables are
linkedMany-To-Many Relationships
https://en.wikipedia.org/wiki/Many-to-many_(data_model)Album
belongs-to
Review:
One to Many
One ManyMany
One One Many
https://en.wikipedia.org/wiki/One-to-many_(data_model)Many to Many
Sometimes we need to model a
relationship that is many-to-many
We need to add a "connection"
table with two foreign keys
There is usually no separate
Database
CREATE TABLE User (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
name TEXT UNIQUE,
email TEXT
)
CREATE TABLE Course (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT UNIQUE,
title TEXT UNIQUE
)
CREATE TABLE Member (
user_id INTEGER,
course_id INTEGER,
role INTEGER,
PRIMARY KEY (user_id, course_id)
)
Student (=0) or Teacher (=1)Insert Users and Courses
INSERT INTO User (name, email) VALUES ('Jane', 'jane@tsugi.org');
INSERT INTO User (name, email) VALUES ('Ed', 'ed@tsugi.org');
INSERT INTO User (name, email) VALUES ('Sue', 'sue@tsugi.org');
INSERT INTO Course (title) VALUES ('Python');
INSERT INTO Course (title) VALUES ('SQL');
INSERT INTO Course (title) VALUES ('PHP');INSERT INTO Member (user_id, course_id, role) VALUES (1, 1, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 1, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 1, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (1, 2, 0);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 2, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (2, 3, 1);
INSERT INTO Member (user_id, course_id, role) VALUES (3, 3, 0);SELECT User.name, Member.role, Course.title
FROM User JOIN Member JOIN Course
ON Member.user_id = User.id AND
Member.course_id = Course.id
ORDER BY Course.title, Member.role DESC, User.name Complexity Enables Speed
? Complexity makes speed possible and allows you to get very fast
results as the data size grows
? By normalizing the data and linking it with integer keys, the overall
amount of data which the relational database must scan is far
lower than if the data were simply flattened out
It might seem like a tradeoff - spend some time designing your
database so it continues to be fast when your application is a
successAdditional SQL Topics to Read About
Indexes improve access performance for things like string fields
Constraints on data - (cannot be NULL, etc..)
Transactions - allow SQL operations to be grouped and done as a
unitSummary
Relational databases allow us to scale to very large amounts of
data
The key is to have one copy of any data element and use relations
and joins to link the data to multiple places
This greatly reduces the amount of data which much be scanned
when doing complex operations across large amounts of data
Database and SQL design is a bit of an art formAcknowledgements / Contributions
These slides are Copyright 2010- Charles R. Severance (www.drchuck.com)
of the University of Michigan School of Information and
open.umich.edu and made available under a Creative Commons
Attribution 4.0 License. Please maintain this last slide in all copies of
the document to comply with the attribution requirements of the license.
If you make a change, feel free to add your name and organization to
the list of contributors on this page as you republish the materials.
Initial Development: Charles Severance, University of Michigan School
of Information
...