Database Architecture Design Chapter 1 - Architectural Frameworks

1. Introduction to Database Architecture Design: Hello and welcome to this course, which I title database, architecture frameworks and best practices. This course is designed to give the student an understanding of the scope of services found within an enterprise data environment. From this course, the student will gain knowledge about architect ING Day systems on a global scale, and he or she will gain an understanding of best practices relating to managing data in a corporation. The course contains tips and strategies to deploying physical database systems within a corporation on a global scale. The course contains knowledge of corporate implications when best practices are followed and it provides the student with an introduction to the need for metadata repositories in a corporation. I am qualified to teach you this course as I have been in information systems consulting professional for over 35 years, often taking on roles as a lead architect of migration architect or a data systems architect. I have helped build major systems for a number of large organisations, including agriculture, Canada, Blue Cross and Blue Shield of Minnesota, Contact Services, where I was the project manager and lead architect for a marketing system for Air Canada, and WestJet Ernst in Giulio Gallo Wines. Gateway Inc. The computer company I Verdean Management Development Corporation, which is an offshore $2 billion plus drilling project. J. P. Morgan Chase, where I was the data architect and ultimately, the lead technical design authority for merging their custody trading systems. Nextel Communications, where I ran a considerable amount of the project for their Y two k modernization. It's a statue in Department of Health and Tong Yang Insurance in South Korea. So I've had a lot of experience in the world of databases and architect ing them on a global scale. So welcome to this course. Go through it and contact me if you have any questions as you get through it and I will respond to you. See you on the inside and thank you for watching this lecture by for now. 2. Chapter 1 - Key Terms to Learn: Hello and welcome to this lecture, which I title key terms to learn in this lecture series. Be lower some key terms the students should learn and understand as he or she progresses through this course. The first term to know is architectural framework. Architectural frameworks are logical. Representation of a corporation's technology infrastructure, defined in terms of components, relationships, management and scope of systems services, provided an architectural principle sometimes termed as a best practice. An architectural principle is a management guidelines statement that reflects the business and systems goals and objectives of the corporation. To qualify as an architectural principle, the statement must meet a number of criteria. These include it must be relevant to the organization as a whole. It must be timeless. As long as the values and business goals of the organisation remain unchanged, it must have traceability to the business values, objectives and goals of the corporation. It must have a solid business rationale for its existence, and lastly it must be actionable. That is to say, it can be translated into activities performed by individuals in the corporation or into the systems. Architectural design of the Corporation on Architectural Strategy on Architectural Strategy is a physical technology deployment management or designed statement used to define a corporation's strategic direction. An architectural strategy must have traceability to the goals, objectives and principles or best practices of the corporation. It must have solid business and technology rationale for its existence. It must be actionable in terms of effort and time. It must have relevance to the organization as a whole, and it must take into account the current situation of the corporation in relation to achieving a target architectural vision. A Logical Data dictionary. This is stored in a repository. A data dictionary is used to rationalize and maintain standard data. Attributes definitions across an organization for the purposes of maximizing data integrity , data reusability and integration between applications. Elements of a logical data dictionary would include attribute, names, attributes, descriptions, field or attributes, length attributes, tight application source application usage, business ownership and Stuart data Categorization Definition. This is a logical definition of data in terms of its usage within the corporation. To complete this definition, data must be defined in terms of its location, such as process application and reports used are created in. It must be defined in terms of its type whether it's operational decision, support data or both, and it must be consistent in definition with the organization's Logical Data Dictionary Data distribution definition. This is a physical definition of data's location within the corporation To complete the Stephan logically categorized data is mapped to physical locations of need and applications that manage its use and availability at these physical locations. To define the physical distribution or replication strategies for this data requires the adherence to the rules defined within a corporation's data, architectural frameworks, principles and strategies. A repository, a repository is a methodology, a disc database, location and a management tool set similar to a library used to maintain and store and retrieve process models. Data models dated dictionary programs etcetera created from and used to support architectural and application technology projects to maintain relevancy and currency process. Rigours required it all systems design and development layers within the corporation. That is to say, architecture, business application, design and application data systems deployed and implemented elements of data structure. First, there would be data entity. A data entity is a logical collection of data attributes, fields of information grouped together due to their similarity of purpose and relationship a data at tribute to find information collection fields within a computer systems application data characteristic a group of rules and scope parameters that combined provide the definition of a data. Attributes elements of data distribution, global data available across all enterprises or business entities or next enterprise data shared by available for an entire enterprise, business entity or business unit. Local data shared by a limited group of individuals and work groups. And, lastly, private data, which is only owned and used by an individual. And this may be individually created or copied at a local enterprise or global data level. This sums up key definitions that will be used within this lecture. Siri's That's all for this lecture. Bye for now. 3. Enterprise Data Environment Framework: Hello and welcome to this lecture, which I titled Enterprise Data Environment Framework. As part of the course data Architect er, frameworks and best practices. This first model depicts the entire enterprise computing environment. As you can see by this pick Toro, there is a server environment and a workstation environment. And there are elements of the enterprise data environment in both sides of the equation. So we have the E. D. D, providing services from a server perspective to clients. And then also there are day environments within client and customer workplace computers, etcetera that our services offered at a workstation level as well. The Enterprise data environments purpose is to provide structured information, storage and access that supports the applications designed to meet the business needs of the corporation and the end users themselves. Within this structure, there's a number of services that work interactive lead to ensure the integrity, security, flexibility and efficiency of access to the information stored within the corporate enterprise. The model depicted on this page is the enterprise data environment framework. Within here you can see that within the EEC, which is the enterprise computing environment, there's a whole suite of services provided by the enterprise data environment, including elements of it that are available on the client application environment, such as design and construct tools, application architecture profiles, distributed database access, tools, etcetera, that air resident on client application workstations. Then we have a repository information, which is basically the metadata repository for data. Then we have enterprise AP eyes working with data transaction management services such as TP monitors and gateways to allow access from the consumer to the distributed database services, which could be in construct either relational or object relational. And there's a whole suite of distributed GMS services such as replication and file services , access, access, control, security it set up and then at the bottom of here of the image. We have the databases itself and these air compartmentalized into both operational and decision support systems. So drill down on the image on the prior page. What is a repository? Well, a repository is a tool similar to a library used to maintain and store objects such as data models, process models, programs, etcetera that are created from application technology projects or are created to support application technology projects. We have operational and decision support databases while the data environment used to support specific business needs in the day to day operations or transactions of a corporation and then in the long term decision support systems such as data warehousing, which stores a company's business date in an integrated relational database or different structure database. To provide a historical perspective of information on the corporation used to make strategic business decisions, we then have distributed database management systems services while these air services and tools used to manage, define, monitor and control the integrity and security of corporate data, including database services, engines, file services, access control and replication services. Also within this rain work, we had data transaction management services, these within the framework of database services that facilitate the movement and manipulation of data through the use of various database level application programming. Interface is known as a P eyes, and these would include such components as gateways and TP monitors, which your transaction processing monitors, the Enterprise Data environment scope of services is to provide a stable base environment for information data, voice image, etcetera in support of the usage and access requirements of a number of groups, including application, design and development personnel and users of operational systems and users of decision support applications, etcetera defined inter relationships between the enterprise, data environment and users of enterprise data Environment services would include availability in effect of repository contents. This is information products created using the appropriate developer workplace environment tools, the customer workplace environment and the developer workplace environment. Require repository data access as defined within the scope of services provided by the enterprise data environment. The application architecture framework must integrate with the enterprise data environment to successfully deployed products, and then, lastly, all components of the enterprise data environment are enabled by services provided by the enterprise computing environment. What are some of the key data management services provided by the enterprise data environment? First, we have data storage, then data recovery services, central repositories and libraries of information usable by developers and end users of systems and customers. Enforce integrity, support and rules for the distribution or the replication of data. We have duplications services themselves that are built into the database, and I'll talk more on that later. Data views and data tuning functions performed by the enterprise data environment would include well drilling down in data management services themselves. Data storage. These would be management services for storing and managing accesses to stored information , and these services would be transparent to the end user. The end user won't know where and how the data or information is being delivered. Here. She is just receiving the data and viewing it recovery services. This provides data recovery services where there's an infrastructure breakdown or incident that causes corruption to corporate information. So this allows the corporation to recover to a prior state and continue to function. Then there's the central Repositories library, metadata dictionary, etcetera. This system would manage the data rules, definitions, attributes and at relationships of all elements and fields within the databases that air deployed by the corporation. Further on data management services here, we're looking at integrity sitting support rules. This ensures that the referential integrity of information in the database through rigorous definition enforcement of rules surrounding the creation updating the leading of data in the database exists. So basically this make sure nothing goes wrong and that the definition in the context and the usage of data remains as designed by those that developed the systems. Duplication services include such capabilities is two face commit and replication or snapshot even for that matter. These services controlled duplication of data across distributed databases. Date of use. These services facilitate the manipulation of data in the database for the purposes of end user viewing, providing that data in effect in whatever form the user wishes to see that information data tooni. These services provide feedback on performance scenarios for the purposes of database optimization. Then we look at data access services. Here. We're talking about security and data views, although these services air defined within the enterprise data environment, access and usage of many of these services are through personnel assigned to the customer workplace environment, the developer, workplace environment and within the scope of services provided by the application architecture itself. The management of the interaction between this tool usage and the enterprise data environment is through all of the services provided by the Enterprise computing environment , which you saw in the very first Model depiction. Lastly, relating to data access services is a discussion on security. These services facilitate the ability of different user groups to view data as they require it, subject to the right of access data views air integrated with security features providing the appropriate views to the data required by end users and in effect, protecting the users from seeing or protecting the corporation from users seeing data inappropriate information. They want to protect such Aziz personnel salaries, etcetera. Now that's the end of the scope of this lecture. But it does lead into now a number of principles relating to this framework that air discussed in further lectures in this Siri's. When we start talking about the principles, their rationale and the implications to the corporation, remember that they will all be provided and will all apply in relation to the framework that you've just viewed in this lecture itself. So that's all for this lecture. Thank you for watching and bye for now. 4. Data Categorization Framework: Hello and welcome to this lecture, which I title the data categorization framework. This lecture discusses the data categorization framework in most larger corporations, both operational data stores and separate decision support data stores will be found. Data usually begins is operational data within a corporation generated by both internal and users and applications, and by external users or customers. Interfacing with the corporation from external sources, usually via some form of Web interface that either a desktop or mobile device access layer of some sort. Okay. Daily data is also imported by the corporation from third parties, either by direct interface with those external systems or by some form of file reception and intake process. When data is ingested to the corporation from third parties, it is most likely that prior to being useful to the corporation and its internal systems that this data has to be scrubbed their plans and then transformed prior to be useful. Operational data often is also transformed in some manner prior to being made available for decision support systems. As internal operational systems have already used this data, it will not require scrubbing. Some of this data will pass directly into a DSS data store without transformation, which will occur if the rules for the DSS reporting requirements demand it. But most often, data movement will occur within system as it is only rarely. The DSS queries will ever be made directly against deployed operational data systems and related data structures due to system performance considerations relating to these operational systems. The data categorization framework model below illustrates the concepts discussed above at a macro level. As noted in the graphic above operational data can be used with decisions support data for planning and analysis activities. Historical decision support data may also be used with the operational data to support daily online analytical reporting. Components of the above model are discussed in more detail below first transactional processing systems, applications and tools developed to support the processing of operational data in end user environments. Response times for these applications air usually measured in seconds. The end user environment is expected to be primarily an online transaction processing system, or OH LTP, with no direct DSS data extraction and reporting involvement, except possibly against smaller tables or records where performance impacts of the DSS type queries would be minimal. Operational data information used with a predictable time. Critical demand to support business processes that air transaction and monitoring control based applications. Characteristics include routine analysis, exception reporting, high volume online transactions and Krug, which stands for create read update Delete of business records. Operational data includes the capture and retention of any required external source data transformation based on business requirements. The appropriate summarize ation deprivation, partitioning time variants and sampling business rules are applied to the data as the E. T. L, which stands for extract transformation and load transformations. Occur Decision Support data or DSS data. This is information used with an unpredictable demand to support strategic in planning and analysis decision making functions that are at all levels of the corporation. Characteristics usually include forms of research, business modeling What if analysis and trend analysis functions. DSS data access tools tools used for performing DSS statistical and trend analysis information access tools. Tools used against GSS data to summarize and present management information for the purposes of making decisions. Examples of this could include E. I s Systems, which stands for executive information systems and geographical information systems, whose acronym is G. I. S. That's all for this lecture by for now 5. Data Distribution Framework: Hello and welcome to this lecture, which I titled the data distribution framework. Larger, more sophisticated corporations need to implement strategies for defining data and accessibility requirements and cross business unit chair ability that is optimized to meet business entity costs and performance requirements by building a framework for decisions to be made when internal systems need to be distributed. For enterprise or business unit use. Global use, etcetera, Consistency of approach to physical deployments of systems, data and services can help to maintain the integrity of data across the corporation. It can help to reduce the costs of a corporation as a whole and help all internal staff know what to build and deploy where thus reducing confusion, unnecessary duplication of systems and eventual corruption of information. With this in mind, I have created the following data distribution strategy, pictorial and narratives to assist the architect of student in understanding these concepts Further with respect to the above model, three key concepts for data distribution are described, and these include levels of sharing data, categorization and location of data for the corporations, customers and business units or enterprises below. Each of these concepts is explained in more detail levels of sharing here. You've got local enterprise and global levels of sharing of data. A given business entity may have local data that is, data shared by a limited group of individuals and work groups. It may have enterprise state data should by or available for the entire enterprise or business unit. When data needs to be available for all enterprises. A global environment will be established, which is accessible by all required business entities. Data categorization primarily here I'm talking about operational and decision support environments. This is actually explained further if you go back to my lecture on data categorization, framework and location, the corporate workstations, workgroup servers, enterprise servers and global servers. These locations air logical as data may reside anywhere within the enterprise based on rules to be defined and contracts negotiated with the business entities. For example, one physical enterprise server could be partitioned to serve to business entities or separate servers may support to business entities to keep them completely separate. To use the model above data needs to be distributed according to consistent standards or rules. These data distribution rules can be viewed through the illustration appearing next. If you take a look at this image here, you've got a number of options for data distribution. For example, if you're sharing level is global and it has common requirements, common design and common implementation, then it can reside on a global server. If it is, for example, halfway down local requirements, only unique design, unique implementation, then you may be sitting on a workgroup server. It's that spend a few minutes on this pictorial and you'll get the gist of what I'm talking about. With respect to data distribution options, the data distribution option stream work shown above is used in conjunction with the data distribution strategy model for each level of sharing. It describes how the characteristics of requirements, design and implementation may be unique or common across our business entity base. Depending on which characteristics apply, a data deployment strategy has been defined. This deployment strategy should be integrated with the deployment strategies defined for applications, so you do not do these without the other. It can be distributed through a number of different strategies, each one having its own level of appropriateness for a given situation. Next, The three primary replication approaches available from RT BMS vendors include primarily snapshot two phase commit and replication. Although the snapshot approach is usually a bundle component of the base product offering, two face commit and replication may require an additional product purchase from the RT BMS vendor. In the final deployed physical environment there made conditions where each one of these approaches may be appropriate. General rules for the usage of each of these approaches are as follows. Snapshot. This is a point in time copy of a database not recommended as part of operational mission critical environments As copied. Database integrity is not verified and is only accurate across the enterprise at the point in time when the entire table or tables have been copied. Integrity between related tables is also not verified during the copy function, so the entire data base needs to be frozen until the copy is completed. Two Phase commit. This is a timed update approach that ensures integrity across the enterprise by forcing all related data bases to be up and available at time of transmission. The downside to this approach is that if any database server is down, it impacts all servers. That is to say none will be updated until all servers air again. available and operational replication. This update approach also guarantees integrity across the enterprise by preserving transactions, but is not constrained in its update functions by a server being down. The shortfall of this approach is that data bases across the enterprise can be out of sync for periods of time. Using this data distribution framework concept as a guideline when you were building out your corporation's business and informational systems will help you in your company to ensure consistency in how you build up functionality across your global enterprise. And this will save your corporation money and will help to maintain the overall quality of your internal information and data. That's all for this lecture by for now. 6. Congratulations on Class Completion: Hello and congratulations on completing this class. It's great to see and making it all the way through. And I hope I was able to give you some useful and lasting knowledge that will help you and whatever you're wishing to achieve. If you're liking the content that I'm creating, I'm very much looking forward to seeing you in more of my classes, which you confined in this site by going to my profile section. You can still reach me through this class. If you have questions by starting a discussion with me, I'll be more than happy to respond if I hear from you. A second thought that comes to mind is that if you have appreciated the content I have created, please give me a thumbs up where and when the site ask you to to rate my class. This helps me trend better in the system and helps me to reach more people who could benefit from the training I'm trying to create. And if you choose to share the class link with friends or anyone else, thank you for that additional support for my creations as well. Bye for now,