Systems analysis design chapter 9

Comparison of file organizations

(a) Sequential (b) Indexed (c) Hashed

four steps of consolidated

1. Represent entities: Each entity type in the E-R diagram becomes a relation. 2. Represent relationships: Each relationship in an E-R diagram must be represented in the relational database design. 3. Normalize the relations: The relations created in steps 1 and 2 may have unnecessary redundancy. 4. Merge the relations: So far in database design we have created various relations from both a bottom-up normalization of user views and from transforming one or more E-R diagrams into sets of relations.

Functional dependency:

A constraint between two attributes in which the value of one attribute is determined by the value of another attribute.

Possible denormalization situations:

(a) Two entities with a one-to-one relationship (b) A many-to-many relationship with nonkey attributes (c) Reference data

Field:

The smallest unit of named application data recognized by system software.

Relations have several properties that distinguish them from nonrelational tables:

1. Entries in cells are simple. An entry at the intersection of each row and column has a single value. 2. Entries in a given column are from the same set of values. 3. Each row is unique. Uniqueness is guaranteed because the relation has a nonempty primary key value. 4. The sequence of columns can be interchanged without changing the meaning or use of the relation. 5. The rows may be interchanged or stored in any sequences.

File organization requirements

1. Fast data retrieval 2. High throughput for processing transactions 3. Efficient use of storage space 4. Protection from failures or data loss 5. Minimal need for reorganization 6. Accommodation of growth 7. Security from unauthorized use

Synonym

Two different names that are used for the same attribute.

Information to be collected during and prior to SDLC:

• Normalized relations, including volume estimates • Definitions of each attribute • Descriptions of where and when data are used: entered, retrieved, deleted, and updated (including frequencies) • Expectations or requirements for response time and data integrity • Descriptions of the technologies used for implementing the files and database so that the range of required decisions and choices for each is known

1. Second normal form (2NF)

Each nonprimary key attribute is identified by the whole key (what we call full functional dependency).

our key steps in logical database modeling and design:

1. Develop a logical data model for each known user interface (form and report) for the application using normalization principles. 2. Combine normalized data requirements from all user interfaces into one consolidated logical database model; this step is called view integration. 3. Translate the conceptual E-R data model for the application or enterprise, developed without explicit consideration of specific user interfaces, into normalized data requirements. 4. Compare the consolidated logical database design with the translated E-R model and produce, through view integration, one final logical database model for the application.

Relation:

A named, two-dimensional table of data. Each relation consists of a set of named columns and an arbitrary number of unnamed rows.

Third normal form (3NF):

A relation is in second normal form and has no functional (transitive) dependencies between two (or more) nonprimary key attributes.

Second normal form (2NF):

A relation is in second normal form if every nonprimary key attribute is functionally dependent on the whole primary key.

Database design has five purposes:

1. Structure the data in stable structures, called normalized tables, that are not likely to change over time and that have minimal redundancy. 2. Develop a logical database design that reflects the actual data requirements that exist in the forms (hard copy and computer displays) and reports of an information system. This is why database design is often done in parallel with the design of the human interface of an information system. 3. Develop a logical database design from which we can do physical database design. Because most information systems today use relational database management systems, logical database design usually uses a relational database model, which represents data in simple tables with common columns to link related tables. 4. Translate a relational database model into a technical file and database design that balances several performance factors. 5. Choose data storage technologies (such as Read/Write DVD or optical disk) that will efficiently, accurately, and securely process database activities.

primary key satisfies the following two properties

1. The value of the key must uniquely identify every row in the relation. 2. The key should be nonredundant; that is, no attribute in the key can be deleted without destroying its unique identification.

Well-structured relation

: A relation that contains a minimum amount of redundancy and that allows users to insert, modify, and delete the rows without error or inconsistencies; also known as a table.

Physical file

A named set of table rows stored in a contiguous section of secondary memory.

Referential integrity:

A rule that states that either each foreign key value must match a primary key value in another relation or the foreign key value must be null (i.e., have no value).

Null value:

A special field value, distinct from zero, blank, or any other value, that indicates that the value for the field is missing or otherwise unknown.

File organization

A technique for physically arranging the records of a file.

Default value

A value a field will assume unless an explicit value is entered for that field.

Data type

A coding scheme recognized by system software for representing organizational data.

Homonym

A single attribute name that is used for two or more different attributes.

Relational database model

Data represented as a set of related tables or relations.

Pointer

A field of data that can be used to locate a related field or row of data.

Calculated field:

A field that can be derived from other database fields. Also known as a computed field or a derived field.

Indexed file organization:

A file organization in which rows are stored either sequentially or nonsequentially, and an index is created that allows software to locate individual rows.

Sequential file organization

A file organization in which rows in a file are stored in sequence according to a primary key value.

Hashed file organization

A file organization in which the address of each row is determined using an algorithm.

Recursive foreign key

A foreign key in a relation that references the primary key values of that same relation.

Physical table:

A named set of rows and columns that specifies the fields in each row of the table

Index:

A table used to determine the location of rows in a file that satisfy some condition.

Primary key

An attribute (or combination of attributes) whose value is unique across all occurrences of a relation.

Foreign key:

An attribute that appears as a nonprimary key attribute in one relation and as a primary key attribute (or part of a primary key) in another relation.

2. Third normal form (3NF)

Nonprimary key attributes do not depend on each other (what we call no transitive dependencies)

Secondary key

One or a combination of fields for which more than one row may have the same combination of values.

Normalization

The process of converting complex data structures into simple, stable data structures

Denormalization

The process of splitting or combining normalized relations into physical tables based on affinity of use of rows and fields.

two other frequently used rules:

1. Second normal form (2NF). Each nonprimary key attribute is identified by the whole key (what we call full functional dependency). 2. Third normal form (3NF). Nonprimary key attributes do not depend on each other (what we call no transitive dependencies)

key physical database design decisions:

• Choosing the storage format (called data type) for each attribute from the logical database model; the format is chosen to minimize storage space and to maximize data quality. Data type involves choosing length, coding scheme, number of decimal places, minimum and maximum values, and potentially many other parameters for each attribute. • Grouping attributes from the logical database model into physical records (in general, this is called selecting a stored record, or data, structure). • Arranging related records in secondary memory (hard disks and magnetic tapes) so that individual records and groups of records can be stored, retrieved, and updated rapidly (called file organization). You should also consider protecting data and recovering data after errors are found. • Selecting media and structures for storing data to make access more efficient. The choice of media affects the utility of different file organizations. The primary structure used today to make access to data more rapid is key indexes on unique and nonunique keys.