- Module 4: Deriving a Logical Data Design Module 5: Normalizing the. - Logical Data Design Module 6: Deriving a. - Physical Data Design Module 7:. - Physical Data Design. - Activity 6.1: Translating the Logical Data Design. - Module 6: Deriving a Physical Data Design. - Derive a physical data design for tables and fields from a logical data. - Analyze data-usage characteristics to optimize a physical data design.. - Determine methods for implementing relationships in a physical data. - Determine the proper criteria for optimizing a physical data design.. - Describing a Physical Data Model. - In this section, you will learn about the physical design of a database.. - Of the many different types of database management systems (DBMS) available today, most use one of three models for storing data. - The structure affects the types of database tables that you can create, as well as the speed of access and versatility of the database.. - Flat-file databases were one of the first methods used to store data in an. - Related items of data can be. - Each entity becomes a table in the physical model. - Tables can be linked. - Fields are the attributes of the entity. - Tables are the physical representation of entities. - If logical data design is done correctly, entities identified in the logical design stage should map directly to tables within a relational database. - Tables can store a wide variety of data. - Tables can be linked to other tables within the same database file. - The data in each record is stored in columns, or fields, that are specified from the attributes of the table’s defining entity. - This data type is defined based on the kind of data stored in the field. - Slide Objective To introduce the role of a table and fields in the physical data design.. - Tables and fields are the basic building blocks of physical data design.. - If the relationship specified between two entities is one-to-one, you have several options when designing the physical model. - relationship, an instance of one entity is directly related to the corresponding instance of the other entity. - If both entities are required for the relationship, the entities and their relationship can be represented in one of two ways:. - You can combine the two entities into one table and use the primary keys as a composite key of the combined tables. - You can keep each entity in its own table and add the primary key of one entity as a foreign key of the other entity. - In this case, you should add the primary key of the child entity as a foreign key in the parent entity because the parent entity owns the child entity. - This arrangement forces the database to allow only unique entries in each key field and helps to ensure that each instance of one entity can relate to only one instance of the other entity.. - entity can exist without a related instance of the child entity, then you should create a separate table for each entity and then use foreign keys to implement the relationship.. - Slide Objective To introduce the issues involved in specifying one- to-one relationships in the physical design.. - One-to-one relationships can be mandatory or optional.. - The physical design of a one-to-many relationship is really an extension of that of a one-to-one relationship. - This foreign key determines the existence of the relationship. - A one-to-many relationship is used frequently in data design because it tends to work well under most circumstances.. - Slide Objective To introduce the issues involved in specifying one- to-many relationships in the physical design.. - Slide Objective To introduce the issues involved in specifying many- to-many relationships in the physical design.. - The many-to-many relationship presents a unique set of issues that must be dealt with in the physical design.. - In this activity, you will evaluate the logical design for part of the solution developed for the Ferguson and Bardell, Inc. - Evaluate a logical data design.. - Produce a physical data design.. - In this activity, you will derive a physical data design from a logical data design.. - Optimizing for Creation of Data. - Optimizing for Retrieval of Data. - Activity 6.2: Optimizing a Physical Data. - You will also learn how data can be optimized to make each action performed with that data as efficient as possible.. - Slide Objective To introduce the topic of data optimization.. - In this section, you will learn how data can be optimized for specific uses.. - The goal of optimization is to minimize the response time for each query and to maximize the throughput of the entire database server by minimizing network traffic, disk I/O, and processor time. - This goal is achieved by understanding the application’s requirements, the logical and physical structure of the data, and the trade-offs between conflicting uses of the database, such as a large number of write-intensive insertions and heavy read-intensive queries.. - Many significant performance improvements can be achieved by carefully optimizing the database design from the outset.. - Most database systems can be classified as one of three types:. - Loading of data. - When optimizing the physical data design, you should consider the impact that inserting new data into tables might have on a database. - It is altogether too easy to make logical design mistakes that have an impact on the physical design.. - Slide Objective. - To discuss how the physical data design can be optimized for the creation of new data.. - Creating new data can be an intensive process for a DBMS.. - Optimizing the physical design for data retrieval is imperative on most systems.. - This type of optimization can be performed in several ways.. - The use of indexes in the physical model can help speed up the retrieval of data.. - The arrangement of the index is much like a tree. - Rather than having to scan an entire table each time a value is needed, the DBMS can use the index to quickly lead the query to the direct location of the required data because the index records the location of the data in the table.. - To discuss how the physical data design can be optimized for the retrieval of data.. - Optimization of data retrieval can be done in several ways.. - Two types of indexes can be used to help speed up retrieval of data:. - A clustered index physically reorders the rows of data within the table to match the order of the index. - Another way of optimizing the physical data design is to reduce the number of, or increase the performance of, joins in the database. - Joins are fairly common in a normalized database, and they can decrease the overall performance of the system based on the number and size of the tables involved in the join.. - Reducing the number of joins can be achieved by denormalizing the database.. - Denormalization introduces data duplication into the physical model. - However, if the data is duplicated in the entities where joins used to be required, then the joins no longer have to be performed because the data can be found via a much simpler query.. - Optimizing the physical data design for updates is a common practice. - Although there are trade-offs between a highly normalized physical data design and an intelligently denormalized design, a strategy of selective and intelligent denormalization will most often result in the greatest performance gains.. - To discuss how the physical data design can be optimized for the updating of data.. - Updating data can be a costly process, especially in large databases.. - When optimizing the physical data design, you should consider using transactions because they allow the design and development teams to greatly simplify the logic within the DBMS and client software, thus improving write performance.. - Before you can optimize a physical data design for the deletion of data, you need to consider what types of deletions will need to be performed. - Nonclustered indexes can be updated during a less critical time, although queries might be affected in the interim.. - Slide Objective To discuss the issues that need to be considered when optimizing the physical data design for the deletion of data.. - Activity 6.2: Optimizing a Physical Data Design. - You will consider ways to optimize a physical data design for insertions, updates, and deletions, as well as any nonfunctional requirements specified by the activity’s scenario.. - You will examine a data design and determine what optimization techniques will improve its performance.. - on the purpose of the data. - Designing an effective database system requires an intimate understanding of the data and how it will be used.. - Derive a physical data design for tables and fields. - from a logical data design. - physical data design. - a physical data design. - Each component is itself an entity, and the relationship is the composition of the computer.. - In what ways do indexes help optimize the physical data design?. - When a query’s search criteria are indexed, the DBMS can quickly and efficiently search the indexes for the location of the data and then move to that location within the table, instead of having to search through the table itself.. - These review questions cover some of the key concepts taught in this module.. - Physical Data Design Module 7: Implementing. - In the next module, you will learn about business rules and ways to implement data integrity in the physical design. - The next module discusses the physical design and topics such as the following.
Xem thử không khả dụng, vui lòng xem tại trang nguồn hoặc xem
Tóm tắt