Help
Options
Didn't know it?
click below
click below
Knew it?
click below
click below
Don't Know
Remaining cards (0)
Know
retry
shuffle
restart
0:00
Embed Code - If you would like this activity on your web page, copy the script below and paste it into your web page.
Normal Size Small Size show me how
Normal Size Small Size show me how
SA3-InfoMan(Mod9-10)
SA3-InfoMan
| Question | Answer |
|---|---|
| Responsible for the overall management of data resources in an organization, including data definitions and standards | Data Administration (managerial) |
| Physical database design and for dealing with technical issues such as security enforcement, database performance, and backup and recovery | Database Administration (technical) |
| Can the same person may perform both roles(Data Administration, and Database Administration) | YES |
| • Analyzing and designing databases • Selecting DBMS and software tools • Installing/upgrading DBMS • Troubleshooting | TRADITIONAL DATABASE ADMINISTRATION FUNCTIONS(p1) |
| • Tuning database performance • Improving query processing performance • Managing data security, privacy, and integrity • Data backup and recovery | TRADITIONAL DATABASE ADMINISTRATION FUNCTIONS(p2) |
| • Data policies, procedures, standards • Planning • Data conflict (ownership) resolution • Managing the information repository • Internal marketing of DA concepts • Raise awareness of the importance of practices | TRADITIONAL DATABASE ADMINISTRATION FUNCTIONS(p3) |
| • Increased use of procedural logic • Proliferation of Internet-based applications • Increase use of smart devices • Cloud computing and database/data administration | TRENDS IN DATABASE ADMINISTRATION |
| • Multiple dissimilar data definitions, causing data integration problems / poor data value • Inappropriate data sources and timing, causing lowered reliability • Inadequate familiarity, causing ineffective use of data for planning and strategy | INEFFECTIVE DATA MANAGEMENT MAY RESULT IN POOR DATA QUALITY(p1) |
| • Accidental deletion or destruction • (update cust set name = “” without using a ‘where’ clause!) • Poor response time and excessive downtime • Unauthorized access, leading to embarrassment to organization | INEFFECTIVE DATA MANAGEMENT MAY RESULT IN POOR DATA QUALITY(p2) |
| New role, coming with growth in data warehouses / rather than operational data work Similar to DA/DBA roles Emphasis on integration and coordination of metadata/data across many data sources. | DATA WAREHOUSE ADMINISTRATION(p1) |
| Specific roles: • Support decision support applications • Manage data warehouse growth • Establish service level agreements regarding data warehouses and data marts | DATA WAREHOUSE ADMINISTRATION(p2) |
| • An alternative to proprietary packages such as Oracle, Microsoft SQL Server, or Microsoft Access • MySQL – an open-source DBMS • Less expensive than proprietary packages | OPEN SOURCE(VS PROPRIETARY) DB MANAGEMENT(p1) |
| • Source code available, for modification • Absence of complete documentation • Ambiguous licensing concerns • Not as feature-rich as proprietary DBMSs • Vendors may not have certification programs | OPEN SOURCE(VS PROPRIETARY) DB MANAGEMENT(p2) |
| Accidental losses attributable to: • Human error • Software failure • Hardware failure Theft and fraud Loss of data integrity Loss of availability (e.g., through sabotage) For each, what preventative measures can be taken? | THREATS TO DATA SECURITY |
| • Loss of privacy (personal data) • Loss of confidentiality (corporate data) | Loss of privacy or confidentiality |
| • Principal of ‘least privilege’ • Encrypting data ‘in transit’ (over the wire) • Encrypting data ‘at rest’ (sitting on disk) | Production environments |
| • Protect PII (personally Identifiable Information – SSN, Name/Address, Credit Card numbers, etc.) • Can be done programmatically • Number of vendors that provide elaborate methods mapping real data to fictitious data | Development environments - Data Masking |
| • Views or subschemas • Integrity controls • Authorization rules • User-defined procedures • Encryption • Authentication schemes • Backup, journalizing, and checkpointing | Database Software Security Features |
| • Subset of the database that is presented to one or more users • User given access privilege to view without allowing access privilege to underlying tables | Views |
| • Protect data from unauthorized use • Domains–set allowable values • Assertions–enforce database conditions • Triggers – prevent inappropriate actions, invoke special handling procedures, write to log files | Integrity Controls |
| • Controls incorporated in the data management system • Restrict: • access to data • actions that people can take on data • Authorization matrix for: • Subjects • Objects • Actions • Constraints | Authorization Rules |
| • access to data • actions that people can take on data | Restrict |
| • Subjects • Objects • Actions • Constraints | Authorization matrix for |
| Obtain a positive identification of the user | Goal |
| First line of defense | Passwords |
| • Should be at least 8 characters long • Should combine alphabetic and numeric data • Should not be complete words or personal information • Should be changed frequently | Password Requirements |
| • Passwords can be flawed: • Users share them with each other • They get written down, could be copied • Automatic logon scripts remove need to explicitly type them in • Unencrypted passwords travel the Internet | Strong Authentication |
| • Two factor–e.g., key FOB, plus PIN • Three factor–e.g., smart card, biometric, PIN | Possible solutions: |
| • The process by which changes to operational systems and databases are authorized • For database, changes to: schema, database configuration, updates to DBMS software • Segregation of processes: development, test, production | IT Change Management - document all changes - ITIL Methods |
| • Hiring practices, employee monitoring, security training, separation of duties | Personnel controls |
| Swipe cards, equipment locking, check-out procedures, screen placement, laptop protection | Physical access controls |
| • Personnel controls • Hiring practices, employee monitoring, security training, separation of duties • Physical access controls Swipe cards, equipment locking, check-out procedures, screen placement, laptop protection | Logical Access to Data |
| • Mechanism for restoring a database quickly and accurately after loss or damage • Recovery facilities: • Backup Facilities • Journalizing Facilities • Checkpoint Facility • Recovery Manager | Database Recovery |
| • DBMS copy utility that produces backup copy of the entire database or subset • Periodic backup (hourly, nightly, weekly) • Cold backup–database is shut down • Hot backup–system can be available • Backups stored in secure, off-site location | Back-up Facilities - various amount of downtime |
| Periodic backup must be? | (hourly, nightly, weekly) |
| Database is shut down | Cold backup |
| System can be available | Hot backup |
| • Audit trail of transactions and database updates • Transaction log–record of essential data for each transaction processed against the database | Journalizing Facilities(p1) |
| • Database change log–images of updated data • Before-image–copy before modification • After-image–copy after modification | Journalizing Facilities(p2) |
| Record of essential data for each transaction processed against the database | Transaction log |
| Images of updated data | Database change log |
| copy before modification | Before Image |
| copy after modification | After Image |
| • Disk Mirroring–switch between identical copies of databases • Transaction Integrity–commit or abort all transaction changes • Restore/Rerun–reprocess transactions against the backup | Recovery and Restart Procedures(p1) |
| • Backward Recovery (Rollback)–apply before images • Forward Recovery (Roll Forward)–apply after images (preferable to restore/rerun) | Recovery and Restart Procedures(p1) |
| Switch between identical copies of databases | Disk Mirroring |
| Commit or abort all transaction changes | Transaction Integrity |
| Reprocess transactions against the backup | Restore/Rerun |
| Apply before images | Backward Recovery (Rollback) |
| Apply after images (preferable to restore/rerun) | Forward Recovery (Roll Forward) |
| • Transaction cannot be subdivided • Constraints don’t change from before transaction to after transaction • Database changes not revealed to users until after transaction has completed • Database changes are permanent | Transaction ACID Properties |
| • Transaction cannot be subdivided | Atomic |
| • Constraints don’t change from before transaction to after transaction | Consistent |
| • Database changes not revealed to users until after transaction has completed | Isolated |
| • Database changes are permanent | Durable |
| Problem–in a multi-user environment, simultaneous access to data can result in interference and data loss (lost update problem) Solution___ | Concurrency Control |
| The process of managing simultaneous operations against a database so that data integrity is maintained and the operations do not interfere with each other in a multi-user environment | Concurrency Control |
| Problem–in a multi-user environment, simultaneous access to data can result in interference and data loss (lost update problem) Solution–Concurrency Control | Control Concurrent Access |
| • Finish one transaction before starting another | Serializability |
| Concurrency Control Techniques | Locking Mechanisms |
| • Serializability • Locking Mechanisms | Concurrency Control Techniques |
| Simultaneous access causes updates to cancel each other. A similar problem is the ____ ____ problem. | Inconsistent read |
| used during database updates | Locking level: Database |
| used for bulk updates | Locking level: Table |
| very commonly used | Locking level: Block or page |
| only requested row; fairly commonly used | Locking level: Record |
| requires significant overhead; impractical | Locking level: Field |
| –Read but no update permitted. Used when just reading to prevent another user from placing an exclusive lock on the record | Locking level: Shared lock |
| No access permitted. Used when preparing to update | Locking level: Exclusive lock |
| • Locking level: • Types of locks: | Locking Mechanisms |
| Setting installation parameters | DBMS Installation |
| • Set cache levels • Choose background processes • Data archiving | Memory and Storage Space Usage |
| • Use striping • Distribution of heavily accessed files | Input/output (I/O) Contention |
| Monitor CPU load / competing processes | CPU Usage |
| • Modification of SQL code in applications • Use of heartbeat queries (aka health tests) | Application tuning |
| • DBMS Installation • Memory and Storage Space Usage • Input/output (I/O) Contention • CPU Usage • Application tuning | Database Performance Tuning |
| ___ is expensive (financially as well as to an organization’s reputation) | Downtime |
| Basic unit of storage; composed of rows | Table |
| Logically represents subsets of data from one or more tables | View |
| Generates numeric values | Sequence |
| Improves the performance of data retrieval queries | Index |
| Gives alternative names to objects | Synonym |
| -To restrict data access -To provide data independence -To make complex queries easy -To present different views of the same data | Advantages of Views |
| Feature: Number of tables | Simple Views: One, Complex Views: One or More |
| Feature: Contain functions | Simple Views: No, Complex Views: Yes |
| Feature: Contain groups of data | Simple Views: No, Complex Views: Yes |
| Feature: DML operations through a view | Simple Views: Yes, Complex Views: Not Always |
| Syntax for Creating Views | CREATE VIEW [name] Ex: CREATE VIEW empvu80 |
| view by using the iSQL*Plus DESCRIBE command | DESCRIBE empvu80 |
| Select the columns from this view by the given alias names. | AS SELECT employee_id ID_NUMBER AS SELECT [From Table] [Subquery_Name] |
| Retrieving Data from a View | SELECT * FROM salvu50; |
| Modify the EMPVU80 view by using a ___ ___ ___ ___ clause. Add an alias for each column name | CREATE OR REPLACE VIEW |
| What is CREATE OR REPLACE VIEW for? | Modifying a View |
| View that contains group functions to display values from two tables | Complex View |
| You can usually perform DML operations on ___ ___. | Simple views |
| You cannot remove a row if the view contains the following: | • Group functions • A GROUP BY clause • The DISTINCT keyword • The pseudocolumn ROWNUM keyword |
| You cannot modify data in a view if it contains: | • Group functions • A GROUP BY clause • The DISTINCT keyword • The pseudocolumn ROWNUM keyword • Columns defined by expressions |
| You cannot add data through a view if the view includes: | • Group functions • A GROUP BY clause • The DISTINCT keyword • The pseudocolumn ROWNUM keyword • Columns defined by expressions • NOT NULL columns in the base tables that are not selected by the view |
| You can ensure that DML operations performed on the view stay in the domain of the view by using the ___ ___ ___ clause: | WITH CHECK OPTION |
| Any attempt to INSERT a row with a department_id other than 20, or to UPDATE the department number for any row in the view fails because it violates the ___ ___ ___ constraint. | WITH CHECK OPTION |
| • You can ensure that no DML operations occur by adding the ___ ___ ___ option to your view definition. • Any attempt to perform a DML operation on any row in the view results in an Oracle server error. | WITH READ ONLY |
| You can remove a view without losing data because a view is based on underlying tables in the database. | DROP VIEW view; |
| • Can automatically generate unique numbers • Is a shareable object • Can be used to create a primary key value • Replaces application code • Speeds up the efficiency of accessing sequence values when cached in memory | Sequence |
| Define a sequence to generate sequential numbers automatically | CREATE SEQUENCE sequence [INCREMENT BY n] [START WITH n] [{MAXVALUE n | NOMAXVALUE}] [{MINVALUE n | NOMINVALUE}] [{CYCLE | NOCYCLE}] [{CACHE n | NOCACHE}]; |
| • Create a sequence named DEPT_DEPTID_SEQ to be used for the primary key of the DEPARTMENTS table. • Do not use the CYCLE option. | CREATE SEQUENCE dept_deptid_seq INCREMENT BY 10 START WITH 120 MAXVALUE 9999 NOCACHE NOCYCLE; |
| Returns the next available sequence value. It returns a unique value every time it is referenced, even for different users. | NEXTVAL |
| Obtains the current sequence value | CURRVAL |
| Must be issued for that sequence before CURRVAL contains a value. | NEXTVAL |
| Insert a new department named “Support” in location ID 2500: | INSERT INTO departments ( department_id, department_name, location_id ) VALUES ( dept_deptid_seq.NEXTVAL, 'Support', 2500 ); |
| View the current value for the DEPT_DEPTID_SEQ sequence: | SELECT dept_deptid_seq.CURRVAL FROM dual; |
| ___ sequence values in memory gives faster access to those values. | Caching |
| Gaps in sequence values can occur when: | • A rollback occurs • The system crashes • A sequence is used in another table |
| Change the increment value, maximum value, minimum value, cycle option, or cache option: | ALTER SEQUENCE dept_deptid_seq INCREMENT BY 20 MAXVALUE 999999 NOCACHE NOCYCLE; |
| You must be the owner or have the ___privilege for the sequence. | ALTER |
| • Only future sequence numbers are affected. • The sequence must be dropped and re-created to restart the sequence at a different number. • Some validation is performed. | Other Guidelines for modifying a Sequence. |
| To remove a sequence, use the ___ statement: | DROP |
| • Is a schema object • May be used by the Oracle server to speed up the retrieval of rows by using a pointer • Can reduce disk input/output (I/O) by using a rapid path access method to locate data quickly | An index(p1) |
| • Is independent of the table that it indexes • Is used and maintained automatically by the Oracle server | An index(p2) |
| A unique index is created automatically when you define a ___ ___ or ___ constraint in a table definition. | PRIMARY KEY or UNIQUE |
| Users can create nonunique indexes on columns to speed up access to the rows. | Manually Making Indexes |
| Create an index on one or more columns | CREATE [UNIQUE] [BITMAP] INDEX index ON table (column[, column]...); |
| Improve the speed of query access to the LAST_NAME column in the EMPLOYEES table | CREATE INDEX emp_last_name_idx ON employees (last_name); |
| -A column contains a wide range of values -A column contains a large number of null values | Create an index when |
| -One or more columns are frequently used together in a WHERE clause or a join condition -The table is large and most queries are expected to retrieve less than 2% to 4% of the rows in the table | Create an index when |
| -The columns are not often used as a condition in the query -The table is small or most queries are expected to retrieve more than 2% to 4% of the rows in the table | Do not create an index when: |
| -The table is updated frequently -The indexed columns are referenced as part of an expression | Do not create an index when: |
| Remove an index from the data dictionary by using the DROP INDEX command | DROP INDEX index; |
| Remove the emp_last_name_idx index from the data dictionary | DROP INDEX emp_last_name_idx; |
| To drop an index, you must be the owner of the index or have the ___ ___ ___ privilege. | DROP ANY INDEX |
| Simplify access to objects by creating a synonym (another name for an object) | Synonyms |
| Simplify access to objects by creating a synonym (another name for an object). With synonyms, you can: | • Create an easier reference to a table that is owned by another user • Shorten lengthy object names |
| Create a Synonym | CREATE [PUBLIC] SYNONYM synonym FOR object; |
| Create a shortened name for the DEPT_SUM_VU view: | CREATE SYNONYM d_sum FOR dept_sum_vu; |
| Drop a synonym: | DROP SYNONYM d_sum; |
| PL/SQL Stands for | Procedural Language extension to SQL |
| Is Oracle Corporation’s standard data access language for relational databases | PL/SQL |
| Seamlessly integrates ___ ___ with SQL | procedural constructs |
| Provides a block structure for executable units of code. Maintenance of code is made easier with such a well-defined structure. | PL/SQL |
| Provides procedural constructs such as: • Variables, constants, and data types • Control structures such as conditional statements and loops • Reusable program units that are written once and executed many times | PL/SQL |
| • Integration of procedural constructs with SQL • Improved performance | Benefits of PL/SQL |
| • Modularized program development • Integration with Oracle tools • Portability • Exception handling | Benefits of PL/SQL |
| Variables, cursors, user-defined exceptions | DECLARE (optional) |
| • SQL statements • PL/SQL statements | BEGIN (mandatory) |
| • Actions to perform when errors occur | EXCEPTION (optional) |
| (mandatory) | END; (mandatory) |
| DECLARE -- Declare variables here (optional) BEGIN -- Statements to be executed EXCEPTION -- Exception handling (optional) END; | Anonymous |
| CREATE PROCEDURE procedure_name IS BEGIN -- Statements to be executed EXCEPTION -- Exception handling (optional) END; | Procedure |
| CREATE FUNCTION function_name RETURN datatype IS BEGIN -- Statements to be executed RETURN value; EXCEPTION -- Exception handling (optional) END; | Function |
| Anonymous blocks Application procedures or functions Application packages Application triggers Object types | Tools Constructs |
| Anonymous blocks Stored procedures or functions Stored packages Database triggers Object types | Database Server Constructs |
| (AT THIS POINT JUST CODE OR LOOK AT THE PPT MODULE 11) | (AT THIS POINT JUST CODE OR LOOK AT THE PPT MODULE 11) |
Created by:
user-1776272