Ch. 9 Database Management Systems

flat file application vs database environment
computer involves two components: data and instructions
conceptually, there are two methods for designing the interface between program instructions and data:
1. file-oriented processing: a specific data file was created for each application (instructions first, data later)
2. data-oriented processing: create a single data repository to support numerous applications (data first, instructions later)
flat-file problems
types of issues:
data storage
data updating
currency of information
task-data dependency
data storage
(flat file problem)
creates excessive storage costs of paper documents and/or magnetic form.
data updating
(flat file problem)
any changes or additions must be performed multiple times
currency of information
(flat file problem)
has the potential problem of failing to update all affected files
task-data dependency
(flat file problem)
user unable to obtain additional information as his or her needs change
flat-file problem solved
types of issues:
no data redundancy
single update
current values
task-data independence
no data redundancy (flat-file problem solved)
except for primary keys, data is only stored once
task-data independence (flat-file problem solved)
users have access to the full domain of data available to the firm
result of data redundancy
files and data elements used in more than one application must be duplicated and because of this the characteristics of data elements and their values are likely to be inconsistent
inaccessibility of data
outputs usually consist of pre-programmed reports instead of ad-hoc queries provided upon request
inflexibility of program
changes to current file-oriented appliactions cannot be made easily nor can new developments be quickly realized
database
set of computer files that minimizes data redundancy and is accessed by one or more application programs for data processing
the set of record types that an organization needs to support its business process
database management system
a computer program that enables users to create, modify, and utilize database information efficiently
purpose is to provide controlled access to the database.
DBMS is designed to know who has access to what.
association
the nature of the functional connection between two entities in a relation. is represented in an ER diagram by a verb label on the connecting line such as ships, requests, receives, and so on
cardinality
is the degree of association between two entities. simply stated, describes the number of possible occurences in one table that is associated with a single occurrence in a related table
four basic forms
zero or one(o:1), one and only one (1:1), zero or many (0:M) and one or many (1:M)
disadvantages of database approach
-can be costly to implement(hard/soft ware, storage, and network resources)
-can only run in certain operating environments (may be unsuitable for some system configurations)
-because so different from file approach, requires training users (can be resist to change)
elements of the database environment
1. users
2. applications (unsure)
3. DBMS (database management system)
4. DBA (database administrator)
5. physical database
users
individuals who employ systems, receive information, and act on the information received
access the database in two ways
1. application programs-request information from DBMS
2. via direct query- retrieving data in a friendly manner to provide an ad hoc management report
applications
requests information from the DBMS, which in turns validates the requests and retrieves the data for processing.
DBMS feature
program development-
backup and recovery-
database usage reporting-
database access-
program development
user created applications
backup and recovery
copies database
database usage reporting
captures statistics on database usage(who, when , etc.)
database access
authorizes access to sections of the database
user programs
makes the presence of teh dbms transparent to the user
direct query
allows authorized users to access data without programming
database administrator (DBA)
responsible for managing the database resource. having multiple users share a common database requires organization, coordination, rules, and guidelines to protect the integrity of the database.
responsibilities of the DBA
-database planning(coding schemes)
-design (tables and relationship forms and queries)
-implementation- (create database in access create procedure manual)
-operation and maintenance
-change and growth
database planning
(responsibilities of the DBA)
develop organizations database strategy
define database environment
define data requirements
develop data dictionary
design
(responsibilities of the DBA)
logical database(schema)
external users’ view (subschemas)
internal view of database
database controls
implementation
(responsibilities of the DBA)
determine access policy
implement security controls
specify test procedures
establish programming standards
operation and maintenance (responsibilities of the DBA)
evaluate database performance
reorganize database as user needs demand
review standards and procedures
change and growth
(responsibilities of the DBA)
plan for change and growth
evaluate new technology
database conceptual models (3)
hierarchical model
network model
relational model
relational model
-portrays data in the form of two dimensional ‘tables’
-its strength is the ease with which tables may be linked to one another
-based on the relational algebra functions of restrict, project, and join
-linked- PK from one table and embed as attribute in another table in its then referred to a foreign key
data attribute/ field
a single item of data
entity
database representation of an individual resource, event, or agent about which we choose to collect data
record type
table or file
difference in association and cardinality
association- represented by a line connecting two entities
described by a verb, such as ships, requests, or receives
cardinality- the degree of association between two entities
the number of possible occurences in one table that are associated with a single occurrence in a related table
used to determine primary keys and foreign keys
s system is relational if:
1. represents data in the form of two-dimensional tables
2. supports the relational algebra functions of restrict, project, and join
restrict-row
project- attribute(column)
join- two tables consisting of all concatenated pair of rows
three types of anomalies
insertion
deletion
update
insertion anomaly
a new item cannot be added to the table until at least one entity uses a particular attribute item
so you add a vendor but have not purchased inventory yet, the table provided will not add the vendor until you buy something, thus an insertion error
deletion anomaly
if an attribute item used by only one entity is deleted, all information about that attribute item is lost
so we dont need part num 1 anymore and we delete it from the records, then vendor 27 will be delete cause thats the only item they sell to us, even though we want to keep them on file, the information is lost
update anomaly
a modification on an attribute must be made in each of the rows in which the attribute appears
is the result of data redundancy in an unnormalized table
an inventory table list 3 different parts and list the same vendors address in each of those rows, so everytime address is updated, its an update of informatoin 3 times, think big with 10,000 vendors
normalization process
systematically splits unnormalized complex tables into smaller tables that meet two conditions:
1. all nonkey(secondary) attributes in the table are dependent on the primary key
2. all nonkey attributes are independent of the other nonkey attributes

when unnormalized tables are split and reduced to third normal form, they must then be linked together by foreign keys.

six phases in designing relational databases
1. identifying entities
2. construct a data model showing entity associations
3. add primary keys and attributes(are descriptors) exact columns you need
4. normalize and add foreign keys
5. construct the physical database
6. prepare the user views
identifying entities
(six phases in designing relational databases)
identify the primary entities(resources, agents, events, misc.-rf) of the organization – what is table about?
construct a data model- entity relation diagrams of their relationships
construct a data model showing entity associations
(six phases in designing relational databases)
determine the association between entities
add primary keys and attributes
(six phases in designing relational databases)
assign primary keys to all entities in the model to uniquely identify records
every attribute should appear in one or more user views-(form, query ar report(doc), dont store data that you will never use)
normalize and add foreign keys
(six phases in designing relational databases)
remove repeating groups, partial and transitive dependencies
assign foreign keys to be able to link tables
construct the physical database
(six phases in designing relational databases)
create physical tables
populate tables with data
prepare the user views
(six phases in designing relational databases)
normalized tables should support all required views of system users
user view restrict users from having access to unauthorized data
access controls
audit procedures: verify
responsibility for authority tables and subschemas
granting appropriate access authority
use or feasibility of biometric controls
use of encryption
database backup
automatic periodic copy of data
transaction log
list of transactions that provides an audit trail
checkpoint features
suspends data during system reconciliation
recovery module
restarts the system after a failure
backup controls
(audit procedure:verify)
that production databases are copied at regular intervals
backup copies of the database are stored off site to support disaster recovery
Tagged In :

Get help with your homework


image
Haven't found the Essay You Want? Get your custom essay sample For Only $13.90/page

Sarah from studyhippoHi there, would you like to get such a paper? How about receiving a customized one?

Check it out