B. Information Technology (Hons.) CMPB245: Database Design

1. B. Information Technology (Hons.)CMPB245:Database Design Logical Database Design

2. Objective • Build and Validate Local Logical Data Model

3. Build and Validate Local Logical Data Model Objective 1

4. Secretary Supervisor 1 1 Supports Supervises d M M M 1 M 1 SetsUp Has Interview Staff Branch 1 1 1 CarryOut Organizes 1 M 1 M Offers Inspection Manages With M Lease Agreement Undergoes M M M M Associated With 1 Holds 1 Private Owner Business Owner 1 Property for Rent N Rents 1 M d Owns 1 N M Described In 1 Views Client Owner M M M 1 Lists Advert Newspaper Local Conceptual Data Model

5. Logical Data Model • The local conceptual model is refined to remove data structures that are difficult to implement using RDBMS • When that is done, the model is referred to as the Logical Data Model

6. Logical Data ModelThe Tasks Involved 2.1 Map logical conceptual data model to local logical data model 2.2 Derive relations from local logical data model 2.3 Validate model using normalization 2.4 Validate model against user transac-tion 2.5 Draw entity-relationship diagram 2.6 Define integrity constraints 2.7 Review local logical data model with user

7. 2.1 Map LCD to LD Model • This step is required to • remove M:N relationships • remove complex relationships • remove recursive relationships • remove relationships with attributes • re-examine 1:1 relationships • remove redundant relationships

8. Property_No Client_No M N Client Property for Rent Views (1) Remove M:N relationship • From Fig. 11.1, we can see that Client Views Property_for_Rent has M:N cardinality ratio • Decompose M:N relations into 1:M relations by creating new (usually weak) entities 4

9. (1) Remove M:N relationship • Decompose the Views relation into two one-to-many (1:M) relationships (Attends and Takes) • A new entity Viewing is created

10. Viewing Attends Takes Client Property for Rent Client_No Property_No (1) Remove M:N relationship M M Weak entity 1 1 Weak relationship

11. (2) Remove complex relationships • A Complex relationship involves more than two entity types; ternary or quarter-nary • If there is such a case, decompose the relationship to 1:M (binary) relationships • No complex relationships are found in Fig. 10.8 4

12. Renter_No Renter Staff_No Property_No M M M Leases Staff Property (2) Remove complex relationships • For an example, consider a relation whereby a staff organizes the leasing of a property by a renter

13. Property_No Staff_No Organizes Associated With Staff Property Lease Agreement Holds Renter Renter_No (2) Remove complex relationships • The original relationship can be decomposed as follows:

14. (3) Remove recursive relationships • A Recursive relationship is one in which an entity has a relationship with itself • Remove it to 1:M relationships • In Fig. 10.8, Supervisor Supervises Staff and Secretary Supports Staff are examples of recursive relations 4

15. (3) Remove recursive relationships • These relationships are removed by creat-ing a weak entity called Allocated_Staff • See Fig. 11.2, pg. 330

16. (3) Remove recursive relationships Supports 1 M Secretary 1 Supervisor Supervises M d Allocated Staff 1 Staff 1 Joins Staff_No

17. Staff_No Branch_No M N WorksAt Branch Staff (4) Remove relationships with attributes • If a relationship has attributes associated with it, we should decompose it to iden-tify an entity • Consider the following relationship:

18. Staff_No Branch_No Hours Worked M N WorksAt Branch Staff (4) Remove relationships with attributes • We wish to record the number of hours worked by temporary staff at each branch • The relationship Staff WorksAt Branch has an attribute called Hours_Worked

19. Staff Branch Assigned To Requires Staff_No Branch_No Branch Allocation Hours Worked (4) Remove relationships with attributes • We decompose the WorksAt relationship into an entity called Branch_Allocation 1 1 M M

20. (4) Remove relationships with attributes • In the Client Views Property_for_Rent re-lationship, we are supposed to store the Date_View and Comments about the clients • The Views relationship has already been decomposed from M:N relationship to produce the Viewing entity 4 11

21. Viewing M M Attends Takes 1 1 Date_View Comments Client Property for Rent Client_No Property_No (4) Remove relationships with attributes

22. (5) Re-examine 1:1 relationships • In some cases, entities related through 1:1 relationship can be merged to form a common entity • In Fig. 11.2 a new entity Allocated Staff is in fact the same as Staff • Can we merge these entities? In this case, decide to leave as it is. 17

23. (6) Remove redundant relationships • A relationship is considered redundant if the same information can be obtained from other relationships • In Fig. 10.8, Client Rents Property_for_Rent is a redundant relationship 4

24. (6) Remove redundant relationships • The relationship is already represented through • Client Holds Lease_Agreement • Lease_Agreement Associated With Property_ for_Rent • A client cannot rent a property without first holding a lease agreement for the property 4

25. (6) Remove redundant relationships • Remove Client Rents Property_for_Rent from the data model • See Fig. 11.3, pg. 332

26. 1 Secretary M Supervisor 1 M d Allocated Staff 1 1 M 1 SetsUp Interview Staff M 1 Branch 1 1 Has CarryOut Joins Supports Supervises Organizes 1 1 M 1 M Offers Inspection Viewing Manages With M Lease Agreement Undergoes M M M M Associated With 1 Holds 1 Private Owner Business Owner 1 Property for Rent N Rents 1 M 1 d Owns 1 Takes M Described In 1 Client Owner M M 1 M M 1 Attends Lists Advert Newspaper

27. 1 Secretary M Supervisor 1 M d Allocated Staff 1 1 M 1 SetsUp Interview Staff M 1 Branch 1 1 Has CarryOut Supervises Supports Joins Organizes 1 1 M 1 M Offers Inspection Viewing Manages With M Lease Agreement Undergoes M M M M Associated With 1 Holds 1 Private Owner Business Owner 1 Property for Rent 1 M 1 d Owns 1 Takes Described In 1 Client Owner M M 1 M M 1 Attends Lists Advert Newspaper

28. 2.1 Map LCD to LD Model • We have simplified the local conceptual data model by removing data structures that are difficult to implement in a rela-tional database • By doing so it is correct to say that we have constructed a logical data model • Why can we say so? • Because this model can now be implemented in a database software package

29. 2.2 Derive relations from local logical data model • To represent the entities and relationships described in the user’s view • This relation is represented as the primary key/foreign key mechanism • We have to identify the parent and child entities of a relationship • The parent entity posts a copy of its primary key into the child entity to establish the rela-tion

30. Attributes BRANCH BNo Street Area City PostCode Tel_No Fax_No B5 22 Deer Rd Sidcup London SW1 4EH 0171-8861212 0171-8861214 B7 16 Argyll St. Dyce Aberdeen AB2 3SU 01224-67125 01244-67111 B3 163 Main St. Patrick Glasgow G11 9QX 0141-3392178 0141-3394439 B4 32 Manse Rd. Leigh Bristol BS99 1NZ 0117-9161170 0117-7761114 Primary Key Foreign Key Tuples STAFF SNo FName LName Address Tel_No Sex BNo SL21 John White 19 Taylor St., Cranford, London 0171-884-5112 M B5 Relations SG37 Ann Beech 81 George St., Glasgow 0141-848-3345 F B3 Cardinality SG14 David Ford 63 Ashby St., Patrick, Glasgow 0141-339-2177 M B3 Degree

31. 2.2 Derive relations from local logical data model • At this stage we describe the composition of each relation using the Database Definition Lan-guage (DDL) to: • specify the name of the relation • list the attributes • identify primary/alternate keys and foreign keys • The relation containing the foreign key must also be defined

32. 2.2 Derive relations from local logical data model • As an example consider the Client Attends Viewing and Property for Rent Takes Viewing The relations are as follows: Client (Client_No, FName, LName, Address Tel_No, Pref_Type, Max_Rent) Primary KeyClient_No 22

33. 2.2 Derive relations from local logical data model Property (Property_No, Street, Area, City, Postcode, for Rent Type, Rooms, Rent) Primary Key Property_No Viewing (Property_No, Client_No, Date_View Comments) Primary Key Property_No, Client_No, Date_View Foreign Key Property_No, references Property_for_Rent (Property_No) Foreign Key Client_No references Client (Client_No)

34. 2.2 Derive relations from local logical data model • This process must be repeated for all the entities of the model • Document all relations and foreign keys • See Appendix 11.1, pg. 348 • Update data dictionary to record all key attributes that are identified

35. 2.3 Validate model using normalization • In this step, we validate the composition of the relations derived from the previous step • This is done to ensure that the model is a closer representation of the enterprise model • Particular attention should be given to relations which are not in BCNF

36. 2.3 Validate model using normalization Any repeating attributes? Any non-key attributes which depends on part of the key? • Consider the functional dependencies of Client, Lease_Agreement and Property_for_Rent rela-tion Client_No FName LName Address Tel_No Pref_Type Max_Rent Any non-key attributes which depends on other non-key attributes Any determinants which are not candidate keys? 28

37. 2.3Validate model using normalization • Consider the Property_for_Rent relations Any non-key attributes which depends on other non-key attributes Any determinants which are not candidate keys? Property_No Street Area City Postcode Type Rooms Rent Owner_No Managed_By_Staff_No Branch_No Any non-key attributes which depends on part of the key? Any repeating attributes?

38. 2.3 Validate model using normalization Yes, Rent depends on Property_No Any non-key attributes which depends on other non-key attributes • Consider the Lease_Agreement relations Lease_No Client_No Property_No Rent Payment_Method Deposit_Paid Rent_Start Rent_Finish Any repeating attributes? Any non-key attributes which depends on part of the key?

39. 2.3 Validate model using normalization • It is not necessary to remove the Rent attribute as it has been represented in the Property_for_Rent relation • Just remove it from the Lease_Agreement relation to normalize it to 3NF • The process of validation should be re-peated for all the relations

40. 2.4 Validate model against user transactions • To ensure that the model supports the transactions required by the user view • Try manual transactions using the data model • Transactions can include: • insert new details • delete existing details

41. 2.4 Validate model against user transactions • The second approach involves checking the database transactions • shown in Section 10.1.2, pg. 278, • against the data model of Fig. 11.4, pg. 336 • The model does not provide the pathways for transactions (g) and (h)

42. 2.4 Validate model against user transactions • Solve this problem by creating a relation Branch Registers Client • See Fig. 11.4, pg. 336

43. 2.4 Validate model against user transactions • Turn back to Fig. 11.4, pg. 336 • There are no transactions associated with • Staff SetsUp Interview • Interview With Client • Staff Organizes Lease Agreement

44. 2.4 Validate model against user transactions • Consult with users on these discrepancies and redraw data model • See Fig. 11.5 pg. 337

45. 2.5 Draw ER diagram • Draw an ER diagram of the user’s view which has been validated using • normalization • transactions it must support • This diagram is a refinement of earlier dia-grams • See Fig. 11.5 pg. 337

46. Allocated Staff Has Branch Supervises Supports Joins CarryOut Viewing Inspection Registers Undergoes 1 Local Logical Data Model (Final) Secretary M Supervisor 1 M d 1 1 1 M Staff 1 1 1 M 1 Offers M Manages Lease Agreement M M M M Associated With 1 Holds 1 Private Owner Business Owner M Property for Rent 1 M 1 d Owns 1 Takes Described In 1 Client Owner M M 1 M M 1 Attends Lists Advert Newspaper

47. 2.6 Define integrity constraints • These are controlled conditions imposed on the relations so that the database is al-ways consistent • At this stage we identify what constraints are required, not how to do it • This will then represent a complete and accu-rate user view

48. 2.6 Define integrity constraints • Consider the following five types of cons-traints at this stage: • required data (e.g. Staff have Position) • attribute domain constraints (e.g. M or F) • entity integrity (NOT NULL) • referential integrity (parent-child relation) • enterprise constraints (the business rules)

49. Integrity ConstraintsOn Required Data • Identify the attributes that must contain a valid value at all times • Attributes like Staff_No, Property_No, Name cannot be blanks or nulls • But attributes like Tel_No does not always need to hold a value • See Appendix 11.2, pgs. 349 - 349

50. Integrity ConstraintsOn Attribute Domain • This defines a set of valid values that an attribute may hold • e.g. the set of values for Client_No attributes of the Client entity is a 5-character variable string containing values ranging from CR1 to CR999 • See Appendix 10.4, pg. 326