ISYS 620 Chapter 9

Foreign Key

Attribute that appears as a non-primary key in one relation and as a primary key attribute in another relation. Designated by a dashed line.

Physical Table

A named set of rows or columns that specifies the fields in each row of the table. May or may not respond to one relation. Two different goals from those of normalization: 1. Efficient use of secondary storage 2. Data-processing speed

Relation

A named, two-dimensional table of data. Each relation consists of a set of named columns and an arbitrary number of unnamed rows. Each row corresponds to a record that contains data values for an entity.

Hashed File Organization

Address for each row is determined using an algorithm that converts a primary key value into a row address. Rows are located non-sequentially ad dictated by the algorithm. Retrieval of random rows is fast

Transitive Dependency

Functional dependency between non-primary key attributes

Weak Entity

Entity whose primary key depends upon the primary key of another entity

Referential Integrity

Integrity constraint specifying that the value/existence of an attribute in one relation depends on the value/existence of the same attribute in anther relation. Also a way to control data integrity. Cross-referencing among relations. Values of the foreign key must be limited to the set of the primary key.

Physical File

Named set of rows stored in a contiguous section of secondary memory

Functional Dependency

Particular relationship between two attributes. For a given relation, attribute B is functionally dependent on attribute A if, for every valid value of A, that value of A uniquely determines the value of B. Represented A-->B. Does not imply mathematical dependence--that the value of one attribute may be computed from the value of another attribute. One value of B for each value of A.

Default Value

Value a field will assume unless an explicit value is entered for that field. Can reduce data-entry time and data-entry errors.

Homonyms

View Integration Problem. A single attribute name may have more than one meaning or describe more than one characteristic. Need to create new attribute names.

Synonyms

View Integration Problem. Two or more attributes have different names but the same meaning. Describe the same characteristic of an entity. Two options: 1. obtain an agreement from users on a single standardized name and eliminate the other name 2. create a third name to replace the other two

Indexed File Organization

Rows are stored either sequentially or non-sequentially, and an index is created that allows the application software to locate individual rows. Reason for storing the data in many contiguous segments is to allow room for some new data to be inserted in sequence without rearranging all the data. Disadvantage: extra space required to store the indexes and the extra time necessary to access and maintain indexes. Advantages: can build multiple indexes-->software can rapidly find records that have compound conditions

Input Mask

A pattern of codes that restricts the width and possible values for each position of a field. EX: L999, convert all characters to uppercase, indicate how to show negative numbers, suppress showing leading zeros, indicate whether entry of a letter or digit is optional

Null Value

A special field value, distinct from 0/blank, that indicates that the value for the field is missing or otherwise unknown. The ultimate question is whether you want to allow a certain attribute (other than the primary key) to have the option to be this value. You can set the relation to restrict this value after a certain point.

Range Control

Both numeric and alphabetic data may have a limited set of permissible values. EX=lower limit of 0 or only the abbreviations for the month

Recursive Foreign Key

Foreign key in a relation that references the primary key values of that same relation. EX: EMPLOYEE(*Emp_ID*, Name Birthdate, Manager_ID^) Manager_ID is this because it takes its values from the same set of worker identification numbers as Emp_ID.

Normalization

Process of converting complex data structures into simple, stable data structures. Result: every non-primary key attribute depends upon the whole primary key and nothing but the primary key

Denormalization

Process of splitting or combining normalized relations into physical tables based on affinity of use of rows and fields. Goal is to create tables that contain only the data used together in programs. By placing data close together, the number of disk operations needed to retrieve all the data needed in a program is minimized. Can increase the chance of errors and inconsistencies that normalization avoided. Optimizes certain data processing at the expense of others, so if the frequencies of different processing activities change, the benefits may no longer exist. No hard-and-fast rules will help you decide when to do this, but here are three common ones: 1. Two entities with a one-to-one relationship 2. A many-to-many relationship (associative entity) with non-key attributes 3. Reference data

Well-Structured Relation

Relation that contains a minimum amount of redundancy and allows users to insert, modify, and delete the rows without errors or inconsistencies. Each row contains data describing one employee, and any modification to an employee's data is confined to one row.

Third Normal Form (3NF)

Relation that is in second normal form and has no functional dependencies between two (or more) non-primary key attributes.

Sequential File Organization

Rows in the file are stored in sequence according to a primary key value. A program must normally scan the file from the beginning until the desired row is located.EX: alphabetical list of persons in the while pages of a phone directory. Are fast if you want to process rows sequentially--impractical for random row retrieval. Deleting rows can cause wasted space or the need to compress the file. Adding rows requires rewriting the file, at least from the point of insertion. Updating a row may also require rewriting the file. Only one sequence can be maintained without duplicating the rows

Calculated (or Computed or Derived) Field

A field that can be derived from other database fields. If you specify a field in this way, you will then be prompted to enter the formula. Formula can involve other fields from the same record and possibly fields from records in related files. The field will either compute the formula or save the result of the formula.

File Organization

A technique for physically arranging the records of a file. In an ideal world, they would be arranged to achieve these goals: 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

Primary Key

An attribute whose value is unique across all occurrences of a relation. Indicated by an underline. An attribute of one relation that is this for a different attribute is designated by a dashed line.

Data Type

Coding scheme recognized by system software for representing organizational data. Space to store data and speed required to access data are of consequence in the physical file and the database design. Specific file/database management software chosen will dictate my choices. Four objectives need to be balanced: 1. Minimize storage space 2. Represent all possible values of the field 3. Improve data integrity for the field 4. Support all data manipulation desired on the field

Relational Database Model

Data represented as a set of related tables or relations.

Pointer

Field of data that can be used to locate a related field or row of data. Contains the address of the associated data, which has no business meaning. Used in file organization when it is not possible to store related data next to each other. Common and usually hidden from the programmer

Secondary Key

One or a combination of fields for which more than one row may have the same combination of values. Important for supporting many reporting requirements and for providing ad hoc data retrieval.

Binary One-to-One Relationship

Represented in one of three ways: 1. Adding the primary key A as a foreign key of B 2. Adding the primary key B as a foreign key of A 3. Both --However, if we are looking at a Zero-To-One with a One-To-One, you would want to add the primary key of the Zero-To-One as a foreign key on the One-To-One as to not have null values

Second Normal Form (2NF)

Relation for which every non-primary key attribute is functionally dependent on the WHOLE primary key. Any of these can be satisfied to complete this: 1. primary key consists of only one attribute 2. No non-primary key attributes exists in the relation 3. every no-primary key attribute is functionally dependent on the full set of primary key attributes

Binary One-to-Many (1:N) Relationship

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

Field

Smallest unit of named application data recognized by system software

Index

Structure that is used to determine the rows in a file that satisfy some condition. Each entry matches a key value with one or more rows. Can point to a unique row or to potentially more than one row.