Modelling Methodologies Chapter 16 17 18 Database Design

Modelling Methodologies Chapter 16, 17, 18

Database Design Hardware independent Software independent Conceptual DB design Hardware independent Software dependent Logical DB design Hardware dependent Software dependent Physical DB design Modeling Methodologies 2

Design Methodology Overview Step 1 Build conceptual data model. Conceptual DB design Step 2 Build and validate logical data model. Logical DB design Step 3 Translate logical data model for target DBMS. Step 4 Design file organizations and indexes. Step 5 Design user views. Step 6 Design security mechanisms. Physical DB design Step 7 Consider the introduction of controlled redundancy. Step 8 Monitor and tune the operational system. Modeling Methodologies 4 4

Database Design Hardware independent Software independent Conceptual DB design Hardware independent Software dependent Logical DB design Hardware dependent Software dependent Physical DB design Modeling Methodologies 5

Step 1 Build Conceptual Data Model for Each View Objective: To build a conceptual data model of an enterprise for each specific view. Step 1. 1 Identify entity types Objective: To identify the main entity types that are required by the view; Document entity types in data dictionary. Data Dictionary for Staff View Showing Description of Entities Entity Name Staff Description Aliases General term describing all staff employed by Dearm. Home Property. For. Rent General term describing all property for rent Employee Property Modeling Methodologies Occurrence Each member of staff works at one particular branch Each property has a single owner & is available at one specific branch, where the property is managed by 1 member of staff. A property is viewed by many clients and rented by a single client at a time 6

Step 1 Build Conceptual Data Model for Each View Step 1. 2 Identify relationship types Objective: To identify the important relationships that exist between the entity types that have been identified. Modeling Methodologies 7

ERD for Staff View of Dream. Home Modeling Methodologies 8

Step 1 Build Conceptual Data Model for Each View Data Dictionary for Staff View Showing Description of Relationships Entity Name Multiplicity Relationship Entity Name Multiplicity Staff 0. . 1 Manages Supervises Property. For. Rent Staff 0. . 100 0. . 10 Property. For. Rent 1. . 1 Associated. With Lease 0. . * Modeling Methodologies 9

Step 1 Build Conceptual Data Model for Each View Step 1. 3 Identify and associate attributes with entity or relationship types Objective: To identify and associate attributes with the appropriate entity or relationship types and document the details of each attribute. Step 1. 4 Determine attribute domains Objective: To determine domains for the attributes in the data model and document the details of each domain. Modeling Methodologies 10

Step 1 Build Conceptual Data Model for Each View Data Dictionary for Staff View Showing Description of Attributes Entity Attribute Name Staff. No Description Data Type Uniquely identifies a staff member varchar Length Nulls Multi- Default Range Valued Value PK CK 5 No No Y Y name FName First name of Staff varchar 15 No No Lname Last name of Staff varchar 15 No No position Job title of member of staff varchar 10 No No sex Gender of member of staff character 1 Yes No DOB Date of Birth of member of staff Date Yes No Modeling Methodologies M F, M 1960 -1990 11

Step 1 Build Conceptual Data Model for Each View Step 1. 5 Determine candidate and primary key attributes Objective: To identify the candidate key(s) for each entity and if there is more than one candidate key, to choose one to be the primary key. Modeling Methodologies 12

ERD for Staff View of Dream. Home with PK Modeling Methodologies 13

Step 1 Build Conceptual Data Model for Each View Step 1. 6 Consider use of enhanced modeling concepts Objective: To consider the use of enhanced modeling concepts, such as specialization / generalization, aggregation, and composition. Modeling Methodologies 14

ERD for Staff View of Dream. Home with specialization / generalization Modeling Methodologies 15

Step 1 Build Conceptual Data Model for Each View Step 1. 7 Check model for redundancy Objective: To check for the presence of any redundancy in the model. 1. Re-examine 1: 1 relationships. 2. Remove redundant relationships. Step 1. 8 Validate conceptual model against user transactions Objective: To ensure that the conceptual model supports the transactions required by the view. Describe the transaction. Use transaction pathways: diagrammatically represent the pathway taken by each transaction on the ERD. Modeling Methodologies 16

Using transaction’s Pathways Modeling Methodologies 17

Step 1 Build Conceptual Data Model for Each View Step 1. 9 Review conceptual data model with user Objective: To review the conceptual data model with the user to ensure that the model is a ‘true’ representation of the user’s view of the enterprise. Modeling Methodologies 18

Database Design Hardware independent Software independent Conceptual DB design Hardware independent Software dependent Logical DB design Hardware dependent Software dependent Physical DB design Modeling Methodologies 19

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) 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 Step 2. 7 Check for future growth Modeling Methodologies 21

Referential Integrity Constraints for Relations Modeling Methodologies 24

Step 2. 6. 1 Merge logical data models into global model (optional step) 1. Review the names & contents of entities/relations and their CK. 2. Review the names & contents of relationships/FK. 3. Merge entities/relations from the local data models. 4. Include (without merging) entities/relations unique to each local data model. 5. Merge relationships/FK from the local data models. 6. Include (without merging) relationships/FKs unique to each local data model. 7. Check the missing entities/relations and relationships/FKs. 8. Check FKs. 9. Check integrity constraints. 10. Draw the global ER/relation diagram. 11. Update the documentation. Modeling Methodologies 27 27

1. Review the names & contents of entities/relations and their CK Staff View Branch View Entity Type Branch Telephone Staff Manager Private. Owner Business. Owner Client Property. For. Rent Lease CK Branch. No Post. Code Tel. No Staff. No Owner. No Bname tel. No Client. No Propertyno Lease. No Property. No Rent. Start Client. No, Rent. Start Modeling Methodologies Entity Type CK Staff. No Private. Owner Business. Owner. No Bname tel. No Owner. No Client. No Propertyno Client. No, Property. No Client Property. For. Rent Viewing 28

2. Review the names & contents of relationships/FK Relation FK Branch View Relationship Branch mgr. Staff. No -> Manager(Staff. No) Telephone branch. N -> Branch(branch. No) Staff supervisor. Staff. No -> Staff(staff. No) Manager branch. No -> Branch(branch. No) Private. Owner Staff. No -> Staff(Staff. No) FK Staff View Relationship Manages Supervisor supervisor. Staff. No -> Staff(staff. No) Supervisor Staff. No -> Staff(Staff. No) Registers Business. Owner Client Property. For. Rent Owner. No -> Private. Owner(owner. No) Owns Owner. No -> Private. Owner(owner. No) POwns Owner. No -> BOwns Business. Owner(owner. No) Viewing Business. Owner(owner. No) Staff. No -> Staff(Staff. No) Oversees Branch. No -> Branch(Branch. No) Offers Modeling Methodologies Staff. No -> Staff(Staff. No) Manages 29

3. Merge entities/relations from the local data models Merge entities/relations with the same name & the same PK. Branch View Staff View PRIVATE OWNER(Ono, name, address) PRIVATE OWNER(Ono, fname, lname, address) Global View PRIVATE OWNER(Ono, fname, lname, address) Modeling Methodologies 30

3. Merge entities/relations from the local data models Merge entities/relations with the same name but different PK. Branch View Staff View BOWNER(b. Name, b. Type, address, tel. NO) BOWNER(Ono, b. Name, b. Type, address, tel. No) AK tel. No AK b. Name, tel. No Global View BOWNER(Ono, b. Name, b. Type, address) AK b. Name, tel. No Modeling Methodologies 31

Relations for the Branch user views of Dream. Home 34 Pearson Education © 2009

Relations that represent the global logical data model for Dream. Home 35 Pearson Education

Global relation diagram for Dream. Home 36 Pearson Education © 2009

Step 2. 7 Check for future growth Objective: To determine whethere any significant changes likely in the foreseeable future and to assess whether the global logical data model can accommodate these changes. 37 Pearson Education © 2009

Database Design Hardware independent Software independent Conceptual DB design Hardware independent Software dependent Logical DB design Hardware dependent Software dependent Physical DB design Modeling Methodologies 38

Comparison of Logical and Physical Database Design Logical database design is concerned with the what, Physical database design is concerned with the how. Process of producing a description of the implementation of the database on secondary storage; it describes the base relations, file organizations, and indexes used to achieve efficient access to the data, and any associated integrity constraints and security measures. Physical DB Design Modeling Methodologies 39 39

Overview of Physical Database Design Methodology 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 Step 4 Design physical representation Step 4. 1 Analyze transactions Step 4. 2 Choose file organizations Step 4. 3 Choose indexes Step 4. 4 Estimate disk space requirements Step 5 Design user views Step 6 Design security mechanisms Step 7 Consider the introduction of controlled redundancy Step 8 Monitor and tune the operational system Modeling Methodologies 40

Step 3. 1 Design Base Relations Objective: To decide how to represent base relations identified in logical model. - For each relation, need to define: the name of the relation; a list of simple attributes in brackets; the PK and, where appropriate, AKs and FKs. referential integrity constraints for any FKs identified. - For each attribute, need to define: its domain, consisting of a data type, length, and any constraints on the domain; an optional default value for the attribute; whether the attribute can hold nulls. whether it is derived, and if so, how it should be computed. Modeling Methodologies 42

Property. For. Rent Relation Modeling Methodologies 43 43

Step 3. 2 Design Representation of Derived Data Objective: To decide how to represent any derived data present in the global logical data model in the target DBMS. Derived attribute can be stored in database or calculated every time it is needed. Option selected is based on: additional cost to store the derived data and keep it consistent with operational data from which it is derived; cost to calculate it each time it is required. Modeling Methodologies 44