The Relational Model Codd 1970 based on set
The Relational Model • Codd (1970): based on set theory • Relational model: represents the database as a collection of relations (a table of values --> file) – Domains – Tuples – Attributes – Relations • Relational model constraints…
4 Relational Model Constraints • Domain: – a range of values an attribute can take on • Key: – a relation must have a primary key – unique & time-invariant – primary key --> name underlined in the relation schema • Entity Integrity – no primary key value can be null • Referential Integrity – to maintain consistency among the tuples of two relations…
Referential Integrity - 1 • Foreign Key: – a set of attributes FK in relation schema R 1 is a foreign key of R 1 if it satisfies the following two rules: • the attributes in FK have the same domain as the primary key attributes PK of another relation schema R 2; the attributes FK are said to reference or refer to the relation R 2. • a value of FK in a tuple t 1 of R 1 either occurs as a value of PK for some tuple t 2 in R 2 or is null. – foreign key can refer to its own relation
Referential Integrity - 2 • Referential integrity rule: – If R 2 includes a foreign key matching the primary key PK of R 1, then every value of FK in R 2 must either be equal to the value of PK in some tuple of R 1 or be wholly null. R 1 and R 2 are not necessarily distinct.
Relational Data Retrieval • • • Results of operations: a new relation SELECT: show tuples (rows) PROJECT: show attributes (columns) JOIN: get data from more than one table Set: UNION, INTERSECTION, DIFFERENCE, CARTSIAN PRODUCT
Relational DB Languages • Standards: SQL 1 (1986) , SQL 2 (1992), SQL 3 (1999) • QBE (Query by Example): vary by implementations • ACCESS has both QBE and SQL • Variations: - Oracle: SQL*, PL/SQL - Sybase: ISQL, T-SQL
A Caution About Basics • Note that the database you are creating in the Lab sessions has been designed for you. – This is not a trivial job; it requires an understanding of database principles and theories • You will learn simple QBE. • Basic SQL is easy; but SQL can be a powerful programming tool which takes time to learn. • Everyone can use Access but not everyone can use it correctly.
- Slides: 7