Systems Analysis and Design Chapter 9

To break the original table into two or more new tables and reassign the fields so that each nonkey field will depend on the entire primary key in its table:

1. Create and name a separate table for each field in the existing primary key. 2. Create a new table for each possible combination of the original primary key fields. 3. Study the three tables and place each field with its appropriate primary key, which is the minimal key on which it functionally depends.

Some code design suggestions are to:

1. keep codes concise. 2. allow for expansion. 3. keep codes stable. 4. make codes unique. 5. use sortable codes. 6. use a simple structure. 7. avoid confusion. 8. make codes meaningful. 9. use a code for a single purpose. 10. keep codes consistent.

Specific database management system advantages include:

1. scalability: a system can be expanded, modified, or downsized easily to meet the rapidly changing needs of a business enterprise. 2. economy of scale: the inherent efficiency of high-volume processing on larger computers. 3. database administrator: the person who assesses overall requirements and maintains the database for the benefit of the entire organization rather than a single department or user. 4. stronger standards, 5. better security. 6. data independence.

Y2K issue

Firms that used only two digits to represent the year at the beginning of the 21st century were faced with this major problem.

American Standard Code for Information Interchange

a coding method used on most personal computers.

database management system

a collection of tools, features, and interfaces that enables users to add, update, manage, access, and analyze data.

crow's foot notation

a common method of cardinality notation with shapes that include circles, bars, and symbols, which indicate various possibilities.

Java database connectivity

a common standard that enables Java applications to exchange data with any database that uses Structured Query Language statements and is Java-database-connectivity-compliant.

foreign key

a field in one table that must match a primary key value in another table in order to establish the relationship between the two tables.

secondary key

a field of combination of fields that can be used to access or retrieve records.

primary key

a field or combination of fields that uniquely and minimally identifies a particular member of an entity.

common field

a field that connects tables.

file (or table)

a figure in a data structure that contains data about people, places, things, or events.

standard notation format

a format that shows a table's structure, fields, and primary key.

long integer format

a format that uses 32 bits, or 4 bytes, to represent the number 1,234,567,890 in binary form.

integer format

a format that uses only 16 bits, or two bytes, to represent the numbers 13,345 in binary form.

data structure

a framework for organizing, storing, and managing data.

query language

a language in which the user provides an example of the data requested.

Structured Query Language

a language that allows client workstations to communicate with servers and mainframe computers.

data manipulation language

a language that controls database operations including storing, retrieving, updating, and deleting data.

entity-relationship diagram

a model that shows the logical relationships and interaction among system entities.

binary storage format

a more efficient storage method that represents numbers as actual binary values, rather than as coded numeric digits.

unicode

a more recent coding standard that uses two bytes per character, rather than one.

entity

a person, place, thing, or event for which data is collected and maintained.

logical record

a set of field values that describes a single person, place, thing, or event.

code

a set of letters or numbers that represents a data item.

repeating group

a set of one or more fields that can occur any number of times in a single record, with each occurrence having different values.

record (or tuple)

a set of related fields that describes one instance, or occurrence, of an entity, such as one customers, one order, or one product.

referential integrity

a set of rules that avoids data inconsistency and quality problems

field (or attribute)

a single characteristic or fact about an entity.

table design

a specification of the fields and identification of the primary key in a paticular table or file.

International Organization for Standardization

a standardization that requires a format of four digits for the year, two for the month, and two for day.

second normal form

a table design in 1NF whose fields are ALL NOT part of the primary key and are functionally dependent on the ENTIRE primary key.

unnormalized

a table design that contains a repeating group.

data mart

a tool designed to serve the needs of a specific department, such as sales, marketing, or finance

subschema

a view of the database used by one or more systems or users.

open database connectivity

an industry-standard protocol that that makes it possible for software from different vendors to interact and exchange data.

data warehouse

an integrated collection of data that can include seemingly unrelated information, no matter where it is stored in the company.

orphan

an order that has no related customer.

table (or file)

an organization of data that contains a set of related records that store data about a specific entity.

data element (or data item)

another name for a field.

candidate key

any field that could serve as a primary key.

nonkey field

any field that is not a primary key or a candidate key.

clicks to close

average page views to accomplish a purchase or obtain desired information.

dimensions

characteristics that a data warehouse allows users to specify.

derivation codes

codes that combine data from different item attributes, or characteristics.

significant digit codes

codes that distinguish items by using a series of subgroups of digits.

action codes

codes that indicate what action to be taken with an associated item.

cipher codes

codes that use a keyword to encode a number.

alphabetical codes

codes that use alphabet letters to distinguish one item from another based on a category, abbreviation, or easy-to-remember value, called a mnemonic code. There are more one classification codes: 1. category codes: codes that identify a group of related item. 2. abbreviation codes: alphabetical abbreviations. 3. mnemonic codes: codes that use a specific combination of letters that are easy to remember.

block sequence codes

codes that use blocks of numbers for different classifications.

key fields

controls used during the systems design phase to organize, access, and maintain data structures.

backup copies

copies to back up system files and databases.

clickstream storage

data gathering when there are clear, measurable goals.

logical store

data that a user can view, understand, and access, regardless of how or where that information actually is organized or stored.

bits (or binary digits)

digital components that have only two possible values: 1 or 0.

market basket analysis

displaying items that are often purchased together in the same area of the store.

byte

eight bits.

audit log files

files that record details of all accesses and changes to the file or database and can be used to recover changes made since the last backup.

sequence codes

numbers of letters assigned in a specific order.

permissions

privileges that can be associated with different users so that some employees can be limited to read-only access, while other users might be allowed to update or delete data.

recovery procedures

procedures used to restore a file or database to its current state at the time of the last backup in the event of a file catastrophe.

middleware

software that integrates different applications and allows them to exchange data.

data mining software

software that looks for meaningful data patterns and relationships.

audit fields

special fields within data records to provide additional control or security information.

cardinality notation

special symbols to represent the relationship between two entities.

physical storage

strictly hardware-related business that involves the process of reading and writing binary data to physical media such as hard drive, CD-ROM, or network-based storage device.

file-oriented systems (or file-processing systems)

systems that store data in separate files that are not connected or linked.

schema

the complete definition of a database, including descriptions of all fields, tables, and relationships.

relational database (or relational mode)

the dominant approach (introduced in the 1970s) for organizing, storing, and managing business data.

associative entity

the even to transaction that links a one-to-one relationship with a one-to-many relationship.

cardinality

the numeric relationship between two entities, which shows how instances of one entity relate to instances of another entity.

combination key (or composite key, concentrated key, or multivalued key)

the primary key that is a combination of something like a student number and course identification.

encryption

the process of converting readable data into unreadable characters to prevent unauthorized access to the data.

normalization

the process of creating table designs by assigning specific fields or attributes to each table in the database.

absolute date

the total number of days from some specific base date.

first normal form

when a table does not contain a repeating group.

third normal form

when a table is in 2NF and no nonkey field is dependent on another nonkey field.

one-to-one relationship

when exactly one of the second entity occurs for each instance of the first entity.

functionally dependent

when one field depends on another.

many-to-many relationship

when one instance of the first entity can relate to many instances of the second entity, and one instance of the second entity can relate to many instances of the first entity.

one-to-many relationship

when one occurrence of the first entity can relate to many instances of the second entity, but each instance of the second entity can associate with only one instance of the first entity.