Database I CJV1 WGU

foreign key

- A column or concatenated columns that are identical to the primary key of a relation in the same database. - used to link data in one table to similar data in another table. - A foreign key can reference only one parent key, and multiple foreign keys can reference the same parent key value. - A column in a table that is the primary key of another table is called a foreign key.

database requirements document

- A database requirements document contains the specifications for the database. - Can contain: A well-defined mission statement, data requirements, objectives, constraints, operational scenarios, Security, reliability, portability, and optionally, a preliminary schedule and a preliminary budget. - Record the interviews or take extensive notes so that none of the information gathered from the interviews is lost. - The database designer will look for nouns or noun phrases that are properties, qualities or characteristics of the entities. - Relationships are usually expressed using verbal expressions within the requirements document. Q: why should you interview management personnel separately from users? A: Because these two groups have different perspectives of the organization Q: What is an operational scenario? A: a proposed specific use of the database system or a description of one or more beginning-to-end transactions involving the database system and its environment. Q: Why can the failure to ascertain all the requirements of a database system in a database requirements document lead to an inadequate or incomplete system? A: A database requirements document contains the specifications for the database and because the database requirements document is used as a blueprint from which the database design is derived.

why many software development projects fail or are abandoned?

- A lack of a suitable development methodology(Main Cause). - projects do not meet performance goals and are completed late and over budget.

composite key

- A primary or foreign key that is made up of multiple attributes. - The composite key should be made up of the least number of columns necessary to ensure uniqueness.

database Transaction

- A transaction is an operation, or series of operations, conducted by a single user against a database that retrieves or alters the data in the database. - A transaction can be the result of an entire or partial program, or it can be as simple as a single SQL statement executed by a user. -A transaction is not limited to one type of operation against the database, but can include various types, such as INSERT, DELETE and UPDATE operations.

relation

- A two-dimensional table consisting of columns and rows in a relational database. - Each row within the relation must be unique.

INTERSECT

- An intersection is a binary operation that creates a new relation containing all the rows that are in both the first and the second relations. - The intersect operation can use the SQL keyword INTERSECT, although not all dialects of SQL support it. - The ∩ symbol represents the intersection operation.

Serializability

- Assuring the serializability of concurrent transactions prevents database inconsistencies. - Serializability refers to whether a set of transactions must be executed individually or whether they can be executed concurrently with one another. - A set of transactions is said to be serializable if the execution of the concurrent transactions produces the same results as some serial execution. - If concurrent transactions only read data, no conflict exists and the order of execution is not significant. - If concurrent transactions read or write entirely different data structures, no conflict exists and the order of execution is not significant. - If concurrent transactions read or write the same data structures, the order of execution is significant. - When you determine serializability, you must analyze the order of read and write operations.

Transactions - four desirable properties

- Atomicity states that the transaction is a complete unit, and is executed in its entirety or not at all. - Consistency states that the transaction must change the database from one consistent state to another consistent state. - Isolation states that each transaction is independent from other transactions. - Durability states that a committed transaction is permanent and should not be lost owing to any ensuing malfunction.

REVOKE Statement

- Can only revoke privilege that was granted to them - When granted privilege using "WITH GRANT OPTION", all users that were granted using this will loose their privilege.

Denormalization

- Denormalization is the process of reuniting relations that were split when they underwent the normalization process to improve performance, specially during query operations. - The duplication of foreign key attributes in 1:n relationships is also a common denormalization process. - use if a set of relations within a database is updated infrequently and is suffering from poor performance. - can improve relational database performance by reducing the number of join operations that are necessary to obtain the needed data. - Denormalization process can improve performance, but can also increase the maintenance needed to ensure that the database remains in a consistent state.

Entity

- Entities are logical categories that make up a database, as opposed to the actual data items that inhabit the entity. - Strong entity: An entity whose data is meaningful without having to reference another entity in the data model - Weak entity: An entity that must reference another entity for its data to be meaningful.

degree of a relationship

- Number of entities that are involved in a relationship. 1. one (referred to as unary) 2. two (referred to as binary) 3. three (referred to as ternary) 4. more than three (referred to as n-ary).

Boolean operators

- SQL supports Boolean operators that are used in a similar way to most programming languages. - Boolean expressions are either true or false. If a NULL value is involved in a Boolean expression, an UNKNOWN will be returned for that expression. - SQL provides the AND, OR, and NOT keywords for use in Boolean expressions.

schema

- Schema is another term for database. A schema is a named group of related tables, views, and other database objects. It represents the complete structure of the database. - The CREATE SCHEMA command is a DDL command. - CREATE SCHEMA Holistic_Herbs; will create an empty database - DROP SCHEMA Holistic_Herbs; will delete a database named Holistic_Herbs and all its database objects. - DROP SCHEMA statement to remove a schema from a DBMS. This statement is used to drop a schema and all database objects within it.

physical design phase

- Schemas and relations are created during the physical design phase of a database. - Once the logical data models have been merged into a finalized enterprise data model, the physical design phase begins. - This phase of the database design life cycle pertains to interpreting the logical data model into a physical data model that can be implemented by a particular DBMS. Several important tasks take place in the physical design phase: 1. These include deciding whether and when to use secondary indexes and denormalization to improve the performance of the database application 2. The database designer is usually involved in creating the various user views and access constraints that a database can have.

data dictionary

- Stores information about the relations in a database, known as metadata. Data dictionaries are created and automatically maintained by the DBMS. - For the database designer, probably the most important information that the data dictionary will contain is in reference to attributes for the relations in the database. - This information will include items such as the attribute's domain, including data type, length, and constraints on the domain, if any. The data dictionary will also state whether the attribute has a default value or whether it can hold a null value. - provided each attribute's domain and any default values for each attribute -Usually, data dictionaries are created automatically as the result of a set of DDL statements.

DELETE

- The SQL DELETE statement is used to remove records from a relation. - It cannot be used to remove individual columns. - When the DELETE statement is used, the entire row is removed from the relation. - The DELETE command is a DML command

client-server system

- The client-server approach has many different configurations, but the underlying concept is the separation of tasks. - The client is responsible for presenting data to the user, and the server is responsible for delivering data services to the client.

tuple (or row or record)

- The collection of one set of attributes in a relation(relational database). Each row must be unique.

Latest SQL standard

- The latest SQL standard is identified as SQL99. - The previous standards were SQL86, SWL89, and SWL92. - administered by International Organization for Standardization (ISO) and the American National Standards Institute (ANSI).

three-tier architecture

- The main business logic of the application is located on the server, creating its own layer or tier. - Uses Thin Client: A client designed to be small so that the bulk of the data processing occurs on the server. - The thin client requires less expensive hardware and less administrative overhead. - The centralized application logic makes maintaining the application easier and solves the problems associated with software distribution. - the client is responsible for presenting a user interface. The client may also handle some processing logic such as user input validation. - The database server provides data validation and database access

Normal Forms

- The normalization process technically begins with the requirement that each instance of an entity have a primary key that uniquely identifies that entity. 1NF 1. Each cell is not further divisible 2. two-dimensional table with each attribute represented by an atomic value. 3. No repeating groups - a. subject to insertion, deletion and update anomalies -> place the relation in 2NF. 2NF - A relation is said to be in second normal form if the relation is in 1NF and all attributes that are not part of the primary key are completely functionally dependent on the primary key. - a. 2NF and 3NF provide constraints that disallow partial and transitive dependencies on the primary key. These constraints do not address situations in which a functional dependency might remain on candidate keys within a relation. Boyce-Codd normal form was designed to address such situations. - b. Any relation that is in 1NF and has a single attribute as its primary key is automatically in 2NF. - c. any relation in which every attribute is part of the primary key is in 2NF. 3NF - Eliminate columns not- transitively dependent (useless) on key. - a. A relation is said to be in 3NF if it is in 2NF and no attributes that are not part of the primary key are transitively dependent on the primary key. - b. loss of functional dependencies - STOP AT 3NF BCNF - A relation is in BCNF if and only if all determinants are candidate keys. - Transitive dependencies would also make BCNF invalid because BCNF is a more stringent version of 3NF.

parent key

- The primary key referenced by a foreign key. - The parent key need not be a primary key, though this is usually the case. If the parent key is not a primary key, the column's value must have the UNIQUE constraint placed on it to ensure that it is a unique value. - The primary key to which a foreign key maps is also known as a parent key.

Theta-join (notated as θ-join) & equi-join

- Theta-join is a binary operation that combines two relations where the combination of rows satisfies a predicate. - A theta-join can be viewed in terms of a restricted Cartesian product operation. - Equi-join can also be viewed in terms of a selection, a projection, and a Cartesian product

INSERT (all non-numeric data values must be enclosed in single quotation marks but Numeric data types do not require the single quotation marks)

- assumes that the list of values is to be inserted into the columns in the order in which they were defined.

functional dependency

- can be thought of as a one-way relationship between two attributes in a relation. In a functional dependency, if B is functionally dependent on A, and C is functionally dependent on B, C is then said to be transitively dependent on A.

A data model

- delineates the transactions required on the data. - A data model will typically resemble a simplified ER diagram. It can be thought of as an actual representation of the objects and associations that make up a database. - A data model is meant to help a database designer fully understand the significance of the data being represented by the database. - A data model is a formal expression of data, data relationships, and constraints on the data. A data model expresses these data relationships to a DBMS. - A formal method of describing data and data relationships 1. complete data model should contain the following three distinct components: • Structural information detailing a particular set of rules by which a database can be built. • Manipulative information detailing the kinds of operations that are permitted on the data. • Integrity information that provides details about how the data will be kept accurate.

Concurrency control

- designed to address are: lost updates, uncommitted updates, and unrepeatable query results (also known as dirty reads). A lost update occurs when one update overrides another as multiple updates are applied to a relation simultaneously. - management of transactions that occur at the same time, and is performed to ensure that the transactions do not interfere with one another. - common method used by DBMSs is The locking method(locks data while in use - AKA Deadlock) - Optimistic concurrency methods be used In situations in which rollbacks due to conflicts are very rare. The overhead for restarting transactions can be significant, and can detrimentally affect performance if it occurs very often. - means of achieving serializability. - The three steps of optimistic concurrency control are: read, validate, write.

Timestamps

- detect and prevent deadlocks - timestamp methods prevent conflicts by rolling back any transactions involved in a conflict and restarting them. - timestamp method for concurrency control = Each data item is assigned a read timestamp and a write timestamp.

E.F. Codd

- first proposed the relational data model in 1970. His paper, "A Relational Model of Data for Large Shared Data Banks," is considered a milestone in modern database evolution. - According to E.F. Codd -> only three pieces of information are needed to retrieve a value: the name of the table, the name of the column, and the primary key of the row. As long as the primary key is unique within a table, the user can be assured that the proper data is being manipulated.

ER model

- is a high-level, conceptual database design method that describes entities, entity attributes, and the relationships among entities. - ER models are used to aid the database design process and to show the data from the perspective of the user. - Entities (IE) are the basic building blocks of an ER model - rectangles to represent the entities Chen ER model: 1. the diamond figures indicate the relationships between the entities. 2. rectangles to represent the entities. 3. an asterisk indicates the entity's primary key.

Candidate key

- is one that can possibly act as a primary key. - If several candidate keys are identified among the attributes, one attribute eventually needs to be selected as the primary key. - If several candidate keys exist: • The candidate key least likely to have its value changed. • The candidate key least likely to lose its uniqueness. • The candidate key that contains the fewest characters. • The candidate key that is easiest for the user to use. Keep in mind that, on occasion, the primary key for an entity may be a combination of several candidate keys. This is an acceptable practice.

database design phase

- must ensure that the database's design supports the needs of the organization. The objectives of the database design stage are: 1. to designate the data and the data relationships required by the users 2. to present a data model that delineates the transactions required on the data 3. and to create a design that is structured to achieve the specified performance goals of the database system. 4. The database requirements document contains the specifications for the database and includes information about the required features of the database.

column

- must have a defined domain. - Columns have a name that is unique within a relation. - Multiple tables within a relational database may have columns with the same name, but a single relation must have uniquely named columns. - Each column can contain values from one domain only.

CHECK

- place restrictions on the data that a relation can receive - Used for Update and insertion operations - CANNOT use CHECK to reference more than one record at the same time. - All CHECK constraint names must be unique within a database.

two-tier architecture

- the client processes the application logic and presentation of the user interface. - Uses Fat Client: A client that performs the bulk of the data-processing operations.

flat-file database (AKA - file-based system)

1. A flat-file database is a file in which data is collected in lines of text, with each value separated by a tab character or a comma(comma-separated values (CSV) file) and with each row represented by a new line. 2. This type of system causes several problems, including repetition of data, separation of data, incompatibility of files, data dependence, and inflexibility of the application programs that access the data. 3. The structure of the data storage files usually becomes embedded in the application programs that access them. 4.. Characterize as Separation of data, incompatibility of files and data dependence

Primary Key

1. A unique identifier within a row. 2. The primary key should be a value that will not change 3. A primary key cannot contain null values. 4. If it is a composite key, the primary key should be made up of the least number of columns necessary to ensure uniqueness.

Enterprise Data Model

1. Combine various logical data models into an enterprise data model. To achieve this, the database designer must examine the names of all entities and their primary keys, as well as the names of all relationships. This is necessary to ascertain entities that (a) may be the same and (b) may have the same name but are really different entities. a. Entities that are found to be the same can be combined to form one entity. b. Entities that are similarly named, yet are different entities, need to be included after they are renamed. c. Entities that are completely unique to any user view need to be included. All unique and similar entities should now be identified. All relationships need to be examined to determine whether they are unique or are the same as relationships in other user views. Relationships with the same name and objective should be combined. Those with different names but the same objective should also be combined. All foreign keys need to be checked, along with the referential integrity for each. All integrity constraints should then be checked to ensure that no conflicts occur with the originally documented integrity constraints. The final enterprise data model should then be produced and the appropriate documentation created. 2. The enterprise data model should then be validated using the normalization process and by validating the data model against the required data operations for the enterprise. 3. Create the finalized ER diagram that will be used to represent the enterprise logical data model. 4. Allow the users to review and assess the finalized enterprise ER diagram. This step will help to ensure that the final logical

Database design methodology three distinct phases

1. Conceptual 2. Logical 3. Physical.

Database Design Life Cycle

1. Create a database strategy(determine the type of data that the database will be handling, and document the existing strengths of the current system) 2. Define the scope of the database application ( involves designating the boundaries of the database application and how it may interact with other parts of the organization's Information Technology (IT) infrastructure. This activity involves defining the users of the database and its corresponding application. This step should also take into account any possible future users of the database, as well as identify the major areas of the database application. ) 3. Create a database requirements document (Conducting interviews with the principal people involved with the database) 4. Design the database 5. Select a DBMS (physical criterion = available file structure, indexing capabilities, support for data compression, support for encryption, hard disk requirements, and memory requirements.) 6. Design the database application 7. Create database and database application prototypes 8. Test the database and database application 9. Implement(The implementation phase) the database and database application (menus, data entry forms, report generating programs, and user logon screens be set up) 10. Convert legacy data and load new data into the database 11. Maintain the database and the database application

Three main subsets of SQL ( Remember: semicolon (;) at the end of the last line, and blank spaces are seperators as well as both parenthesis))

1. Data Definition Language (DDL): Database designers can define databases using (DDL), a subset of SQL. DDL permits the database designer to specify the data types held in the database as well as constraints on the data. - DDL commands: CREATE INDEX, CREATE VIEW, CREATE TABLE, CREATE SCHEMA - Usually, data dictionaries are created automatically as the result of a set of DDL statements. 2. Data Manipulation Language (DML): DML can be used to operate on the data, including retrieval, update, delete and insertion operations and is a subset of SQL. - DML is used to perform data operations on the data within a database. - DML commands: SELECT , UPDATE , DELETE, INSERT - A procedural DML can be used not only to manipulate data but also to provide mechanisms that allow the user to tell the system precisely how to manipulate the data. - A nonprocedural DML simply allows the user to tell the system what data is to be manipulated, not how it will be manipulated. - A nonprocedural DML - include the functionality needed to retrieve data from a database. This functionality is referred to as a query language. ADVANTAGE: It is easier for the user. - When creating a database: Usually the application programs contain DML statements embedded within a specific programming language such as C, Java or Visual Basic. These statements represent the transaction functionality built into the application programs. 3. Data Control Language (DCL): A language used to control access to data in a database. - is used to define privileges for users of the database. - is considered part of the DDL by the ISO.

Application programs that process data and databases consist of four major components:

1. The database 2. the transaction programming 3. the application programming 4. some type of user interface.

normalization

1. The normalization process technically begins with the requirement that each instance of an entity have a primary key that uniquely identifies that entity(Required). 2. Normalization is designed to address the problems of unwanted data redundancy as well as insertion, deletion and update anomalies. 3. Normalization often results in the creation of additional tables to achieve these goals. 4. Normalization provides a mechanism by which the database design can accurately represent the data, relationships and constraints of an enterprise.

insertion anomaly

A circumstance in which data may not be entered into a relation because a complete primary key is not available.

update anomaly

A circumstance in which redundant data in a relation may not be properly updated.

deletion anomaly

A circumstance in which wanted data is deleted from a relation.

attribute

A column -> A row in the table represents individual records whereas a column represents the attributes of each record.

null

A database value meaning "unknown."

view

Advantages: •Hiding certain parts of the database from users makes for powerful and flexible security. The DBA can allow users to see only the data they need to see. • Views permit customized access to data. Different users can see the same data in different ways at the same time. • Views are stored as named database operations, so they can be used to keep frequently used, or perhaps complex, data operations ready to be called at any time

entity-relationship (ER)

After the logical data model has been completed, the next step is to validate the model against the documented user operations and system requirements. This step involves reviewing the database requirements document to ensure that all the specified data has been included in the logical data model. Typically, the database designer will use the ER diagram to manually perform all the required data operations to ensure that they are all possible. The validation process can be performed by writing each required transaction to ensure that all the data is present to carry out that operation. • Creating an entity-relationship (ER) diagram (ER model = is a high-level, conceptual database design method that describes entities, entity attributes, and the relationships among entities. ER models are used to aid the database design process and to show the data from the perspective of the user. • Reviewing the ER model by the user and design team(chance to catch and correct any errors or possibly add or subtract data as necessary.)

If the same attribute name is shared among different tables, how would you refer to the attributes in the same SQL statement?

All relations in a given database must have unique names. It is permissible for different relations to share attribute names. When similarly named attributes are referred to in the same SQL statement, the name of the table precedes the attribute name and is separated by a dot.

determinant

An attribute or group of attributes on which another attribute is functionally dependent.

atomic value

An attribute's value that cannot be divided into multiple pieces of information.

Recursive Relationship

An entity relationship in which an entity is involved in a relationship with itself.

BETWEEN

Between 2 values. Do not count the start and end number. AND is used with BETWEEN to define range. - between means the first value preceded or equals the second value, and the second value is grater than or follows the first value. EG: between 30000 and 20000 - This will return nothing .

Blank spaces

Blank spaces are used to separate individual words of statements in SQL so table names cannot contain any blank spaces. Typically, the underscore character is used in place of a blank space.

A database transaction can have either of two results:

COMMIT and ROLLBACK. COMMIT and ROLLBACK used To delimit a transaction

Aliases are supported by SQL

Column aliases and table aliases

Complex relationships

Complex relationships occur when more than two entities participate in a relationship. Often, complex relationships contain m:n relationships, and the relations involved should be decomposed to create intermediate entities.

Why is the support of embedded SQL statements critical when determining the programming environment in which to create an application interface to the database?

Correct Response: Because most of the data manipulation operations will be performed from the user interface, not from the DBMS. The inclusion of SQL statements embedded within the program code

database design activities

Create a database requirements document; create an ER diagram; apply normalization; create user views.

data definition functionality criterion

Data definition functionality refers to: enforcement of primary keys, foreign key support, support for data types, domain support, data integrity mechanisms, support for views, data dictionary, data model type, and schema support.

characteristic of attributes

Every attribute in a relation must have a defined domain

Four types of join operations in relational algebra (symbols for a join relational algebraic operation is hourglass on its side)

Full outer join - keeps all rows in both relations, entering nulls in the columns with no matching values. Right outer join - keep every unmatched row in the second relation in the result relation. Left outer join - a binary operation, rows from the first relation that do not have matching values in the shared column of the second relation are included in the result relation. Natural join - is a binary operation that combines two relations over their common attributes, eliminating one occurrence of each common attribute. - prevents replicated data from appearing in the resulting relation.

Not all dialects of SQL support

INTERSECT, EXCEPT or MINUS keywords

multivalued attributes

If an attribute for an entity contains more than one value, should be decomposed and a new entity created to hold the multiple values. This should be familiar, because it is actually the first step in the normalization process. In other words, the relation is being placed into 1NF.

redundant relationships

If the same data can be derived from multiple relationships, a redundant relationship may be present.

transitive dependency

In a functional dependency, if B is functionally dependent on A, and C is functionally dependent on B, C is then said to be transitively dependent on A.

partial functional dependency

In a functional dependency, when B is functionally dependent on A, and an attribute can be removed from A and the dependency still exists, B is said to be partially dependent on A.

Integrity Constraints

Necessary data: This refers to any attributes that cannot be left blank or null under any circumstances. Obviously, candidate key attributes Domain constraints: creates clear restrictions on the type of data that a column's value can hold. Entity integrity: This step ensures that all primary key attributes are not null. All primary keys should be reviewed and documented at this point. Referential integrity: it ensures the consistency of data between cross-referenced tables. No Action: This prevents a record from being deleted if any child relations reference the parent key. Cascade: If the parent key is deleted, any child keys that are referenced by the parent key are automatically deleted. If the child key is a parent key in another relationship, then the deletion will be applied to the child keys in a cascading manner. Set Null: When the parent key is deleted, all referenced child keys are automatically set to null. This option can only be used if the child key is allowed to contain null values. Set Default: When the parent key is deleted, the child keys will all be set to a default value. This option can only be used if all the child keys have default values defined for them. No Check: When the parent key is deleted, do not check that referential integrity is maintained. This option is for use in special situations when the database designer is confident that its use will cause no referential integrity problems. (This action is not supported in MySQL.) Enterprise constraints: This step involves ensuring that all the business rules concerning the data have been enforced. Enterprise constraint: numeric values can be checked to ensure that they fall within a desired range, a value's format can be checked, or a value can be limited to a defined list and must come only from that list.

Common SQL data types

SMALLINT 16-bit signed integer INTEGER 32-bit signed integer DECIMAL(p, q) Signed decimal number, p digits, q decimals FLOAT(p) Floating point number of p bits precision CHAR(n) Fixed length string of n characters VARCHAR(n) Variable length string of up to n characters DATE Date (in the form yyyymmmdd) TIME Time (in the form hhmmss) TIMESTAMP Combines a DATE and a TIME

Union Compatible

Same number of attributes with matching domains.

Five fundamental operations of relational algebra.

Selection: (Selection is a unary operation) - uses a condition to select ROWS from a relation and create a new relation(Male symbol Austin powers wore) Projection: (Projection is a unary operation) - select COLUMNS from a relation (ultimately returning rows containing just the attributes selected) - uses a condition to select columns from a single relation to create a new relation - The symbol π represents the projection operation Cartesian product: - A Cartesian product operation multiplies two relations to create a new relation containing every possible pair of rows from the two original relations. ----A restricted Cartesian product operation combines data from two relations but eliminates data redundancy (a join operation equivalent)--- - The X symbol represents the Cartesian product operation. Union: - is a binary operation that combines all rows into a single relation while eliminating any duplicates. - The ∪ symbol represents the union operation. Difference: - is a binary operation that creates a relation including the rows that are in the first relation but not in the second. - The − symbol represents the difference operation.

white-box testing or "Glass-Box"

Someone conducting this type of testing has direct access to the code, and may be authorized to make changes to the code.

black-box testing

Someone who has no knowledge of the code, or any predetermined notions of how the application is supposed to perform, conducts this type of testing.

CASCADE

The CASCADE action ensures that if the parent key is deleted, any child keys that are referenced by the parent key are automatically deleted.

IN

The IN keyword is used to define a set of values against which a comparison is made.

domain

The allowable values for an attribute.

Creating base relations for a target DBMS

The first step in creating relations for a particular DBMS involves collecting and organizing all the information that was created in the logical design phase The database designer then needs to begin the process of creating the base relations in the DBMS. To do this, the database designer uses the relations derived from the logical data model and the data dictionary. Each relation should provide the following information: • The relation's name. • The relation's attributes. • The primary key and any foreign keys. • The integrity constraints for any foreign keys. The data dictionary should provide the following information: • Each attribute's domain. • Any default values (if present and permitted) for each attribute. • The attribute's value, if it can be null.

logical design phase

The logical design phase involves refining and clarifying the conceptual data models. The cardinality of all relationships is examined, with the many-to-many relationships resolved and replaced by one-to-many relationships, usually through decomposition of the relations involved in the relationship. The resulting data model is then taken through the normalization process. The data model is then validated against the user and system requirements. Finally, the various user views are merged into one enterprise data model, which represents the entire data structure for the enterprise. After the logical data model has been completed, the next step is to validate the model against the documented user operations and system requirements. This step involves reviewing the database requirements document to ensure that all the specified data has been included in the logical data model. Typically, the database designer will use the ER diagram to manually perform all the required data operations to ensure that they are all possible. The validation process can be performed by writing each required transaction to ensure that all the data is present to carry out that operation. The final step of the logical database design phase involves creating an enterprise data model from the various user views that may exist.

degree

The number of attributes a relation contains

what determines the degree of a relationship

The number of entities involved in the relationship

query language

The part of a nonprocedural DML that retrieves data from a database

decomposition

The process of creating new relations from existing relations based on functional dependencies within the original relation.

Why does the separation of the database systems and the programs that access them make an application programmer's job easier?

The programmer no longer needs to be concerned with how the database is defined or how it holds the data.

Three levels of conformity

Three levels of conformity were designated by the ISO: 1. Entry -Most products today have conformed only to the Entry level. 2. Intermediate - An entity created as a result of the decomposition of a relation while resolving a many-to many relationship. 3. Full.

For which task would you use a database definition language (DBDL)?

To describe the structure of each relation The database designer can organize the refined and normalized relations in preparation for the physical design phase by describing the structure of each relation using a database definition language (DBDL).

DISTINCT

To retrieve nonduplicate data, the DISTINCT keyword can be used. The following example illustrates this keyword: SELECT DISTINCT emp_id FROM Orders;

In a decentralized environment, why is the repetition of data problematic?

When data is duplicated in a system, it is much more difficult to maintain the integrity of that data. When the data is changed in one location, it must also be changed everywhere else that the data exists. In a decentralized environment, this can be difficult at best.

LIKE

Wildcards: 1. the underscore _ means one character of any to fit there. 2. the percent % means one or more will fit there.

transactions criterion

backup and recovery capabilities, logging systems, concurrency control, rollback and commit support, and support for stored procedures.

secondary index

creates an additional key for a relation to attempt to help to make the retrieval of data from the relation more efficient. Secondary indexes incur additional overhead in the performance of the database. Use the following suggestions when determining whether to use secondary indexes. Small relations generally do not need secondary indexes. Searching the relation in RAM may be faster than using a secondary index. If a particular attribute is updated often, it may not be a good candidate for a secondary index.

system catalog

database of Data Dictionary

Peterlee Relational Test Vehicle

developed by The IBM UK Scientific Center in 1976 - Important information concerning query processing and database optimization emerged from this project.

An advantage of using a relational database management system

is that data can be accessed or reassembled from the database in many different ways without having to reorganize the database tables.

Cardinality of the relationships

ll -- one and only one; 0l -- zero or one; >l -- one or more; >0 -- zero, one or more. 1. 1:1 (one-to-one) 2. 1:n (one-to-many): 3. m:n or 0:0(many-to-many): a. They must be eliminated in the relational data model. b. To resolve an m:n relationship, relations are decomposed, creating new entities. An entity created to resolve an m:n relationship is known as an intermediate entity. Acts as a go-between for the two entities that have the m:n relationship. - Cardinality refers to the mapping of instances of one entity to another. - cardinality of a relation changes as records are added or deleted. - number of tuples or rows relation contains is known as its cardinality. Examples: 1. for each instance of entity A, there are zero or more instances of entity B, but for each instance of entity B, there is only one entity A = one-to-many (1:n) 2.

creating a GUI to a database

major concerns: 1. including descriptive page titles 2. adding clear instructions 3. grouping input fields consistently 4. using logically labeled fields 5. using color consistently 6. sizing data entry fields properly 7. allowing for logical cursor movement from one field to the next. 8. designing error messages that inform the user if unacceptable values have been entered 9. clearly indicating optional fields 10. Designing completion messages that let the user know when a data operation has been completed successfully.

Persistent information

means that the data remains intact after the program that accessed the information has ended.

Unary operation

operates on a single relation. -Projection and Selection are a unary operations.

Access criteria

query language support, interface capabilities with other systems, interface capabilities with programming languages, multiuser capabilities, and security capabilities.

database testing phase:

testing should be performed with carefully designed testing strategies with real data so that the entire system is thoroughly tested.

relational database design process

the conceptual design phase involves constructing a raw data model, independent of any DBMS. Identified and documented in this design phase are entities, attributes, relationships, domains, and candidate and primary keys. Preliminary ER diagrams are constructed, creating data models based on the various user views that exist in the enterprise. The data models representing the various user views are then reviewed with the users. This is done to ensure that the data models correctly identify the proper entities for each view.

binary operation

two relations are involved - union, Intersections, Joins, and difference are binary operations.

A row within a relation will always be

unique. Repeated rows of data cannot occur. In a relational database, just as with columns, the sequence of the rows is insignificant. The main benefit of this characteristic is that the rows of a relation can be retrieved in any order or sequence.

IBM System R

was a database system built in the 1970s. It was the first implementation of Structured Query Language (SQL). System R influenced many relational systems since its inception.

Conceptual Database Design

• Identifying entities(identifying the main entities that will make up the database) • If a relationship in the conceptual data model has identifiable attributes, an entity needs to be created to absorb the attributes. • Identifying relationships(all the entities are examined to determine any relationships that exist between them) • Identifying candidate and primary keys for entities(This step involves determining which attribute within an entity will be used as the primary key.) • Creating an entity-relationship (ER) diagram (ER model = is a high-level, conceptual database design method that describes entities, entity attributes, and the relationships among entities. ER models are used to aid the database design process and to show the data from the perspective of the user. • Reviewing the ER model by the user and design team(chance to catch and correct any errors or possibly add or subtract data as necessary.) • Once conceptual data models have been created for the various user views that may exist in the enterprise, the next step is to use the conceptual data models to create logical data models.