modern database management – Flashcards

The separation of data descriptions (metadata) from the application programs that use the data is called data independence. With the database approach, data descriptions are stored in a central location called the repository. This property of database systems allows an organization's data to change and evolve (within limits) without changing the application programs that process the data.

1. Program-data independence 2. Planned data redundancy 3. Improved data consistency 4. Improved data sharing 5. Increased productivity of application development 6. Enforcement of standards 7. Improved data quality 8. Improved data accessibility and responsiveness 9. Reduced program maintenance 10. Improved decision support

1. New, specialized personnel 2. Installation and management cost and complexity 3. Conversion costs 4. Need for explicit backup and recovery 5. Organizational conflict

Although any number of tiers may exist (from one to many), three tiers of computers relate to the client/server architecture for database processing: (1) the client tier, where database contents are presented to the user; (2) the application/Web server tier, where analyses on database contents are made and user sessions are managed; and (3) the enterprise server tier, where the data from across the organization are merged into an organizational asset.

...

Database development activities occur in each of these overlapping phases, and feedback may occur that causes a project to return to a prior phase.

(1) a conceptual schema, which provides a complete, technology-independent picture of the database; (2) an internal schema, which specifies the complete database as it will be stored in computer secondary memory in terms of a logical schema and a physical schema; and (3) an external schema or user view, which describes the database relevant to a specific set of users in terms of a set of user views combined with the enterprise data model.

--Stored attribute is an attribute that is already stored in the database. -ex) calculating a person's age from the person's date of birth and present date.-stored attribute is the date of birth. --derived attribute is an attribute whose values can be calculated from related attribute values (plus possibly data not in the database, such as today's date, the current time, or a security code provided by a system user). -Symbolized by square brackets "[ ]" - Or a forward slash (/) in front of the attribute name

Simple (atomic) attribute is an attribute that cannot be broken down into smaller components that are meaningful for the organization. -Composite attribute is an attribute such as Address, that has meaningful component parts, which are more detailed attributes.

Entity type is a collection of entities that share common properties, whereas an entity instance is a single occurrence of an entity type. -Relationship type is a meaningful association between (or among) entity types.

Strong entity type is an entity that has its own identifier and can exist without other entities -Weak entity type is an entity that has its own identifier and can exist without other entities and don't have their own identifiers but a partial identifier

Degree of a relationship is the number of entity types that participate in that relationship. -Cardinality specifies the number of instances of entity B that can (or must) be associated with each instance of entity A.

Required attribute is an attribute that must have a value for every entity (or relationship) instance with which it is associated. -Symbolized by boldface or " * " --Optional attribute is an attribute that may not have a value for every entity (or relationship) instance with which it is associated.

Composite attribute is an attribute such as Address, that has meaningful component parts, which are more detailed attributes. --Multivalued attribute is one that can have multiple values for a single instance of an entity

Ternary relationship is a simultaneous relationship among the instances of three entity types. --Three binary relationship when an attribute of a relationship cannot be properly associated with any one of the three possible binary relationships among the three entity types.

-In the database world, it has been more common to use the related term integrity constraint when referring to such rules -A business rules approach is based on the following premises: 1. Business rules are a core concept in an enterprise because they are an expression of business policy and guide individual and aggregate behavior. Well-structured business rules can be stated in natural language for end users and in a data model for systems developers. 2. Business rules can be expressed in terms that are familiar to end users. Thus, users can define and then maintain their own rules. 3. Business rules can be expressed in terms that are familiar to end users. Thus, users can define and then maintain their own rules. 4. Enforcement of business rules can be automated through the use of software that can interpret the rules and enforce them using the integrity mechanisms of the database management system (Moriarty, 2000).

Now that you have an example of a data model in mind, let's step back and consider more generally what a data model is representing. Now that you have an example of a data model in mind, let's step back and consider more generally what a data model is representing. In fact, documenting rules and policies of an organization that govern data is exactly what data modeling is all about. Business rules and policies govern creating, updating, and removing data in an information processing and storage system; thus they must be described along with the data to which they are related. For example, the policy "every student in the university must have a faculty advisor" forces data (in a database) about each student to be associated with data about some student adviser. Also, the statement "a student is any person who has applied for admission or taken a course or training program from any credit or noncredit unit of the university" not only defines the concept of "student" but also states a policy of the university (e.g., implicitly, alumni are students, and a high school student who attended a collage fair but has not applied is not a student, assuming the college fair is not a noncredit training program). Business rules and policies are not universal; different universities may have different policies for student advising and may include different types of people as students. Also, the rules and policies of an organization my change (usually slowly) over time: a university may decide that a student does not have to be assigned a faculty adviser until the student chooses a major. Your job as a database analyst is to -Identify and understand those rules that govern data -Represent those rules so that they can be unambiguously understood by information systems developers and users. -Implement those rules in database technology Data modeling is an important tool in this process. Because the purpose of data modeling is to document business rules about data, we introduce the discussion of data modeling and the entity-relationship notation with an overview of business rules. Data models cannot represent all busines rules (and do not need to, because not all business rules govern data); data models along with associated documentation and other types of information system models (e.g., models that document the processing of data) represent all business rules that must be enforce through information systems.

1. Choose an identifier that will not change its value over the life of each instance of the entity type. 2. Choose an identifier such that for each instance of the entity, the attribute is guaranteed to have valid values and not be null (or unknown). 3. Avoid the use of so-called intelligent identifiers (or keys), whose structured indicates classifications, locations, and so on. 4. Consider substituting single-attribute surrogate identifiers for large composite identifiers.

Some identifiers are composite than simple in order to be more specific and consider all the details.

1:1 one-to-one 1:N one-to-many N:1 many-to-one N:M many-to-many

A weak entity type would be a ROOM because it cannot exist without constructing a BUILDING. It is necessary to indicate an identifying relationship in order to understand the relationship between the two entities.

1. Unary Relationship is a relationship between the instances of a single entity type. They are also called Recursive Relationships. 2. Binary Relationship is a relationship between the instances of two entity types and is the most common type of relationship encountered in data modeling. 3. Ternary Relationship is a simultaneous relationship among the instances of three entity types.

You should consider using subtypes when either (or both) of the following conditions are present: 1. there are attributes that apply to some (but not all) instances of an entity type. For example, see the EMPLOYEE entity type in Figure 3-2. 2. The instances of a subtype participate in a relationship unique to that subtype. Figure 3-3 is an example of the use of subtype relationships that illustrates both of these situations. The hospital entity type PATIENT has two subtypes: OUTPATIENT and RESIDENT PATIENT. (The identifier is Patient ID.) All patients have an Admit.

An entity cluster is a set of one or more entity types and associated relationships grouped into a single abstract entity type. Because an entity cluster behaves like an entity type, entity clusters and entity types can be further grouped to form a higher-level entity cluster. Entity clustering is a hierarchical decom- position of a macro-level view of the data model into finer and finer views, eventually resulting in the full, detailed data model.

Imagine a business which needs to track payments from customers. Customers can pay by cash, by check or by credit card. All payments have some common attributes: payment date, payment amount and so on. But only credit cards would have a "card number" attribute. And for credit card and check payments we may need to know which CUSTOMER made the payment, while this is not needed for cash paymen

Disjoint Rule: An instance of the supertype can be only ONE of the subtypes. Following Fig. shows that at any one time a PATIENT must be either an outpatient or a resident patient but cannot be both - specified by the letter 'd' The subclass of a patient may change over time, but at any given time a patient is of only one type

When subtypes overlap, a slightly modified approach must be applied for the subtype discriminator.

The reason is that a given instance of the supertype may require that we create an instance in more than one subtype. A subtype discriminator is an attribute of a supertype whose values determine the target subtype or subtypes.

In fact, working with a package requires advanced skills. As we will see, the packaged data models are rather complex because they are thorough and devel- oped to cover all possible circumstances. Adata modeler has to be very knowledgeable of the organization as well as the package to customize the package to fit the specific rules of that organization.

What you are buying is meta- data. You receive, usually on a CD, a fully populated description of the data model, usually specified in a structured data modeling tool, such as ERwin from Computer Associates or Oracle Designer from Oracle Corporation. The supplier of the data model has drawn the EER diagram, named and defined all the elements of the data model, and given all the attributes characteristics of data type (character, numeric, image), length, format, and so forth. You can print the data model and various reports about its contents to support the customization process. Once you customize the model, you can then use the data modeling tool to automatically generate the SQLcommands to define the database to a variety of database management systems.

A candidate key is an attribute, or combination of attributes, that uniquely identifies a row in a relation. Acandidate key must satisfy the following properties (Dutka and Hanson, 1989), which are a subset of the six properties of a relation previously listed: 1. Unique identification For every row, the value of the key must uniquely identify that row. This property implies that each nonkey attribute is functionally dependent on that key. 2. Nonredundancy No attribute in the key can be deleted without destroying the property of unique identification.

Redundancies in a table may result in errors or inconsistencies (called anomalies) when a user attempts to update the data in the table. We are typically concerned about three types of anomalies: 1. Insertion anomaly Suppose that we need to add a new employee to EMPLOYEE2. The primary key for this relation is the combination of EmpID and CourseTitle (as noted earlier). Therefore, to insert a new row, the user must supply values for both EmpID and CourseTitle (because primary key values cannot be null or nonexistent). This is an anomaly because the user should be able to enter employee data without supplying course data. 2. Deletion anomaly Suppose that the data for employee number 140 are deleted from the table. This will result in losing the information that this employee completed a course (Tax Acc) on 12/8/201X. In fact, it results in losing the information that this course had an offering that completed on that date. 3. Modification anomaly Suppose that employee number 100 gets a salary increase. We must record the increase in each of the rows for that employee (two occurrences in Figure 4-2); otherwise, the data will be inconsistent.

• Second normal form (2NF):: A relation in first normal form in which every nonkey attribute is fully functionally dependent on the primary key.

• Third normal form (3NF):: A relation that is in second normal form and has no transitive dependencies.

• Fourth normal form (4NF):: A normal form of a relation in which the relation is in BCNF and contains no multivalued dependencies.

Normalization is the process of successively reducing relations with anomalies to produce smaller, well-structured relations. Following are some of the main goals of normalization: 1. Minimize data redundancy, thereby avoiding anomalies and conserving storage space 2. Simplify the enforcement of referential integrity constraints 3. Make it easier to maintain data (insert, update, and delete) 4. Provide a better design that is an improved representation of the real world and a stronger basis for future growth.

An example of mapping a ternary relationship (represented as an associative entity type) is shown in Figure 4-19. Figure 4-19a is an E-R segment (or view) that represents a patient receiving a treatment from a physician. The associative entity type PATIENT TREATMENT has the attributes PTreatment Date, PTreatment Time, and PTreatment Results; values are recorded for these attributes for each instance of PATIENT TREATMENT.

This approach is most appropriate when the end user can best visualize the relationship as an entity type rather than as an M:N relationship. Mapping the associative entity involves essentially the same steps as mapping an M:N relationship. The first step is to create three relations: one for each of the two participating entity types and a third for the associative entity. We refer to the relation formed from the associative entity as the associative relation. The second step then depends on whether on the E-R diagram an identifier as assigned to the associative entity.

Referential integrity constraint: A rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null. If each order must have a customer (a mandatory relationship), then the foreign key CustomerID cannot be null in the ORDER relation. If the relationship is optional, then the foreign key could be null. Whether foreign key can be null must be specified as a property of the foreign key attribute when the database is defined.

a technique for physically arranging the records of a file on secondary storage devices.

The storage of records in a file in sequence according to a primary key value.

The storage of records either sequentially or nonsequentially with an index that allows software to locate individual records.

A storage system in which the address for each record is determined using a hashing algorithm.

the process of transforming normalized relations into non-normalized physical record specifications.

A primary key that consists of more than one attribute.

one field or a combination of fields for which more than one records may have the same combinations of values. Also called a nonunique key.

a detailed coding scheme recognized b system software, such as a DBMS for representing organizational data.

An index on columns from two or more tables that come from the same domain of values.

1. Substitute an estimate of the missing value. For ex. for a missing sales value when computing monthly product sales, use a formula involving the mean of the existing monthly sales value for that product indexed by total sales for that month across all products. 2. Track missing data so special reports and other systems elements cause people to resolve unknown values quickly. Done by setting up a trigger in the database. A trigger is a routine that will automatically execute when some event occurs or time period passes. One trigger could log the missing entry to a file when a null or other missing value is stored, and another could run periodically to create a report of the contents of this log file. 3.Perform sensitivity testing so that missing data are ignored unless knowing a value might significantly change results. (e.g. if total monthly sales for a particular salesperson are almost over threshold that would make a difference in that person's compensation.) most complex of the three methods.

Efficient processing of data depends on how close together related data are. A fully normalized database usually creates a large number of tables causing the queries and processing to run slow. Partially normalized databases run much faster.

1. Two entities with a one-to one relationship. 2. A many to many relationship (associative entity) with nonkey attributes. 3. reference data.

1. indexes are most useful on large tables. 2. specify a unique index for the primary key of each table. 3. Indexes are most useful for columns that frequently appear in WHERE clauses of the SQL commands. 4. Use an index for attributes referenced in ORDER BY (sorting) and GROUP BY (categorizing) clauses. 5. use an index when there is significant variety in the values of an attribute. 6. Before creating an index on a field with long values, consider first creating a compressed version of the values and then indexing on the coded version. 7. If the key for the index is going to be used for determining the location where the record will be stored, then the key for this index should be a surrogate key so that the values cause records to be evenly spread across the storage space. 8. Check your DBMS for a limit, if any, on the number of indexes allowable per table. 9. Be careful of indexing attributes that have null values.

A high-level function that is responsible for the overall management of data resources in an organization, including maintaining corporate-wide definitions and standards.

A technical function that is responsible for physical database design and for dealing with technical issues, such as security enforcement, database performance, ad backup recovery.

A procedure for acquiring the necessary locks for a transaction in which all necessary locks are acquired before any locks are released, resulting in a growing phase when locks are acquired and shrinking phase when they are released.

A component that stores metadata that describes an organization's data and data processing resources, manages the total information processing environment, and combines information about an organization's business information and its application portfolio.

A process in which any data that are retrieved by a user for updating must be locked, or denied to other users, until the update is completed or aborted.

An approach to concurrency control in which each transaction is restricted to a view of the database as of the time that transaction started, and when a transaction modifies a record, the DBMS creates a new record version instead of overwriting the old record. Hence, no form of locking is required.

An impulse that results when two or more transactions have locked a common resource, and each waits for the other to unlock that resource.

A discrete unit of work that must be completely processed or not processed at all within a computer system. Entering a customer system order is an example of a transaction.

The coding or scrambling of data so that humans cannot read them.

Ensuring the availability of databases.

The process of moving inactive data to another storage location where it can be accessed when needed.

A query submitted by a DBA to test the current performance of a database or to predict the response time for queries that have promised response times. Also called a canary query.

Free DBMS source code software that provides the core functionality of an SQL-compliant DBMS.

data administration

database administration

data administration

database administration

data administration

database administration

database administration

database administration

Many organizations have blended together the data administration and database administration roles. The blending also means that Database administrators in such situations must be able to create and enforce data standards and policies.

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 13-2. 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.

...

Integrity controls protect data from unauthorized use and update. Integrity controls limit the values a field may hold, limit the actions that can be performed, or trigger the execution of some procedure (such as placing an entry into a log to record which users have performed which actions on which data). A domain is an example of an integrity constraint that can be used to define a user-defined data type. If this type ever needs to be changed, it can be changed in only one place, and all fields using this domain will be updated automatically. Assertion constraints enforce desirable database conditions and check automatically whenever transactions are run. Triggers can be used for events or conditions, and actions needed to be tracked against a database. Triggers cannot be circumvented.

...

A database is updated by processing transactions that result in changes to one or more database records. If an error occurs during the processing of a transaction, the transaction integrity may be compromised, and some form of database recovery is required. Thus, to understand database recovery, we must first understand the concept of transaction integrity. When processing transactions, a DBMS must ensure that the transactions follow four well accepted properties, called the ACID properties: 1. Atomic, meaning that the transaction cannot be subdivided and, hence, it must be processed in its entirety or not at all. Once the whole transaction is processed, we say that the changes are committed. If the transaction fails at any midpoint, we say that it has aborted. For example, suppose that the program accepts a new customer order, increases Balance Due, and stores the updated CUSTOMER record. However, suppose that the new ORDER record is not inserted successfully (perhaps due to a duplicate Order Number key or insufficient physical file space). In this case, we want none of the parts of the transaction to affect the database. 2. Consistent, meaning that any database constraints that must be true before the transaction must also be true after the transaction. For example, if the inventory on-hand balance must be the difference between total receipts minus total issues, this will be true both before and after an order transaction, which depletes the on-hand balance to satisfy the order. 3. Isolated, meaning that changes to the database are not revealed to users until the transaction is committed. For example, this property means that other users do not know what the on-hand inventory is until an inventory transaction is complete; this property then usually means that other users are prohibited from simultaneously updating and possibly even reading data that are in the process of being updated. We discuss this topic in more detail later under concurrency controls and locking. A consequence of transactions being isolated from one another is that concurrent transactions (i.e., several transactions in some partial state of completion) all affect the database as if they were presented to the DBMS in serial fashion. 4. Durable, meaning that changes are permanent. Thus, once a transaction is committed, no subsequent failure of the database can reverse the effect of the transaction. To maintain transaction integrity, the DBMS must provide facilities for the user or application program to define transaction boundaries—that is, the logical beginning and end of a transaction. In SQL, the BEGIN TRANSACTION statement is placed in front of the first SQL command within the transaction, and the COMMIT command is placed at the end of the transaction. Any number of SQL commands may come in between these two commands; these are the database processing steps that perform some well-defined business activity, as explained earlier. If a command such as ROLLBACK is processed after a BEGIN TRANSACTION is executed and before a COMMIT is executed, the DBMS aborts the transaction and undoes the effects of the SQL statements processed so far within the transaction boundaries. The application would likely be programmed to execute a ROLLBACK when the DBMS generates an error message performing an UPDATE or INSERT command in the middle of the transaction. The DBMS thus commits (makes durable) changes for successful transactions (those that reach the COMMIT statement) and effectively rejects changes from transactions that are aborted (those that encounter a ROLLBACK). Any SQL statement encountered after a COMMIT or ROLLBACK and before a BEGIN TRANSACTION is executed as a single statement transaction, automatically committed if it executed without error, aborted if any error occurs during its execution.

Aborted transaction: A transaction in progress that terminates abnormally. Incorrect Data: For example, an incorrect grade may be recorded for a student, or an incorrect amount could be input for a customer payment. Incorrect data are difficult to detect and often lead to complications. System Failure: In a system failure, some component of the system fails, but the database is not damaged. Database Destruction: The database itself is lost, destroyed, or cannot be read.

Hardware Failures: The usual solution is to provide redundant or standby components that replace a failing system. Human Error: The use of standard operating procedures, which are mature and repeatable, is a major deterrent to human errors. In addition, training, documentation, and insistence on following internationally recognized standard procedures Loss or Corruption of Data: Mirrored (or backup) databases are almost always provided in high-availability systems. Also, it is important to use the latest backup and recovery systems. Maintenance Downtime: New database products are now available that automate maintenance functions. For example, some utilities (called nondisruptive utilities) allow routine maintenance to be performed while the systems remain operational for both read and write operations, without loss of data integrity. Network-Related Problems: To counter these threats, an organization should carefully monitor its traffic volumes and develop a fast-response strategy when there is a sudden spike in activity. An organization also must employ the latest firewalls, routers, and other network technologies.

Two common forms of encryption exist: one key and two key. With a one-key method, also called Data Encryption Standard (DES), both the sender and the receiver need to know the key that is used to scramble the transmitted or stored data. A two-key method, also called asymmetric encryption, employs a private and a public key.

• Develop a detailed written disaster recovery plan. Schedule regular tests of the plan. • Choose and train a multidisciplinary team to carry out the plan. • Establish a backup data center at an offsite location. This site must be located a sufficient distance from the primary site so that no foreseeable disaster will disrupt both sites. If an organization has two or more data centers, each site may serve as a backup for one of the others. If not, the organization may contract with a disaster recovery service provider. • Send backup copies of databases to the backup data center on a scheduled basis. Database backups may be sent to the remote site by courier or transmitted by replication software.

A heartbeat query is a very simple query (possibly SELECT * FROM table WHERE some condition) that a DBA runs many times during the day to monitor variations in processing times. When heartbeat queries are taking extraordinarily long to run, there is probably either an inappropriate mix of jobs running or some inefficient queries are consuming too many DBMS resources. A heartbeat query may also be exactly like certain regularly run user queries for which there are service-level agreements (SLAs) with users on maximum response times. In this case, the heartbeat query is run periodically to make sure that if the user were to submit this query, the SLA goals would be met.

A view is a subset of a database that is presented to one or more users. A view is created by querying one or more of the base tables, producing a dynamic result table for the user at the time of the request. Thus, a view is always based on the current data in the base tables from which it is built. The advantage of a view is that it can be built to present only the data (certain columns and/or rows) to which the user requires access, effectively preventing the user from viewing other data that may be private or confidential. The user may be granted the right to access the view, but not to access the base tables upon which the view is based. So, confining a user to a view may be more restrictive for that user than allowing him or her access to the involved base tables.