Lecture 3 Week 3 (10.8.21) - Logical Design PDF

Preview

Summary

Lecture 3notes...

Description

ISYS224 Week 3: Chapter 16

Methodology Conceptual Database Design

Pearson Education © 2014

Chapter 16 - Outline Database design methodology Three main phases • Conceptual design • Logical design • Physical design Constructing Conceptual data model (CDM) Validation of CDM Design documentation 2 Pearson Education © 2014

Design Methodology Structured approach that uses procedures, techniques, tools, documentation aids to support and facilitate process of design https://www.slideshare.net/ramaniak/database-3-conceptual-modeling-and-er

Database Design Methodology Three main phases Conceptual database design Logical database design Physical database design https://www.guru99.com/data-modelling-conceptual-logical.html

Conceptual Database Design Process of constructing a model of data used in enterprise independent of all physical considerations based on specific data model (e.g. relational) but independent of particular DBMS and other physical considerations …

http://www.computingstudents.com/notes/database_systems/logical_conceptual_data_models.php

ion

Physical Database Design Process of producing description of implementation of database on secondary storage Describes: • • • • •

base relations file organizations index design - used to achieve efficient access to data integrity constraints and security measures http://people.cs.aau.dk/~torp/mindmaps/mindmaps.html

Database Design Methodology Conceptual database design Step 1 Build conceptual data model

Step 1.1 Identify entity types Step 1.2 Identify relationship types Step 1.3 Identify and associate attributes with entity or relationship types Step 1.4 Determine attribute domains Step 1.5 Determine candidate, primary, and alternate key attributes Step 1.6 Consider enhanced modeling concepts (optional) Step 1.7 Check model for redundancy Step 1.8 Validate conceptual model against user transactions Step 1.9 Review conceptual data model with user Pearson Education © 2014

7

Overview …

Logical database design for the relational model Step 2 Build and validate logical data model Step 2.1 Derive relations for logical data model Step 2.2 Validate relations using normalization Step 2.3 Validate relations against user transactions Step 2.4 Define integrity constraints Step 2.5 Review logical data model with user Step 2.6 Merge logical data models into global model (optional) Step 2.7 Check for future growth

Pearson Education © 2014

8

Overview …

Physical database design for relational database Step 3 Translate logical data model for target DBMS Step 3.1 Design base relations Step 3.2 Design representation of derived data Step 3.3 Design general constraints http://people.cs.aau.dk/~torp/mindmaps/mindmaps.html

Overview …

Step 4 Design file organizations and indexes Step 4.1 Step 4.2 Step 4.3 Step 4.4

Analyze transactions Choose file organization Choose indexes Estimate disk space requirements

http://people.cs.aau.dk/~torp/mindmaps/mindmaps.html

Overview …

Step 5 Design user views Step 6 Design security mechanisms Step 7 Consider introduction of controlled redundancy Step 8 Monitor and tune operational system http://people.cs.aau.dk/~torp/mindmaps/mindmaps.html

Step 1 Build Conceptual Data To build conceptual data model of data requirements of enterprise Model comprises entity types, relationship types, attributes and attribute domains, primary and alternate keys, and integrity constraints

Step 1.1 Identify entity types To identify required entity types

Step 1.2 Identify relationship types To identify important relationships between entity types

Pearson Education © 2014

12

Extract from data dictionary for Staff user views of DreamHome showing description of entities

13

First-cut ER diagram for Staff user views of DreamHome

Pearson Education © 2014

14

Extract from data dictionary for Staff user views of DreamHome showing description of relationships

Pearson Education © 2014

15

Step 1 Build Conceptual Data Step 1.3 Identify and associate attributes with entity or relationship types Associate attributes with appropriate entity or relationship types and document details of each attribute

Step 1.4 Determine attribute domains Determine domains for attributes in data model and document details of each domain

https://en.wikipedia.org/wiki/Attribute_domain

Extract from data dictionary for Staff user views of DreamHome showing description of attributes

Pearson Education © 2014

17

Step 1 Build Conceptual Data

Step 1.5 Determine candidate, primary, and alternate key attributes Identify candidate key(s) for each entity and if >1 candidate key, choose one to be primary key - others as alternate keys

Step 1.6 Consider use of enhanced modeling concepts (optional)? Consider use of enhanced modeling concepts? such as specialization/generalization, aggregation and composition

Pearson Education © 2014

ER diagram for Staff user views of DreamHome with primary keys added

Pearson Education © 2014

19

Revised ER diagram for Staff user views of DreamHome with specialization / generalization

Pearson Education © 2014

20

Step 1 Build Conceptual Data Model Step 1.7 Check model for redundancy Check for presence of redundancy in model and remove any that exist

Step 1.8 Validate conceptual model against user transactions Ensure conceptual model supports required transactions

Step1.9 Review conceptual data model with user Review conceptual data model with user to ensure model is ‘true’ representation of data requirements of enterprise

https://www.slideshare.net/correctsystems/ pragmatic-model-checking-from-theory-to-implementations

Pearson E

Example of removing redundant relationship called Rents

Pearson Education © 2014

22

Example of non-redundant relationship FatherOf

Pearson Education © 2014

23

Using pathways to check conceptual model supports user transactions Transaction (d): List the details of properties managed by a named member of staff at the branch

Sample ERD Exercise Manufacturing company: A manufacturing company produces products. The following product information is stored: product name, product ID and quantity on hand. These products are made up of many components. Each component can be supplied by one or more suppliers. The following component information is kept: component ID, name, description, suppliers who supply them, and products in which they are used.

Pearson Education © 2014

25

Sample ERD Exercise Assumptions: A supplier can exist without providing components. A component does not have to be associated with a supplier. A component does not have to be associated with a product. Not all components are used in products. A product cannot exist without components.

Exercise: Identify the multiplicity constraints https://opentextbc.ca/dbdesign01/back-matter/appendix-b-erd-exercises/ Pearson Education © 2014

26

Sample ERD Exercise Manufacturing company:

Exercise: Identify the attribute data types & domains https://opentextbc.ca/dbdesign01/back-matter/appendix-b-erd-exercises/ Pearson Education © 2014

27

ISYS224 Week 3: Chapter 17 Methodology Logical Database Design (LDM) for Relational Model

Pearson Education © 2014

Chapter 17 - Outline

Build a logical data model (LDM) from CDM Validate LDM to ensure • accords data requirements • supports all transactions

Merge local LDMs into global model Global LDM true and accurate representation of data requirements? https://en.wikipedia.org/wiki/Logical_data_model

Step 2 Build and Validate LDM Translate conceptual data model into logical data model validate this model to check: structurally correct using normalization supports required transactions

https://en.wikipedia.org/wiki/Logical_data_model

Step 2 Build and Validate LDM Step 2.1 Derive relations for logical data model Create relations for logical data model to represent entities, relationships and attributes identified

Pearson Education © 2014

31

Sample ERD Exercise Manufacturing company:

Translate the conceptual data model into the logical data model https://opentextbc.ca/dbdesign01/back-matter/appendix-b-erd-exercises/ Pearson Education © 2014

32

Sample ERD Exercise Manufacturing company:

https://opentextbc.ca/dbdesign01/back-matter/appendix-b-erd-exercises/ Pearson Education © 2014

33

Conceptual data model for Staff view showing all attributes

Step 2.1 Derive relations for LDM (1)

Strong entity types For each strong entity in data model, create a relation that includes all simple attributes of that entity For composite attributes, include only constituent simple attributes

Staff (staffNO, fName, lName, position, sex, DOB) Primary key staffNo

Pearson Education © 2014

35

Step 2.1 Derive relations for LDM (2) Weak entity types For each weak entity in data model, create a relation that includes all the simple attributes of that entity Primary key of a weak entity is partially or fully derived from each owner entity and so identification of primary key of a weak entity cannot be made until after all relationships with owner entities have been mapped Preference (preType, maxRent) Primary key None(at present)

Pearson Education © 2014

Step 2.1 Derive relations for LDM (3) One-to-many (1:*) binary relationship types For each 1:* binary relationship, entity on ‘one side’ of relationship = parent entity entity on ‘many side’ = child entity To represent this relationship - post copy of primary key attribute(s) of parent entity into relation representing child entity - to act as foreign key

Pearson Education © 2014

37

Step 2.1 Derive relations for LDM (4) One-to-one (1:1) binary relationship types Creating relations to represent 1:1 relationship is more complex as cardinality cannot be used to identify the parent and child entities in relationship Instead, participation constraints used to decide whether best to represent relationship by combining entities involved into one relation or creating two relations and posting copy of primary key from one relation to other Consider following

(a) mandatory participation on both sides of 1:1 relationship (b) mandatory participation on one side of 1:1 relationship (c) optional participation on both sides of 1:1 relationship

Pearson Education © 2014

38

Step 2.1 Derive relations for LDM (a) Mandatory participation on both sides of 1:1 relationship Combine entities involved into one relation and choose one of primary keys of original entities - to be primary key of new relation while other (if one exists) used as alternate key

Pearson Education © 2014

39

Step 2.1 Derive relations for LDM (b) Mandatory participation on one side of 1:1 relationship Identify parent and child entities using participation constraints Entity with optional participation - parent entity Entity with mandatory participation - child entity (remember existence dependencies – child must have parent) Copy of primary key of parent entity placed in relation representing child entity If relationship has >=1 attributes, these attributes should follow posting of primary key to child relation

Pearson Education © 2014

Step 2.1 Derive relations for LDM (c) Optional participation on both sides of a 1:1 relationship In this case - designation of parent and child entities arbitrary unless can find out more about relationship to make decision one way or other

Pearson Education © 2014

41

Step 2.1 for LDM (5) Oneto-oneDerive (1:1) recursiverelations relationships For 1:1 recursive relationship, follow rules for participation as described above for 1:1 relationship. mandatory participation on both sides represent recursive relationship as single relation with two copies of primary key mandatory participation on only one side option to create single relation with two copies of primary key, or to create new relation to represent relationship new relation would only have two attributes, both copies of primary key as before - copies of primary keys act as foreign keys - have to be renamed to indicate purpose of each in relation optional participation on both sides again create new relation as described above

Pearson Educa

Step 2.1 Derive relations for LDM (6) Superclass/subclass relationship types Identify superclass entity as parent entity and subclass entity as child entity Selection of most appropriate option dependent on number of factors such as dis-jointness and participation constraints on superclass/subclass relationship whether subclasses involved in distinct relationships number of participants in superclass/subclass relationship

Pearson Education © 2014

43

Representation of superclass/subclass relationship based on participation and disjointness

4 combinations: {Mandatory, And} {Optional, And} {Mandatory, Or} {Optional, Or}

Pearson Education © 2014

Representation of superclass/subclass relationship based on participation and disjointness

45

Step 2.1 Derive relations for LDM (7) Many-to-many (*:*) binary relationship types Create relation to represent relationship and include any attributes part of relationship Post copy of primary key attribute(s) of entities that participate in relationship into new relation - as foreign keys these foreign keys will also form primary key of new relation, possibly in combination with some attributes of relationship

Pearson Education © 2014

Step 2.1 Derive relations for LDM (8) Complex relationship types Create relation to represent relationship and include any attributes as part of relationship Post copy of primary key attribute(s) of entities that participate in complex relationship into new relation - as foreign keys any foreign keys that represent ‘many’ relationship (for example, 1..*, 0..*) generally also form primary key of this new relation - possibly in combination with some attributes of relationship

Pearson Education © 2014

47

Step 2.1 Derive relations for LDM (9) Multi-valued attributes Create new relation to represent multi-valued attribute and include primary key of entity in new relation - as foreign key Unless multi-valued attribute is itself alternate key of entity primary key of new relation is combination of multi-valued attribute and primary key of entity

Pearson Education © 2014

48

Summary of how to map entities and relationships to relations

Relations for Staff user views of DreamHome

Pearson Education © 2014

50

Step 2.2 Validate relations using normalization Validate relations in LDM using normalization Ensure set of relations has minimal yet sufficient number of attributes to support data requirements Minimal data redundancy Allow some redundancy to avoid joins (caution!)

http://people.cs.aau.dk/~torp/mindmaps/mindmaps.html

Step 2.3 Validate relations against user transactions To ensure relations in logical data model support required transactions

http://people.cs.aau.dk/~torp/mindmaps/mindmaps.html

Step 2.4 Check integrity constraints Check integrity constraints represented in LDM Includes identifying: Required data Attribute domain constraints Multiplicity Entity integrity Referential integrity General constraints

https://slideplayer.com/slide/5096711 /

Pearson E

Referential integrity constraints for relations in Staff user views of DreamHome

Step 2.5 Review LDM with user Review LDM with user(s)

to ensure they consider model true representation of data requirements of enterprise

http://dinesql.blogspot.com/2015/10/types-of-database-end-users.html

Pearson Education © 2014

Step 2.6 LDMs into global Model (optional) Merge LDMs into single global LDM that represents all user views of database

https://www.tutorialcup.com/dbms/er-data-model.htm

Step 2.6.1 Merge local LDMs into global model Merge local LDM into single global logical data model Activities include: Step 2.6.1 Merge local logical data models into global model Step 2.6.2 Validate global logical data model Step 2.6.3 Review global logical data model with users

Pearson Education © 2014

57

Step 2.6.1 Merge LDMs into global model Tasks typically includes: (1) Review names and contents of entities/relations and their candidate keys (2) Review names and contents of relationships/foreign keys (3) Merge entities/relations from local data models (4) Include (without merging) entities/relations unique to each local data model (5) Merge relationships/foreign keys from local data models (6) Include (without merging) relationships/foreign keys unique to each local data model (7) Check for missing entities/relations and relationships/foreign keys (8) Check foreign keys (9) Check Integrity Constraints (10) Draw global ER/relation diagram (11) Update documentation …. (remember for your project units next year!)

Pearson Education © 2014

58

Step 2.6.2 Validate global LDM Validate relations created from global LDM using technique of normalization and ensure they support required transactions - if necessary

https://gerardnico.com/data/modeling/normalization

Pearson Education © 2014

59

Step 2.6.3 Review global LDM with user(s) Review global LDM with users to ensure they consider model true representation of data requirements of enterprise

https://gcn.com/articles/2017/01/19/cloud-users-priority.aspx

Relations for Branch user views of DreamHome

Relations that represent global LDM for DreamHome

Global relation diagram – DreamHome...