1 relational databases. 2 find databases here… 3 and here…
TRANSCRIPT
![Page 1: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/1.jpg)
1
Relational Databases
![Page 2: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/2.jpg)
2
Find Databases here…
![Page 3: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/3.jpg)
3
And here…
![Page 4: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/4.jpg)
4
The “Deep Web”
• Dynamic pages, generated from databases• Not easily discovered using crawling • Perhaps 400-500 times larger than surface
Web• Fastest growing source of new information
![Page 5: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/5.jpg)
5
Deep Web• 60 Deep Sites Exceed Surface Web by 40 Times
NameType URL
Web Size
(GBs)
National Climatic Data Center (NOAA) Public http://www.ncdc.noaa.gov/ol/satellite/satelliteresources.html
366,000
NASA EOSDIS Public http://harp.gsfc.nasa.gov/~imswww/pub/imswelcome/plain.html
219,600
National Oceanographic (combined with Geophysical) Data Center (NOAA)
Public/Fee http://www.nodc.noaa.gov/, http://www.ngdc.noaa.gov/
32,940
Alexa Public (partial)
http://www.alexa.com/ 15,860
Right-to-Know Network (RTK Net) Public http://www.rtk.net/ 14,640
MP3.com Public http://www.mp3.com/
![Page 6: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/6.jpg)
6
Content of the Deep Web
![Page 7: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/7.jpg)
7
Database Basics
• What is a database?– Collection of data, organized to support access– Models some aspects of reality
• Components of a relational database:– Field = an “atomic” unit of data– Record = a collection of related fields– Table = a collection of related records
• Each record is one row in the table• Each field is one column in the table
– Primary Key = the field that uniquely identifies a record
– Database = a collection of tables
![Page 8: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/8.jpg)
8
Why “Relational”?
• Databases model some aspects of reality
• A relational database views the world in terms of entities and relations between them
![Page 9: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/9.jpg)
9
The Registrar Example
• What do we need to know (i.e., model)?– Something about the students (e.g.,
first name, last name, email, department)
– Something about the courses (e.g., course ID, description, enrolled students, grades)
– Which students are in which courses
![Page 10: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/10.jpg)
10
A First TryPut everything in a big table…
Discussion: Why is this a bad idea?
Student ID Last Name First Name Dept ID Dept Course ID Course name Grade email
1 Arrows John EE EE lbsc690 Information Technology 90 jarrows@wam1 Arrows John EE Elec Engin ee750 Communication 95 ja_2002@yahoo
2 Peters Kathy HIST HIST lbsc690 Informatino Technology 95 kpeters2@wam2 Peters Kathy HIST history hist405 American History 80 kpeters2@wma
3 Smith Chris HIST history hist405 American History 90 smith2002@glue4 Smith John CLIS Info Sci lbsc690 Information Technology 98 js03@wam
![Page 11: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/11.jpg)
11
Good Database Design
• Save space– Save each fact only once
• More rapid updates– Every fact only needs to be updated once
• More rapid search– Finding something once is good enough
• Avoid inconsistency– Changing data once changes it everywhere
![Page 12: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/12.jpg)
12
Another Try...
Department ID DepartmentEE Electrical EngineeringHIST HistoryCLIS Information Studies
Course ID Course Namelbsc690 Information Technologyee750 Communicationhist405 American History
Student ID Course ID Grade1 lbsc690 901 ee750 952 lbsc690 952 hist405 803 hist405 904 lbsc690 98
Student ID Last Name First Name Department ID email1 Arrows John EE jarrows@wam2 Peters Kathy HIST kpeters2@wam3 Smith Chris HIST smith2002@glue4 Smith John CLIS js03@wam
Student Table
Department Table Course Table
Enrollment Table
![Page 13: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/13.jpg)
13
Approaches to Normalization
• For simple problems:– Start with “binary relationships”: pairs
of fields that are related– Group together wherever possible– Add keys where necessary
• For more complicated problems:– Entity relationship modeling (LBSC
670)
![Page 14: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/14.jpg)
14
Some Lingo
• “Primary Key” uniquely identifies a record– e.g., student ID in the student table
• “Foreign Key” is primary key in the other table– It need not be unique in this table
![Page 15: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/15.jpg)
15
The Data Model
Department ID DepartmentEE Electrical EngineeringHIST HistoryCLIS Information Studies
Course ID Course Namelbsc690 Information Technologyee750 Communicationhist405 American History
Student ID Course ID Grade1 lbsc690 901 ee750 952 lbsc690 952 hist405 803 hist405 904 lbsc690 98
Student ID Last Name First Name Department ID email1 Arrows John EE jarrows@wam2 Peters Kathy HIST kpeters2@wam3 Smith Chris HIST smith2002@glue4 Smith John CLIS js03@wam
Student Table
Department Table Course Table
Enrollment Table
![Page 16: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/16.jpg)
16
Project
SELECT Student ID, Department
Student ID Last Name First Name Dept ID Department email1 Arrows John EE Electrical Engineering jarrows@wam2 Peters Kathy HIST History kpeters2@wam3 Smith Chris HIST History smith2002@glue4 Smith John CLIS Information Stuides js03@wam
Student ID Department1 Electrical Engineering2 History3 History4 Information Stuides
![Page 17: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/17.jpg)
17
RestrictStudent ID Last Name First Name Dept ID Department email
1 Arrows John EE Electrical Engineering jarrows@wam2 Peters Kathy HIST History kpeters2@wam3 Smith Chris HIST History smith2002@glue4 Smith John CLIS Information Stuides js03@wam
Student ID Last Name First Name Department ID Department email2 Peters Kathy HIST History kpeters2@wam3 Smith Chris HIST History smith2002@glue
WHERE Department ID = “HIST”
![Page 18: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/18.jpg)
18
Join
Student ID Last Name First Name Dept ID Department email1 Arrows John EE Electrical Engineering jarrows@wam2 Peters Kathy HIST History kpeters2@wam3 Smith Chris HIST History smith2002@glue4 Smith John CLIS Information Stuides js03@wam
“Joined” Table
Student ID Last Name First Name Department ID email1 Arrows John EE jarrows@wam2 Peters Kathy HIST kpeters2@wam3 Smith Chris HIST smith2002@glue4 Smith John CLIS js03@wam
Student Table
Department TableDepartment ID DepartmentEE Electrical EngineeringHIST HistoryCLIS Information Studies
![Page 19: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/19.jpg)
19
Relational Operations
• Choosing columns: SELECT– Based on their label
• Choosing rows: WHERE– Based on their contents
• Joining tables: JOIN • These can be specified together
department ID = “HIST”
SELECT Student ID, Dept WHERE Dept = “History”
![Page 20: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/20.jpg)
20
Some SQL
• SQL = Structured Query Language• Used in many types of database
systems
![Page 21: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/21.jpg)
21
Select query
• SELECT LastName, FirstName from StudentTable
Student ID Last Name First Name Dept ID Department email1 Arrows John EE Electrical Engineering jarrows@wam2 Peters Kathy HIST History kpeters2@wam3 Smith Chris HIST History smith2002@glue4 Smith John CLIS Information Stuides js03@wam
StudentTable
![Page 22: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/22.jpg)
22
Select with Restriction
• SELECT LastName, FirstName from StudentTable where DeptID = ‘HIST’
• Will return – Peters, Kathy– Smith, Chris
Student ID Last Name First Name Dept ID Department email1 Arrows John EE Electrical Engineering jarrows@wam2 Peters Kathy HIST History kpeters2@wam3 Smith Chris HIST History smith2002@glue4 Smith John CLIS Information Stuides js03@wam
![Page 23: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/23.jpg)
23
Select with Restriction
• SELECT StudentID from EnrollmentTable where Grade > 81
Student ID Course ID Grade1 lbsc690 901 ee750 952 lbsc690 952 hist405 803 hist405 904 lbsc690 98
Enrollment Table
![Page 24: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/24.jpg)
24
Select with JoinSELECT LastName, FirstName from StudentTable JOIN EnrollmentTable on StudentTable.StudentID =EnrollmentTable.StudentID where EnrollmentTable.Grade > 95
Results:Smith, John
Student ID Course ID Grade1 lbsc690 901 ee750 952 lbsc690 952 hist405 803 hist405 904 lbsc690 98
Student ID Last Name First Name Department ID email1 Arrows John EE jarrows@wam2 Peters Kathy HIST kpeters2@wam3 Smith Chris HIST smith2002@glue4 Smith John CLIS js03@wam
Student Table
Enrollment Table
![Page 25: 1 Relational Databases. 2 Find Databases here… 3 And here…](https://reader034.vdocuments.us/reader034/viewer/2022042717/56649dcf5503460f94ac3ced/html5/thumbnails/25.jpg)
25
Discussion Point
• How is a relational database different from a spreadsheet?