23rd Annual National Undergraduate Literature Conference

Sas

Description

• mémoire
• cours magistral

Thursday, April 3rd: Pre-conference Events (open to conference participants & community) 12:00-1:00 pm - Convocations: Eleanor Wilner 2:00-3:00 pm - My Favorite Poem Project with Featured Authors Conference Events 6:00-7:00 pm - Registration, Check-in, & Social Hour at the Timbermine Steak House 7:00-9:00 pm - Opening Banquet Featured speaker: Geoffrey Wolff Friday, April 4th: 7:30-8:00 am - Registration, Check-in, & Continental Breakfast 8:00-9:15 am - Session 1 Breakouts 9:30

• short fiction award
• conference events
• idaho things
• wildcat dining room moderator
• feminist dolores kimball
• moderator
• registration
• state university
• college

Sujets

• Mémoire
• Cours magistral
• Earring
• Krantz
• Collège
• Wilner
• Knowlton

Informations

• Publié par: mevang
• Nombre de lectures: 21
• Langue: English

Extrait

Relational Query Languages
Languages for describing queries on a
relational databaseRelational Algebra and SQL
Structured Query Language (SQL)
Predominant application-level query language
DeclarativeChapter 5
Relational Algebra
Intermediate language used within DBMS
Procedural
1 2
What is an Algebra? Relational Algebra
A language based on operators and a domain of values
Domain: set of relations
Operators map values taken from the domain into
Basic operators: select, project, union, setother domain values
difference, Cartesian productHence, an expression involving operators and
arguments produces a value in the domain Derived operators: set intersection, division, joinset intersection division join
When the domain is a set of all relations (and the Procedural: Relational expression specifies query
operators are as described later), we get the relational by describing an algorithm (the sequence in which
algebra operators are applied) for determining the result of
We refer to the expression as a query and the value an expression
produced as the query result
3 4
The Role of Relational Algebra in a DBMS Select Operator
Produce table containing subset of rows of
argument table satisfying condition
(relation)condition
Example:
Person (Person)Hobby= stamps
Id Name Address Hobby Id Name Address Hobby
1123 John 123 Main stamps 1123 John 123 Main stamps
1123 John 123 Main coins 9876 Bart 5 Pine St stamps
5556 Mary 7 Lake Dr hiking
9876 Bart 5 Pine St stamps
5 6
1Selection Condition - ExamplesSelection Condition
Operators: <, , , >, =,
(Person)ORId>3000 Hobby= hiking
Simple selection condition:
(Person)PersonANDId>3000 Id <3999<attribute> operator <constant>
<> <attribute>
(Person)NOT(Hobby= hiking )
<condition> AND <condition>
(Person)Hobby hiking<> OR <>
NOT <condition>
7 8
Project Operator Project Operator
Example:Produces table containing subset of columns
Person (Person)Name,Addressof argument table
Id Name Address Hobby Name Address(relation)attribute list
John 123 Main1123 John 123 Main stampsExample: Mary 7 Lake Dr1123 John 123 Main coins
Bart 5 Pine StPerson (Person)Person Person 5556 Mary 7 Lake Dr hikingName,Hobby
9876 Bart 5 Pine St stamps
Id Name Address Hobby Name Hobby
John stamps1123 John 123 Main stamps
John coins1123 John 123 Main coins Result is a table (no duplicates); can have fewer tuples
Mary hiking5556 Mary 7 Lake Dr hiking than the original
9876 Bart 5 Pine St stamps Bart stamps
9 10
Expressions Set Operators
( (Person) )Id, Name Hobby= stamps OR Hobby= coins Relation is a set of tuples, so set operations
should apply: , , (set difference)
Id Name Address Hobby Id Name
1123 John1123 John 123 Main stamps Result of combining two relations with a set
1123 John 123 Main coins 9876 Bart operator is a relation => all its elements
5556 Mary 7 Lake Dr hiking
ResultResult must be tuples having same structure9876 Bart 5 Pine St stamps
Person Hence, scope of set operations limited to
union compatible relations
11 12
2Example
Union Compatible Relations
Tables:
Person (SSN, Name, Address, Hobby)Two relations are union compatible if
Professor (Id, Name, Office, Phone)
Both have same number of columns
are not union compatible.
Names of attributes are the same in both
Attributes with the same name in both relations But
have the same domain (Person) and (Professor)Name Name
Union compatible relations can be are union compatible so
combined using union, intersection, and set (Person) - (Professor)Name Name
differencedifference
makes sense.
13 14
Cartesian Product Renaming
If R and S are two relations, R S is the set of all
Result of expression evaluation is a relation
concatenated tuples <x,y>, where x is a tuple in R
Attributes of relation must have distinct names. and y is a tuple in S
This is not guaranteed with Cartesian productR and S need not be union compatible
e.g., suppose in previous example a and c have the R S is expensive to compute:
same nameFactor of two in the size of each row
Quadratic in the number of rows Renaming operator tidies this up. To assign the
names A , A , A to the attributes of the n1 2 nA B C D A B C D
column relation produced by expression expr use
x1 x2 y1 y2 x1 x2 y1 y2
expr [A , A , A ]1 2 nx3 x4 y3 y4 x1 x2 y3 y4
x3 x4 y1 y2
R S x3 x4 y3 y4
15 16
R S
Derived Operation: JoinExample
A (generalgeneral or thetatheta) join join of R and S is the expression
R S
join-condition
Transcript (StudId, CrsCode, Semester, Grade)
where join-condition is a conjunction of terms:
Teaching (ProfId, , Semester) A oper Bi i
in which A is an attribute of R; B is an attribute of S;
i i
(Transcript)[StudId, CrsCode1] and oper is one of =, <, >, , . StudId, CrsCode
(Teaching) [ProfId, CrsCode2]TeachingProfId, CrsCode The meaning is:
(R S)
join-condition´
where join- and join-condition´ are the same,
This is a relation with 4 attributes: except for possible renamings of attributes (next)
StudId, CrsCode1, ProfId, CrsCode2
17 18
3Theta Join ExampleJoin and Renaming
Employee(Name,Id,MngrId,Salary)
Problem: R and S might have attributes with the Manager(Name,Id,Salary)
same name in which case the Cartesian Output the names of all employees that earn
product is not defined
more than their managers.
Solutions:
1. Rename attributes prior to forming the product and (Employee Manager)ANDEmployee.Name MngrId=Id Salary>Salary
use new names in join-condition´.
2. Qualify common attribute names with relation names
(thereby disambiguating the names). For instance: The join yields a table with attributes:
Transcript.CrsCode or Teaching.CrsCode Employee.Name, Employee.Id, Employee.Salary, MngrId
This solution is nice, but doesn t always work: consider Manager.Name, Manager.Id, Manager.Salary
R Rjoin_condition
In R.A, how do we know which R is meant?
19 20
Equijoin Join - Example Natural Join
Equijoin: Join condition is a conjunction of equalities.
Special case of equijoin:
(Student (Transcript)) join condition equates all and only those attributes with the Name,CrsCode Id=StudId Grade= A
same name (condition doesn t have to be explicitly stated)
Student Transcript duplicate columns eliminated from the result
Id Name Addr Status StudId CrsCode Sem Grade
TranscriptTranscript (StudId, CrsCode, Sem, Grade)
111 John .. .. 111 CSE305 S00 B
Teaching (Teaching (ProfId, CrsCode, Sem)
222 Mary .. .. 222 CSE306 S99 A
333 Bill .. .. 333 CSE304 F99 A
Transcript Teaching =
444 Joe .. ..
StudId, Transcript.CrsCode, Transcript.Sem, Grade, ProfIdThe equijoin is used very
( Transcript Teaching )frequently since it combines CrsCode=CrsCode AND Sem=SemMary CSE306
related data in different relations. [StudId, CrsCode, Sem, Grade, ProfId ]
Bill CSE304
21 22
Natural Join (cont d) Natural Join Example
More generally:
List all Ids of students who took at least two
R S = ( (R × S) )attr-list join-cond different courses:
where
( (attr-list = attributes (R) attributes (S) StudId CrsCode CrsCode2
Transcript(duplicates are eliminated) and join-cond has [StudId, CrsCode2, Sem2, Grade2] ))the form:
A = A AND AND A = A1 1 n n
We don t want to join on CrsCode, Sem, and Grade attributes,
where
hence renaming!
{A A } = attributes(R) attributes(S)1 n
23 24
4Division (cont d)Division
Goal: Produce the tuples in one relation, r,
that match all tuples in another relation, s
r (A , A , B , B )1 n 1 m
ss (B B )1 m
r/s, with attributes A , A , is the set of all tuples 1 n
<a> such that for every tuple <b> in s, <a,b> is
in r
Can be expressed in terms of projection, set
difference, and cross-product
25 26
Division - Example
Schema for Student Registration System
List the Ids of students who have passed all
courses that were taught in spring 2000
Numerator:
Student (Id, Name, Addr, Status)
StudId and CrsCode for every course passed by
Professor (DeptId)every student:
( (TranscriptTranscript) ) CourseCourse (DeptId, CrsCode, CrsName, Descr)StudId, CrsCode Grade F
Denominator: Transcript (StudId, , Semester, Grade)
CrsCode of all courses taught in spring 2000 Teaching (ProfId, CrsCode, Semester)
( (Teaching) ) Department (DeptId, Name)CrsCode Semester= S2000
Result is numerator/denominator
27 28
Query Sublanguage of SQL Join Queries
C.CrsName
C.CrsName Course C, Teaching T
C.CrsCode=T.CrsCode T.Semester= S2000Course C
C.DeptId = CS List CS courses taught in S2000
Tuple variables clarify meaning.
Tuple variable C ranges over rows of Course.<