The Relational Model

1. The Relational Model Chapter 3

2. History of the Relational Model The objectives of the relational model : • allow a high degree of data independence, application programs should not be affected by modifications to the internal data representation, file organisation record orderings or access paths • deal with data semantics, consistency and redundancy problems, the database files should be based on normalised relations • enable the expansion of set-oriented data manipulation languages, using a many-records-at-a-time logic.

3. Terminology Relation • A relation is a table with rows and columns • Mathematical definition Attribute • An attribute is a named column of a relation Domain • A domain is the set of allowable values for one ore more attributes Tuple • A tuple is a row of a relation

4. The DOMAIN concept The domain concept is used to describe a set of possible values for an attribute. This offers more flexibility. Domain description Domain part-number character 6 Domain part-name character 20 Domain color character 6 Domain weight numeric 4 Domain location character 15 Relation PART (P# : Domain part-number pname : Domain part-name color : Domain color weight : Domain weight city : Domain location

5. Sample Database S S# SNAME STATUS CITY S1 Smith 20 London S2 Jones 10 Paris S3 Blake 30 Paris S4 Clark 20 London S5 Adams 30 Athens • SP S# P# QTY • S1 P1 300 • S1 P2 200 • S1 P3 400 • S1 P5 200 • S1 P6 100 • S2 P1 300 • S2 P2 400 • S3 P2 200 • S4 P2 200 • S4 P4 300 • S4 P5 400 • P P# PNAME COLOR WEIGHT CITY • P1 Nut Red 12 London • P2 Bolt Green 17 Paris • P3 Screw Blue 17 Rome • P4 Screw Red 14 London • P5 Cam Blue 19 London • P6 Cog Red 19 London

6. Terminology 2 Degree • The degree of a relation is the number of attributes it contains Cardinality • The cardinality of a relation is the number of tuples it contains Relational database • A collection of normalised relations Relation Schema • A relation name followed by a set of attribute and domain name pairs

7. Alternative Terminology

8. Properties of a Relation • A relation in a database has a unique name. • Each cell of the relation contains exactly one atomic value. • Each attribute has a distinct name within a relation. • The values of an attribute are all from the same domain. • column homogenious • The order of the attributes has no significance. • Each tuple is distinct; there are no duplicate tuples. • The order of tuples has no significance, theoretically. Most properties can be derived from the mathematical definition

9. Mathematical Relations Mathematical definition of relation • Consider two sets, D1 and D2, where D1 = {2, 4} and D2 = {1, 3, 5}. • Cartesian product is D1 D2, the set of all ordered pairs, first element is member of D1 and second element is member of D2. • Alternative way is to find all combinations of elements with first from D1 and second from D2. • D1D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)}

10. Mathematical Relation • Any subset of Cartesian product is a relation. For example R = {(2, 1), (4, 1)} • May specify which pairs are in relation using some condition for selection. For example, second element is 1 R = {(x, y) | xD1, yD2, and y = 1}

11. Relational Keys • Superkey • An attribute or a set of attributes that uniquely identifies a tuple within a relation.

12. Relational Keys • Candidate Key • A superkey (K) such that no proper subset is a superkey within the relation. • In each tuple of R, the values of K uniquely identify that tuple (uniqueness). • No proper subset of K has the uniqueness property (irreducibility).

13. Relational Keys • Primary Key • Candidate key selected to identify tuples uniquely within relation. • Alternate Keys • Candidate keys that are not selected to be the primary key.

14. Relational Keys • Foreign Key • An attribute or set of attributes within one relation that matches candidate key of some (possibly same) relation.

15. Relational Integrity • Null (value) • Represents a value for an attribute that is currently unknown or is not applicable for this tuple. • Deals with incomplete or exceptional data. • Null represents the absence of a value and is not the same as zero or spaces, which are values.

16. Relational Integrity • Entity Integrity • In a base relation, no attribute of a primary key can be null. • Referential Integrity • If foreign key exists in a relation, either the foreign key value must match a candidate key value of some tuple in its home relation or foreign key value must be wholly null.

17. Relational Integrity • Enterprise Constraints • Additional rules specified by users or database administrators.

18. Views Base relation • a named relation, corresponding to an entity in the conceptual schema, whose tuples are physically stored in the database. View • a view is the dynamic result of one or more relational operations operating on the base relations to produce another relation. A view is a virtual relation that does not actually exist in the database but is produced upon request by a particular user, at the time of request.

19. Views • A view is a virtual relation that does not actually exist in the database but is produced upon request, at time of request. • Contents of a view are defined as a query on one or more base relations. • Views are dynamic, meaning that changes made to base relations that affect view attributes are immediately reflected in the view.

20. Purpose of Views • Provides a powerful and flexible security mechanism by hiding parts of database from certain users. • Permits users to access data in a customized way, so that same data can be seen by different users in different ways, at same time. • It can simplify complex operations on base relations.

21. Updating Views • All updates to a base relation should be immediately reflected in all views that reference that base relation. • If view is updated, underlying base relation should reflect change.

22. Updating Views • However, there are restrictions on types of modifications that can be made through views: • Updates are allowed if query involves a single base relation and contains a candidate key of base relation. • Updates are not allowed involving multiple base relations. • Updates are not allowed involving aggregation or grouping operations.

23. Updating Views • Classes of views are defined as theoretically not updateable, theoretically updateable and partially updateable.

24. Relational Database Architecture Data Language application External level view 1 view 2 Conceptual level Base table B2 Base table B3 Base table B1 Base table B4 Stored file S2 Stored file S3 Stored file S1 Stored file S4 Internal level