StudentShare
Contact Us
Sign In / Sign Up for FREE
Search
Go to advanced search...
Free

Data Base Administration - Assignment Example

Cite this document
Summary
"Data Base Administration" paper states that 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…
Download full paper File format: .doc, available for editing
GRAB THE BEST PAPER96.7% of users find it useful
Data Base Administration
Read Text Preview

Extract of sample "Data Base Administration"

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
Cite this document
  • APA
  • MLA
  • CHICAGO
(Data Base Administration Assignment Example | Topics and Well Written Essays - 1500 words, n.d.)
Data Base Administration Assignment Example | Topics and Well Written Essays - 1500 words. https://studentshare.org/information-technology/1785460-database-administration
(Data Base Administration Assignment Example | Topics and Well Written Essays - 1500 Words)
Data Base Administration Assignment Example | Topics and Well Written Essays - 1500 Words. https://studentshare.org/information-technology/1785460-database-administration.
“Data Base Administration Assignment Example | Topics and Well Written Essays - 1500 Words”. https://studentshare.org/information-technology/1785460-database-administration.
  • Cited: 0 times

CHECK THESE SAMPLES OF Data Base Administration

The Importance of Graduate Study at This Time in My Life

I earned my BA in Business administration from Baruch College back in 2001, and I have updated my professional skills and work experience over the past five years.... I am currently employed by the New York Department of Transportation.... I work in the Parking Bureau's Pre-Paid Card Unit as a Principle Administrative Assistant....
3 Pages (750 words) Essay

Employee Feedback Questionaire

Health Information administration Linking Leadership Style to Staff Satisfaction and Motivation in a Healthcare Organization: A Qualitative and Quantitative ResearchIn APA Style(Name of student)In partial fulfillment of the requirements for (subject)(Instructor's name)(Submission date)Health Information administration 2Problem Statement In the past, the workforce is regarded as merely a regular contributor in the services and goods production.... ealth Information administration 3Data Analysis With the high level of positive responses from the respondents, it can be inferred that the class is highly motivated and satisfied with their working environment and working relationship with their immediate supervisor....
2 Pages (500 words) Essay

Registration System

This history of the customer can be checked by the main HQ or the local administration and they can base any booking discounts from this customer's history.... Both of the main HQ and the local administration can update the room type, availability and pricing of their rooms.... Both the main HQ motel administration and other local administrations can check their customer's history.... Costumer's history includes with the customer's booking number, the total of booking the customer made the other details of his booking from this the company will base their decision if they will give a special discount of not....
4 Pages (1000 words) Assignment

Database Administration -2

It is sure, if the data gathering and storing process is malfunctioning, the established data will be incorrect as well; this process is known to be as Garbage in Garbage Out (GIGO) (Rushinek & Rushinek, 1985).... Updated and current data have been always important for any organization; therefore, the stored data has to be updated and current with respect to the requirements of the organization.... A DBMS system contains metadata i-e data about data, in other words the data and a full explanation of the data....
4 Pages (1000 words) Essay

Case study # An Invisible Superintendent

Sagossi has the duty to attend to the need of other Educational administration affiliation Educational administration In this case, Dr.... For this reason, she was angered by the school administration to ignore the seriousness of the issue.... An Invisible Superintendent adapted from Theodore Kowalski, Case Studies on Educational administration.... Washington, DC: Urban Institute, Center for the Analysis of Longitudinal data in Education Research....
2 Pages (500 words) Case Study

Media and Entertainment Law

The administrative administration for each… The aggregate sum of time distributed to the television of qualifying projects included in the station gets allotted to the TV of Any exchange of the part of the Commercial Public Sector Broadcasters inescapably concentrates the degree to which it satisfies its transmit to supply public administration TV.... It also involves administrations that, taken together, satisfy the reasons of public administration TV in the UK2....
4 Pages (1000 words) Essay

Structures of a Database vs Data Warehouse

Database administration makes it conceivable to arrange and examine these data.... To compel the performances given, operational frameworks store data in numerous tables, each with a base count of fields.... Accordingly, the data necessities in a common transaction framework for the most part oblige standardized structures that results in numerous tables, each containing the base count of qualities.... A data warehouse is a sort of database that incorporates duplicates of transaction data from unique source frameworks....
2 Pages (500 words) Assignment

The Benefits of Computerizing Employees Records

These computer systems would significantly reduce the amount of administration time required in the collection and storage of data, together with any calculations needed to be attached to those records.... The assignment presents an example of a particular data structure aimed at aiding in employee tracking....
7 Pages (1750 words) Assignment
sponsored ads
We use cookies to create the best experience for you. Keep on browsing if you are OK with that, or find out how to manage cookies.
Contact Us