The Relational Model

1. The Relational Model Stanislava Armstrong http://www.cs.nott.ac.uk/~saw/teaching/G64DBS/lecture_slides.html

2. The Relational Model • Introduced in E.F. Codd’s 1970 paper “A relational Model of the Data for Large Shared Data Banks” • The foundation for most (but not all) current database systems • Concerned with 3 main things • Data Structure (how data is represented) • Data integrity (what data is allowed) • Data manipulation (what you can do with the system)

3. The Database Terms • Data is stored in relations (tables) • Each relation has a scheme (heading) • The scheme defines the relation’s attributes (columns) • Data takes the form of tuples (rows) Fruit

4. The Database Terms Fruit More formally: • A scheme is a set of attributes • A tuple assigns a value to each attribute in its scheme • A relation is a set of tuples with the same scheme Fruit{fruitName, fruitPrice} { { (fruitName, Melon), (fruitPrice, 800)}, { (fruitName, Strawberry), (fruitPrice, 150)}, { (fruitName, Apple), (fruitPrice, 120)}, { (fruitName, Lemon), (fruitPrice, 200)}}

5. Properties of Relations • Since relations are sets of tuples, the tuples of a relation are unique and unordered • The number of tuples in a relation is called the cardinality of the relation • Since schemes are sets of attributes, the attributes of a relation are unique and unordered • The number of attributes in a relation is called the relation’s degree

6. Example Fruit

7. Relational Data Integrity • Data integrity controls what data can be in a relation • Domains restrict the possible value a tuple can assign to each attribute • Candidate and Primary Keys identify tuples within a relation • Foreign Keys link relations to each other

8. Attributes and Domains • A domain is given for each attribute • A domain lists the possible values for each attribute • Each tuple assigns a value to each attribute from its domain Examples • ‘price’ could be chosen from the set of positive real numbers • An attribute called ‘name’ could have a domain which is a string of length 50.

9. Candidate Keys A set of attributes in a relation is called a Candidate Key if and only if: • Every tuple has a unique value for the set of attributes (uniqueness) • No proper sunset of the set had the uniqueness property (minimality) Candidate Keys: {ID} and {prodName, price} {ID, prodName} and {ID, price} can uniquely identify each row, but are not minimal

10. Primary Keys • One Candidate Key is usually chosen to identify tuples in the relation • This is called Primary Key • Often a special ID attribute is chosen for a Primary Key We could choose either {ID} or {profName, price} for Primary Key. {ID} is more convenient as it is a single column and it will always be unique. What would happen if we want to add another melon(maybe its a different variety) with the same price?

11. NULLs and Primary Keys • Missing information can be represented using NULLs • A NULL represents a missing or unknown value • More on NULL in the later lectures  • Entity Integrity : a Primary Key cannot contain NULLs

12. Foreign Keys • Foreign Keys are used to link data in two relations • A set of attributes in the first (referencing) relation is a Foreign Key for the second (referenced) relation, if its value always either: • Matches a Candidate Key value in the referenced relation, or • Is wholly NULL • This is called Referential Integrity

13. Example Fruit Transaction fruitID is a Candidate Key for the Fruit relation fruitID is a Foreign Key in the Transaction relation – each fruitID in the Transaction relation is either NULL or matches an entry in the fruit relation.

14. Referential Integrity • When relations are updated referential integrity can be violated • This usually occurs when a referenced tuple is updated or deleted • There are a number of options: • RESTRICT – stop the user from doing it • CASCADE – let the changes flow on • NULLIFY – make values NULL • Triggers – user defined action

15. Example What happens if: • fruitID for Melon is changed from 1 to 5? • The entry for Lemon is deleted from the fruit table?

16. RESTRICT RESTRICT stops any action that violates integrity • You cannot update or delete Melon or Lemon • You can change or delete Strawberry as it is not referenced

17. CASCADE CASCADE allows changes made to flow through • If Melon fruitID is changed to 5 in the fruit table, it is also changes in the Transaction table • If Lemon is deleted so it transaction 2

18. NULLIFY NULLIFY sets problematic values to NULL • If Melon fruitID changes, the fruitID for transaction 1 and 3 will become NULL, where they were 1 before • If Lemon is deleted the fruitID for transaction 2 will be NULL

19. Naming Conventions Naming attributes • A consistent naming convention can help remind you of the structure of the relation • Assign each relation a unique prefix, so Fruit relation will have fruitName and Client relation CliName • Naming Keys • Having a unique number as the primary key can be useful • If the table prefix is fruit, call this fruitID • A Foreign Key to this table is then also called fruitID

20. Exercise For each of the relation below: • Determine the Scheme, Attributes, Tuples, Cardinality, Degree • Suggest a domain for each attribute • Determine the Candidate Keys and suggest the most appropriate one to become Primary Key • Explain the term entity integrity • Determine any Foreign Keys • Explain the term referential integrity • Show how the tables will change if: • holID for Majorca changes to 10 • Holidays to Amsterdam go up in price by £50 • Holidays to Amsterdam are discontinued • Steve Benford has to cancel his holiday Depending on whether RESTRICT, CASCADE or NULLIFY are used.

21. Exercise Holiday Client

22. Reading Material • The Manga Guide to Databases – chapters 1 and 2 • Database Systems – A Practical Approach to Design, Implementation and Management by Connolly and Begg – chapters 3 and 4