question

Relationship

answer

association between entities

question

Types of relationships:

answer

One-to-many: Each rep is associated with many customers. Each customer is associated with a single rep

question

Database

answer

structure that can store information about different categories of information and relationships between those categories

question

Database Management System (DBMS)

answer

program, or collection of programs, through which users interact with a database; can create forms and reports

question

Advantages of database processing:

answer

Getting more information from the same amount of data Sharing data Balancing conflicting requirements Controlling redundancy Facilitating consistency Expanding security Increasing productivity Providing data independence

question

Disadvantages of database processing:

answer

Larger file size Increased complexity Greater impact of failure More difficult recovery

question

Rules of a Relation

answer

Two-dimensional table in which: Entries are unnormalized Each column has a distinct name (attribute name) All values in a column are values of the same attribute Order of columns is immaterial Each row is distinct Order of rows is immaterial

question

Attribute

answer

characteristic or property of an entity (field/column)

question

Primary Key

answer

column or collection of columns of a table (relation) that uniquely identifies a given row in that table

question

Foreign key

answer

field(s) whose value is required to match the value of the primary key for a second table

question

Relational database

answer

collection of relations or tables

question

Basic relational algebra operations:

answer

SELECT-retrieves certain rows from an existing table (based on criteria) and saves them as a new table (includes WHERE) PROJECT-causes only certain columns to be included in the new table (includes OVER) JOIN-two tables being joined

question

Structure of an SQL query:

answer

SELECT-FROM-WHERE

question

Use of compound conditions in WHERE statements:

answer

connecting two or more simple conditions using one or both (AND/OR)

question

Use of LIKE:

answer

include in the WHERE clause WHERE Street LIKE "Oxford";

question

Wildcards:

answer

Access: *-collection of characters ?-any individual character Other: %-any collection of characters _-any individual character

question

Computed Fields

answer

field whose values you derive from existing fields (+,-,*,/)

question

Built-in Functions

answer

(aggregate functions) COUNT, SUM, AVG, MAX, MIN

question

JOIN

answer

can list multiple tables in SELECT, multiple columns in FROM, and multiple conditions in WHERE

question

SELECT

answer

lists fields (columns) to display

question

INTO

answer

saves the results of a query as a table specified before FROM and WHERE clauses SELECT * INTO SmallCust (<-name of table to create) FROM Customer WHERE CreditLimit <=7500;

question

INSERT

answer

command adds new data to a table

question

CREATE TABLE

answer

CREATE TABLE Rep (RepNum CHAR(2) PRIMARY KEY, LastName CHAR(15), FirstName CHAR(15));

question

ALTER TABLE

answer

ALTER TABLE Customer CHANGE COLUMN CustomerName TO CHAR (40);

question

DROP TABLE

answer

DROP TABLE SmallCust

question

Concept of stored procedures:

answer

Special file used to store a query that is run often Placed on the server Improves overall performance Convenience ****Access does not support store procedures.

question

Concept of triggers:

answer

Action that occurs automatically in response to an associated database operation Stored and compiled on the server

question

Using Access macros for triggers:

answer

****Access does not support triggers -has data macros instead (go to Table Tools, Named Macro)

question

View:

answer

application program's or individual user's picture of the database Less involved than full database Simplification Security Database independence Each user has his or her own view

question

VIEW

answer

CREATE VIEW Games AS SELECT ItemNum, Description, OnHand, Price FROM Item WHERE Category='GME' ****Does not work in Access -Create and save a query to create a view

question

Determinants

answer

constraint that describes the relationship between attributes in a relation

question

Functional Dependence

answer

constraint between two sets of attributes in a relation from a database

question

Alternate Key

answer

candidate keys not chosen as primary key

question

Candidate Key

answer

column(s) on which all columns in table are functionally dependent

question

Information level design:

answer

completed independently of any particular DBMS

question

Physical level design:

answer

Information-level design adapted for the specific DBMS that will be used

question

Entity Subtypes

answer

Can result from splitting tables to avoid use of null values Table that is a subtype of another table

question

Nulls

answer

Special value Represents absence of a value in a field Used when a value is unknown or inapplicable

question

Incomplete category in subtypes:

answer

records that do not fall in to the subtype

question

Complete category in subtypes:

answer

all records fall into the categories

question

Existence Dependency(Weak Entities)

answer

depends on another entity for its own existence

question

Bottom-up Design Method:

answer

Design starts at low level Specific user requirements drive design process

question

Top-down Design Method:

answer

Begins with general database that models overall enterprise Refines model until design supports all necessary applications

question

Cardinality

answer

Number of items that must be included in a relationship An entity in a relationship with minimum cardinality of zero plays an optional role in the relationship An entity with a minimum cardinatly of one plays a mandatory role in the relationship

question

Functions or services provided by a DBMS:

answer

Update and retrieve data Provide catalog services Support concurrent update Recover data Provide security services Provide data integrity features Support data independence Support data replication Provide utility services

question

How a DBMS handles updating and retrieving data:

answer

1) Enter data 2) Request DBMS add the data 3) DBMS verifies 4) DBMS adds information 5) DBMS notifies data is now stored

question

Catalog Services

answer

stores metadata and makes it accessible to users

question

Concurrency Control

answer

ensures accuracy when several users update database at the same time

question

Recovery

answer

returning database to a correct state from an incorrect state

question

Security

answer

prevention of unauthorized access, either intentional or accidental, to a database

question

Data Integrity

answer

rules followed to ensure data is accurately and consistently updated

question

Data Independence

answer

can change database structure without needing to change programs that access the database

question

Utility Services

answer

assist in general database maintenance

question

Role of locking in concurrency control:

answer

Usually more limited than locking facilities on enterprise DBMSs Programs can lock entire table or individual row within a table, but only one or the other Programs can release any or all of the locks they currently hold Programs can inquire whether a given row or table is locked

question

Role of time-stamps in concurrency control:

answer

DBMS assigns each database update a unique time when update started

question

Problem of deadlock:

answer

Two users hold a lock and require a lock on the resource that the other already has To minimize occurrence, make sure all programs lock records in the same order whenever possible

question

Why do you need recover:

answer

backup or save a copy of database

question

How journaling is used for recovery:

answer

maintaining a log of all updates

question

Encryption

answer

converts data to a format indecipherable to another program and stores it in an encrypted format

question

Authorization

answer

specify which users have what type of access to which data

question

Authentication

answer

techniques for identifying the person attempting to access the DBMS

question

Privacy

answer

right of individuals to have certain information about them kept confidential

question

Types of data integrity features:

answer

Data type Legal values Format

question

Distributed Database Management Systems (DDBMSs)

answer

supports and manipulates single logical database physically divided among networked computers

question

Client/Server Systems

answer

file server architecture

question

Ways databases are accessed on the Web:

answer

Web page Web server Web client Web browser

question

Ways databases are accessed from data warehouses:

answer

through Online Transaction Processing (OLTP) systems

question

How data warehouses are organized:

answer

Subject-oriented, integrated, time-variant, nonvolative collection of data in support of management's decision-making process

question

What data warehouses are used for:

answer

analysis of existing data

question

Types of DDBMS:

answer

Homogeneous DDBMS-same local DBMS at each site Heterogeneous DDBMS-at least two sites at which local DBMSs are different

question

Location Transparency

answer

users do not need to be aware of location of data in a distributed database

question

Data Fragmentation

answer

DDBMS can divide and manage a logical object among various locations under its control

question

Replication Transparency

answer

users unaware of steps taken by DDBMS to up various copies of data

question

Advantages of DDBMS:

answer

Local control of data Increased database capability System availability Improved performance

question

Disadvantages of DDBMS:

answer

Update of replicated data Primary copy More complex query processing More complex treatment of concurrent update More complex recovery measures More complex design, management, and security requirements

question

Fat Client

answer

a client that performs presentation functions and business functions

question

Thin Client

answer

a client that performs only presentation functions

question

Advantages of client/server systems:

answer

Lower network traffic Improved processing distribution Thinner clients Greater processing transparency Increased network, hardware, and software transparency Improved security Decreased costs Increase scalability

Database Management-Final Exam – Flashcards

Unlock all answers in this set

Haven't found what you were looking for?

Search for samples, answers to your questions and flashcards