Database Management-Final Exam

Relationship
association between entities
Types of relationships:
One-to-many: Each rep is associated with many customers. Each customer is associated with a single rep
Database
structure that can store information about different categories of information and relationships between those categories
Database Management System (DBMS)
program, or collection of programs, through which users interact with a database; can create forms and reports
Advantages of database processing:
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

Disadvantages of database processing:
Larger file size

Increased complexity

Greater impact of failure

More difficult recovery

Rules of a Relation
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

Attribute
characteristic or property of an entity (field/column)
Primary Key
column or collection of columns of a table (relation) that uniquely identifies a given row in that table
Foreign key
field(s) whose value is required to match the value of the primary key for a second table
Relational database
collection of relations or tables
Basic relational algebra operations:
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

Structure of an SQL query:
SELECT-FROM-WHERE
Use of compound conditions in WHERE statements:
connecting two or more simple conditions using one or both (AND/OR)
Use of LIKE:
include in the WHERE clause

WHERE Street LIKE “Oxford”;

Wildcards:
Access:
*-collection of characters
?-any individual character

Other:
%-any collection of characters
_-any individual character

Computed Fields
field whose values you derive from existing fields (+,-,*,/)
Built-in Functions
(aggregate functions) COUNT, SUM, AVG, MAX, MIN
JOIN
can list multiple tables in SELECT, multiple columns in FROM, and multiple conditions in WHERE
SELECT
lists fields (columns) to display
INTO
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;

INSERT
command adds new data to a table
CREATE TABLE
CREATE TABLE Rep
(RepNum CHAR(2) PRIMARY KEY,
LastName CHAR(15),
FirstName CHAR(15));
ALTER TABLE
ALTER TABLE Customer
CHANGE COLUMN CustomerName TO CHAR (40);
DROP TABLE
DROP TABLE SmallCust
Concept of stored procedures:
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.

Concept of triggers:
Action that occurs automatically in response to an associated database operation

Stored and compiled on the server

Using Access macros for triggers:
****Access does not support triggers
-has data macros instead (go to Table Tools, Named Macro)
View:
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

VIEW
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

Determinants
constraint that describes the relationship between attributes in a relation
Functional Dependence
constraint between two sets of attributes in a relation from a database
Alternate Key
candidate keys not chosen as primary key
Candidate Key
column(s) on which all columns in table are functionally dependent
Information level design:
completed independently of any particular DBMS
Physical level design:
Information-level design adapted for the specific DBMS that will be used
Entity Subtypes
Can result from splitting tables to avoid use of null values

Table that is a subtype of another table

Nulls
Special value

Represents absence of a value in a field

Used when a value is unknown or inapplicable

Incomplete category in subtypes:
records that do not fall in to the subtype
Complete category in subtypes:
all records fall into the categories
Existence Dependency(Weak Entities)
depends on another entity for its own existence
Bottom-up Design Method:
Design starts at low level

Specific user requirements drive design process

Top-down Design Method:
Begins with general database that models overall enterprise

Refines model until design supports all necessary applications

Cardinality
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

Functions or services provided by a DBMS:
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

How a DBMS handles updating and retrieving data:
1) Enter data
2) Request DBMS add the data
3) DBMS verifies
4) DBMS adds information
5) DBMS notifies data is now stored
Catalog Services
stores metadata and makes it accessible to users
Concurrency Control
ensures accuracy when several users update database at the same time
Recovery
returning database to a correct state from an incorrect state
Security
prevention of unauthorized access, either intentional or accidental, to a database
Data Integrity
rules followed to ensure data is accurately and consistently updated
Data Independence
can change database structure without needing to change programs that access the database
Utility Services
assist in general database maintenance
What happens without concurrency control:
Role of locking in concurrency control:
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

Role of time-stamps in concurrency control:
DBMS assigns each database update a unique time when update started
Problem of deadlock:
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

Why do you need recover:
backup or save a copy of database
How journaling is used for recovery:
maintaining a log of all updates
Encryption
converts data to a format indecipherable to another program and stores it in an encrypted format
Authorization
specify which users have what type of access to which data
Authentication
techniques for identifying the person attempting to access the DBMS
Privacy
right of individuals to have certain information about them kept confidential
Types of data integrity features:
Data type

Legal values

Format

Distributed Database Management Systems (DDBMSs)
supports and manipulates single logical database physically divided among networked computers
Client/Server Systems
file server architecture
Ways databases are accessed on the Web:
Web page

Web server

Web client

Web browser

Ways databases are accessed from data warehouses:
through Online Transaction Processing (OLTP) systems
How data warehouses are organized:
Subject-oriented, integrated, time-variant, nonvolative collection of data in support of management’s decision-making process
What data warehouses are used for:
analysis of existing data
Types of DDBMS:
Homogeneous DDBMS-same local DBMS at each site

Heterogeneous DDBMS-at least two sites at which local DBMSs are different

Location Transparency
users do not need to be aware of location of data in a distributed database
Data Fragmentation
DDBMS can divide and manage a logical object among various locations under its control
Replication Transparency
users unaware of steps taken by DDBMS to up various copies of data
Advantages of DDBMS:
Local control of data

Increased database capability

System availability

Improved performance

Disadvantages of DDBMS:
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

Fat Client
a client that performs presentation functions and business functions
Thin Client
a client that performs only presentation functions
Advantages of client/server systems:
Lower network traffic

Improved processing distribution

Thinner clients

Greater processing transparency

Increased network, hardware, and software transparency

Improved security

Decreased costs

Increase scalability