1 / 23

Chapter 9

Chapter 9. Methodology - Logical Database Design. Chapter 16 in Textbook. Objectives. Remove features from a local conceptual model that are not compatible with the relational model. Map the conceptual model into relations. Validate these relations using the technique of normalization.

akeem-andrews
Download Presentation

Chapter 9

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 9 Methodology - Logical Database Design Chapter 16 in Textbook

  2. Objectives • Remove features from a local conceptual model that are not compatible with the relational model. • Map the conceptual model into relations. • Validate these relations using the technique of normalization. • Validate a logical data model to ensure it supports required user transactions. • Merge local logical data models based on specific views into a global logical data model of the enterprise. • Ensure that resultant global model is a true and accurate representation of enterprise.

  3. Step 2 Build and Validate Local Logical Data Model Step 2.1 Remove features not compatible with the relational model Step 2.2 Derive relations for local logical data model Step 2.3 Validate relations using normalization Step 2.4 Validate relations against user transactions Step 2.5 Define integrity constraints Step 2.6 Review local logical data model with user

  4. Step 2.1 Remove features not compatible with the relational model This involves: • remove *:* recursive relationship types; • remove complex relationship types; • remove multi-valued attributes.

  5. Remove *:* Recursive Relationship Types supervisor supervise (0,*) STAFF supervisee (0,*) STAFF STAFF supervise (0,*) (0,*)

  6. Remove Complex Relationship Types BRANCH STAFF register CLIENT BRANCH register REGISTER process STAFF agree CLIENT

  7. Step 2.2 Derive relations for local logical data model Objective: To create relations for the local logical data model to represent the entities, relationships, and attributes that have been identified. • Strong entity types. • Weak entity types. • 1:1 binary relationship types. • 1:M relationship types. • 1:1 recursive relationship types. • Superclass/Subclass relationship types. • M:N relationship types. Document relations and foreign key attributes.

  8. Relations for the Staff View of DreamHome Staff (staffNo, FName, Lname, position, sex, DOB, SupervisorStaffNo) Foreign Key SupervisorStaffNo reference Staff(StaffNo) BusinessOwner (OwnerNo, BName, Btype, ContactName, address, TelNo) Alternate Key bName Alternate Key TelNo Lease (LeaseNo, PaymentMethod, depositPaid, RentStart, RentFinish, ClientNo, PropertyNo) Alternate Key PropertyNo, RentStart Foreign Key ClientNo refrences Client(ClientNo) Foreign Key PropertyNo refrences Client(ClientNo) Derived deposit (PropertForRent.rent * 2) Derived duration (RentFinish -RentStart)

  9. Step 2 Build and Validate Local Logical Data Model Step 2.3 Validate relations using normalization Objective: To ensure that each relation is in at least BCNF. Step 2.4 Validate relations against user transactions Objective: To ensure that the relations in the local logical data model support the transactions required by the view.

  10. Step 2 Build and Validate Local Logical Data Model Step 2.5 Define integrity constraints Integrity constraints protects DB from being inconsistent. Types of integrity constraints: • Required data. • Entity integrity. • Referential integrity. • Attribute domain. • Enterprise constraints.

  11. Step 2 Build and Validate Local Logical Data Model In testing referential integrity check the following cases: • Insert tuple into child relation. • Delete tuple from child relation. • Update FK of child tuple. • Insert tuple into parent relation. • Delete tuple from parent relation. • Update PK of parent tuple.

  12. Referential Integrity Constraints for Relations Staff (staffNo, FName, Lname, position, sex, DOB, SupervisorStaffNo) Foreign Key SupervisorStaffNo reference Staff(StaffNo) ON UPDATE CASCADE ON DELETE SET NULL BusinessOwner (OwnerNo, BName, Btype, ContactName, address, TelNo) Alternate Key bName Alternate Key TelNo Lease (LeaseNo, PaymentMethod, depositPaid, RentStart, RentFinish, ClientNo, PropertyNo) Alternate Key PropertyNo, RentStart Foreign Key ClientNo refrences Client(ClientNo) ON UPDATE CASCADE ON DELETE NO ACTION Foreign Key PropertyNo refrences Client(ClientNo) ON UPDATE CASCADE ON DELETE NO ACTION Derived deposit (PropertForRent.rent * 2) Derived duration (RentFinish -RentStart)

  13. Step 2 Build and Validate Local Logical Data Model Step 2.6 Review local logical data model with user Objective: To ensure that the local logical data model and supporting documentation that describes the model is a true representation of the view.

  14. Step 3 Build and validate global logical data model • Combine the individual local logical data models into a single global logical data model that represents the enterprise. • Step 3.1 Merge local logical data models into global model • Step 3.2 Validate global logical data model • Step 3.3 Check for future growth • Step 3.4 Review global logical data model with users

  15. Step 3.1 Merge local logical data models into global model 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 models. 4. Include (without merging) entities/relations unique to each local model. 5. Merge relationships/FK from the local models. 6. Include (without merging) relationships/FKs unique to each local 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.

  16. 1. Review the names & contents of entities/relations and their CK Staff View Entity Type CK Branch View Entity Type CK Staff StaffNo PrivateOwner OwnerNo BusinessOwner Bname telNo OwnerNo Client ClientNo PropertyForRent Propertyno Viewing ClientNo, PropertyNo Branch BranchNo PostCode Telephone TelNo Staff StaffNo Manager StaffNo PrivateOwner OwnerNo BusinessOwner Bname telNo Client ClientNo PropertyForRent Propertyno Lease LeaseNo PropertyNo RentStart ClientNo, RentStart

  17. 2. Review the names & contents of relationships/FK Staff View FK Relationship Relation Branch View FK Relationship Branch Telephone Staff Manager PrivateOwner BusinessOwner Client PropertyForRent Viewing mgrStaffNo -> Manager(StaffNo) Manages branchN -> Branch(branchNo) supervisorStaffNo -> Staff(staffNo) Supervisor branchNo -> Branch(branchNo) StaffNo -> Staff(StaffNo) OwnerNo -> PrivateOwner(ownerNo) Owns OwnerNo -> Owns BusinessOwner(ownerNo) StaffNo -> Staff(StaffNo) Oversees BranchNo -> Branch(BranchNo) Offers supervisorStaffNo -> Staff(staffNo) Supervisor StaffNo -> Staff(StaffNo) Registers OwnerNo -> PrivateOwner(ownerNo) POwns OwnerNo -> BOwns BusinessOwner(ownerNo) StaffNo -> Staff(StaffNo) Manages

  18. 3. Merge entities/relations from the local models 1. 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)

  19. 3. Merge entities/relations from the local models 2. Merge entities/relations with the same name but different PK. Branch View Staff View BOWNER(bName, bType, address) BOWNER(Ono, bName, bType, address) AK telNo AK bName, telNo Global View BOWNER(Ono, bName, bType, address) AK bName, telNo 3. Merge entities/relations with the different names using the same or different PK.

  20. 4. Include (without merging) entities/relations unique to each local model • The Previous task should identify all entities/relations that are the same. • All remaining entities/relations are included in the global model without change.

  21. 5. Merge relationship/FK from the local models 1. Merge relationship/FKs with the same name & the same purpose. 2. Merge relationship/FKs with different names but the same purpose.

  22. 6. Include (without merging) relationships/FKs unique to each local model • Again, the Previous task should identify all relationships/FKs that are the same. • All remaining relationships/FKs are included in the global model without change.

  23. Step 3 Build and Validate Global Logical Data Model Step 3.2 Validate global logical data model Objective: To validate the relations created from the global logical data model using the technique of normalization and to ensure they support the required transactions, if necessary. Step 3.3 Check for future growth Objective: To determine whether there are any significant changes likely in the foreseeable future and to assess whether the global logical data model can accommodate these changes. Step 3.4 Review global logical data model with users Objective: To ensure that the global logical data model is a true representation of the enterprise.

More Related