Database Management

Front 1

View

Back 1

An application program's or individual user's picture of the database.

Front 2

View Example

Back 2

CREATE VIEW Games AS

SELECT ItemNum, Description, OnHand, Price

FROM Item

WHERE Category= 'GME'

;

Front 3

Defining Query

Back 3

Indicates what to include in the view. SELECT command creates this view. Data doesn't exist in this form and it never will.

Front 4

Query involving View

Back 4

DBMS changes the query to one that selects data from the tables in the database that created the view. Merges query to the query that defines the view.

SELECT ItemNum, Description, OnHand, PriceFROM Item

WHERE Category= 'GME'

AND OnHand<25

Front 5

Row-and-Column Subset View

Back 5

A view that consists of a subset of the rows and columns in a table.

Front 6

Advantages of Views

Back 6

-Provides data independence. User can still access and use the same view even after fields being added or relationships changing between tables.

-Because each user has their own view, different users can view the same data in different ways.

-Greatly simplifies the user's perception of the database.

-Measure of security: Fields that are not included in the field are not accessible to the view's user.

Front 7

Index

Back 7

A file that relates key values to records that contain those key values. Increases efficiency with which data is retrieved from the database. Add and Delete indexes as necessary.

Front 8

Index Key

Back 8

The field or combination of fields on which the index is built.

Front 9

Index Disadvantages

Back 9

-Index occupies space on a storage device. Not necessary because retrieval can be made, less efficiently, without the index.

-DBMS must update the index whenever corresponding data in the database is updated.

Front 10

Conditions to Create an Index

Back 10

-Field is primary key of the table

-Field is foreign key in a relationship you have created

-Frequently use the field as a sort field

-Frequently need to locate a record based on the value in this field

Front 11

CREATE INDEX Command

Back 11

Creates an Index.

Front 12

Indexed Options

Back 12

No (Default)- When you need to remove a previously created index

Yes (Duplicates OK)- Create an index that allows duplicate values

Yes (No Duplicates)- Cannot add same values

Front 13

Single-field Index

Back 13

An index whose key is a single field

Front 14

Multiple-field Index

Back 14

An index with more than one field. List more important key first.

Front 15

Drop Index

Back 15

Delete an index that is not necessary

Front 16

Security

Back 16

Prevention of unauthorized access to the database

Front 17

GRANT Statement

Back 17

Provides access, different types of privileges can be granted to user. Rights to select, insert, update, and delete table data.

Front 18

REVOKE Statement

Back 18

Revoke user privileges, no access.

Front 19

Entity Integrity

Back 19

Rule that no field that is part of the primary key may accept null values. Ensures to recognize one record from another in the primary key.

Front 20

Null

Back 20

When value is missing, unknown, or inapplicable. Primary keys NEVER allows Null.

Front 21

Referential Integrity

Back 21

Rule that if table A contains a foreign key that matches the primary key of table B, the values of this foreign key must match the value of the primary key for some row in table B or be Null.

Front 22

Foreign Key

Back 22

Field in a table whose value is required to match the value of the primary key for a second table.

Front 23

Cascade Delete

Back 23

Ensures that deleting a rep record also deletes the customer records

Front 24

Cascade Update

Back 24

Ensures that changing a rep record are also made to the customer records

Front 25

Legal-Values Integrity

Back 25

Property that states that no record can exist in the database with a value in the field other than one of the legal values.

Front 26

CHECK Clause

Back 26

Enforces legal-values integrity.

Access uses validation rule and validation text.

Front 27

ALTER TABLE Command

Back 27

Allows the creation of new fields in existing tables (SQL). ADD, MODIFY, DROP COLUMN, DROP TABLE commands

Front 28

System Catalog

Back 28

Information about tables in the database. Maintained automatically by DBMS.

Front 29

Documenter

Back 29

Allows you to print detailed documentation about any table, query, report, form, or other object in the database.

Front 30

Client-server System

Back 30

Database resides on a computer called the server and users access the database through clients.

Front 31

Client

Back 31

A computer that is connected to a network and has access through the server to the database.

Front 32

Stored Procedure

Back 32

Saving a particular query that is used often. Placed on the server and is convenient. Response to user request.

Front 33

Trigger

Back 33

Action that occurs automatically in response to an associated database operation. Stored and compiled on the server. Response to a command that causes the associated database operation to occur. (Access= data micro)

Front 34

Arguments

Back 34

Additional info required about an action in a data micro to complete the action

Front 35

Normalization Process

Back 35

The process of removing repeating groups to produce a first normal form table. Goal of normalization is to take a table and produce a new collection of tables that represent the same info but that is free of update anomalies.

Front 36

Update Anomaly

Back 36

An update problem that can occur in a database as a result of a faulty design

Front 37

Functional Dependence

Back 37

Column B is functionally dependent on column A if a value for A determines a single value for B at any one time.

Front 38

Primary Key

Back 38

1. All columns in Relation Table (R) are functionally dependent on A
2. No subcollection of the columns in A also has Property 1.

Front 39

Candidate Key

Back 39

A column in the table that are functionally dependent AKA, alternate key. (Same definition as Primary Key)

Front 40

Unnormalized relation

Back 40

A relation that contains a repeating group.

Front 41

First Normal Form

Back 41

When it doesn't contain repeating groups. Remove inner parenthesis.

Front 42

Problems in 1st Normal Form

Back 42

Update- Changing each row that contains same data

Inconsistent data- Description of object can be different for each row

Additions- Make up numbers to create new rows

Deletions- Deleting one order would cause you to lose all info about that product.

Front 43

Nonkey Column

Back 43

When a column is not a part of the primary key.

Front 44

Second Normal Form

Back 44

When it is in 1st Normal Form and no nonkey column is dependent on only a portion of the primary key (no partial dependencies).

Front 45

Dependency Diagram

Back 45

Uses arrows to indicate all the functional dependencies present in the Orders table. Arrow above boxes= normal dependencies that should be present. Arrow below= Prevent table from second normal form, partial dependencies- dependencies on only a portion of the primary key.

Front 46

Problems in 2nd Normal Form

Back 46

Wasted space

Updates

Inconsistent data

Additions

Deletions

Front 47

Determinant

Back 47

Any column that determines another column

Front 48

Third Normal Form

Back 48

When it is in second normal form and the only determinants it contains are candidate keys.

Front 49

Problems in 3rd Normal Form

Back 49

Update

Additions

Deletions

Front 50

Multivalued Dependence of column B on column A

Back 50

When each value for A is associated with a specific collection of values for B and further this collection is independent of any values for C.

Front 51

Fourth Normal Form

Back 51

When it is in third normal form and there are no multivalued dependencies.

Front 52

Interrelation Constraint

Back 52

A condition that involves two or more relations

Front 53

Informational-level Design

Back 53

Database designers design a database that satisfies the organization's requirements as cleanly as possible. Completed independently of any particular DBMS that the organization will use.

1. Represent the user view as a collection of tables

2. Normalize these tables

3. Identify all keys in these tables

4. Merge the results of Steps 1 through 3 into the cumulative design

Front 54

Physical-level Design

Back 54

Designer must consider the characteristics of the particular DBMS that the organization will use.

Front 55

User View

Back 55

The set of requirements that is necessary to support the operations of a particular database user.

Front 56

Cumulative Design

Back 56

A design that supports all the user views encountered thus far in a design process.

Front 57

Steps of the Design Process

Back 57

1. Determine the entities involved and create a separate table for each type of entity.

2. Determine the primary key for each table.

3. Determine the properties of each entity.

4. Determine relationships between entities.

Front 58

Many to Many Relationship

Back 58

Creating a new table whose primary key is the combination of the primary keys of the original tables.

Front 59

One to Many Relationship

Back 59

Primary key of "one" table as a foreign key in the "many" table.

Front 60

Secondary Keys

Back 60

Columns that are of interest strictly for the purpose of retrieval.

Front 61

Natural Key (Logical or Intelligent Key)

Back 61

A primary key that consists of a column that uniquely identifies an entity.

Front 62

Artificial Key

Back 62

A column for an entity to serve solely as the primary key and that is visible to users.

Front 63

Surrogate Key (Synthetic Key)

Back 63

A system-generated primary key that is usually hidden from users

Front 64

Database Design Language (DBDL)

Back 64

A relational-like language that is used to represent the result of the database design process.

Tables, columns, and primary keys are written by first listing the table name and listing the column that make up the table.

Primary key= underlined

Alternate key= AK

Secondary key= SK

Foreign key= FK, followed by an arrow pointing to the table identified by the foreign key.

Front 65

Entity-Relationship Diagram

Back 65

Visually illustrates all the information listed in the DBDL.

Front 66

Independent entity

Back 66

An entity that does not require a relationship to another entity for identification.

Front 67

Dependent entity

Back 67

An entity that does require a relationship to another entity for identification.

Front 68

Identifying relationship

Back 68

Relationship that is necessary for identification

Front 69

Nonidentifying relationship

Back 69

Relationship that is not necessary for identification

Front 70

Bottom-up Design Method

Back 70

Which specific user requirements are synthesized into a design

Benefits: Ensures each requirement is met.

Front 71

Top-down Design Method

Back 71

Begins with a general database design that models the overall enterprise and repeatedly refines the model to achieve a design that supports all necessary applications.

Benefits: Have an idea where you are headed.

Front 72

Survey Form

Back 72

Entity Information- For each entity, record a name and description and identify any synonyms for the entity

Attribute information- List general information about its use.

Relationships- Include entities involved, type of relationship, significance, and restrictions

Functional dependencies- Information about it existing among the columns.

Processing information- Description of manner

Front 73

Incomplete/Complete Categories

Back 73

Incomplete= 1 horizontal line

Complete= 2 horizontal lines

Front 74

Composite entity

Back 74

Both an entity and relationship and is represented in an E-R diagram by a diamond within a rectangle.

Front 75

Existence dependency

Back 75

Existence of one entity depends on the existence of another related entity

Front 76

Weak Entity

Back 76

Entity depends on another entity for its own existence

Front 77

Cardinality

Back 77

Number of items that must be included in a relationship

Front 78

Optional Role

Back 78

Entity in a relationship with minimum cardinality of 0

Front 79

Mandatory Role

Back 79

Entity in a relationship with minimum cardinality of 1