INF 510代写、代做SQL、代写SQL编程语言、代做Systems Engineering

日期：2019-04-05 10:25

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

3

Make-Up Lab Is Next Week

4

Relational Databases

and SQL, Continued

And We Have Three Videos Today

Album

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 keys

The 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 clause

select 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

Album

select Album.title, Album.artist_id, Artist.id,Artist.name

from Album join Artist on Album.artist_id = Artist.id

SELECT 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 linked

select 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

linked

Many-To-Many Relationships

https://en.wikipedia.org/wiki/Many-to-many_(data_model)

Album

belongs-to

Review:

One to Many

One Many

Many

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

success

Additional 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

unit

Summary

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 form

Acknowledgements / 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

...