LOGICAL DATABASE DESIGN

1. LOGICAL DATABASE DESIGN

2. Starting Point --> Local Conceptual Models • Database design methodology: conceptual, logical and physical database design. • Logical database Design: • The process of constructing a model of information used in an enterprise based on a specific data model, but independent of a particular DBMS or other physical considerations. • Step2: Build and validate local logical data model for each user view. • Step3: Build and validate global logical data model

3. Step2: Build and Validate Local Logical Data model for each user view. • Step2.1: Map local conceptual data model to local logical data model. • Step2.2: Derive relations from logical data model. • Step2.3: Validate model using Normalization. • Step2.4: Validate model against user transactions • Step2.5: Draw E-R Diagram. • Step2.6: Define integrity constraints • Step2.7: Review local logical data model with user.

4. Step2.1: Map local conceptual data model to local logical data model. • Objectives: • Remove M:N relationships. • Remove complex relationships. • Remove recursive relationships • Remove relationships with attributes • Remove multi-valued attributes • Re-examine 1:1 relationships • Remove redundant relationships.

5. Step2.2: Derive relations from logical data model • Objective: To derive relations from the local logical data model • Relationship: • primary key/ foreign key mechanism • identify the ‘parent’ and ‘child’ entities • Strong entity types • Staff (Staff_No, Fname, Lname, Street, City, Postcode, Position, Sex, Salary) • PrimaryKey Staff_No • Weak entity types • include a foreign key - - - of the owner entity • the primary key of a weak entity is partially or fully derived from the owner entity. • Next-of-Kin (Staff_No,Name, Address, Tel_No, Relationship) • Primary Key: Staff_No, Name • Foreign Key: Staff_No, references Staff(Staff_No)

6. Step2.3: Validate model using Normalization • Objective: To validate a local logical data model using the technique of normalization. • Organizes the data according to functional dependencies • A normalized relationship is robust and free of update anomalies • Normalization provides a flexible database design that can easily be extended • First Normal Form (1NF),removes repeating relationship • Second Normal Form (2NF),removes partial dependencies on the primary key • Third Normal Form (3NF),removes transitive dependencies on the primary key

7. Step2.4: Validate model against user transactions • Objective • To ensure that the local logical data model supports the transactions that are required by the user views. • ER diagram, data dictionary, primary key/ foreign key links shown in the relations • Likely that we have omitted an entity, a relationship or an attribute.

8. Staff_No Property_No M 1 Property Manages Staff • (a) Insert details for a new member of staff • First check that the new staff no does not already exist --> Prohibit the insertion; otherwise insert the new staff details.. • (b) Delete details of a given staff no • Search for the given staff no, if not found --> user error, otherwise delete the tuple.

9. Step2.5: Draw E-R Diagram • Objective • To identify the final ER diagram that is a local logical representation of the given data given in a user’s view of the enterprise.

10. Step2.6: Define integrity constraints • Objective: to define the entity constraints given in a user’s view of the enterprise. • Five types of integrity constraints • Required data • Attribute domain constraints • Entity integrity • Referential Integrity • Enterprise constraints

11. Required data • Not allowed to hold nulls • e.g.: every member of staff must have an associated position • Attribute Domain Constraints • e.g.: ‘M’ or ‘F’ for Gender • Entity Integrity • Primary key of entity cannot hold nulls • Referential Integrity • The foreign key contains a value that matches with an existing occurrence in the parent table.

12. Case1: Insert occurrence into child relation (Property) • To ensure referential integrity Staff_No -- property occurrence. • Case2: Delete occurrence from child relation • No problem as referential integrity is not affected. • Case3: Update foreign key of a child occurrence • Ensure referential integrity • Case4: Insert occurrence into parent table • Does not cause a problem for referential integrity . Parent without any children . Staff member without a property to manage.

13. Case5: Delete occurrence from Parent relation (Staff) • referential integrity is lost if there exists a child's occurrence. • Several strategies • NO ACTION: Prevent deletion from parent relation, if any child reference. • CASCADE: When the parent occurrence is deleted, delete any child occurrences (not wise) • SET NULL: When the parent occurrence is deleted the child occurrences are set to NULL • SET DEFAULT: The foreign key values in all child occurrences should be set to default values. • Eg: Handled by another (default) member of staff -- Manager • NO CHECK: When a parent occurrence is deleted do nothing to ensure referential integrity exists.

14. Case6: Update the primary key of parent occurrences (Staff) • If the primary key value of the parent occurrence is updated, referential integrity is lost if there exists a child occurrence referencing the old primary key value. • Enterprise Constraints • Called business rules. • Eg: No member of staff can manage more than 10 properties at a time • Document all Integrity Constraints

15. Step2.6: Review local logical data model with user • Objective • To ensure that the logical data model is a true representation of the user’s view. • Relationship between the logical data model and data flow diagram • Each datastore should represent a whole number of entity types • Attributes on data flows should belong to entity types

16. Step3: Build and Validate Global Logical data Model • Objective • To combine the individual data logical models into a single global logical data model that can be used to represent the part of the enterprise that we are interested in modeling. • Step3.3: Merge local logical data models into global model • Step3.2: Validate global logical data model • Step3.3: Check for future growth • Step3.4: Draw final ER diagram • Step3.5: Review global logical data model with users.

17. Step3.1: Merge local logical data models into global model • Objective • To merge the individual local logical data models into a single global logical data model of the enterprise. • Typical tasks • Review the names of entities and their primary keys • Review the names of relationships • Merge entities form local views • Include (without merging) entities unique to each user view. • Merge relationships from the local views • Include (without merging) entities unique to each local view. • Check for missing entities or relationships • Check foreign keys • Check integrity constraints • Draw the global logical data model • Update the documentation

18. Merging Staff (Staff_No, Name, Position, Sex, Salary, Branch_No) Primary Key: Staff_No Foreign Key: Branch_No references Branch(Branch_No) Staff (Staff_No, Fname, Lname, Address, Branch_No) Primary Key: Staff_No Foreign Key: Branch_No references Branch(Branch_No) Staff (Staff_No, Fname, Lname, Address, Position, Sex, Salary, Branch_No) Primary Key: Staff_No Foreign Key: Branch_No references Branch(Branch_No) View1 View2 Global View

19. Merge entities from the local views • Merge entities with the same name and primary key • Merge entities with the same name using different primary keys • Merge entities with different names using different primary keys

20. Merging equivalent entities using different primary keys View1 Staff (Staff_No, Name, Position, Sex, Salary, Branch_No) Primary Key: Name Alternate Key: Staff_No Foreign Key: Branch_No references Branch(Branch_No) View2: Staff (Staff_No, Fname, Lname, Address, Branch_No) Primary Key: Staff_No Alternate Keys: Fname, LName Foreign Key: Branch_No references Branch(Branch_No)

21. Step3.2: Validate Global Logical Data Model • Step3.3: Check for future growth • Step3.4: Draw final ER diagram • Step3.5: Review global logical data model with users.