Chapter slides in the attachment. XYZ Corporation does not use a tool to implement changes to database objects. The IS department made a data request that would require that you insert a column...

1 answer below »

Chapter slides in the attachment.










XYZ Corporation does not use a tool to implement changes to database objects.


The IS department made a data request that would require that you insert a column between the third and fourth columns of the Customers table within the database.This table has a ten columns currently.



Task:Describe the preparation process you would employ to make such a change.



What to Submit


1. A single Word file



Note: If you include SQL queries, this content should be in .sqlfile



Naming convention of all files:
firstnameLastname_Module6Assignment.docx




Database Administration: The Complete Guide to Practices and Procedures Database Administration: The Complete Guide to Practices and Procedures Chapter 7 Database Change Management Agenda • Change Management Requirements • Types of Changes – DBMS Software – Hardware Configuration – Logical and Physical Design – Applications – Physical Database Structures • Impact of Change on Database Structures • Questions Database Change Management Change is the only constant in today’s complex business environment. Each of the following comprises a different facet of the “change management” experience: • The physical environment or workplace changes to accommodate more employees, fewer employees, or perhaps just different employees with new and different skill sets. • The organization changes such that “things” like processes or methodology have to adapt to facilitate a quicker pace for product and service delivery. • The network infrastructure changes to provide support for a growing, and perhaps geographically dispersed, workforce. • Applications and systems change to perform different processes with existing data or to include more or different types of data. • The type and structure of data changes, requiring modifications to the underlying database schemata to accommodate the new data. Why Are Changes Required Change is the only constant in today’s complex business environment. Many factors require database change, including: • Changes to application programs that require additional or modified data elements • Performance modifications and tweaks to make database applications run faster • Regulatory changes that mandate storing new types of data, or the same data for longer periods of time • Changes to business practices, requiring new types of data • Technological changes that enable databases to store new types of data and more data than ever before http://www.craigsmullins.com/dbta_061.htm http://www.craigsmullins.com/dbta_061.htm Change Management Requirements • Proactivity • Intelligence • Planning Analysis • Impact Analysis • Automation • Standardization of Procedure • Reliable and Predictable Process • Availability • Quick and Efficient Delivery http://www.craigsmullins.com/dbt_1000.htm http://www.craigsmullins.com/dbt_1000.htm DBA Perspective on Change • The DBA is the custodian of database changes. – DBA is not usually the one to request change • Programmers • Application owners • Business users – DBA carries out most changes • To effectively implement database changes the DBA must consider each requirement as outlined on the previous slide Difficulty of Database Change • Today’s major DBMS products do not support fast and efficient database structure changes. – Each DBMS provides differing levels of support for making changes to its databases, but none easily supports every type of change that might be required. • Most organizations have at least two, and sometime more, copies of each database. – A single change might need to be enacted upon a large number of database instances. Types of Changes • DBMS Software • Hardware Configuration • Logical and Physical Design • Applications • Physical Database Structures DBMS Software • DBA manages the migration to new DBMS versions and releases – Complexity depends on the new features and functions supported by the new version. – Additional complexity if features are removed • Because databases and programs may need to change if the removed features were being used. • DBA must create the proper policies and procedures for the proper use of each new DBMS feature Hardware Configuration • The DBMS may require hardware upgrades or configuration changes. • DBA works in conjunction with the system programmers and administrators responsible for setting up and maintaining the hardware. • Also, when hardware changes for other reasons, the DBMS configuration may have to change. – System parameter and database structure changes Logical and Physical Database Design • When the database changes, the blueprints that define the database must also change. • Perhaps changes are made at the logical level first and then migrated through physical design and then to the actual database. – If not, then changes made to the actual database must be retrofitted to the models or they cease to be useful • Data modeling tools are helpful Applications • Application changes need to be synchronized with database changes. – Application changes usually accompany physical database structure changes. • After all, why modify the database if the modified data is not going to be accessed? • When the database change is migrated to a production environment, the application change must be migrated as well. – Failure to do so will render the database change ineffective. – If the application change is backed off, the database change should be backed off, as well. • And vice versa. Physical Database Structures • The most complicated type of change for DBAs is making changes to physical database structures. – Planning – Analyzing – Implementing Impact of Change on Database Structures • DDL is used to build and modify database structures: – CREATE – DROP – ALTER • Not every aspect of a database object can be changed by using the ALTER statement. – Some types of changes require the database object to be dropped and recreated with the new parameters. – The exact specifications for what can, and cannot, be changed using ALTER differs from DBMS to DBMS. The Limitations of ALTER Changes Not Likely to be Supported:  Changing the name of some objects.  Moving an object to another database.  Changing the number of table space partitions or data files or removing a partition.  Moving a table from one table space to another.  Rearranging the order of columns in a table.  Removing columns from a table.  Changing the definition of a key (P or F)  Changing a view (adding or removing cols).  Modifying the contents of a trigger.  Changing a hash key.  Changing table clustering.  And so on... Database Table Space or DB Space Table Index View Synonym Column Trigger Database Object Hierarchy Simple Database Changes • Adding a new column to the end of a table: A Little More Difficult • Modifying free space for a table space: • Simple… but the free space will NOT magically appear. – Usually requires a REORG after the ALTER Very Difficult EMPNO LAST_NAME FIRST_NAME PAY_GRADE HIRE_DATE 00010 MULLINS CRAIG 10 2007-10-12 00020 CODD TED 9 1999-04-03 00030 SIMPSON HOMER 2 2001-02-20 00040 JOHNSON HOWARD 5 2005-10-10 00050 KISON RANDOLPH 7 2008-09-10 00060 WAYNE BRUCE 8 1998-07-01 … … … … … What if we want to add a column right here? Adding a Column to Middle of a Table – Retrieve current table def by querying sys catalog. – Retrieve def for any views on that table. – Retrieve def for all indexes on that table. – Retrieve def for all triggers on that table. – Capture all referential constraints for the table & related tables. – Retrieve all security. – Obtain a list of all programs that access the table. – Unload the data in the table. – Drop the table, which in turn drops any related objects. – Re-create table adding the new column. – Reload the table using the unloaded data from step 8. – Re-create any referential constraints. – Re-create any triggers, views and indexes for the table. – Re-create the security authorizations from step 6. – Examine each application program to determine if changes are required for it to continue functioning appropriately. Database Change Management Tools Change management tools can provide: • A reduction in the amount of time required to specify what needs to change • A more simple and elegant method of analyzing the impact of database changes • A reduction in technical knowledge needed to create, alter and drop database objects • Ability to track all changes over time • An increase in application availability by reducing the time it takes to perform changes. Comparing Database Structures • The DBA may need to compare the database of one environment to another – With multiple environments the DBA must be able to identify that all changes were applied in each environment accordingly… – And to rectify the situation when differences are found • Usually accomplished with an add-on tool Comparing Databases Without a Tool • Must save the DDL scripts used to create databases and keep them up-to-date. • Every change made to the database must also be made to the DDL scripts. – ALTER statements can change the database, but will not change the DDL scripts. – DBA will need to either update the DDL scripts either by appending the ALTER statements to the appropriate DDL scripts or by changing the DDL scripts to reflect the effect of the ALTER statement. • Neither approach is ideal Requesting Database Changes • Institute policies governing how changes are to be requested and implemented. • It is not reasonable to expect database changes to be implemented immediately, or even the same day. • The DBA group should be held accountable to reasonable deadlines for implementing database changes. • The DBA may need to modify requests based on his knowledge of the DBMS. – Any deviations from the request must be discussed with the development team to ensure that they are still workable within the realm of the application. Standardized Change Requests • Establish standardized forms for implementing database changes. – Standardized change request forms prevent miscommunications. • The form should include all pertinent information related to each change including, at a minimum: – operating system − database subsystem or instance name – object owner − object name – object type − desired change – date requested • The form should include sign-off boxes for those personnel that are required to sign off on the change before it can be implemented. • Required sign-offs should include at least the application development team leader and a senior DBA – Could also include a business unit representative, DA, or SA, depending on the nature of the request. Checklists • Many DBAs develop checklists that they follow for each type of database change. • These checklists may be incorporated into an online change request system so that the DBA can walk through changes as required. • Share checklists between DBA and application teams to verify that each step in the database change and turnover process is completed successfully. Communication • DBAs must educate developers on how to request database changes – Unrealistic service expectations can be eliminated through proper education Compliance • Integrate regulatory compliance demands into your database change management practices. • Many regulations require the logging of changes to track who made what change to which piece of data when. • Be sure to properly
Answered Same DayMar 06, 2021

Answer To: Chapter slides in the attachment. XYZ Corporation does not use a tool to implement changes to...

Neha answered on Mar 07 2021
151 Votes
/*
The addition of a new column in between two columns can be done by using alter command. The alte
r command allows to change the structure of an already exisitng
table. Alter does not alter the data but it allows to add column, remove columns, change name or type of any columns. We can use after...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here