240 likes | 414 Views
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.
E N D
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
Assignment 4 solution posted (with algebra) • Assignment 6 posted • For procedure question, sample on next page might help
Review • A transactionhas the following properties: • Atomicity: all-or-nothing property • Consistency: 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?
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. since name dname ssn budget lot did Works_In Employees Departments
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 calleddomains. 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.
Relationship Sets ER Diagram SQL 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); Relational
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.
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 • Schemasof input relations for a query are fixed (but query will run regardless of instance!) • The schema for the resultof a given query is also fixed! Determined by definition of input relations and query language constructs.
Relational Algebra Operations • Basic operations • Selection ( ) Selects a subset of rows from relation. • Projection ( ) Deletes unwanted columns from relation. • Cartesian product( ) Combinetwo relations. • Set-difference ( ) Tuples in relation 1, but not in relation 2. • Union( ) Tuples in relation 1 or in relation 2.
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
S2 Projection • Similar in concept to VIEWs • Other fields are projected out
S2 Selection
Union S2 S1 • 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 SELECT * FROM S1 UNION SELECT * FROM S2
Intersection S2 S1 • Result contains ALL tuples that occur in both S1 or S2 • Schemas must be identical SELECT * FROM S1 INTERSECT SELECT * FROM S2
Set-Difference S2 S1 • Result contains ALL tuples that occur in S1 but not in S2 • Schemas must be identical SELECT * FROM S1 MINUS SELECT * FROM S2
Cartesian Product • Field names in conflict become unnamed R1 S1
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.
Example Queries • Find names of sailors who’ve reservedboat #103. • Solution 1: • Solution 2: • Solution 3: • Which is most efficient? Why?
Example Queries • 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.
Example Queries • How to find number of sailors?
Example Queries • How to find oldest sailor?