1 / 24

Database Systems I Admin Stuff

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.

tarika
Download Presentation

Database Systems I Admin Stuff

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Systems IAdmin Stuff

  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

  3. Assignment 4 solution posted (with algebra) • Assignment 6 posted • For procedure question, sample on next page might help

  4. Database Systems IWeek 6: Review

  5. 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?

  6. 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

  7. 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.

  8. 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

  9. 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.

  10. 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.

  11. 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.

  12. 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

  13. S2 Projection • Similar in concept to VIEWs • Other fields are projected out

  14. S2 Selection

  15. 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

  16. 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

  17. 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

  18. Cartesian Product • Field names in conflict become unnamed R1 S1

  19. 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.

  20. Example Queries • Find names of sailors who’ve reservedboat #103. • Solution 1: • Solution 2: • Solution 3: • Which is most efficient? Why?

  21. 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.

  22. Example Queries • How to find number of sailors?

  23. Example Queries • How to find oldest sailor?

More Related