In general, update operations to data in a view are permitted as long as the update is unambiguous in terms of data modification in the base table. However, when the CREATE VIEW statement contains any of the following situations, that view may not be updated directly.
a. The SELECT clause includes the keyword DISTINCT.
b. The SELECT clause contains expressions, including derived columns, aggregates, statistical functions, and so forth.
c. The FROM clause, a subquery, or a UNION clause references more than one table.
d. The FROM clause or a subquery references another view, which is not updateable.
e. The CREATE VIEW command contains an ORDER BY, GROUP BY, or HAVING clause.
a. Subject-oriented. A data warehouse is organized around the key subjects (or high-level entities) of the enterprise. Major subjects may include customers, patients, students, products, time, etc., depending on the real-world domain within which the organization operates.
b. Integrated. The data housed in the data warehouse are defined using consistent naming conventions, formats, encoding structures, and related characteristics gathered from several internal systems of record and also often from sources external to the organization. This means that the data warehouse holds the one version of "the truth."
c. Time-variant. Data in the data warehouse contain a time dimension so that they may be used to study trends and changes.
d. Non-updatable. Data in the data warehouse are loaded and refreshed from operational systems, but cannot be updated by end users.
Data administration is a high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide data definitions and standards. Typically, the role of database administration is taken to be a more hands-on, physical involvement with the management of a database or databases. Database administration is 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. As business practices change, the roles are also changing within organizations. There are, however, a core set of database administration functions, which must be met in every organization, regardless of the database administration chosen. On one hand, these functions may be spread across data administrators and database administrators. At the other extreme, all of these functions may be handled by a single DBA. a. Database planning: Improve technology selection through selective evaluation of possible products. Consider each technology's fit with the enterprise data model, reducing time required in later stages by effective selection of technology at the database planning stage.
b. Database analysis: Work on physical design in parallel with development of the logical and physical models. Prototyping the application now may well lead to changes in the logical and physical data models earlier in the development process.
c. Database design: Prioritize application transactions by volume, importance, and complexity. These transactions are going to be most critical to the application, and specifications for them should be reviewed as quickly as the transactions are developed. Logical data modeling, physical database modeling, and prototyping may occur in parallel. DBAs should strive to provide adequate control of the database environment while allowing the developers space and opportunity to experiment.
d. Database implementation: Institute database change control procedures so that development and implementation are supported rather than slowed. Wherever possible, segment the model into modules that can be analyzed and implemented more quickly. Find ways to test the system more quickly without compromising quality. Testing may be moved earlier in the development; use testing and change control tools to build and manage the test and production environments.
e. Operation and maintenance: Review all timesaving measures that have been taken to ensure that database quality has not been compromised. Consider using third-party tools and utilities wherever possible to save work; other tools, such as Lotus Notes, may reduce the need for meetings, thus saving time.
a. Accidental losses, including human error, software, and hardware-caused breaches: Establishing operating procedures, such as user authorization, uniform software installation procedures, and hardware maintenance schedules, are examples of actions that may be taken to address threats from accidental losses. As in any effort that involves human beings, some losses are inevitable, but well thought-out policies and procedures should reduce the amount and severity of losses.
b. Theft and fraud: These activities are going to be perpetrated by people, quite possibly through electronic means, and may or may not alter data. Attention here should focus on each possible location shown in Figure 12-3. For example, control of physical security, so that unauthorized personnel are not able to gain access to the machine room, should be established. Data access policies that restrict altering data immediately prior to a payroll run will help to secure the data. Establishment of a firewall to protect unauthorized access to inappropriate parts of the database through outside communication links is another example of a security procedure that will hamper people who are intent on theft or fraud.
c. Loss of privacy or confidentiality: Loss of privacy is usually taken to mean loss of protection of data about individuals, while loss of confidentiality is usually taken to mean loss of protection of critical organizational data that may have strategic value to the organization. Failure to control privacy of information may lead to blackmail, bribery, public embarrassment, or use of user passwords. Failure to control confidentiality may lead to loss of competitiveness.
d. Loss of data integrity: When data integrity is compromised, data will be invalid or corrupted. Unless data integrity can be restored through established backup and recovery procedures, an organization may suffer serious losses or make incorrect and expensive decisions based on the invalid data.
e. Loss of availability: Sabotage of hardware, networks, or applications may cause the data to become unavailable to users, which again may lead to severe operational difficulties.
The purpose of the GRANT and REVOKE SQL commands is to control the access to data, and the actions that can be taken on the data, within a database management system. The GRANT and REVOKE commands are generally operated against User accounts within a database management system. The DBMS vendors may vary in their ability to control the authorization rules within a particular DBMS. In Oracle, authorization rules are handled with privileges on User accounts at the database or the table level, as well as INSERT and UPDATE privileges on a column level. Oracle privileges that can be GRANTed or REVOKEd from a user include: SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES, EXECUTE.