PL/SQL - KSU

PL SQL KSU-ppt Download

  • Date:25 Jun 2020
  • Views:26
  • Downloads:1
  • Size:1.51 MB

Share Presentation : PL SQL KSU

Download and Preview : PL SQL KSU

Report CopyRight/DMCA Form For : PL SQL KSU


Transcription:

Modelling MethodologiesChapter 16 17 18 Database DesignHardware independent ConceptualSoftware independent DB design.
Hardware independent LogicalSoftware dependent DB designHardware dependent PhysicalSoftware dependent DB designModeling Methodologies 2.
Design Methodology OverviewConceptualStep 1 Build conceptual data model Step 2 Build and validate logical data model Step 3 Translate logical data model for target DBMS .
Step 4 Design file organizations and indexes Step 5 Design user views PhysicalStep 6 Design security mechanisms DB designStep 7 Consider the introduction of controlled redundancy Step 8 Monitor and tune the operational system .
Modeling Methodologies 4 Database DesignHardware independent ConceptualSoftware independent DB designHardware independent Logical.
Software dependent DB designHardware dependent PhysicalSoftware dependent DB designModeling Methodologies 5 Step 1 Build Conceptual Data.
Model for Each ViewObjective To build a conceptual data model of an enterprise for each specific view Step 1 1 Identify entity typesObjective 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 EntitiesEntity Name Description Aliases OccurrenceStaff General term describing all staff Employee Each member of staff works at one particularemployed by DearmHome Each property has a single owner is available atPropertyForRent General term describing all Property one specific branch where the property is managed.
property for rent by 1 member of staff A property is viewed bymany clients and rented by a single client at a timeModeling Methodologies 6 Step 1 Build Conceptual DataModel for Each View.
Step 1 2 Identify relationship typesObjective To identify the important relationships that exist between the entity typesthat have been identified Modeling Methodologies 7 ERD for Staff View of DreamHome.
Modeling Methodologies 8 Step 1 Build Conceptual Data Modelfor Each ViewData Dictionary for Staff View ShowingDescription of Relationships.
Entity Name Multiplicity Relationship Entity Name MultiplicityStaff 0 1 Manages PropertyForRent 0 1000 1 Supervises Staff 0 10PropertyForRent 1 1 AssociatedWith Lease 0 Modeling Methodologies 9.
Step 1 Build Conceptual DataModel for Each ViewStep 1 3 Identify and associate attributes with entity or relationship typesObjective To identify and associate attributes with the appropriate entity or relationshiptypes and document the details of each attribute .
Step 1 4 Determine attribute domainsObjective To determine domains for the attributes in the data model and document thedetails of each domain Modeling Methodologies 10 Step 1 Build Conceptual Data.
Model for Each ViewData Dictionary for Staff View ShowingDescription of AttributesEntity Attribute Description Data Length Nulls Multi Default Range PK CKName Type Valued Value.
Staff StaffNo Uniquely identifies a staff member varchar 5 No No Y YFName First name of Staff varchar 15 No NoLname Last name of Staff varchar 15 No Noposition Job title of member of staff varchar 10 No Nosex Gender of member of staff character 1 Yes No M F M.
DOB Date of Birth of member of staff Date Yes No 1960 1990Modeling Methodologies 11 Step 1 Build Conceptual DataModel for Each ViewStep 1 5 Determine candidate and primary key attributes.
Objective To identify the candidate key s for each entity and if there is more than onecandidate key to choose one to be the primary key Modeling Methodologies 12 ERD for Staff View ofDreamHome with PK.
Modeling Methodologies 13 Step 1 Build Conceptual DataModel for Each ViewStep 1 6 Consider use of enhanced modeling conceptsObjective To consider the use of enhanced modeling concepts such as specialization.
generalization aggregation and composition Modeling Methodologies 14 ERD for Staff View of DreamHomewith specialization generalizationModeling Methodologies 15.
Step 1 Build Conceptual DataModel for Each ViewStep 1 7 Check model for redundancyObjective 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 transactionsObjective To ensure that the conceptual model supports the transactions required by Describe the transaction Use transaction pathways diagrammatically represent the pathway taken by each.
transaction on the ERD Modeling Methodologies 16 Using transaction s PathwaysModeling Methodologies 17 Step 1 Build Conceptual Data.
Model for Each ViewStep1 9 Review conceptual data model with userObjective To review the conceptual data model with the user to ensure that the model isa true representation of the user s view of the enterprise Modeling Methodologies 18.
Database DesignHardware independent ConceptualSoftware independent DB designHardware independent LogicalSoftware dependent DB design.
Hardware dependent PhysicalSoftware dependent DB designModeling Methodologies 19 Step 2 Build and ValidateLogical Data Model.
Step 2 1 Derive relations for logical data modelStep 2 2 Validate relations using normalizationStep 2 3 Validate relations against user transactionsStep 2 4 Define integrity constraintsStep 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 usersStep 2 7 Check for future growth.
Modeling Methodologies 21 Referential Integrity Constraints forModeling Methodologies 24 Step 2 6 1 Merge logical data modelsinto 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 1 Review the names contents ofentities relations and their CKBranch View Staff View.
Entity Type CK Entity Type CKBranch BranchNoTelephone TelNoStaff StaffNo Staff StaffNoManager StaffNo.
PrivateOwner OwnerNo PrivateOwner OwnerNoBusinessOwner Bname BusinessOwner BnametelNo telNoClient ClientNo OwnerNoPropertyForRent Propertyno Client ClientNo.
Lease LeaseNo PropertyForRent PropertynoPropertyNo Viewing ClientNo PropertyNoClientNo RentStartModeling Methodologies 28 2 Review the names contents of.
relationships FKBranch View Staff ViewRelation FK Relationship FK RelationshipBranch mgrStaffNo Manager StaffNo ManagesbranchN Branch branchNo .
Telephone supervisorStaffNo Staff staffNo Supervisor supervisorStaffNo Staff staffNo SupervisorStaff branchNo Branch branchNo Manager StaffNo Staff StaffNo PrivateOwnerBusinessOwner StaffNo Staff StaffNo Registers.
Client OwnerNo PrivateOwner ownerNo Owns OwnerNo PrivateOwner ownerNo POwnsPropertyForRent OwnerNo Owns OwnerNo BOwnsBusinessOwner ownerNo BusinessOwner ownerNo StaffNo Staff StaffNo Oversees StaffNo Staff StaffNo ManagesBranchNo Branch BranchNo Offers.
Modeling Methodologies 29 3 Merge entities relations from the localdata modelsMerge 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 ViewPRIVATE OWNER Ono fname lname address Modeling Methodologies 30 3 Merge entities relations from the local.
data modelsMerge entities relations with the same name but different PK Branch View Staff ViewBOWNER bName bType address telNO BOWNER Ono bName bType address telNo AK telNo AK bName telNo.
Global ViewBOWNER Ono bName bType address AK bName telNoModeling Methodologies 31 Relations for the Branch user.
views of DreamHomePearson Education 2009 Relations that represent theglobal logical data model forPearson Education 2009.
Global relation diagram forPearson Education 2009 Step 2 7 Check for future growthObjective To determine whether there are any significant changes likely in theforeseeable future and to assess whether the global logical data model can.
accommodate these changes Pearson Education 2009 Database DesignHardware independent ConceptualSoftware independent DB design.
Hardware independent LogicalSoftware dependent DB designHardware dependent PhysicalSoftware dependent DB designModeling Methodologies 38.
Comparison of Logical andPhysical Database DesignLogical 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 andindexes used to achieve efficient access to the data and any associated integrityconstraints and security measures Physical DB Design 39Modeling Methodologies 39.
Overview of PhysicalDatabase DesignMethodologyStep 3 Translate logical data model for target DBMSStep 3 1 Design base relations.
Step 3 2 Design representation of derived dataStep 3 3 Design general constraintsStep 4 Design physical representationStep 4 1 Analyze transactionsStep 4 2 Choose file organizations.
Step 4 3 Choose indexesStep 4 4 Estimate disk space requirementsStep 5 Design user viewsStep 6 Design security mechanismsStep 7 Consider the introduction of controlled redundancy.
Step 8 Monitor and tune the operational systemModeling Methodologies 40 Step 3 1 Design Base RelationsObjective 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.
PropertyForRent RelationModeling Methodologies 43 Step 3 2 Design Representationof Derived DataObjective 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 Optionselected is based on additional cost to store the derived data and keep it consistent with operational datafrom which it is derived .
cost to calculate it each time it is required Modeling Methodologies 44 PropertyforRent Relation and Staff Relation withDerived Attribute noOfPropertiesPearson Education 2009.
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 ...

Related Presentations