Ch 9 Systems Design

Entity

A person

Table (or File)

A set of related records that store data about a specific entity

Field (or Attribute)

A single characteristic or fact about an entity

Record (or Tuple)

A set of related fields that describes one instance

Primary Key

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

Foreign Key

A field in one table that must match a primary key value in another table for a relationship to exist

Candidate Key

Any field that has the potential to serve as a primary key

Referential Integrity

A set of rules that avoids data inconsistency and quality problems by ensuring foreign keys match valid primary keys

Entity-Relationship Diagram (ERD)

A graphic representation of all system entities and the relationships among them

Cardinality

A description of the numeric relationship between two entities

Normalization

A process for avoiding problems in data design by assigning specific fields to each table

First Normal Form (1NF)

A table design that contains no repeating groups

Second Normal Form (2NF)

A design in 1NF where all non-key fields are functionally dependent on the entire primary key

Third Normal Form (3NF)

A design in 2NF where no non-key field is dependent on another non-key field

Data Warehouse

An integrated collection of data that can include seemingly unrelated information from across the entire company

Data Mining

The process of looking for meaningful data patterns and relationships among large sets of data

Logical Storage

Data that a user can view

Physical Storage

Hardware-related storage involving the process of reading and writing binary data to media

Mnemonic Codes

Codes that use a specific combination of letters that are easy to remember

Middleware

Software used to integrate different applications and allow them to exchange data

database management system

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

advantages of a DBMS

Scalability, economy of scale

economy of scale

Database design allows better utilization of hardware

database administrator DBA

Assess’s overall requirements and maintains a database

database manipulation language DML

Controls database operations (SQL)

schema

descriptions of all fields, tables, and relationships

subschema

portions of the database that a particular system or user needs or is allowed to access

physical data repository

contains the schema and subschemas, can be centralized or distributed, used ODBC

Open database connectivity ODBC

allows communication between systems and DBMSs

databases are created and managed by using languages and commands that have nothing to do with?

HTML

Data security

Web-based data must be secure, yet easily accessible to authorized users

common field

Attribute that appears in more than one entity

one-to-one relationship

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

One to many relationship

exist when one occurrence at the first entity can relate to many instances of the second entity

crow’s foot notation

indicates various possibilities using circles, bars, and symbols

table design

specifies fields

stages in a normalization process

Unnormalized design, first normal form, second normal form, third normal form

Standard notation format

Used to show a table structure, fields, and primary key

repeating group

Set of one or more fields that can occur any number of times in a single record

codes

are shorter than the data they represent to save storage space and costs and decrease data entry time in transmission time, can also reduce data input error errors and it’s easier to remember

sequence codes

Numbers or letters assigned in a specific order

Block sequence codes

use blocks of numbers for different classifications

significant digit codes

distinguished items by using a series of subgroups of digits

alphabetic codes

use letters to distinguish one item from another

types of alphabetic codes

Category codes, abbreviation code codes, mnemonic codes

derivation codes

combine data from different item attributes, or characteristics

cipher codes

Use a keyword to encode a number

action codes

Indicate what action is to be taken with an associated item

Data mart

designed to serve the needs of a department

EBCDIC

used on main frame computers and high capacity servers

ASCII

used on most personal computers

binary storage format

represents numbers as actual binary values

unicode

uses two bytes per character

ISO

Requires YYYYMMDD format

absolute date

Total number of days from some specific base date

binary digit

Data that only has two possibility values: 1 and 0

clickstream storage

Records how users interact with a website

Key fields

used during system designed to organize, access, and maintain data structures

permissions

Associated with different users as means of establishing their levels of access

query language

Allow allows a user to specify a task without specifying how the task is going to be accomplished

third normal form

Avoid the redundancy and data integrity problems that can exist in previous stage stages of the normalization process

byte

8 bits

scalability

ability for a system to easily be expanded, modified, or downsized