1 / 27

The Relational Model

The Relational Model. Lecture Topics Overview of SQL Underlying relational model Relational database structure SQL DDL and DML Views Textbook Chapter 3. Overview of SQL. Structured Query Language (SQL, sometimes pronounced “sequel”)

shaw
Download Presentation

The Relational Model

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. The Relational Model Lecture Topics • Overview of SQL • Underlying relational model • Relational database structure • SQL DDL and DML • Views Textbook • Chapter 3 The Relational Model

  2. Overview of SQL • Structured Query Language (SQL, sometimes pronounced “sequel”) • ISO/IEC 9075, international standard for relational database systems • the standard is evolving: • 1986: SQL1; initial version, 75 pages • 1989: SQL89; most commercial products conform to this version, with extensions • 1992: SQL2; three levels of conformity, ~600 pages • 1999: SQL99 (sometimes called SQL3), ~1200 pages • 2007: under revision again The Relational Model

  3. ...continued Main features: • powerful view definition language • integrity constraints in conceptual schema • DML can be embedded in various programming languages (called “embedded SQL”), or used via programming libraries • object/class libraries in OO environments • transaction control • authorization sublanguage/model The Relational Model

  4. ...continued Application 1 Application 2 Application 3 S Q L View A View B D B M S Conceptual Level Physical Schema Database The Relational Model

  5. Underlying relational model Vendor Vno Vname City Vbal 1 2 3 4 Sears Kmart Esso Esso Toronto Ottawa Montreal Waterloo 200.00 671.05 0.00 2.25 Customer AccNum Cname Prov Cbal Climit 101 102 103 Smith Jones Martin Ont BC Que 25.15 2014.00 150.00 2000 2500 1000 Transaction Tno Vno AccNum Tdate Amount 1001 1002 1003 1004 1005 2 2 3 4 4 101 103 101 102 103 070115 070116 070115 070120 070125 13.25 19.00 25.00 16.13 33.12 Example relational database for a credit card company The Relational Model

  6. Structure of a relational database Database: collection of uniquely named tables (relations) Relation: set of rows (tuples) Attribute: column Domain: set of allowed values for an attribute Attribute values must be atomic (single values): no tuples or sets or repetition Row: distinguishable thing Table: set of related things The Relational Model

  7. Diagrammatic conventions Vendor Vno Vname City Vbal Vendor Vno Vname City Vbal or The Relational Model

  8. Pictorial schema Customer AccNum Cname Prov CbalClimit Vendor Vno VnameCityVBal Transaction Tno Vno AccNumTdateAmount The Relational Model

  9. The SQL DDL • used for defining • tables • views • example of table definition (conceptual schema): create table Vendor (Vno INTEGER not null, Vname VARCHAR(20), City VARCHAR(10), Vbal DECIMAL(10,2), primary key (Vno) ); The Relational Model

  10. ...continued create table Customer (AccNum INTEGER not null, Cname VARCHAR(20) not null, Prov VARCHAR(20), Cbal DECIMAL(6,2) not null, Climit DECIMAL(4,0) not null, primary key (AccNum) ); create table Transaction (Tno INTEGER not null, Vno INTEGER not null, AccNum INTEGER not null, Tdate DATE, Amount DECIMAL(6,2) not null, primary key (Tno), foreign key (Vno) references vendor(Vno), foreign key (AccNum) references Customer(AccNum) ); The Relational Model

  11. Attribute domains in SQL • INTEGER: integers representable with 32 bits • SMALLINT: integers representable with 16 bits • DECIMAL(m,n): fixed point numbers • FLOAT: 32 bit floating point numbers • CHAR(n): fixed length strings • VARCHAR(n): variable length strings • BIT(n): n bits • BIT VARYING(n): variable number of bits The Relational Model

  12. ...continued • DATE (year, month, day) • TIME (hour, minute, second) • TIME(i) (hour, minute, second, second fraction) • TIMESTAMP (date, time, second fraction) • INTERVAL YEAR/MONTH (year month interval) • INTERVAL DAY/TIME (day time interval) • plus many, many product-specific (non-standard) extensions The Relational Model

  13. Modifying table definitions • Table schemas can be changed after the table has been created: • adding columns • removing columns • removing constraints (e.g. p-key) • some SQL implementations allow • renaming a column • modifying a column • Example:ALTER TABLE Vendor ADD Street VARCHAR(15) The Relational Model

  14. Removing tables • SQL operation is “drop” • Tables can be dropped at any time • Dropping a table deletes the schema and the instance • All views, foreign-key definitions are also removed • Example:DROP TABLE Transaction The Relational Model

  15. SQLhas a non-navigationalDML: The SQL DML E.g. “Find names and provinces of customers who owe more than $1000 to the company.”select Cname, Provfrom Customerwhere Cbal > 1000; The Relational Model

  16. ...continued • basic querying:selectcolumnsfrom R1,...,Rk[ wherefilter ]; • result is a relation over columns (columns = “*” means all attributes in R1,..., Rk) • R1,..., Rk: tables from which the data is retrieved • filter : conditions on tuples used to form the result; optional The Relational Model

  17. ...continued • conditions may include: • arithmetic operators +, -, *, / • comparisons =, <>, <, <=, >, >= • logical connectives and, or and not E.g. “List the names of the customers who live in Ontario and whose balance is over 80% of their balance limit.” select Cnamefrom Customerwhere Prov = 'Ont' and Cbal > 0.8 * Climit; The Relational Model

  18. ...continued • basic insertion:insert into Customervalues (104, ‘Anne', 'ON',0, 4000); • deletion: delete Customer rows for customers named Smith:delete from Customerwhere Cname = 'Smith'; • delete all transactions:delete from Transaction; The Relational Model

  19. ...continued • modification, changing existing rows • set balance of account 102 to zero:update Customer set Cbal = 0where AccNum = 102; • add $100 to each customer’s monthly limit:update Customer set Climit = Climit + 100; The Relational Model

  20. SQL external schema • called views • a view is a named query (result is usually computed when the view is used)create view WatVendors asselect VNo, VName, VBalfrom Vendorwhere City = 'Waterloo'; • views can be used in retrieval exactly like tables (but updates of views are restricted) The Relational Model

  21. Advantages of views • logical data independence • simplified perception of the database • different views for different users • restricting data access The Relational Model

  22. Basic relational concepts Relating to descriptions of data: • Attribute (column): a name denoting a property or characteristic • Relation schema (table header): a finite set of attributes and a mapping of each attribute to a domain (defined below) The Relational Model

  23. ...continued Relating to data: Domain: an “abstract data type” (i.e. a name, a set of values and a number of functions defined over the values) • Null value: a special exception value (meaning “not known”, “not applicable”) • Tuple: a set of attribute/value pairs, with each attribute occurring at most once • Relation (table): a relation schema, and a finite set of tuples • Relational database: a finite set of relation names and a mapping of each relation name to a relation The Relational Model

  24. ...continued Other: • Intention of a relation: the associated relation schema • Extension of a relation: the associated set of tuples The relational model assumes no ordering of either rows or columns for any table. The Relational Model

  25. Basic rules • Domain constraints: the value associated with each attribute in a tuple must occur in the set of values associated with the domain of the attribute; or the value is Null • First normal form: domain values cannot be tuples or relations • Completeness: each tuple in a relation has an attribute/value pair for precisely the set of attributes in the associated relation schema • Closed world: the database “knows of” all tuples in all relations • Unique rows: no two distinct tuples in any given relation consist of the same set of attribute/value pairs The Relational Model

  26. Keys • Relation superkey: a subset of the associated relation schema for which no pair of distinct tuples in the relation will ever agree on the corresponding values. • Relation candidate key: a minimal superkey • Relation primary key: a distinguished candidate key of the relation • Foreign key: primary key of one relation appearing as attributes of another relation • Foreign keys enable capturing more complex entity structure The Relational Model

  27. Integrity of primary and foreign keys • Entity integrity: No component of a primary key value may be the null value, nor may be updated. • Referential integrity: A tuple with a non-null value for a foreign key that does not match the primary key value of a tuple in the referenced relation is not allowed. The Relational Model

More Related