Concrete Database Pattern

Database Evolution Process

Ming Wang , Russell 1000. Chan , in Encyclopedia of Information Systems, 2003

I.E. Physical Design

The aim of physical database design is to decide how the logical database design will be implemented. For the relational database, this involves:

•

Defining a set of the tabular array structures, data types for fields, and constraints on these tables such equally main primal, foreign key, unique key, not nix and domain definitions to bank check if information are out of the range.

•

Identifying the specific storage structures and access methods to think data efficiently. For example, calculation a secondary index to a relation.

•

Designing security features for the database system including business relationship cosmos, privilege granting/revocation, access protection, and security level assignment.

Physical pattern is DBMS-specific whereas logical blueprint by contrast is DBMS-independent. Logical blueprint is concerned with the what; physical database blueprint is concerned with the how. In short, physical blueprint is a process of implementing a database on secondary storage with a specific DBMS.

Read full affiliate

URL:

https://www.sciencedirect.com/science/article/pii/B0122272404000265

Physical Database Considerations

Charles D. Tupper , in Data Architecture, 2011

Queries, Reports, and Transactions

Part of the consideration for physical database design is the activity being passed confronting it. The transaction, query, or written report creates a unit of work that threads its way through the database in a traversal route that can be mapped. Some of the procedure mapping has been covered in Chapters 9 and 10 Affiliate 9 Chapter 10 , simply a pocket-sized recap would not injure here. Functional decomposition in those capacity was defined as the breakdown of activity requirements in terms of a hierarchical ordering and is the tool for analysis of activity. The function is at the acme of the bureaucracy and is divers every bit a continuously occurring activity within the corporation. Within each function are many processes. Processes have a showtime activity, a procedure activeness, and a termination activity, which completes the process. Each process may or may not be broken downward into subprocesses. Each subprocess or event also has an initiation, an activity state, and a termination and differs from the process in that it represents action at the lowest level.

Read total chapter

URL:

https://world wide web.sciencedirect.com/scientific discipline/commodity/pii/B9780123851260000152

Database Assistants

Ming Wang , in Encyclopedia of Data Systems, 2003

Ii.C. Concrete Design

Database assistants is typically responsible for concrete database pattern and much of database implementation. Physical blueprint is the process of choosing specific structures and access paths for database files to achieve good performance for the various database applications. Each DBMS provides a variety of options for file organization and access paths. These include various types of indexing and clustering of related records on disk blocks. Once a specific DBMS is selected, the physical pattern process is restricted to choosing the most advisable structure for the database files from the options offered by that DBMS. One of the advantages of relational database is that users are able to admission relations and rows without specifying where and how the rows are stored. The internal storage representation for relations should be transparent to users in a relational database.

Read full chapter

URL:

https://www.sciencedirect.com/scientific discipline/article/pii/B0122272404000253

Basic Requirements for Physical Design

Charles D. Tupper , in Information Architecture, 2011

Data Admission

In order to do a proper physical database design, it is of import to understand how and how frequently data will be accessed. Where does this information come up from? Ideally, process models should incorporate references to business functions that will bespeak how frequently a business process should be followed. This can be translated to pseudo-SQL (pseudo-code that does not need to parse merely needs to comprise admission and ordering information). The criticality and concurrency of transactions are also important. This section will cover the following subparts of information vital to physical design of a loftier-performance database system.

•

Access implications: Data gathering and analysis must be done in the way in which the user accesses the data. Additionally, the tools used for the admission must be taken into consideration. For instance, reporting tools often are wide spectrum—that is, they will work with many different DBMSs, and every bit such they use very generic methods for admission. Unless they have a pass-through option, similar WebFocus does for Microsoft Access and SQLServer, the passed through query volition have poor admission performance. If the access method is through a GUI front end that invokes DBMS stored procedure triggers or functions, then information technology is far more tunable for performance.

•

Concurrent access: Concurrent access is of concern for two considerations: network load and locking contention. Network load is non discussed here. Locking implications are dependent on the required admission. If the data are required to exist held static—that is, unchanged—an exclusive lock must be secured past the program executing the action. This sectional lock prevents others from accessing the information while information technology is in use. At that place is an option to let a read of the information while information technology is locked, knowing it will exist changed. This is known as a dirty read and is done when the data needed are not those being updated. When too many programs are trying to access the same data, locking contention develops and a lock protocol is invoked, depending on the DBMS involved. In some cases the lock is escalated to the next higher object level in order to preclude a buildup of processes waiting to execute.

Read full chapter

URL:

https://www.sciencedirect.com/science/commodity/pii/B9780123851260000140

Data Warehousing and Caching

AnHai Doan , ... Zachary Ives , in Principles of Data Integration, 2012

10.ane.ane Data Warehouse Design

Designing a data warehouse can be even more involved than designing a mediated schema in a data integration setting considering the warehouse must support very demanding queries, possibly over data archived over time. Physical database design becomes critical — effective employ of partition across multiple machines or multiple disk volumes, creation of indices, definition of materialized views that can exist used by the query optimizer. Most data warehouse DBMSs are configured for query-only workloads, as opposed to transaction processing workloads, for performance: this disables near of the (expensive) consistency mechanisms used in a transactional database.

Since the early on 2000s, all of the major commercial DBMSs have attempted to simplify the tasks of physical database design for information warehouses. Most tools have "alphabetize option wizards" and "view choice wizards" that take a log of a typical query workload and perform a (ordinarily overnight) search of culling indices or materialized views, seeking to find the best combination to improve performance. Such tools help, merely still there is a need for expert database administrators and "tuners" to obtain the best data warehouse performance.

Read total affiliate

URL:

https://www.sciencedirect.com/science/article/pii/B9780124160446000107

Foreword

John Zachman , in Data Modeling and Relational Databases (2nd Edition), 2008

There is one more interesting dimension of these rigorous, precise semantic models —they have to exist transformed into databases for implementation. The authors depict in detail and past illustration the transformation to logical models, to physical database design, and to implementation. In this context, it is easy to evaluate and compare the diverse database implementation possibilities including relational databases, object-oriented databases, object-relational databases, and declarative databases; and they throw in star schemas and temporal databases for good measure! Once again, I cannot remember seeing then dispassionate and objective an evaluation and comparison of the various database structures. Within this context, it is straight-forward to make a considered and realistic project of database technology trends into the foreseeable future.

Read full affiliate

URL:

https://www.sciencedirect.com/science/article/pii/B9780123735683500011

Designing a Warehouse

Lilian Hobbs , ... Pete Smith , in Oracle 10g Data Warehousing, 2005

two.1.1 Don′t Apply Entity Human relationship (E-R) Modeling

The typical approach used to construct a transaction-processing system is to construct an entity-relationship (E-R) diagram of the business. It is then ultimately used as the basis for creating the physical database design, because many of the entities in our model go tables in the database. If you have never designed a data warehouse earlier but are experienced in designing transaction-processing systems, then you will probably call up that a data warehouse is no different from any other database and that you can employ the aforementioned approach.

Unfortunately, that is not the instance, and warehouse designers will quickly discover that the entity-relationship model is not really suitable for designing a data warehouse. Leading authorities on the subject, such as Ralph Kimball, advocate using the dimensional model, and nosotros take institute this arroyo to be ideal for a data warehouse.

An entity-relationship diagram can show us, in considerable particular, the interaction between the numerous entities in our system, removing redundancy in the system whenever possible. The outcome is a very flat view of the enterprise, where hundreds of entities are described along with their relationships to other entities. While this arroyo is fine in the transaction-processing world, where we require this level of particular, it is far too complex for the information warehouse. If you ask a database administrator (DBA) if he or she has an entity-relationship diagram, the DBA will probably respond that he or she did once, when the organization was first designed. But due to its size and the numerous changes that have occurred in the system during its lifetime, the entity-relationship diagram hasn′t been updated, and it is now but partially accurate.

If we use a different approach for the data warehouse, one that results in a much simpler motion-picture show, then it should be very easy to continue it up-to-date and also to give it to end users, to help them understand the data warehouse. Another factor to consider is that entity-human relationship diagrams tend to upshot in a normalized database pattern, whereas in a data warehouse, a denormalized design is frequently used.

Read full affiliate

URL:

https://world wide web.sciencedirect.com/science/article/pii/B9781555583224500047

CASE Tools for Logical Database Design

Toby Teorey , ... H.V. Jagadish , in Database Modeling and Design (Fifth Edition), 2011

Introduction to the CASE Tools

In this affiliate we will introduce some of the most popular and powerful products available for helping with logical database design: IBM'due south Rational Data Architect, Reckoner Associates' AllFusion ERwin Information Modeler, and Sybase's PowerDesigner. These CASE tools assistance the designer develop a well-designed database past walking through a process of conceptual design, logical design, and physical creation, as shown in Effigy 11.2.

Figure xi.ii. Database design process.

Computer Associates' AllFusion ERwin Data Modeler has been around the longest. A stand up-alone product, AllFusion ERwin'due south strengths stem from relatively potent back up of concrete database modeling, the broadest ready of applied science partners, and 3rd-political party preparation. What it does it does well, but in recent years it has lagged in some advanced features. Sybase's PowerDesigner has come up on stiff in the past few years, challenging AllFusion ERwin. Information technology has some advantages in reporting, and advanced features that will be described afterwards in this chapter. IBM's Rational Information Architect is a new product that supplants IBM's previous product Rational Rose Data Modeler. Its force lies in strong blueprint checking; rich integration with IBM's broad software development platform, including products from their Rational, Information Management, and Tivoli divisions; and avant-garde features that volition be described beneath.

In previous chapters, we take discussed the aspects of logical database design that Example tools help pattern, annotate, apply, and modify. These include, for instance, entity–relationship (ER) and Unified Modeling Linguistic communication (UML) modeling, and how this modeling can be used to develop a logical database design. Inside the ER design, there are several types of entity definitions and relationship modeling (unrelated, one-to-many, and many-to-many). These relationships are combined and normalized into schema patterns known as normal forms (e.1000., 3NF, snowflake schema). An effective design requires the clear definition of keys, such every bit the primary key, the strange key, and unique keys within relationships. The addition of constraints to limit the usage (and abuses) of the system within reasonable bounds or business rules is also disquisitional. The effective logical blueprint of the database will have a profound affect on the performance of the organization, as well as the ease with which the database organization tin be maintained and extended.

There are several other Case products that we will not discuss in this volume. A few additional products worth investigating include Datanamic'southward DeZign for Databases, QDesigner past Quest Software, Visible Annotator by Standard, and Embarcadero ER/Studio. The Visual Studio .Net Enterprise Architect edition includes a version of Visio with some database pattern stencils that tin be used to create ER models. The cost and office of these tools varies wildly, from open-source products up through enterprise software that costs thousands of dollars per license.

The full development cycle includes an iterative wheel of understanding business organisation requirements; defining product requirements; analysis and design; implementation; test (component, integration, and organization); deployment; administration and optimization; and modify management. No unmarried product currently covers that unabridged telescopic. Instead, product vendors provide, to varying degrees, suites of products that focus on portions of that wheel. CASE tools for database blueprint largely focus on the analysis and design portion, and to a lesser degree, the testing portion of this iterative bike.

Case tools provide software that simplifies or automates some of the steps described in Figure 11.2 . Conceptual design includes steps such as describing the business entities and functional requirements of the database; logical blueprint includes definition of entity relationships and normal forms; and concrete database design helps transform the logical blueprint into actual database objects, such as tables, indexes, and constraints. The software tools provide significant value to database designers by:

1.

Dramatically reducing the complication of conceptual and logical design, both of which can be rather difficult to do well. This reduced complication results in better database design in less time and with less skill requirements for the user.

2.

Automating transformation of the logical design to the concrete design (at least the basic physical design). This not simply reduces fourth dimension and skill requirements for the designer, only significantly removes the chance of transmission error in performing the conversion from the logical model to the physical data definition linguistic communication (DDL), which the database server will "eat" (i.due east., as input) to create the physical database.

3.

Providing the reporting, roundtrip technology, and reverse engineering that make such tools invaluable in maintaining systems over a long period of time. System blueprint tin can and does evolve over time due to changing and expanding business needs. Also, the people who design the system (sometimes teams of people) may not be the same as those charged with maintaining the system. The complexity of big systems combined with the demand for continuous adjustability most necessitates the use of Case tools to help visualize, opposite engineer, and runway the arrangement blueprint over fourth dimension.

You can observe a broader listing of available database blueprint tools at the website Database Answers ( world wide web.databaseanswers.com/modelling_tools.htm ), maintained by David Alex Lamb at Queen'due south University in Kingston, Canada.

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B9780123820204000136

Information Modeling: Entity-Relationship Information Model

Salvatore T. March , in Encyclopedia of Information Systems, 2003

I.B. Data Models and Database Implementations

A data model does not specify the physical storage of the data. It provides a precise representation of the data content, structure, and constraints required by an application. These must be supported by the database and software physically implemented for the application. The process of developing a database implementation (schema) from a data model is termed concrete database design . In brusk, the data model defines what information must be represented in the application and the database schema defines how that data is stored. The goal of data modeling, also termed conceptual database design, is to accurately and completely represent the data requirements. The goal of physical database design is to implement a database that efficiently meets those requirements.

Clearly there must be a correspondence between a data model and the database schema developed to implement it. For example, a information model may specify that each employee must report to exactly one section at any indicate in fourth dimension. This is represented every bit a relationship between employees and departments in the information model. This relationship must take a physical implementation in the database schema; however, how it is represented is not of concern to the data model. That is a concern for the concrete database design process. In a relational DBMS (RDBMS), relationships are typically represented by primary key-foreign central pairs. That is, the department identifier (primary primal) of the department to which an employee reports is stored as a cavalcade (foreign key) in the employee's record (i.e., row in the Employee table). In an object DBMS relationships can exist represented in a number of ways, including complex objects and embedded object identifiers (OIDs).

Numerous information modeling formalisms accept been proposed; withal, the entity-relationship (ER) model and variations loosely termed binary-human relationship models are the well-nigh widely known and the most usually used. Such formalisms accept come to be known equally semantic data models to differentiate them from the storage structures used by commercial DBMSs to define a database schema. Information modeling has become a common component of system evolution methodologies. A number of object-oriented system evolution approaches, such as the Unified Modeling Language, accept extended information models into what has been termed grade diagrams. These use the same bones constructs as information models to represent the semantic data construction of the arrangement, but typically extended the representation to include operations, organization dynamics, and circuitous constraints and assertions.

Read full chapter

URL:

https://www.sciencedirect.com/science/article/pii/B0122272404000344

Information Virtualization, Information Management, and Data Governance

Rick F. van der Lans , in Information Virtualization for Concern Intelligence Systems, 2012

11.2 Touch on of Data Virtualization on Information Modeling and Database Blueprint

Data virtualization has an touch on certain aspects of how databases are designed. To testify clearly where and what the differences are, this book considers this design process to consist of 3 steps: information modeling, logical database design, and concrete database design .

I of the tasks when developing a business intelligence system is to analyze the users' information needs. On which business objects exercise they need reports? What are the properties of those business objects? On which level of particular do they need the data? How practice they define those business objects? This is information modeling, which is nearly getting a precise agreement of the business organisation processes, the information these processes need, and the corresponding decision-making processes. It'southward an action that requires piffling to no knowledge of database technology. What's needed is business cognition. The more than an analyst understands of the business and its needs, the meliorate the results of information modeling. This step is sometimes referred to every bit data modeling, conceptual data modeling, or information analysis. The term information modeling is used in this book because it'southward the most commonly used term.

The consequence of data modeling, chosen the information model, is a nontechnical but formal description of the information needs of a group of users. Normally, it consists of a diagram describing all the cadre concern objects, their backdrop, and their interrelationships. Diagramming techniques used are normally based on entity-relationship diagramming (see, for instance, [54]). Another diagramming technique used regularly in business intelligence environments is based on multidimensional modeling (see [55]).

In the 2nd stride—logical database design—the information model is transformed to tables consisting of columns and keys that are implemented in a staging area, data warehouse, or data mart. These tables volition hold the users' data needs. This is a semitechnical step. Unremarkably, the result is merely a clarification or model of all the tables with their columns and keys structures.

The 3rd stride—physical database design—focuses on finding the most effective and efficient implementation of these tables for the database server in use. In this step, database specialists study aspects such every bit which columns need indexes, whether tables take to be partitioned, and how the physical parameters of table spaces should exist set. They tin can even decide to restructure tables to improve functioning. For example, information from two tables is joined to course a more denormalized structure, or derived and aggregated data is added to existing tables. The consequence of concrete database design is a database model showing all the tables, their columns, and their keys. An instance of such a database model is shown in Figure 11.1.

Figure 11.ane. An example of a database model.

 Reprinted with permission of Composite Software.

Compared to logical database design, physical database design is a very database server-specific step. This means that the best imaginable solution for an Oracle database server doesn't accept to be the all-time solution for a Microsoft database server.

For business intelligence systems with a more classic architecture, early on in the projection designers make up one's mind which data stores are needed. Should the organisation be built around a data warehouse, is a staging expanse needed, and should data marts be adult? These decisions don't have to be made when data virtualization forms the eye of a business intelligence system. Initially, only a data warehouse is created, so no data marts or personal data stores are developed at the start of the project. For performance reasons, they might be created afterwards.

Using data virtualization has affect on data modeling and database blueprint:

Affect one—Less Database Design Piece of work: When a business concern intelligence arrangement is developed, that three-step design process has to be practical to all the data stores needed. And then information modeling and logical and physical database design have to be performed, for example, for the data warehouse, the staging area, and the data marts. An information model has to be created, and a database model has to exist developed for each of these data stores. For a system based on data virtualization, data modeling is all the same necessary, but database design only applies to the information warehouse because at that place are no other data stores. Because there are fewer data stores, there is less database design work.

Impact 2—Normalization Is Applied to All Tables: In a classic arrangement, unlike database blueprint approaches are used: normalization is quite oft applied to the data warehouse, whereas the information marts unremarkably receive a star schema or snowflake schema (meet Section 2.half dozen). Compare this to all the tables of a data warehouse in a organization based on information virtualization, where initially they receive normalized structures. The reason they are normalized is that this is still the almost neutral form of a data structure—neutral in the sense that it can back up the widest range of queries and reports. Adjacent, virtual tables are designed (according to the rules in Affiliate vii). But for these virtual tables, no physical database design is needed because there are no data stores.

Touch on 3—Information Modeling and Database Pattern Become More Iterative: An iterative approach for information modeling and database blueprint is easier to deploy when data virtualization is used. The initial blueprint of a information warehouse doesn't accept to include the information needs of all the users, and new data needs can be implemented step by step.

Only why is this easier to deploy? When new information needs are implemented, new tables have to be added, columns may have to be added to existing tables, and existing tabular array structures might have to exist changed. In a arrangement with a archetype architecture, making these changes requires a lot of fourth dimension. Not merely practise the tables in the data warehouse take to be changed, but the data marts and the ETL scripts that copy the data must be inverse as well. And changing the tables in the data marts leads to changes in existing reports too. Reporting code has to be changed to testify the same results.

This is not the case when data virtualization is used. If the information needs to be changed, the tables in the data warehouse have to be changed, but this doesn't apply to data marts and ETL scripts. Those changes can exist hidden in the mappings of the virtual tables accessed by the existing reports. The event is that the extra amount of work needed to keep the existing tables unchanged is considerably less. The changes to the real tables are hidden for the reports. This is why a more than iterative approach is easier to utilize when information virtualization is deployed.

Impact 4—Logical Database Design Becomes More than Interactive and Collaborative: Ordinarily, logical database design is quite an abstract do. The designers come up with a ready of table definitions. In the optics of the business users, peculiarly if they don't have a calculating background, those definitions are quite abstruse. Information technology'due south sometimes difficult for them to see how those tables together represent their information needs. The main reason is that they don't ever think in terms of data structures merely in terms of the data itself. For instance, a designer thinks in terms of customers and invoices, while a user thinks in terms of customer Jones based in London and invoice 6473 which was sent to client Metheny Metals. Therefore, it can be hard for a user to decide whether the table structures resulting from logical database design are really what he needs.

It would be ameliorate if the data structures plus the real data are shown then the users can meet what those tables stand for. When information virtualization is used, a logical database model can be implemented as virtual tables. The advantage is that when a virtual table is defined, its (virtual) contents can be shown instantaneously—in other words, both the analyst and the user can browse the contents and the user tin ostend that what he sees satisfies his information needs. Logical database design becomes a more collaborative and more interactive process.

Impact five—Physical Database Blueprint Decisions Tin Be Postponed: Physical database pattern changes in two ways. Outset, instead of having to make all the right physical design decisions upfront, many can be postponed. For example, if a report is likewise irksome, a cache tin be defined. That cache tin can be created instantaneously, and no existing reports have to be changed for that. A more drastic solution might exist to create a data mart to which the virtual tables are redirected.

The assumption fabricated hither is that derived information stores are not needed initially and therefore require no physical database design. Second, at that place is less to pattern. If, indeed, because of information virtualization, fewer databases have to be designed, so there is less physical database design work to do. In a classic architecture where information warehouses and data marts have to be designed, only the first is designed. This makes it a simpler process.

Touch on half-dozen—Denormalization Is Less Negative: When designing real tables, denormalization leads to duplication of data, increases the size of a database (in bytes), slows down updates and inserts, and can lead to inconsistencies in the data. These have always been seen as the master disadvantages of denormalization. Every database designer knows this, and it'south on page one of every volume on database blueprint. If denormalization is applied when designing virtual tables, these assumptions are not true, and these disadvantages don't apply anymore. The point is that a virtual table doesn't have a physical content. So if a virtual table has a denormalized construction, no redundant information is stored, the database doesn't increase, it does non by definition slow down updates and inserts, and it does non lead to inconsistent data. However, if a cache is defined for a denormalized virtual table, then the cache does comprise duplicated information.

Read full affiliate

URL:

https://www.sciencedirect.com/science/article/pii/B9780123944252000113