Database Processing-Final
Terms
undefined, object
copy deck
- What are the functions of a Database Application
-
C-Create,
R-Read,
U-Update
D-Delete - Where and how constraints are enforced
-
Where-entering data
How-using graphical interfaces,such as drop down boxes that provide the domain constraints or radio or check list itmes, tabbing to the next item, ID values,referential integrity, relationship cardinality, triggers for business rules. - List the 3 types of Locking
-
Shared/Exclusive
Implicit/Explicit
Grandularity - Form Design components and rules
-
-Reflect the underlying sturction of the view (how user sees the data)
-Make data associations graphically evident (logical order, area code next to phone number)
-Encourage or guide users actions (tab , right to left, top to bottom in US) - Where and how is security enforced?
-
Exists on many levels. The more places you do it the less likely the average user will stumble into an area they should not be accessing.
Log In to system with username/password.
Log into the database with UN/PW
Granted permissions within DB program.
Horizontal and Vertical security - Horizontal vs. Vertical security measures
-
Horizontal refers to restricting certain rows of data. (only see data for ltd not PCS rows)
Vertical refers to restricting certain columns (all names but no other info) - What problems can a multi-user system create?
-
Deadlock
Locking
-shared/exculusive
-implicit/explicit
-granularity
-Cursors0000000 - What is deadlock?
- The condition know as a deadly embrace is where a transaction begins to lock resources but has to wait for a particular resource to be released by another transaction and the other transaction is waiting on the first to release a resourse.
- What is locked granularity?
- The size of the material being locked--could be one file or an entire table.
- Implicit vs explicit
-
Implicit locks are issued automatically by the DBMS based on an activity.
Explicit locks are issued by users requesing exclusive rights to the data. - List 4 DB Recovery issues
-
1Logs
2Rollback/Rollforward
3Commit
4Checkpoint - What is Rollback?
- Rollback is the procedure where the database performs "undo" processes until the database has been corrected to where the erroneous or malicious transaction has not occurred.
- What is Rollforward?
- The procedure of replaying the activities recorded in the log files. This procedure is used to resynchronize restored database data.
- What is a log?
- The file where database activities are recored.
- What is commit?
- Ensuring all actions are successful before posting to the databse.
- What is a cursor?
- A cursor may be defined within a SQL statement to point to a particular record. The cursor type deines how the cursor behaves, forward only, static, keyset, or dynamic.
- Why is a DBA concerned with System perforamance and DBSM activity and space use
- The DBA is responsible for making sure the system is running smothly and a proper speeds so the end users can work at appropriate speeds. This means the DBA must monitor the activity to make sure no one is locking up the system. It also means the system needs to have enough resources available to run the appropriate application so is the DB sized properly or is it sitting somewhere wasting storage space with nothing in it. Is the level of security appropriate for the database and the users? Does someone keep crashing the system by attempting to do something that is not appropriate for the current system?
- What are the 3 Database Processing Architectures?
-
Teleprocessing systems
Client-Server Systems
Distributed Database Systems - What is teleprocessing?
- A systme where all processing is done by one computer. Users may use dumb terminals to transmit information to a centalized computer.
- What is a client-Server system?
- This is a form of cooperative computing. Client computers and servers using a network, share the computing burden. DBMS is provided by one computer, the server.
- What is a distributed Database?
- Portions of the database ar stored on multibple systems that are interconnected using a network. No one area contains the entire database.
- What is OLAP?
- This is an on-line system that analyzes and presents data in a particular manner.
- When is OLAP used?
-
Most used where a number of predetermined factors are to be tracked and analyzed.
It's weakness is that iusually it is difficult to add or change axes being analyzed across.
It's very good where an understanding exists of the relevant factors that need to be tracked exists. - What is a data warehouse?
-
A store of enterprise data and procedures that is designed to facilitate management decision making.
Data warehouse includes data tools, procedures, training, personnel, and the resources that are required or that make decision making easier.
The data comes from many different sources and may output to many different sources. - What are the Data Warehouse Challenges?
-
Inconsistent Data
Tool Integration
Missing Warehouse Data Mangement Tools
Ad Hoc Nature of Requiements - What is a data Mart?
-
Similar to a datawarehouse but smaller. The goal is to provide the same functionality but withing a limited domain.
Best practices would be by designing smaller data marts and connecting them to create the Data Warehouse. - What determines data base usage?
- Every need, company, and projects has it's own factors that determine how a project is used including, skill sets, application functionality need, timeframe, budget, etc.
- Data Managment depends upon...Remember to LOOK and THINK about....
- Each application /data presentation situation occures in a unique situation---what you do is dermined by who is involved, skill level, time, money, who will be the end user...
- Data Administration
- Data is critical and expensive to acquire as a resource to an organization. Careful administrative procedures and contols are required.
- What are the 6 Data Administration Challenges?
-
1--Many types of data
2--Basic Categoies of data are not obvious.
3--The same data can have many names
4--The same data can have many descriptions and formats
5--Data are changed, often concurrently
6--Political/Organizational issues complicate operational issues. - What are the 5 Data Administrative Functions?
-
1--Marketing
2--Data Standards
3--Data Policies
4--Forum for Data Conflict Resolution
5--Return on an Organization's Data Investment - What Marketing functions does a DBA have?
-
--Communicate the existance of the data administration to the organization.
--Explain the reason for the standards, policies, and guidelines.
--Describe in a positive light the services provided. - What Data Standard functions does the DBA establish?
-
--Establish standard means for describing data items. Standads include name, definition, description, processing restrictions, etc
--Establish data proponents - What function does the DBA have on Data Policy?
- Establish organization-wide data policy. Examples are security, data proponency, and distribution.
- Data Admin Functions on Forums for Data Conflict Resolution?
-
--Establish procedures for reporting conficts.
--Provide means for hearing all perspective and veiws.
--Have authority to make decisions to resolve conflict. - Return on Organizations Data investment and the DBA.
-
--Focus attention on value of data investment.
--Investigate new methodologies and technologies.
--Take proactive attitude toward information management. - What is DML and what does it allow?
-
Data Manipulation Language
Allows users to:
--select
--delete
--insert
--update - What is an API?
-
Application Program Interface
used to manipulate data. Controls the look of the query and he result of the query. - Union Operator
- Like "or"
- Difference Operator
- Contains 1st set minus anything in the second set.
- Intersection Operator
-
This is the "AND" feature
Only lists what is on both sets.
123, 456, 789 ----345, 123, 567
123 - Projection Operator
-
This results in a listing of columns
1234 * 4
123 columns - Selection Operator
-
Selects rows not columns
SELECT name = Bob
Bob, 123, Robin Lane, ...
Bob, 456, Robin Lane, ... - Join Operator
-
JOIN 1234 and 4567
1234567 - What are the 3 types of Joins
-
1-Equijoin
2-Natural Join
3-Left Outer Join - Equijoin
- Lists everything that both sets have in common
- natural join
- Doesn't repeat the identifier
- Left Outter Join
- Includes everything from both sets even if there is no data.
- API
- Application Program Interface
- Projection
- filters by column
- Wildcards
-
Like =
_ single character substitution
% a multicharacter substitution