Logical Database Design

1. Logical Database Design Nazife Dimililer

2. II - Logical Database Design • Two stages • Building and validating local logical model • Building and validating global logical model The local model represents different (or individual) user views What payroll sees vs. what the accounting sees The global model represents ALL user views What will be the requirements of all departments combined?

3. Stage 1: Local logical model(Step 2)(for relational model) 1. Remove features not compatible with the model you choose (relational model) 2. Derive relations for local logical model 3. Validate relations using normalization 4. Validate relations against user transactions 5. Define integrity constraints 6. Review local logical data model with the user

4. Stage 2: Build and validate global logical data model (Step 3) 1. Merge local logical data models into global model 2. Validate global logical data model 3. Check for future growth 4. Review global logical data model with the users

5. 2.1. Remove features not compatible with the relational model Objective: Refine the local conceptual data model to remove undesirable/unsupported features to map to local logical model • Remove M:N Relationships • Remove complex Relationships • Remove relationships with attributes • Remove multivalued Attributes • Remove complex Attributes • Remove redundant relationships • Remove inheritance relationships • Re-examine 1:1 relationships

6. 2.1.i. Remove M:N Relationships Example 1

7. 2.1.i. Remove M:N Relationships Example 1 Solution

8. 2.1.i. Remove M:N Relationships Example 2 Solution?

9. 2.1.i. Remove M:N Relationships Solution? Solution 1 PK of the invoiceline entity is {invoiceId,ProductId}. This means that the same product cannot appear twice on an invoice! In supermarkets, when you buy two of the same product, each of them may be ringed separately. Therefore this solution is not suitable for supermarkets.

10. 2.1.i. Remove M:N Relationships Another problem with the previous solution is that, if you need to print (select from) the contents of the invoiceline entity, you may get a different order every time. But when it comes to invoices, the order is important and every print-out must look exactly identical. For this reason and to solve the problem mentioned before, it is customary to add lineno attribute to invoiceline entity. Please notice that the invoice line entity will not depend on the product entity in this case. Solution 2

11. 2.1.ii Remove complex Relationships Example 1

12. 2.1.ii Remove complex Relationships Solution?

13. 2.1.ii Remove complex Relationships Solution

14. 2.1.iii Remove Relationships with Attributes • Relationships with Attributes are usually M:N or complex relationships. We dealt with these in step 2.1.i and step 2.1.ii • If a M:1 relationship contains an attribute move the attribute in the same direction as the Foreign Key. • If a 1:1 relationship has an attribute move it to either of the participating entities

15. 2.1.iii. Remove Relationships with Attributes Example 1 Is the relationship really 1:M? Foreign Key Moves in This Direction Solution

16. 2.1.iii. Remove Relationships with Attributes Example Is the relationship really 1:1? Solution OR

17. 2.1.iv. Remove Multivalued Attributes Assumptions: Each student has only two parents: mother and father Store as many phone numbers for each student as needed! Example Solution

18. 2.1.iv. Remove Multi-valued Attributes Enhancing the previous solution : Add new attributes/entities/relationships Another solution : Assume that two students may use the same phone number

19. 2.1.v. Remove Complex Attributes If the complex attribute may be shared by one or more entities consider representing it as a separate entity Example Solution

20. 2.1.vi. Remove redundant relationships Example Solution

21. 2.2. Derive relationships from the logical data model Objective : derive tables to represent entities, relationships, and attributes defined in the user view. The local logical model at this step includes only the following • Strong entities • Weak entities • Strong 1:M Binary relationships • Strong 1:1 Binary Relationships • Identifying (Weak) relationships Document Relations and Foreign Key attributes

22. 2.2 Validate Model Using Normalization • Normalization is a procedure for deciding which attributes belong together in an entity type. • Normalization forces us to understand each attribute represented in the database completely. • Normalization minimizes redundancy and thus update and deletion anomalies. • Derive functional dependencies and normalize all relations up to BCNF! There are arguments against normalization Some times you need to Denormalize

23. Denormalization • Transforming normalized relations into unnormalized physical record specifications • Benefits: • Can improve performance (speed) be reducing number of table lookups (i.e reduce number of necessary join queries) • Costs (due to data duplication) • Wasted storage space • Data integrity/consistency threats • Common denormalization opportunities • One-to-one relationship • Many-to-many relationship with attributes • Reference data (1:N relationship where 1-side has data not used in any other relationship)

24. 2.4 Validate against user transactions Objective: To ensure that the local logical data model supports the transactions required by the user view There are two approaches: • Check that all information (entity, relationship, attribute) required by the transaction is provided by the model by documenting a verbal description of the transaction • Represent the pathway taken by the transaction diagrammatically on the ERD

25. 2.5 Draw Entity Relationship Diagram • Draw a final ERD that is the local logical representation of the data given in a user’s view of the enterprise.

26. 2.6 Define Integrity Constraints • Integrity constraints are the constraints that we need to impose in order to protect the database from becoming inconsistent. • The following constraints must be included in your design: • Required Data • Attribute Domain Constraints • Entity Integrity • Referential Integrity • Enterprise Consttraints

27. 2.6 Define Integrity constraints II • We have dealt with the required data, attribute domain and entity integrity constraints before. Revise the design to make sure they are correct and complete • Referential Integrity constraint must be further qualified to include existence constraint. • Existence constraint defines conditions under which a unique key or foreign key may be inserted, deleted or updated.

28. 2.6 Define Integrity constraints III A foreign key definition should include one of the following : • No action : Do not allow • Cascade : Cascade the changes to the children • Set Null : set the value of the children to null • Set Default : Set the value of the children to a default value • No Check : Do not heck any thing (This is not a desirable option)

29. 2.6 Define Integrity constraints IV • Enterprise constraints are also known as business rules. • Constrained by enterprise rules governing the “real world” transactions • Usually include more than one entity or a complex rule based on aggregate values. • Document of all the integrity constraints. This an important step in the logical design process!!

30. 2.7 Review local logical data model with user. Objective: To ensure that the local logical data model is a true representation of the user’s view.

31. Stage 2: Build and Validate Global Logical Data Model(Step 3) Objective: Combine individual local logical data models into a single global logical data model representing the part of the enterprise we are interested in modeling This is stage 2 in the Logical design and Step 3 of database design.

32. 3.1 Merge local logical data models into global model Objective : Merge local logical data models into global model Some typical tasks • Review the names and contents of entities/relations and their candidate keys • Review the names and contents of relationships and foreign keys • Merge entities (relations) from the local models • Include, without merging, entities (relations) unique to each local data model • Merge relationships (foreign keys) form the local data models • Include (without merging) relationships (foreign keys) • Check for missing entities (relations) and relationships (foreign keys) • Check foreign keys • Check integrity constraints • Draw global ER diagram • Update documentation

33. 3.2. Validate global logical data model Validate global logical data model • Validate the global logical data model using normalization and against the required transactions. • Carefully inspect the boundaries!

34. 3.3 Check for future growth Objective : To determine whether there any significant changes likely in the foreseeable future and to assess whether the global logical data model can accommodate these changes? Develop a model • that is extensible, • that can evolve with minimal effort • That can evolve with minimal effect on existing users

35. 3.4 Draw Final Entity Relationship Diagram After the validation we have a final ERD representing the data requirements of the part of enterprise to be modeled. All the other documentation should also be updated to reflect any changes.

36. 4. Review global logical data model with users Objective: To ensure that the global logical data model is a true representation of the entreprise.

37. Final Word • Remember that the goal of the DB development is to produce a DB that provides an important resource for an organization. • The DB should be designed so that it can serve the customers and other team members efficiently.