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