阅读材料:基于数据的数据库方法学

zcy778

贡献于2014-04-02

字数:0 关键词:

阅读材料 面向数据库的数据库方法学 by iKirly 2 Stages of database system development lifecycle  Database planning  System definition  Requirements collection and analysis  Database design  DBMS selection (optional) 2014/2/26 by iKirly 3 Stages of database system development lifecycle  Application design  Prototyping (optional)  Implementation  Data conversion and loading  Testing  Operational maintenance. 2014/2/26 by iKirly 4 2014/2/26 Stages of database system development lifecycle ER modeling  Top-down approach to database design.  Start by identifying the important data (called entities) and relationships between the data.  Then add more details such as the information we want to hold about the entities and relationships (called attributes) and any constraints on the entities, relationships, and attributes. 2014/2/26 by iKirly 5 Entities  Entity ◦ A set of objects with the same properties, which are identified by a user or organization as having an independent existence.  Entity occurrence ◦ Each uniquely identifiable object within a set. 2014/2/26 by iKirly 6 Entities with physical and conceptual existence 2014/2/26 by iKirly 7 ER diagram of entities 2014/2/26 by iKirly 8 Relationships  Relationship ◦ A set of meaningful associations among entities.  Relationship occurrence ◦ Each uniquely identifiable association within a set. 2014/2/26 by iKirly 9 ER diagram of relationships 2014/2/26 by iKirly 10 Relationships  Degree of a relationship ◦ Number of participating entities in relationship.  Relationship of degree : ◦ two is binary ◦ three is ternary ◦ four is quaternary. 2014/2/26 by iKirly 11 Example of ternary relationship 2014/2/26 by iKirly 12 Recursive relationships  Relationship where same entity participates more than once in different roles.  Relationships may be given role names to indicate purpose that each participating entity plays in a relationship. 2014/2/26 by iKirly 13 Example of a recursive relationship 2014/2/26 by iKirly 14 Attributes  Property of an entity or a relationship.  Hold values that describe each occurrence of an entity or relationship, and represent the main source of data stored in the database.  Attribute can be classified as being: ◦ simple or composite ◦ single-valued or multi-valued ◦ or derived 2014/2/26 by iKirly 15 Keys  Superkey {SK}  Candidate key {CK}  Primary key {PK}  Alternate key {AK} 2014/2/26 by iKirly 16 ER diagram of entities and attributes 2014/2/26 by iKirly 17 Strong and weak entities  Strong entity ◦ Entity that is not dependent on the existence of another entity for its primary key.  Weak entity ◦ Entity that is partially or wholly dependent on the existence of another entity, or entities, for its primary key. 2014/2/26 by iKirly 18 Multiplicity constraints on relationships  Represents the number of occurrences of one entity that may relate to a single occurrence of an associated entity.  Represents policies (called business rules) established by user or company. 2014/2/26 by iKirly 19 Multiplicity constraints  The most common degree for relationships is binary.  Binary relationships are generally referred to as being: ◦ one-to-one (1:1) ◦ one-to-many (1:*) ◦ many-to-many (*:*) 2014/2/26 by iKirly 20 1:1 relationship – individual examples 2014/2/26 by iKirly 21 1:1 relationship – multiplicity 2014/2/26 by iKirly 22 1:* relationship – individual examples 2014/2/26 by iKirly 23 1:* relationship – multiplicity 2014/2/26 by iKirly 24 *:* relationship – individual examples 2014/2/26 by iKirly 25 *:* relationship – multiplicity 2014/2/26 by iKirly 26 Complex relationships  Multiplicity is the number (or range) of possible occurrences of an entity type in an n-ary relationship when other (n-1) values are fixed. 2014/2/26 by iKirly 27 Complex relationship – individual examples 2014/2/26 by iKirly 28 Complex relationship – multiplicity 2014/2/26 by iKirly 29 Summary of multiplicity constraints 2014/2/26 by iKirly 30 Multiplicity  Made up of two types of restrictions on relationships: ◦ cardinality ◦ and participation 2014/2/26 by iKirly 31 Multiplicity as cardinality and participation constraints 2014/2/26 by iKirly 32 Relationship with attributes 2014/2/26 by iKirly 33 Normalization  A technique for producing a set of tables with desirable properties that support the requirements of a user or company. 2014/2/26 by iKirly 34 Data redundancy and update anomalies  Major aim of relational database design is to group columns into tables to minimize data redundancy and reduce file storage space required by base tables.  Problems associated with data redundancy are illustrated by comparing the Staff and Branch tables with the StaffBranch table. 2014/2/26 by iKirly 35 Data redundancy and update anomalies 2014/2/26 by iKirly 36 Data redundancy and update anomalies 2014/2/26 by iKirly 37 Data redundancy and update anomalies  StaffBranch table has redundant data; the details of a branch are repeated for every member of staff.  In contrast, the branch information appears only once for each branch in the Branch table and only the branch number (branchNo) is repeated in the Staff table, to represent where each member of staff is located. 2014/2/26 by iKirly 38 Data redundancy and update anomalies  Tables that contain redundant information may potentially suffer from update anomalies.  Types of update anomalies include ◦ insertion ◦ deletion ◦ modification 2014/2/26 by iKirly 39 First normal form (1NF)  Only 1NF is critical in creating appropriate tables for relational databases. All subsequent normal forms are optional.  A table in which the intersection of every column and record contains only one value. 2014/2/26 by iKirly 40 Branch table is not in 1NF 2014/2/26 by iKirly 41 Converting Branch table to 1NF 2014/2/26 by iKirly 42 Second normal form (2NF)  2NF only applies to tables with composite primary keys.  A table that is in 1NF and in which the values of each non-primary-key column can be worked out from the values in all the columns that make up the primary key. 2014/2/26 by iKirly 43 TempStaffAllocation table is not in 2NF 2014/2/26 by iKirly 44 Functional dependency  The particular relationships that we show between the columns of a table are more formally referred to as functional dependencies.  Functional dependency describes the relationship between columns in a table. 2014/2/26 by iKirly 45 Functional dependency  For example, consider a table with columns A and B, where B is functionally dependent on A (denoted A  B). If we know the value of A, we find only one value of B in all the records that has this value of A, at any moment in time. 2014/2/26 by iKirly 46 Second normal form (2NF)  Formal definition of 2NF is a table that is in 1NF and every non-primary-key column is fully functional dependent on the primary key.  Full functional dependency indicates that if A and B are columns of a table, B is fully dependent on A if B is functionally dependent on A but not on any proper subset of A. 2014/2/26 by iKirly 47 Converting TempStaffAllocation table to 2NF 2014/2/26 by iKirly 48 Third normal form (3NF)  A table that is in 1NF and 2NF and in which all non- primary-key column can be worked out from only the primary key column(s) and no other columns. 2014/2/26 by iKirly 49 StaffBranch table is not in 3NF 2014/2/26 by iKirly 50 Third normal form (3NF)  The formal definition of 3NF is a table that is in 1NF and 2NF and in which no non-primary-key column is transitively dependent on the primary key. 2014/2/26 by iKirly 51 Third normal form (3NF)  For example, consider a table with A, B, and C. If B is functional dependent on A (A  B) and C is functional dependent on B (B  C), then C is transitively dependent on A via B (provided that A is not functionally dependent on B or C).  If a transitive dependency exists on the primary key, the table is not in 3NF. 2014/2/26 by iKirly 52 Converting the StaffBranch table to 3NF 2014/2/26 by iKirly 53 Step 1 - Tasks  Step 1.1 Identify entities  Step 1.2 Identify relationships  Step 1.3 Identify and associate attributes with entities or relationships  Step 1.4 Determine attribute domains  Step 1.5 Determine candidate, primary, and alternate key attributes  Step 1.6 Specialize/Generalize entities (optional step)  Step 1.7 Check model for redundancy  Step 1.8 Check model supports user transactions Copyright © 2007 by iKirly All right reserved 54 Step 1.1 Identify entities  One method is to examine the users’ requirements specification for nouns or noun phrases  Also look for major objects such as people, places, or concepts of interest, excluding those nouns that are merely qualities of other objects.  Document entities Copyright © 2007 by iKirly All right reserved 55 Step 1.2 Identify relationships  One method is to examine users’ requirements specification for verbs or verbal expressions.  Use entity–relationship (ER) modeling  Determine the multiplicity constraints of relationships  Check for fan and chasm traps  Document relationships Copyright © 2007 by iKirly All right reserved 56 First ER diagram of StayHome Copyright © 2007 by iKirly All right reserved 57 Adding multiplicity constraints to ER diagram Copyright © 2007 by iKirly All right reserved 58 Step 1.3 Identify and associate attributes with entities or relationships  Attributes can be identified where noun or noun phrase is a property, quality, identifier, or characteristic of one of the entities or relationships previously found.  Document attributes Copyright © 2007 by iKirly All right reserved 59 Step 1.4 Determine attribute domains  A domain is a pool of values from which one or more attributes draw their values  A domain specifies:  allowable set of values for the attribute;  size and format of the attribute.  Document attribute domains Copyright © 2007 by iKirly All right reserved 60 Step 1.5 Determine candidate, primary, and alternate key attributes  Identifying candidate key(s) for an entity and then selecting one to be the primary key.  Candidate keys can never be null.  Remaining candidate keys are called alternate keys.  Document candidate, primary, and alternate keys Copyright © 2007 by iKirly All right reserved 61 Guidelines for choosing a primary key  Select the candidate key  the minimal set of attributes;  that is less likely to have its values changed;  that is less likely to lose uniqueness in the future;  with fewest characters (for those with textual attribute(s));  with the smallest maximum value (for numerical attributes);  that is easiest to use from the users’ point of view. Copyright © 2007 by iKirly All right reserved 62 ER diagram showing primary keys Copyright © 2007 by iKirly All right reserved 63 Step 1.6 Specialize/Generalize entities (optional step)  Objective is to identify superclass and subclass entities, where appropriate.  The modeling of superclasses and subclasses adds more information to the data model, but also adds more complexity as well. Copyright © 2007 by iKirly All right reserved 64 Step 1.7 Check model for redundancy  Examine the ER model and if redundancy found, remove from model.  The three activities in this step are: ◦ (1) re-examine one-to-one (1:1) relationships; ◦ (2) remove redundant relationships; ◦ (3) consider the time dimension when assessing redundancy. Copyright © 2007 by iKirly All right reserved 65 Remove redundant relationships Copyright © 2007 by iKirly All right reserved 66 Non-redundant relationships Copyright © 2007 by iKirly All right reserved 67 Step 1.8 Check model supports user transactions  ER model represents the data requirements of the organization  Objective is to check that ER model supports the required transactions.  Two possible approaches: ◦ (1) Describing the transaction ◦ (2) Using transaction pathways Copyright © 2007 by iKirly All right reserved 68 Using pathways to check ER model supports user transactions Copyright © 2007 by iKirly All right reserved 69 Step 2 Map ER model to tables  To create tables for the ER model and to check the structure of the tables. ◦ Step 2.1 Create tables ◦ Step 2.2 Check table structures using normalization ◦ Step 2.3 Check tables support user transactions ◦ Step 2.4 Check business rules ◦ Step 2.5 Review logical database design with users Copyright © 2007 by iKirly All right reserved 70 Step 2.1 Map tables  Create tables for the ER model to represent the entities, relationships, attributes, and constraints.  Tables created from information that describes the ER model, including the ER diagrams, data dictionary, and any other supporting documentation. Copyright © 2007 by iKirly All right reserved 71 Step 2.1 Map tables - Discuss using example ER Copyright © 2007 by iKirly All right reserved 72 How to represent relationships  Consider how to represent the following relationships: ◦ one-to-many (1:*) binary relationships; ◦ one-to-many (1:*) recursive relationships; ◦ one-to-one (1:1) binary relationships; ◦ one-to-one (1:1) recursive relationships; ◦ many-to-many (*:*) binary relationships; ◦ complex relationships;  Also, consider multi-valued attributes. Copyright © 2007 by iKirly All right reserved 73 1:*binary relationships  Entity on ‘one side’ of relationship is designated as the parent entity and entity on ‘many side’ is designated as child entity.  A copy of primary key of parent entity is placed into table representing the child entity, to act as a foreign key. Copyright © 2007 by iKirly All right reserved 74 1:* relationship – (a) ER diagram; (b) as tables Copyright © 2007 by iKirly All right reserved 75 1:* recursive relationships  The representation of a 1:* recursive relationship is similar to 1:* binary relationship.  However, in this case, both the parent and child entity is the same entity. Copyright © 2007 by iKirly All right reserved 76 1:* recursive relationships – (a) ER diagram; (b) as tables Copyright © 2007 by iKirly All right reserved 77 1:1 binary relationships  Cannot use cardinality to help identify the parent and child entities.  Instead, use participation to help decide whether it’s best to represent the relationship by combining the entities involved into one table or by creating two tables and posting a copy of the primary key from one table to the other. Copyright © 2007 by iKirly All right reserved 78 1:1 binary relationships  Consider how to create tables to represent the following participation constraints: ◦ Mandatory participation on both sides of 1:1 relationship ◦ Mandatory participation on one side of 1:1 relationship ◦ Optional participation on both sides of 1:1 relationship. Copyright © 2007 by iKirly All right reserved 79 Mandatory participation on both sides of 1:1 relationship  Combine entities involved into one table and choose one of the primary keys of the original entities to be the primary key of the new table, while the other is used as an alternate key. Copyright © 2007 by iKirly All right reserved 80 Mandatory participation on both sides of 1:1 relationship – (a) ER diagram; (b) as table Copyright © 2007 by iKirly All right reserved 81 Mandatory participation on one side of a 1:1 relationship  Identify parent and child entities using participation constraints.  Entity with optional participation is parent entity, and entity with mandatory participation is child entity.  A copy of primary key of parent entity is placed in the table representing the child entity. Copyright © 2007 by iKirly All right reserved 82 Mandatory participation on one side of a 1:1 relationship – (a)ER diagram; (b) as tables Copyright © 2007 by iKirly All right reserved 83 Mandatory participation on one side of a 1:1 relationship (2nd Example) Copyright © 2007 by iKirly All right reserved 84 Optional participation on both sides of a 1:1 relationship  In this case, the designation of the parent and child entities is arbitrary unless you can find out more about the relationship that can help you reach a decision one way or the other. Copyright © 2007 by iKirly All right reserved 85 Optional participation on both sides of a 1:1 relationship – (a) ER diagram; (b) as tables Copyright © 2007 by iKirly All right reserved 86 1:1 recursive relationships  Follow rules for participation as described for a 1:1 relationship.  However, in this case, the entity on both sides of the relationship is the same. Copyright © 2007 by iKirly All right reserved 87 *:* binary relationships  Create a table to represent the relationship and include any attributes that are part of the relationship.  Post a copy of the primary key attribute(s) of the entities that participate in the relationship into the new table, to act as foreign keys. Copyright © 2007 by iKirly All right reserved 88 *:* binary relationships – (a) ER diargram; (b) as tables Copyright © 2007 by iKirly All right reserved 89 Complex relationships  Create a table to represent the relationship.  Post a copy of the primary key attribute(s) of the entities that participate in the complex relationship into the new table, to act as foreign keys, and include any attributes that are associated with the relationship. Copyright © 2007 by iKirly All right reserved 90 Complex relationship – ER diagram Copyright © 2007 by iKirly All right reserved 91 Complex relationship – representation as tables Copyright © 2007 by iKirly All right reserved 92 Multi-valued attributes  A new table is created to hold the multi-valued attribute and the parent entity posts a copy of its primary key, to act as a foreign key.  Unless the multi-valued attribute is itself an alternate key of the parent entity, the primary key of the new table is composed of the multi-valued attribute and the original primary key of the parent entity. Copyright © 2007 by iKirly All right reserved 93 Multi-valued attributes – ER diagram and representation as tables Copyright © 2007 by iKirly All right reserved 94 Step 2.2 Check table structures using normalization  Check composition of each table using the rules of normalization, to avoid unnecessary duplication of data.  Ensure each table is in at least 3NF.  If tables are not in 3NF, this may indicate that part of the ER model is incorrect, or that error(s) introduced while creating the tables from the model.  If necessary, may need to restructure the data model and/or tables. Copyright © 2007 by iKirly All right reserved 95 Step 2.3 Check tables support user transactions  Check tables support the required transactions, which were documented in the users’ requirements specification.  Ensures that no error has been introduced while creating tables.  One approach is to examine transaction’s data requirements to ensure that the data is present in one or more tables.  If a transaction requires data in more than one table, check these tables are linked through the primary key/foreign key mechanism. Copyright © 2007 by iKirly All right reserved 96 Step 2.4 Check business rules  Business rules are the constraints that you wish to impose in order to protect the database from becoming incomplete, inaccurate, or inconsistent. Copyright © 2007 by iKirly All right reserved 97 Step 2.4 Check business rules  Consider the following types of business rules: ◦ required data, ◦ column domain constraints, ◦ entity integrity, ◦ multiplicity, ◦ referential integrity, ◦ other business rules. Copyright © 2007 by iKirly All right reserved 98 Step 2.4 Check business rules - referential integrity  There are two issues regarding foreign keys ◦ Are nulls allowed for the foreign key? ◦ How do you ensure referential integrity? Copyright © 2007 by iKirly All right reserved 99 How do you ensure referential integrity?  Consider the following six cases. ◦ Case 1: Insert record into child table ◦ Case 2: Delete record from child table ◦ Case 3: Update foreign key of child record ◦ Case 4: Insert record into parent table ◦ Case 5: Delete record from parent table ◦ Case 6: Update primary key of parent record  There are several strategies to consider for Case 5 Copyright © 2007 by iKirly All right reserved 100 Case 5: Delete record from parent table  Strategies to consider include: ◦ NO ACTION ◦ CASCADE ◦ SET NULL ◦ SET DEFAULT ◦ NO CHECK Copyright © 2007 by iKirly All right reserved 101 Step 2.5 Review logical database design with users  To ensure that the logical database design is a true representation of the data requirements of an organization (or part of the organization) to be supported by the database. Copyright © 2007 by iKirly All right reserved 102 Specialization/generalization  Associated with special types of entities known as superclasses and subclasses, and the process of attribute inheritance. ◦ Superclass  An entity that includes one or more distinct groupings of its occurrences, which require to be represented in a data model. ◦ Subclass  A distinct grouping of occurrences of an entity type, which require to be represented in a data model. Copyright © 2007 by iKirly All right reserved 103 Superclass/subclass relationship  Superclass/subclass relationship is one-to-one (1:1).  Each member of a subclass is also a member of the superclass but has a distinct role. Copyright © 2007 by iKirly All right reserved 104 Superclasses and subclasses  We can use superclasses and subclasses to avoid describing different types of entities with possibly different attributes within a single entity.  Can also show relationships that are only associated with particular subclasses and not with superclass. Copyright © 2007 by iKirly All right reserved 105 AllStaff table holding details of all staff Copyright © 2007 by iKirly All right reserved 106 Attribute inheritance  An entity occurrence in a subclass represents the same ‘real world’ object as in the superclass.  Hence, a member of a subclass inherits those attributes associated with the superclass, but may also have subclass-specific attributes. Copyright © 2007 by iKirly All right reserved 107 Specialization/generalization  Specialization ◦ The process of maximizing the differences between members of an entity by identifying their distinguishing characteristics.  Generalization ◦ The process of minimizing the differences between entities by identifying their common characteristics. Copyright © 2007 by iKirly All right reserved 108 Staff entity with subclasses representing job roles Copyright © 2007 by iKirly All right reserved 109 Shared subclass and a subclass with its own subclass Copyright © 2007 by iKirly All right reserved 110 Constraints on specialization/ generalization  Two constraints may apply to a specialization/generalization called participation constraints and disjoint constraints.  Participation constraint ◦ Determines whether every occurrence in the superclass must participate as a member of a subclass. ◦ May be mandatory or optional. Copyright © 2007 by iKirly All right reserved 111 Vehicle entity into vehicle types Copyright © 2007 by iKirly All right reserved 112 Constraints on specialization / generalization  Disjoint constraint ◦ Describes the relationship between members of the subclasses and indicates whether it is possible for a member of a superclass to be a member of one, or more than one, subclass. ◦ May be disjoint or nondisjoint Copyright © 2007 by iKirly All right reserved 113 Constraints on specialization / generalization  There are four categories of constraints of specialization and generalization: ◦ mandatory and disjoint ◦ optional and disjoint ◦ mandatory and nondisjoint ◦ optional and nondisjoint Copyright © 2007 by iKirly All right reserved 114 Creating tables to represent specialization/generalization Copyright © 2007 by iKirly All right reserved 115 Logical vs Physical Database Design  Logical db design independent of implementation details, such as functionality of target DBMS.  Logical db design concerned with the what, physical database design is concerned with the how.  Sources of information for physical design includes logical data model and data dictionary. Copyright © 2007 by iKirly All right reserved 116 Overview of Physical Database Design Methodology  Step 3 Translate logical database design for target DBMS  Step 4 Choose file organizations and indexes  Step 5 Design user views  Step 6 Design security mechanisms  Step 7 Consider introduction of controlled redundancy  Step 8 Monitor and tune operational system Copyright © 2007 by iKirly All right reserved 117 Step 3 Translate logical database design for target DBMS  To produce a basic working relational database from the logical data model.  Consists of the following steps: ◦ Step 3.1 Design base tables ◦ Step 3.2 Design representation of derived data ◦ Step 3.3 Design remaining business rules Copyright © 2007 by iKirly All right reserved 118 Step 3.1 Design base tables  To decide how to represent base tables identified in logical model in target DBMS.  Need to collate and assimilate the information about tables produced during logical database design (from data dictionary and tables defined in DDL). Copyright © 2007 by iKirly All right reserved 119 Step 3.1 Design base tables  For each table, need to define: ◦ name of the table; ◦ list of simple columns in brackets; ◦ PK and, where appropriate, AKs and FKs. ◦ referential integrity constraints for any FKs identified.  For each column, need to define: ◦ its domain, consisting of a data type, length, and any constraints on the domain; ◦ an optional default value for the column; ◦ whether the column can hold nulls. Copyright © 2007 by iKirly All right reserved 120 DBDL for the Branch table Copyright © 2007 by iKirly All right reserved 121 Step 3.2 Design representation of derived data  To design the representation of derived data in the database.  Produce list of all derived columns from logical data model and data dictionary.  Derived column 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 data from which it is derived; ◦ cost to calculate it each time it’s required;  Less expensive option is chosen subject to performance constraints. Copyright © 2007 by iKirly All right reserved 122 RentalAgreement and Member with derived column noOfRentals Copyright © 2007 by iKirly All right reserved 123 Step 3.3 Design remaining business rules  To design the remaining business rules for the target DBMS.  Some DBMS provide more facilities than others for defining business rules. Example: CONSTRAINT member_not_renting_too_many CHECK (NOT EXISTS (SELECT memberNo FROM RentalAgreement GROUP BY memberNo HAVING COUNT(*) > 10)) Copyright © 2007 by iKirly All right reserved 124 Step 4 Choose file organizations and indexes  Determine optimal file organizations to store the base tables, and the indexes required to achieve acceptable performance.  Consists of the following steps: ◦ Step 4.1 Analyze transactions ◦ Step 4.2 Choose file organizations ◦ Step 4.3 Choose indexes Copyright © 2007 by iKirly All right reserved 125 Step 4.1 Analyze transactions  Often not possible to analyze all expected transactions, so investigate most ‘important’ ones.  To help identify which transactions to investigate, can use: ◦ transaction/table cross-reference matrix, showing tables that each transaction accesses, and/or ◦ transaction usage map, indicating which tables are potentially heavily used. Copyright © 2007 by iKirly All right reserved 126 Step 4.1 Analyze transactions  To focus on areas that may be problematic: (1) Map all transaction paths to tables. (2) Determine which tables are most frequently accessed by transactions. (3) Analyze the data usage of selected transactions that involve these tables. Copyright © 2007 by iKirly All right reserved 127 Transaction usage map for some sample transactions showing expected occurrences Copyright © 2007 by iKirly All right reserved 128 Step 4.1 Analyze transactions – Data usage analysis  For each transaction determine: (a) Tables and columns accessed and type of access. (b) Columns used in any search conditions. (c) For query, columns involved in joins. (d) Expected frequency of transaction. (e) Performance goals of transaction. Copyright © 2007 by iKirly All right reserved 129 Example Transaction Analysis Form Copyright © 2007 by iKirly All right reserved 130 Step 4.2 Choose file organizations  To determine an efficient file organization for each base table.  File organizations include Heap, Hash, Indexed Sequential Access Method (ISAM), B+-Tree, and Clusters.  Some DBMSs (particularly PC-based DBMS) have fixed file organization that you cannot alter. Copyright © 2007 by iKirly All right reserved 131 Step 4.3 Choose indexes  Determine whether adding indexes will improve the performance of the system.  One approach is to keep records unordered and create as many secondary indexes as necessary. Copyright © 2007 by iKirly All right reserved 132 Step 4.3 Choose indexes  Or could order records in table by specifying a primary or clustering index.  In this case, choose the column for ordering or clustering the records as: ◦ column that is used most often for join operations - this makes join operation more efficient, or ◦ column that is used most often to access the records in a table in order of that column. Copyright © 2007 by iKirly All right reserved 133 Step 4.3 Choose indexes  Have to balance overhead in maintenance and use of secondary indexes against performance improvement gained when retrieving data.  This includes: ◦ adding an index record to every secondary index whenever record is inserted; ◦ updating a secondary index when corresponding record is updated; ◦ increase in disk space needed to store the secondary index; ◦ possible performance degradation during query optimization to consider all secondary indexes. Copyright © 2007 by iKirly All right reserved 134 Copyright © 2007 by iKirly All right reserved 135 Step 5 Design User Views  Design user views identified during Requirements Collection and Analysis stage of the database application lifecycle.  Normally views created using SQL or a QBE-like facility. For example: CREATE VIEW Staff1_View AS SELECT staffNo, name, position FROM Staff WHERE branchNo = ‘B001’; Copyright © 2007 by iKirly All right reserved 136 Step 6 Design Security Measures  Design security measures for the database as specified by the users.  RDBMSs generally provide two types of database security: ◦ system security: access and use of database at system level (such as username/password); ◦ data security: access and use of database objects (such as tables and views). Copyright © 2007 by iKirly All right reserved 137 Step 6 Design Security Measures - SQL  Each database user assigned an authorization identifier by DBA (usually has an associated password).  Each object created has an owner.  Privileges are the actions that a user is permitted to carry out on a given base table or view (such as SELECT, UPDATE).  GRANT statement allows owner to give privileges to other users.  REVOKE statement takes privileges away. Copyright © 2007 by iKirly All right reserved 138 Step 7 Consider the Introduction of Controlled Redundancy  Determine whether introducing redundancy in a controlled manner by relaxing the normalization rules will improve system performance. Copyright © 2007 by iKirly All right reserved 139 Denormalization  Refinement to relational schema such that the degree of normalization for a modified table is less than the degree of at least one of the original tables.  Also use term more loosely to refer to situations where two tables are combined into one new table, which is still normalized but contains more nulls than original tables. Copyright © 2007 by iKirly All right reserved 140 Step 7 Consider the Introduction of Controlled Redundancy  Consider denormalization in following situations, specifically to speed up frequent or critical transactions: ◦ Step 7.1 Combining 1:1 relationships ◦ Step 7.2 Duplicating nonkey columns in 1:* relationships to reduce joins ◦ Step 7.3 Duplicating FK columns in 1:* relationships to reduce joins ◦ Step 7.4 Duplicating columns in *:* relationships to reduce joins Copyright © 2007 by iKirly All right reserved 141 Step 7 Consider the Introduction of Controlled Redundancy ◦ Step 7.5 Introducing repeating groups ◦ Step 7.6 Creating extract tables Copyright © 2007 by iKirly All right reserved 142 Step 7.1 Combining 1:1 relationships Copyright © 2007 by iKirly All right reserved 143 Step 7.2 Duplicating nonkey columns in 1:* relationships to reduce joins Copyright © 2007 by iKirly All right reserved 144 Step 7.3 Duplicating FK columns in 1:* relationship to reduce joins Copyright © 2007 by iKirly All right reserved 145 Step 7.4 Duplicating columns in *:* relationships to reduce joins Copyright © 2007 by iKirly All right reserved 146 Step 7.5 Introducing repeating groups Step 7.6 Creating extract tables  Reports can access derived data and perform multi-table joins on same set of base tables. However, data report based on may be relatively static or may not have to be current.  Can create a single, highly denormalized extract table based on tables required by reports, and allow users to access extract table directly instead of base tables. Copyright © 2007 by iKirly All right reserved 147 Step 7.7 Partitioning tables  Rather than combining tables, could decompose a table into a smaller number of partitions.  Horizontal partition: distribute records across a number of (smaller) tables.  Vertical partition: distribute columns across a number of (smaller) tables. PK duplicated to allow reconstruction.  Partitions useful for applications that store and analyze large amounts of data.

下载文档,方便阅读与编辑

文档的实际排版效果,会与网站的显示效果略有不同!!

需要 5 金币 [ 分享文档获得金币 ] 0 人已下载

下载文档

相关文档