DOC PREVIEW
UW CSE 444 - Lecture Notes

This preview shows page 1-2-3-4-5 out of 14 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 14 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

The Relational ModelHistoryThe Big IdeaA few more factsSimplicity of the ModelConstraints in the Model"Relating" RelationsRelational DB OperationsSelect and Project"Join"Aggregate FunctionsUpdate OperationsDDL OperationsRelational Query Languages01/14/19 D-1The Relational ModelTextbook 6.1-6.401/14/19 D-2History•Earlier models: Hierarchical, Network (ch. 10 & 11)–complex to implement and use; widely used•Introduced by Codd & Date (IBM), 1970–Semantically poor; tractable to analyze•Of primarily research interest until 1980's–by now is overwhelmingly the leading model01/14/19 D-3The Big Idea•"Tables" ("relations") represent entities–made up of "rows" ("tuples") of fixed-sized attribute values, one row for each entity instance•A "column" of a table holds all the values of a particular attribute•A database typically contains a number of tablesThat's about it!01/14/19 D-4A few more facts•Tables are sets (mathematically)–no duplicate rows (implies each table has a key)–no ordering implied–RDBMS may allow ordering, duplicates•"Null" attribute values allowed•Caution: "relation" is not the same as E/R "relationship"01/14/19 D-5Simplicity of the Model•Even compared to E/R...–no explicit relationships between entities–no compound attributes, no multivalued attributes ("first normal form")–no weak entities–no cardinality constraints•Up to programmers to realize such semantics01/14/19 D-6Constraints in the Model•Domain constraints: –that values of a column come from a defined domain•Key constraints: –that there be a key–that primary key value not be null•"Referential integrity" (in the case where R1 contains a "foreign key" of R2)–that a foreign key value in R1 always refer to some row in R2 with that key value01/14/19 D-7"Relating" Relations•As noted: there is no direct analog of the E/R "relationship" (diamond shape)•Possible solution: foreign key in one of the relations–awkward if not 1-1 •Common solution: A "relationship relation"–attributes are the keys of the two relations–tuples stand for pairs of related entities–As in E/R, the two entity sets may be the same01/14/19 D-8Relational DB Operations•Keep in mind: relations are sets•As long as two relations have the same columns:–set union, intersection, difference–result has same columns as inputs•Cartesian product–has how many columns?–has how many rows?01/14/19 D-9Select and Project•"Select"–take a subset of the rows based on some condition•"Project"–take a subset of the columns•We'll see notation later01/14/19 D-10"Join"•Perhaps the most characteristic operation of the relational model•Used constantly•Challenging to implement efficiently•The idea: a Cartesian product on two relations with common attribute domains, followed by a select based on those attributes.–especially common: equality match ("natural join")01/14/19 D-11Aggregate Functions•Not set-theoretic•COUNT, AVERAGE, MAX, MIN, etc.•Actual query languages have many of these•Rows can be "grouped" by some attribute and the function applied to each group (rows with common values of the grouping attribute)01/14/19 D-12Update Operations•"Insert"–Add a new tuple to a table•"Modify"–Change an attribute value in an existing tuple of an existing table•Update operations compared to retrieval queries:–simple–in many applications, relatively infrequent01/14/19 D-13DDL Operations•Used only the the DBA•Create table–define attribute domains and names–declare constraints•Declare schema–group relations together into a database•Modify relation or schema–add/delete attributes, etc.01/14/19 D-14Relational Query Languages•Operations are specified in a particular "query language"•Relational Algebra: whole-table operations•Relational Calculus: set construction•SQL: set operations and procedural features, English-like syntax•QBE: 2-dimensional set


View Full Document

UW CSE 444 - Lecture Notes

Documents in this Course
XML

XML

48 pages

SQL

SQL

25 pages

SQL

SQL

42 pages

Recovery

Recovery

30 pages

SQL

SQL

36 pages

Indexes

Indexes

35 pages

Security

Security

36 pages

Wrap-up

Wrap-up

6 pages

SQL

SQL

37 pages

More SQL

More SQL

48 pages

SQL

SQL

35 pages

XML

XML

46 pages

Triggers

Triggers

26 pages

Load more
Download Lecture Notes
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Lecture Notes and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Lecture Notes 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?