1
Q
How does ineffective data administration lead to poor data quality?
A
- Multiple data defintions, causing data integration problems
- Missing data elements, causing reduction in data value
- Inappropriate data sources and timing, cuasing lowered reliability
- Inadequate familitary, causing ineffective use of data for planning and strategy
- Poor response time and excessive downtime
- Damaged, sabotage, and stolen data
- Unauthorized access leading to embarassment to organization
2
Q
What is data administration?
A
A high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporte-wide definitions and standards
3
Q
What is Database Administration?
A
A technical function that is responsible for physical database design and for dealing with technical issues such as security enforcement, database performance, and backup and recovery
4
Q
What are traditional data administration functions?
A
- Data policies, procedures, standards
- Planning
- Data conflict (ownership) resolution
- Managing the information repository
- Internal marketing of DA concepts
5
Q
What are traditional database administration functions?
A
- Analyzing and designing databases
- Selecting DBMS and software tools
- Intalling/Upgrading DBMS
- Tuning database performace
- Improving query processing performace
- Managing data security, privacy, and integrity
- Data backup and recovery
6
Q
What is data warehouse administration?
A
New role that is coming with the growth in data warehouses.
Similar to DA/DBA roles but with an emphasis on integrations and coordination of metadata/data across many data sources.
7
Q
What are the specific roles of Data warehouse administration?
A
- Support decision support applications
- Build a stable architecture - corporate information factory
- Establish service level agreements regarding data warehouses and data marts
8
Q
What is Open Source DBMS?
A
An alternative to propriertary packages such as Oracle, Microsoft SQL Server, or DB2
Examples: MySQL, PostgreSQL
9
Q
What are the advantages of Open Source DB Management?
A
- Pool of volunteer developers and testers
- Less expensive than proprietary packages
- Source code available, for modification
10
Q
What are the disadvantages of Open Source DB Management?
A
- Absence of complete documentation
- Ambiguous licensing concerns
- Not as feature-rich as proprietary DBMSs
- Vendors may not have certification programs
11
Q
What are some considerations when selecting an open source DBMS?
A
- Features
- Support
- Ease of use
- Stability
- Speed
- Training
- Licensing
12
Q
What is database security?
A
Protection of the data against accidental or intentional loss, destruction, or misuse.
13
Q
What makes database security tougher?
A
There is increased difficulty due to Internet access and client/server technologies
14
Q
What are threats to data security?
A
- Accidental losses attributed to Human error, Software failure, Hardware failure
- Theft and fraud
- Loss of privacy (personal data) and confidentiality(corporate data)
- Loss of data integrity
- Loss of availability (sabotage, etc.)
15
Q
Static HTML are easy to secure, how can you secure them?
A
- Standard database access controls
- Place Web files in protected directories on server
16
Q
Dynamic pages are harder to secure, what can be done to secure them?
A
- User authentication
- Session security
- SSL for encryption
- Restrict number of users and open ports
- Remove unnecessary programs
17
Q
For the W3C Web privacy standard, what is P3P?
A
Platform for Privacy Protection
18
Q
What does P3P address?
A
- Who collects data
- What data is collected and for what purpose
- Who is data shared with
- Can users control access to their data
- How are disputes resolved
- Policies for retaining data
- Where are policies kept and how can they be accessed
19
Q
What are features of database software security?
A
- Views or subschemas
- Integrity controls
- Authorization rules
- User-defined procedures
- Encryption
- Authentication schemas
- Backup, journalizing, and checkpointing
20
Q
What are views?
A
- Subset of the database that is presented to one or more users
- User can be given access privilage to view without allowing access privilege to underlying tables
21
Q
What are integrity controls?
A
- Protect data from unauthorized use
- Domains - set allowable values
- Assertions - Enforce database conditions
- Triggers - prevent innapropriate actions, invoke special handling procedures, write to log files
22
Q
What are authorization rules?
A
Controls incorporated in the data management system.
23
Q
What do authorization rules restrict?
A
- Access to data
- Actions that people can take on data
24
Q
What does the authorization matrix for authorization rules include that expresses the rules in the form of a table?
A
- Subjects
- Objects
- Actions
- Constraints
25
Some DBMS also provide capabilities for what to customize the authorization process?
User-defined procedures
26
What is encryption?
The coding or scrambling of data so that humans cannot read them
27
What is the goal of authentication schemes?
To obtain a positve identification of the user
28
What is the first line of defense in authentication?
Passwords
They should:
- Be at least 8 characters long
- Combine alphabetic and numeric data
- Not be complete words or personal information
- Be changed frequently
29
Why are passwords 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
30
What are some solutions to provide stronger authentication in addition to passwords?
- Two factor authentication (Smart care plus PIN)
- Three factor authentication (Smart care, biometric, PIN)
31
What is the purpose of the Sarbanes-Oxley (SOX) act?
- Requires companies to audit the access to sensitive data
- Designed to ensure integrity of public companies' financial statements
32
What do SOX audits involve?
- IT change management
- Locical access to data
- IT operations
33
What is IT change management?
The process by which changes to operational systems and databases are authorized
34
What are the most common type of changes to databases?
Schema, Database configuration, updates to DBMS software
35
What three areas are required to be segragated in IT change management?
Development, test, production
36
What are two types of security policies and procedures for logical access to data?
Personnel controls
Physical Acces controls
37
What are personnel controls?
- Hiring practices, employee monitoring, security training, separation of duties
38
What are physical access controls?
Swipe cards, equipment locking, check-out procedures, screen placement, laptop protection
39
What are IT operations?
Policies and procedures for day-to-day management of infrastructure applications, and databases in an organization.
40
What is also involved in IT operations?
Vendor Management
41
What are responsibilies of vendor management?
- Review external maintenance agreements
- Access source code? (If vendor goes out of business)
42
What is database recovery?
A mechanism for restoring a database quickly and accurately after loss or damage
43
What are some recovery facilities for database recovery?
- Backup facilities
- Journalizing facilities
- Checkpoint facility
- Recovery manager
44
What should a DBMS provide that produces a backup copy (or save) of the entire database plus conrol files and journals?
Back-up facilities
45
How often should back-up facilities back up databases?
Periodically (nighty, weekly, etc.)
46
What is a cold backup?
Database is shut down during backup
47
What is a hot backup?
Selected portion is shut down and backed up at a given time
48
Where should backups be stored?
Secure, off-site location
49
What do journalizing facilities do?
Provide audit trail of transactions and database updates
50
What is a transaction log?
Record of essential data for each transaction processed against the database.
51
What is a database change log?
Contains before and after images of updated data
52
What are checkpoint facilities?
DBMS periodically refuses to accept new transactions.
This means the system is in a quite state.
In this time the Database and transaction logs are synchronized.
53
How do checkpoint facilities help the recovery manager?
It allows the recovery manager to resume processing from short period, instead of repeating the entire day.
54
What is the Recovery Manager?
DBMS module that restores the database to a correct condition when a failure occurs and then resumes processing user requests.
55
What are the Recovery and Restart Procedures?
- Disk Mirroring
- Restore/Run
- Transaction Integrity
- Backward Recovery (Rollback)
- Forward Recovery (Roll Forward)
56
What is disk mirroring?
Switching between identical copies of databases
57
What is restore/rerun?
Reprocess transactions against the backup (only done as a last resort)
58
What is transaction integrity?
Commit or Abort all transaction changes.
59
What is backward recovery (Rollback)?
Apply before images
60
What is Forward recovery (Roll forward)?
Apply after images (preferable to restore/run)
61
What are the transaction ACID properties?
- **Atomic** - Transaction cannot be subdivided
- **Consistent** - Constraints don't change from before transaction to after transaction
- **Isolated** - Database changes not revealed to users until after transaction has completed
- **Durable** - Database changes are permanent
62
Types of failure and their recommended actions
63
What is a problem with controlling concurrent access?
In a multi-user environment, simultaneous access to data can result in interference and data loss (lost update problem)
64
What is the solution to the lost update problem?
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 evironment
65
What happens to updates if there is simultaneous access to a database?
Updates cancel each other.
66
What is serializability?
Finish one transaction before starting another
67
What is a locking mechanism?
- The most common way of achieving serialization
- Data that is retrieved for the purpose of updating is locked for the updater
- No other user can perform update until unlocked
68
What are the locking levels?
**Database** - used during database updates
**Table** - used for bulk updates
**Block or Page** - very commonly used
**Record** - only requested row; fairly commonly used
**Field** - requires significant overhead; impractical
69
What are the types of locks?
Shared lock
Exclusive lock
70
What is a shared lock?
Read but no update permitted. Used when just reading to prevent another user from placing an exclusive lock on the record.
71
What is an exclusive lock?
No access permitted. Used when preparing to update.
72
What is a deadlock?
An impasse that results when two or more transactions have locked common resources, and each waits for the other to unlock their resources.
73
How do you prevent a deadlock?
- Lock all records required at the beginning of a transaction
- Two-phase locking protocol (Growing phase, Shrinking phase)
- Maybe be difficult to determine all needed resources in advance
74
How do you resolve a deadlock?
- Allow deadlocks to occur
- Mechanisms for detecting and breaking them (Resource usage matrix)
75
What is versioning?
An optimistic approach to concurrency control instead of locking
76
What is the assumption with versioning?
That simultaneous updates will be infrequent
77
How does versioning work?
- Each transaction can attempt an update as it wishes
- The system will create a new verison of a record instead of replacing the old one
78
What happens if a conflict occurs during versioning?
It accepts one user's update and informs the other user that its update needs to be tried again.
79
What is a data dictionary?
Documents data elements of a database
80
What is a system catalog?
Systems-created database that describes all database objects
81
What is an information repository?
Stores metadata describing data and data processing resources
Data Management Concepts
flashcards
Decks in class (12)
# Cards
