Data Base Administration - Assignment Example

Database Administration 3rd November Teacher’s DBA Tasks ID Table of Contents Database Administration Table of Contents 2 Introduction 3 Task #1: Table with a Foreign Key for a Compound/Composite Primary Key 4 Task #2: Daily Challenges Faced by DBA and Justification for One DBA Task 4 Task #3: Create Product and Order_line with Correct Relationship 5 Task #4: SQL Command for Converting Logical Schema into Physical Database Design and Explanation of Physical and Logical Data Independence in the Exercise 5 Task #5: Preparation of a Database Architectures and Steps and Description of Tasks in the Lab 6 Task #6: Benefits of using Client/Server Architecture for Database Applications 6 Task #7: Three Types of Different DBA Tools 7 Conclusion 9 References 10 Introduction Database administrator needs to understand development of applications along with tasks of database design and administrator to remain effective in their job. Database administrators has to face challenges of not only resolving technological issues, but problems arising from influential developers lacking basic understanding of database design and development that can affect performance and maintenance. Entity relationship diagram assists in representing abstraction of logically grouped entities and their relationship to define underlying structure of business objects. Physical data model further derives generation of schema from logical schema to create primary key, foreign key, relationship of objects, indexes, views, stored procedures, and other database objects to support applications by storing and processing data for business functions. Physical and logical data independence separates applications from database dependency in running business logic. Preparation of database architecture provides steps of applying data manipulation language in different schema. Client/server architecture for a database application further underpins the concept of independence of data processing and application logic. Finally, database administrator tool provides utilities for database modeling and design, change management, and backup process to facilitate tasks of a DBA. Task #1: Table with a Foreign Key for a Compound/Composite Primary Key LOC tables have referential integrity constraint with a compound/composite primary key (Postalcode, country) with table GEOCOORDINATES in following example: CREATE TABLE SCPOMGR.GEOCOORDINATES ( POSTALCODE VARCHAR2(10 CHAR) NOT NULL, COUNTRY VARCHAR2(20 CHAR) NOT NULL, LATITUDE NUMBER(9,5) NOT NULL, LONGITUDE NUMBER(9,5) NOT NULL); CREATE TABLE LOC (LOC VARCHAR2 (50 CHAR) NOT NULL, DESCR VARCHAR2 (50 CHAR) NOT NULL, LOC_TYPE INTEGER NOT NULL, POSTALCODE VARCHAR2 (10 CHAR), COUNTRY VARCHAR2 (20 CHAR)); ALTER TABLE GEOCOORDINATES ADD (CONSTRAINT GEOCOORDINATES_PK PRIMARY KEY (POSTALCODE, COUNTRY)); ALTER TABLE LOC ADD (CONSTRAINT LOC_GEOCOORDINATES_FK1 FOREIGN KEY (POSTALCODE, COUNTRY) REFERENCES SCPOMGR.GEOCOORDINATES (POSTALCODE, COUNTRY)); Task #2: Daily Challenges Faced by DBA and Justification for One DBA Task Although DBA faces the daily challenges in performing technical task, but interaction with developer is critical in running application and seamless flow of information in the system. Database server evaluation, installation, planning, creating and opening, back up, user creation, design, back up, Performance tuning, patches, and roll out to hosts for database are daily tasks in the life of DBA (Oracle, 2012). DBA can face challenges of boredom from stereotype or overwhelming responsibility in resolving chaos arising from environment issues that requires 24/7 working hours. The most challenging tasks for DBA is to deal with developers of inadequate understanding those can create havoc to the system while using locking or caching. But life can be easy with experienced developers who are well-versed in the system. Task #3: Create Product and Order_line with Correct Relationship CREATE TABLE PRODUCT (PRODUCT_ID VARCHAR2 (50) PRIMARY KEY, PRODUCT_DESCRIPTION VARCHAR2 (50) NOT NULL, PRODUCT_FINSH DATE, UNIT_PRICE NUMBER); CREATE TABLE ORDER_LINE (ORDER_ID NUMBER REFERENCES ORDERS, PRODUCT_ID VARCHAR2 (50) REFERENCES PRODUCT, ORDER_QUANTITY NUMBER); ALTER TABLE ORDER_LINE ADD (CONSTRAINT ORDER_LINE_PK PRIMARY KEY (ORDER_ID, PRODUCT_ID)); Task #4: SQL Command for Converting Logical Schema into Physical Database Design and Explanation of Physical and Logical Data Independence in the Exercise CREATE TABLE RENTER (RENTER_ID VARCHAR2 (15) PRIMARY KEY, FIRST_NAME VARCHAR2 (50) NOT NULL, MIDDLE_INITIAL VARCHAR2 (50), LAST_NAME VARCHAR2 (50), ADDRESS VARCHAR2 (200), PHONE# VARCHAR2 (15), E_MAIL VARCHAR2 (100)); CREATE TABLE RENTAL_AGREEMENT (AGREEMENT_ID VARCHAR2 (15) PRIMARY KEY REFERENCES RENTER (RENTER_ID), BEGIN_DATE DATE NOT NULL, END_DATE DATE NOT NULL, RENTAL_AMOUNT NUMBER); CREATE TABLE PROPERTY (PROPERTY_ID VARCHAR2 (15) PRIMARY KEY REFERENCES RENTAL_AGREEMENT (AGREEMENT_ID), STREET_ADDRESS VARCHAR2 (100), CITY_STATE VARCHAR2 (50), ZIP NUMBER, NO_ROOMS NUMBER, BASE_RATE NUMBER, PROPERTY_TYPE CHAR); CREATE TABLE BEACH_PROPERTY (BLOCKS_TO_BEACH VARCHAR2 (50)); CREATE TABLE MOUNTAIN_PROPERTY (ACTIVITY VARCHAR2 (50)); Physical data independence in exercise does not affect storage or indexing organization of oracle system as data structure remains independent of application requirement i.e. business users will not see any different order process according to underlying data storage(Tablespace) in the oracle. Logical data independence also does not affect business function whether data is manipulated in the oracle system after DML operation such create, update, or delete. If new user is added into the system, he will follow same business to complete the order processing. Task #5: Preparation of a Database Architectures and Steps and Description of Tasks in the Lab Steps and Description of Tasks for preparing a Database Architectures: 1. Created Schema HQ on Database Server 2. Created Schema Sales on Database Server 3. Create DB link between schema HQ and Sales 4. Inserted data into EMP table of Sales Schema. 5. Deleted data from Dept table of HQ Schema; 6. Selected data from EMP table of Sales Schema. 7. Committed the transaction. Task #6: Benefits of using Client/Server Architecture for Database Applications The benefits of using client/server architecture for database applications are follows: Client application remains agnostic of database as they do not have to handle logic of data process. They can take input from server and reflect it in applications applying capabilities of application to display data in desired format on the screen (Oracle, 2011). Client/Server architecture provides physical data independence as client application does not have to depend on where database server is residing in distributed environment as functions of application will continue to work without any changes in code even after change of location of database servers (Oracle, 2011). Client/server architecture provides high concurrency, data integrity, and better performance to applications (Oracle, 2011). Performance tuning can happen at both application and database level (Oracle, 2011). Database server can be scaled to accommodate high volume of data (Oracle, 2011). SQL request from client can only affect network traffic that carries result whereas data processing happens on the database servers (Oracle, 2011). Task #7: Three Types of Different DBA Tools Data Modeling and Design Tools CA ERwin Data Modeler (ERwin) is a data modeling and design tools that assist in representing abstraction of logically related entities to develop information system based on business requirement (Burbank, 2011). ERwin provides logical representation of conceptual schema for database design that includes transactional systems and data marts. Erwin has capability of logical and physical data modeling as well as features of logical-to-physical transformations (DeAngelis, 2000). Logical data modeling includes placeholder of entity and attributes along with relationship among those entities. Physical data modeling includes features of tables and their columns to create them in database. Features of primary, foreign, and alternate keys are included in Erwin tool. Constraints can also be defined. Database features of indexes, views, procedures, and triggers are also included in the tool. ERwin provides feature of forward engineering that can generates data definition languages to create tables and their relationship in database environment (Burbank, 2011). Erwin also provides feature of generating ER diagram from physical schema residing in database if DBA or technical designer is interested in understanding data structure. Features of Model-to-Model comparison assist in finding what has been changed in different phase or version (Burbank, 2011). Database Change Management Tools Oracle Change Management Pack included in Oracle Enterprise Manager and Oracle SQL developer provides database change management tools to database administrators and developers to automate schema changes from lower environment such QA or development to higher environment of production. Oracle change management pack also assists in assessing the impact of application upgrades and minimizing the impact of unidentified changes in plan to reduce the time of application downtime (Oracle, 2009). This tool applies the dictionary baseline for deploying application schema objects in production environment through automation. This pack identifies database gaps in objects or data types as validation steps to roll back or correct application changes to address issues (Oracle, 2009). Oracle change management pack even pinpoints that which column or indexes has been changed in schema that allows application developers in customization of script. DBA can work with developers in identifying mismatches DBA can track changes using Oracle Change Management Pack for finding whether business logic or parameters of INIT.ora has been changed by comparing schema with gold baselines standard. ENABLE_DDL_LOGGING should be set to TRUE in INIT.ora for tracking schema changes by DBA. DBA can regenerate missing objects in few clicks after identifying missing objects in schema (Oracle, 2009). Log Manager Tools Log manager tools provide capability of managing redo logs and archive logs in which DBA can or users with necessary privileges can use toolbar for refreshing associating tabs in log manager for retrieving the latest changes (AquaClusters, 2012). Refresh buttons with cyclic arrow in the left top corner of log manager tool allows DBA to do so as shown in the following picture. (AquaClusters, 2012) Create Redo Log Group with + sign provides ability to create new redo log groups. There are tabs for editing and dropping redo log group is available in the toolbar. DBA or users with necessary privileges can also view properties of redo log group using the button Redo Log Group Properties. Switch Log File tabs allows DBA to force oracle database to start writing in new redo log file group irrespective of status of existing redo log file group that is in operation (AquaClusters, 2012). Oracle database performs checkpoint but allows taking control even when checkpoint is not complete. Take Checkpoint further ensures that checkpoint is complete and all committed transactions are written to datafiles on disk. Redo Logs Tab includes information about Group Number, Status, Members, Size, Sequence Number, First Change Number, and Archived status of log group from the control file (AquaClusters, 2012). Archive Logs Tab includes information about Name, First Change #, and Completion Time of archived log (AquaClusters, 2012). This also contains information about Sequence #, Thread #, and Archived status of Redo log file from the control file. An Archive log is created after completion of the online redo log or clearance of it. In case of duplicate archiving with same Sequence #, Thread #, and First Change #, archive log is created twice but with a different name (AquaClusters, 2012). If RMAN COPY command is used for back up and restoring of database, archive log record is also inserted (AquaClusters, 2012). Conclusion Database needs to understand challenges of technical and practical aspects database design and development along with administrative tasks. Above tasks educates about creation of physical database and logical model based on E-R diagram. Physical and logical data independence further makes maintenance of database and application independent. Understanding of client and server architecture provides independence of logical handling of application and database system. Preparation of a database architecture and availability of DBA tools provides efficiency in handling tasks. References AquaClusters (2012) Oracle DBA Tools - Log Manager. [Online], Available: https://www.aquaclusters.com/app/home/project/public/aquadatastudio/wikibook/Documentation11/page/20/15-1-3-Log-Manager [8Nov 2012] Burbank, D. (2011). Data Modeling Made Simple with CA ERwin Data Modeler r8. Technics Publications. ISBN 978-1-935504-09-2. Chien, T. (2008) Oracle Database 11g Backup and Recovery Technologies. [Online], Available: http://www.oracle.com/technetwork/database/features/availability/twp-br-11gr1-129681.pdf [3Nov 2012] Oracle (2012) Common challenges of an Oracle DB. [Online], Available: https://forums.oracle.com/forums/thread.jspa?threadID=1127952 [3Nov 2012] DeAngelis, C. (2000). Data Modeling with ERwin. Sams. ISBN 0-672-31868-7. Oracle (2009) Oracle change management pack for oracle database. [Online], Available: http://www.oracle.com/us/products/enterprise-manager/change-management-pack-11g-ds-068451.pdf [3Nov 2012] Oracle (2011) Oracle® Database Concepts 11g Release 1 (11.1): Application Architecture. [Online], Available: http://docs.oracle.com/cd/B28359_01/server.111/b28318/dist_pro.htm [3Nov 2012] Read More