database systems i admin stuff
DESCRIPTION
Database Systems I Admin Stuff. Mid-term exam Tuesday, Oct 19 @ 2:30pm Room 3005 (usual room) Closed book No cheating, blah blah No class on Oct 21 The amount of time we spent on each topic in class is a good indication of importance & likelihood of exam question. - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/1.jpg)
DATABASE SYSTEMS IADMIN STUFF
![Page 2: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/2.jpg)
2
Mid-term exam Tuesday, Oct 19 @ 2:30pm Room 3005 (usual room) Closed book No cheating, blah blah
No class on Oct 21
The amount of time we spent on each topic in class is a good indication of importance & likelihood of exam question
![Page 3: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/3.jpg)
3
Assignment 4 solution posted (with algebra)
Assignment 6 posted For procedure question, sample on next page
might help
![Page 4: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/4.jpg)
4
![Page 5: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/5.jpg)
DATABASE SYSTEMS IWEEK 6: REVIEW
![Page 6: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/6.jpg)
6
REVIEW A transaction has the following properties:
Atomicity: all-or-nothing propertyConsistency: must leave the DB in a
consistent state if DB is consistent when the transaction begins
Isolation: transaction is performed as if only one transaction at a time (serial processing)
Durability: effects of completed transactions are permanent
What is a transaction?
![Page 7: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/7.jpg)
7
ENTITY-RELATIONSHIP DIAGRAMS
An Entity-Relationship diagram (ER diagram) is a graph with nodes representing entity sets, attributes and relationship sets. Entity sets denoted by rectangles. Attributes denoted by ovals. Relationship sets denoted by diamonds. Edges (lines) connect entity sets to their attributes and
relationship sets to their entity sets.
lotdname
budgetdid
sincename
Works_In DepartmentsEmployees
ssn
![Page 8: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/8.jpg)
8
RELATIONAL DATABASE Rows are called tuples (or records), columns called
attributes (or fields). Attributes are referenced not by column number,
but by name. Order of attributes does not matter
Attribute types are called domains. Domains consist of atomic values such as integers or strings.
No structured values such as lists or sets The order of tuples does not matter, a relation is a
set of tuples. The order of tuples resulting from a relational query is undefined.
![Page 9: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/9.jpg)
9
RELATIONSHIP SETS
ER Diagram
Relational
CREATE TABLE Works_In( ssn CHAR(11), did INTEGER, since DATE, PRIMARY KEY (ssn, did), FOREIGN KEY (ssn) REFERENCES Employees, FOREIGN KEY (did) REFERENCES Departments);
SQL
![Page 10: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/10.jpg)
10
FORMAL QUERY LANGUAGES
Two mathematical query languages form the basis for “real” languages (e.g. SQL), and for implementation: Relational Algebra (RA)
More procedural, very useful for representing execution plans, relatively close to SQL.
Composed of a collection of operators A step-by-step procedure for computing the answer
Relational Calculus (RC) Lets users describe what they want, rather than how to
compute it. (Non-procedural, declarative.) Describes the answer, not the steps.
Understanding these formal query languages is important for understanding SQL and query processing.
![Page 11: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/11.jpg)
11
PRELIMINARIES A query is applied to relation instances, and
the result of a query is also a relation instance. Inputs and Outputs of Queries are relations Query evaluated on instances of input relations
Different instance (DB?) as input = different answer Schemas of input relations for a query are fixed
(but query will run regardless of instance!) The schema for the result of a given query is also
fixed! Determined by definition of input relations and query language constructs.
![Page 12: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/12.jpg)
12
RELATIONAL ALGEBRA OPERATIONS Basic operations
Selection ( ) Selects a subset of rows from
relation. Projection ( )
Deletes unwanted columns from relation.
Cartesian product ( ) Combine two relations.
Set-difference ( ) Tuples in relation 1, but not in
relation 2. Union ( )
Tuples in relation 1 or in relation 2.
![Page 13: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/13.jpg)
13
Renames relations / attributes, without changing the relation instance.
relation R is renamed to S, attributes are renamed A1, . . ., An
Rename only some attributes
using the positional notation to reference attributes
No renaming of attributes, just the relation
RENAMING
)(),...,2,1( RAnAAS
)(),...,11( RAkkAS
)(RS
![Page 14: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/14.jpg)
14
PROJECTION Similar in concept to VIEWs sname rating
yuppy 9lubber 8guppy 5rusty 10
sname rating S, ( )2
age35.055.5
age S( )2
sid sname rating age28 yuppy 9 35.031 lubber 8 55.544 guppy 5 35.058 rusty 10 35.0
S2
Other fields are projected out
![Page 15: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/15.jpg)
15
SELECTION
rating S8 2( )
sid sname rating age28 yuppy 9 35.058 rusty 10 35.0
sid sname rating age28 yuppy 9 35.031 lubber 8 55.544 guppy 5 35.058 rusty 10 35.0
S2
![Page 16: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/16.jpg)
16
UNION
Concatenates S1 and S2 Result contains ALL tuples that occur in either S1 or S2
Schemas must be identical If they have the same number of fields Fields have same domains
sid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.044 guppy 5 35.028 yuppy 9 35.0
S S1 2
sid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.0
S1sid sname rating age28 yuppy 9 35.031 lubber 8 55.544 guppy 5 35.058 rusty 10 35.0
S2
SELECT * FROM S1UNIONSELECT * FROM S2
![Page 17: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/17.jpg)
17
INTERSECTION
Result contains ALL tuples that occur in both S1 or S2 Schemas must be identical
21 SS
sid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.0
S1sid sname rating age28 yuppy 9 35.031 lubber 8 55.544 guppy 5 35.058 rusty 10 35.0
S2
SELECT * FROM S1INTERSECTSELECT * FROM S2
![Page 18: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/18.jpg)
18
SET-DIFFERENCE
Result contains ALL tuples that occur in S1 but not in S2 Schemas must be identical
21 SS
sid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.0
S1sid sname rating age28 yuppy 9 35.031 lubber 8 55.544 guppy 5 35.058 rusty 10 35.0
S2
SELECT * FROM S1MINUSSELECT * FROM S2
![Page 19: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/19.jpg)
19
CARTESIAN PRODUCT Field names in conflict become unnamed
(sid) sname rating age (sid) bid day22 dustin 7 45.0 22 101 10/ 10/ 9622 dustin 7 45.0 58 103 11/ 12/ 9631 lubber 8 55.5 22 101 10/ 10/ 9631 lubber 8 55.5 58 103 11/ 12/ 9658 rusty 10 35.0 22 101 10/ 10/ 9658 rusty 10 35.0 58 103 11/ 12/ 96
sid sname rating age22 dustin 7 45.031 lubber 8 55.558 rusty 10 35.0
sid bid day 22 101 10/10/96 58 103 11/12/96
R1S1
21 SS
![Page 20: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/20.jpg)
20
JOIN Condition Join
Each tuple of the one relation is paired with each tuple of the other relation if the two tuples satisfy the join condition.
Condition c refers to attributes of both R and S.
)( SRcScR
(sid) sname rating age (sid) bid day22 dustin 7 45.0 58 103 11/ 12/ 9631 lubber 8 55.5 58 103 11/ 12/ 96
11:Example .1.1 RS sidRsidS
![Page 21: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/21.jpg)
21
Find names of sailors who’ve reservedboat #103.
Solution 1:
Solution 2:
Solution 3:
Which is most efficient? Why?
))Re(( 103 Sailorsservesbidsname
)Re( 1031 servesbidTemp
)1(2 SailorsTempTemp
sname bid serves Sailors( (Re ))103
EXAMPLE QUERIES
![Page 22: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/22.jpg)
22
Find names of sailors who’ve reserved a red boat.
Information about boat color only available in Boats; so need an extra join:
A more efficient solution:
A query optimizer can find the second solution given the first one.
sname color red Boats serves Sailors(( ' ' ) Re )
sname sid bid color red Boats s Sailors( (( ' ' ) Re ) )
EXAMPLE QUERIES
![Page 23: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/23.jpg)
23
How to find number of sailors?EXAMPLE QUERIES
![Page 24: Database Systems I Admin Stuff](https://reader035.vdocuments.us/reader035/viewer/2022062305/56816174550346895dd100ad/html5/thumbnails/24.jpg)
24
How to find oldest sailor?EXAMPLE QUERIES