Relational Model

1. Relational Model • Reading: chapter 5, section 7.1 • Contents: • Basic concepts and structure • Relation constraints and relational DB schemas • Issues in update operations • Mapping ER model to relational model

2. Example of relation

3. Basic concepts: Domain • Domain: a set of atomic values • Example: a set of first name of persons • The set of allowed values for each attribute is called the domain of the attribute

4. Basic concepts: Attributes • Attributes: is the name of a role played by some domain D in the relation schema R. • Example: First name • Attribute values are (normally) required to be atomic, that is, indivisible • E.g. multivalued attribute values are not atomic • E.g. composite attribute values are not atomic • The special value null is a member of every domain

5. Basic concepts: Tuples Example: <A-101, Dowmtown, 500> <A-201, Brighton, 900>

6. Basic concepts: Relation (I) • Formally, given domain D1, D2, …. Dn a relation r is a subset of D1 x D2 x … x Dn • A relation is a set of n-tuples (a1, a2, …, an) where each ai Di

7. Basic concepts: Relation (II) • Example: if customer-name = {Jones, Smith, Anders}customer-street = {Storgatan, Bankgatan}customer-city = {Sundsvall, Stockholm}Then r = { (Jones, Storgatan, Sundsvall), (Smith, Bankgatan, Stockholm), (Anders, Bankgatan, Sundsvall)} is a relation over customer-name x customer-street x customer-city

8. Basic concepts: Relation Schema • A1, A2, …, Anare attributes • R (A1, A2, …, An ) is a relation schema E.g. Customer-schema(customer-name, customer-street, customer-city) • r(R) is a relation on the relation schema R E.g. customer (Customer-schema)

9. Basic concepts: others • Relation instance: The current values of a relation are specified by a table • Relations are unordered.

10. Relational constraints: Keys (I) Superkey: • Is a set of attributes, K, in Relation schema, R. • Values for K are sufficient to identify a unique tuple of each possible relation r(R) • Example: Suppose that no two customers can possibly have the same name then Both {name} and {name, city} are superkeys of Customer(name, street, city)

11. Relational constraints: Keys (II) • candidate key is a minimal superkey • Example: {name} is a candidate key for Customer, since it is a superkey • A relation schema may have more than one candidate key.

12. Relational constraints: Keys (III) • Primary key: • A candidate key that is designated as a primary key. • It is usually better to choose a primary key with a single attribute or small number of attributes • Primary key is often used in physical database design.

13. Relational constraints: not Null • Specifies whether null values are or are not permitted. • Entity integrity: no primary key value can be null.

14. Relational DBs and relational DB schemas • A database consists of multiple relations • Information about an enterprise is broken up into parts, with each relation storing one part of the information • Fig 7.5, Fig 7.6

15. Foreign Keys • A set of attributes FK in R2 is a foreign key that reference R1, if • FK have the same domain(s) as the primary key PK of R1. • Let t1 is a tuple of r1(R1) then • t1(FK) = t2(PK), where t2 is a tuple in r2(R2) • Or t1(FK) = NULL

16. Foreign Keys Employee Department

17. Foreign keys Employee Department

18. Integrity constraints • Integrity constraints • Entity integrity • Referential integrity: foreign key constraints • Preceding integrity constraints • E.g. (amount of loan should be less than 1,000,000)

19. Integrity constraints (cont.) • All integrity constraints should be specified on the relational database schema if we want to enforce these constraints on the database sate. • Entity and referential integrity constraint can be specified by DDL. DBMS can automatically enforce them. • Preceding integrity constraints can be specified and enforced by using a general purpose constraint specification language.

20. Issues in update operations: Insert • Insert operation can violate • Domain constraints • Key constraints • Entity integrity constraints • Referential integrity constraints • Preceding integrity constraints

21. Issues in update operations: Insert (cont) Account Balance >= 10 Insert <A-222, 4444, 5> into Account Violate: Domain constraints, Key constraints, Preceding integrity constraints

22. Issues in update operations: Delete • Delete operation can violate only referential integrity constraints

23. Issues in update operations: Delete Employee Department Delete department with deptname = ‘sail’ Violate referential integrity constraints

24. Issues in update operations: Update • The update operation is used to change the values of one or more attributes in a tuple. • Updating an attribute that is neither a primary key nor a foreign key dose not cause key constraints, entity integrity constraints and referential integrity constraints

25. Mapping ER model to relational model: step one I STEP ONE • For each strong (regular) entity type E in the ER schema: • a. Create a relation R that includes all the simple attributes of E. • b. Choose one of the key attributes of E as primary key for R

26. ID# Name address telephone Customer Mapping ER model to relational model: step one II Customer

27. Name address telephone Last Name First Name Mapping ER model to relational model: step one III Customer Customer

28. Mapping ER model to relational model: step two I For each weak entity type W in the ER with owner entity type E: • Create a relation R, include all the atomic attributes of W as attributes of R. • Include as foreign key attributes of R the primary key attributes that correspond to the owner entity types.

29. ID# Birth date First name Last name address Mapping ER model to relational model: step two II First name M 1 Dependents-of dependent Employee Dependent

30. Mapping ER model to relational model: step three I • For each binary one to one relationship type R in the ER schema: • Identify the relations S and T that correspond to the entity types participating in R. • Choose one of the relations—S, say—and include as foreign key in S the primary key of T.

31. 1 1 Manage Manager Department ID# address name name address Mapping ER model to relational model: step three II department Manager

32. Mapping ER model to relational model: step four I For each binary 1:M (one to many) relationship type R: • Identify the relation S that represents the participation entity type at the M-side of the relationship type. • Include as foreign key in S the primary key T of the relation R that represents the other entity type participating in R.

33. M 1 Works-for Employee department ID# address name name address Mapping ER model to relational model: step four II Department Employee

34. Mapping ER model to relational model: step five I For each binary N:M (many to many) and each n-ary, n>2 relationship type R: • Create a new relation S to represent the participation entity types. • Include as foreign key attributes in S the primary keys of the relations that represent the participating entity types. This combination forms the primary key of S. • Include any atomic attributes of the relationship type as attributes of S.

35. N M Employee project Works-on ID# address name name description Hours per week Mapping ER model to relational model: step five II Works-on

36. Mapping ER model to relational model: step six I For each multivalued attribute A: • Create a new relation R. • This relation R will include an attribute corresponding to A, plus the primary key attribute K—as a foreign key in R—of the relation that represents the entity type of relationship type that has A as an attribute. • The primary key of R is the combination of A and K.

37. ID# Name address telephone Mapping ER model to relational model: step five II Customer Customer-telephone Customer

38. M 1 M 1 M N N M E-R Diagram for the Banking Enterprise