Systems Analysis Chapter 9

Recursive foreign key

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

Physical table

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

Physical file

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

Functional dependency

A particular relationship between two attributes. For every valid value of an attribute, that value uniquely determines the value of another attribute.

Well-structured relation

A relation that contains a minimum amount of redundancy. Allows users to insert, modify, and delete the rows without errors or inconsistencies.

Indexed file advantages

Allows for both random and sequential processing.

Referential integrity

An integrity constraint specifying that the value of an attribute in one relation depends on the value of the same attribute in another relation.

Binary and higher-degree M:N relationships

Create another relation and include primary keys of all relations as primary key of new relation.

Dependencies between non keys

Dependencies may be created as a result of view integration. To resolve, the new relation must be normalized.

Demoralization

The process of splitting or combining normalized relations into physical tables based on affinity of use of rows and fields. Optimizes certain data processing activities at the expense of others.

Choosing data types

Minimize storage space. Represent all possible values of the field. Improve data integrity of the field. Support all data manipulation a desired on the field.

Unwary M:N

Model as one relation. Create a separate relation to represent the M:N relationship. The primary key of the new relation is a composite key of two attributes that both take their values from the same primary key. Any attribute associated with the relationship is included as a non key attribute in this new relation.

Third normal form

Non primary key attributes do not depend on each other- no transitive dependencies. A relation is in 3 if is in the second form and there are no functional dependencies between two or more non primary key attributes.

Field

The smallest unit of named application data recognized by system software. Attributes from relations will be represented as fields.

First normal form

Unique rows, no multi valued attributes. All relations are in 1NF

Database design

Develop a logical database model, which describes data using notation that corresponds to a data organization used by a database management system. Prescribe the technical specifications for computer files and databases in which to store the data.

Representing entities

Each regular entity is transformed into a relation. The identifier of the entity type becomes the primary key of the corresponding relation. Primary key must satisfy the following two conditions. The entity type label is translated into a relation name.

Relations have several properties that distinguish them from nonrelational tables:

Entries in cells are simple and columns are from the same set of values. The sequence of columns can be interchanged without changing the meaning of use of the relation. Rows may be interchanged or stored in any sequence.

Arranging table rows

Fast data retrieval, high throughout for processing transactions, efficient use of storage space, protection from failures or data loss, minimizing need for reorganization, accommodating growth, security from unauthorized use.

Indexed file organization

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

Binary 1:N relationship

Represented by adding the primary key attribute of entity on the one side of the relationship as a foreign key in the relation that is on the many side of the relationship. Add the primary key of A as a foreign key of B.

Guidelines for choosing indexes

Specify a unique index for the primary key of each table. Specify an index for foreign keys. Specify an index for non key fields that are referenced in qualification, sorting and grouping commands for the purpose of retrieving data.

Binary 1:N

The procedure for representing relationships depends on both the degree of the relationship-unary, binary, ternary- and the cardinalities of the relationship.

Means of building data security into a file

Coding the data in the file. Requiring data file users to identify themselves by entering user names and passwords. Prohibit users to identify themselves by entering user names and passwords. Prohibiting users from directly manipulating any data in the file by forcing users to work with a copy.

View integration problems

Must understand the meaning of the data and be prepared to resolve any problems that arise in the process.

Physical file and database design

Normalized relations, including volumes estimates. Definitions of each attribute. Descriptions of where and when data are used. Expectations or requirements for response time and data integrity. Descriptions of the technologies used for implementing the files and database.

Techniques for file restoration

Periodically making a backup copy of a file. Storing a copy of each change to a file in a transaction log. Storing a copy of each row before or after it is changed.

Two goals of physical table design are:

Protection from failure of data loss and security from unauthorized used. Achieved by implementing controls on each file. Controls address file backup and security.

Merging relations

Purpose is to remove redundant relations. The last step in logical database design. Prior to physical file and database design.

Normalization

The process of converting complex data structures into simple, stable data structures. Result is that every non primary key attribute depends upon the whole primary key.

Synonyms

Two different names used for the same attribute. When merging, get agreement from users on a single, standard name.

Three common situations where de normalization may be used:

Two entities with a one-to-one relationship. A many-to-many relationship with non key attributes. Reference data.

Four key steps in logical database modeling and design

1. Develop a model for each user interface for the application. 2. Combine data requirements from all interfaces into one database model. 3. Translate E-R data model into data requirements. 4. Compare database design with E-R model and produce final database model.

Data type

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

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. It is common for an attribute to be mathematically related to other data. To calculate value is either stored or computer when it is requested.

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 for each row is determined using an algorithm.

Homonyms

A single attribute name that is used for two or more different attributes. Resolved by creating a new name.

Hash partitioning

A table row is assigned to a partition by an algorithm and then maps the specified attribute value to a partition.

Index

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

File organization

A technique for physical arranging the records of a file.

Default value

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

Foreign key

An attribute that appears as a non primary key attribute in one relation and as a primary key attribute in another relation.

Primary key

An attribute whose value is unique across all occurrences of a relation. This is how rows are ensured to be unique. May involve a single attribute or be composed of multiple attributes.

Key physical database design decisions include:

Choose storage format for each database model from logical database model. Group logical attributes into physical records. Arrange related records in secondary memory-hard disks- so reorder can be stored. Selecting media and structures for storing data to make access more efficient.

Relational database model

Data represented as a set of related tables or relations.

Second normal form

Each non primary key attribute is identified by the whole key- called full functional dependency. To convert, decompose the relation into new relations using attributes called determinants.

Indexed disadvantages

Extra space required to store the indexes. Extra time necessary to access and maintain indexes.

Secondary keys

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

Range partitioning

Partitions are defined by non overlapping ranges of values for a specified attribute.

Class/ subclass

Relationships may be hidden in user views or relations. Resolved by creating a new name.

Relation

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

Null value

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

Composite partitioning

Combines range and hash partitioning by first segregating data by ranges on the designated attribute, and then within each of these partitions.

Physical database design

Converts relations into database tables. Programmers and database analysts code the definitions of the database. Written in Structured Query Language.

Range control

Limits range of values that can be entered into field. Both numeric and alphanumeric data.

Unary 1:N relationship

Modeled as a relation. Primary key of that relation is the same as for the entity type. Foreign key is added to the relation that references the primary key values.

Logical database design

Must account for every data element on a system input or output. Normalized relations are the primary deliverable.