ISTM 320 Chapter 9

5. Deleting rows

*Sequential* - Can create wasted space or require reorganizing *Indexed* - if space can be dynamically allocated, this is easy, but requires maintenance of indexes *Hashed* - very easy

3. Random retrieval on primary key

*Sequential* - Impractical *Indexed* - Moderately fast *Hashed* - Very fast

4. Multiple Key Retrieval

*Sequential* - Possible, but requires scanning whole file *Indexed* - Very fast with multiple indexes *Hashed* - Not possible

1. Storage Space

*Sequential* - no wasted space *Indexed* - no wasted space for data, but extra space for index *Hashed* - extra space may be needed to allow for addition and deletion of records

6. Adding rows

*Sequential* - requites rewriting file *Indexed* - if space can be dynamically allocated, this is easy, but requires maintenance of indexes *Hashed* - very easy, except multiple keys with same address require extra work

2. Sequential retrieval on primary key

*Sequential* - very fast *Indexed* - moderately fast *Hashed* - impractical

Merging Relations

*Some relations may be redundant / refer to same entities -- if so, you should merge those relations to remove the redundancy*

These inputs allow you to make key physical database design decisions, like the following

- choosing the storage format (*data type*) for each attribute. involves choosing length, coding scheme, number of decimal places, minimum and maximum values, and potentially other paramters - grouping attributes from logical database model into physical records (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 (*file organization*). also consider protecting data and recovering data after errors are found - selecting media and structures for storing data to make access more efficient

Information system designer can build data security into a file by

- coding or encrypting the data in the file so that they cannot be read unless the reader knows how to decrypt the stored values - requiring data file users to identify themselves by entering user names and passwords, and then possibly only allowing certain file activities (read, add, delete, change) for selected users to selected data in the file - prohibiting users from directly manipulating any data in the file, but rather force programs and users to work with a copy (real or virtual) of the data they need

5. DATE

Any date from January 1, 4712 BC to December 31, 4712 AD; date stores the century, year, month, day, hour, minute, and second

6. BLOB

Binary large object, capable of storing up to four gigabytes of binary data (e.g. a photograph or sound clip)

3. LONG

Capable of storing up to two gigabytes of one variable-length character data field (e.g. to hold a medical instruction or a customer comment)

3. Represent Relationships - Unary Relationships

also known as a *recursive relation* use a *recursive foreign key* - foreign key in a relation that references the primary key values of that same relation

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

1. Represent Entities

check if primary key satisfies the following two properties: 1. value of the key must uniquely identify every row in the relation 2. the key should be nonredundant; no other attribute in the key can be deleted without destroying its unique identification

4. Class/subclass

class/subclass relationships may be hidden in user views or relations

denormalization

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

field

the smallest unit of named application data recognized by system software

determinants

to convert a relation to second normal form, you can decompose the relation into new relations using attributes (called determinants) that determine the other attributes

1. Synonyms

two different names that are used for the same attribute

Decisions in translating logical database design into physical database design

- create one or more fields for each attribute and determine a data type for each field - for each field, decide if it is calculated; needs to be coded or compressed; must have a default value or picture; or must have range, referential integrity, or null value controls - for each relation, decide if it should be denormalized to achieve desired processing efficiencies - choose a file organization for each physical file - select suitable controls for each file and the database

Designing controls for files -- a file and database designer has several techniques:

- periodically making a backup of a file - storing a copy of each change to a file in a transaction log or audit trail - storing a copy of each row before or after it is changed

Oracle - 3 types of partitioning

1. *Range partitioning* - partitions are defined by nonoverlapping ranges for a specified attribute (so separate tables are formed of the rows whose specified attribute values fall in indicated ranges) 2. *Hash partitioning* - a table row is assigned to a partition by an algorithm and then maps the specified attribute to a partition 3. *Composite partitioning* - combine range and hash partitioning by first segregating data by ranges on the designated attribute and then within each of tehse partitions, it further partitions by hashing on the designated attribute

Steps to transform E-R diagram into normalized relations and then merge all relations into one final, consolidated set of entities

1. *Represent entities* - each entity in ERD becomes a relation 2. *Represent relationships* - Each relationship in ERD must be represented in relational database design 3. *Normalize the relations* - The relations in steps 1 and 2 may have unnecessary redundancy, so normalize them 4. *Merge the relations* - There may be redundant sets of relations (two relations that describe the same entity type) so merge and remove redundancy)

Common situations in which denormalization across tables often makes accessing related data faster

1. *Two entities with a one-to-one relationship* 2. *A many-to-many relationship (associative entity) with nonkey attributes* 3. *Reference Data*

4 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 (*view integration*) 3. Translate the conceptual ERD model for the application or enterprise, developed without explicit consideration of specific UI, into normalized data requirements 4. Compare consolidated logical database design with the translated ER model and produce, through view integration, one final logical database model for the application

File and database design steps

1. Develop logical database model 2. Develop physical database design

Criteria for choosing a file organization

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. Accomodation of growth 7. Security from unauthorized user

Objectives for selecting a data type

1. Minimize storage space 2. Represent all possible values of the field 3. Improve data integrity for the field 4. Support all data manipulations desired on the field

E-R Diagrams to Relational Transformation -- E-R Structures

1. Regular Entity 2. Weak entity 3. Binary or Unary 1:1 relationship 4. Binary 1:N relationship 5. Binary or unary M:N relationship or associative entity 6. Binary or unary M:N relationship or associative entity with additional key(s) 7. Binary or unary M:N relationship or associative entity with its own key 8. Supertype/subtype

Guidelines for choosing indexes for relational databases

1. Specify unique index for the primary key of each table (file). this selection ensures the uniqueness of primary key values and speeds retrieval based on those values 2. Specify an index for foreign keys 3. Specify an index for nonkey fields that are referenced in qualification and sorting commands for the purpose of retrieving data

Comparative Features of Sequential, Indexed, and Hashed File Organizations

1. Storage Space 2. Sequential Retrieval on primary key 3. Random retrieval on primary key 4. Multiple key retrieval 5. Deleting rows 6. Adding rows 7. Updating rows

Purposes of database design

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 3. Develop logical database design from which we can do physical 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 disc) that will effectively, accurately, and securely process database activities

Commonly used data types in Oracle 10g

1. VARCHAR2 2. CHAR 3. LONG 4. NUMBER 5. DATE 6. BLOB

Controlling Data Integrity

1. default value 2. range control 3. referential integrity 4. null value control

Properties that distinguish relations 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 non-empty 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

Second normal form satisfied when

1. primary key consists of only one attribute 2. no nonprimary key attributes exist in the relation 3. every nonprimary key attribute is functionally dependent on the full set of primary key attributes

8. Supertype / subtype

Create a relation for the superclass which contains the primary relationship key and all nonkey attributes in common with all subclasses + create a separate relation for each subclass with the same primary key (with the same or local name) but owith only the nonkey attributes related to that sublcass

6. Binary or unary M:N relationship or associative entity with additional key(s)

Create a relation with a composite primary key using the primary keys of the related entities and additional primary key attributes associated with the relationship or associative entity, plus any nonkey attributes of the relationship or associative entity

5. Binary or unary M:N relationship or associative entity

Create a relation with a composite primary key using the primary keys of the related entities, plus any nonkey attributes associative entity of the relationship or associative entity

7. Binary or unary M:N relationship or associative entity with its own key

Create a relation with the primary key associated with the relationship or associative entity plus any nonkey attributes of the relationship or associative entity and the primary keys of the related entities (as foreign key attributes)

2. CHAR

Fixed-length character data with a maximum length of 255 characters; default length is 1 character (e.g. CHAR(5) for a field with a fixed length of five characters, capable of holding a value from 0 to 5 characters long)

2. Represent Relationships - Binary 1:N and 1:1

Relationship can be represented by any of the following: 1. Adding the primary key of A as a foreign key to B 2. Adding the primary key of B as a foreign key to A 3. Both of the above

Physical file and database design

Requires certain information that should have been collected and produced during the SDLC phases - normalized relations, including volume estimates - definitions of each attribute - description of where and when data are used: entered, retrieved, delete, and updated (including frequencies) - expectations or requirements for resposne 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

2. Represent Relationships - Binary and Higher-Degree M:N Relationships

The primary key of this relation is a composite key consisting of the primary key for each of the two entities in the relationship. Any nonkey attributes associated with the M:N relationship are included with relation C. If *three or more entities*: create a separate relation that has a primary key for the composite of the primary keys of each of the participating entities

Data type

a coding scheme recognized by system software for representing organizational data

Functional dependency

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

Pointer

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

Calculated field / Computed Field / Derived Field

a field that can be derived from other database fields

Indexed file organization p 338

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

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

relation

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

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

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 (have no value)

tablespace

a separate contiguous section of the disk space where each partition is stored (Oracle)

2. Homonyms

a single attribute that is used for two or more different attributes

4. Null value control

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

Index

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

File organization

a technique for physically arranging the records of a file

1. Default value

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

normalization

a way to build a data model that has the properties of simplicity, non redundancy, and minimal maintenance

Physical design stage

consider results of these four key logical database design steps also consider: - definitions of each attribute - descriptions of where/when data are entered, retrieved, deleted, and updated - expectations for response time and data integrity - descriptions of the file and database technologies to be used

2. Weak entity

create a relation with a composite primary key (which includes the primary key of the entity on which this weak entity depends) and nonkey attributes

1. Regular Entity

create a relation with a primary key and nonkey attributes

3. Referential integrity

cross-referencing between relations

relational database model

data represented as a set of related tables or relations

Second normal form (2NF)

each nonprimary key attribute is identified by the whole key (*functional dependency*)

Third Normal Form

if it is in 2NF and there are no functional dependencies between two or more nonprimary key attributes

Rules of normalization

in addition to the 5 listed above, must also be 1. Second Normal Form (2NF) 2. Third Normal Form (3NF)

2. Range control

limited set of permissible values for both numeric and alphabetic data

Third normal form (3NF)

nonprimary key attributes do not depend on each other (*no transitive dependencies*)

Secondary key

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

Coding and Compression Techniques

page 333

3. Binary or Unary 1:1 Relationship

place the primary key of either entity in the relation for the other entity or do this for both entities

4. Binary 1:N Relationship

place the primary key of the entity on the one side of the relationship as a foreign key in the relation for the entity on the many side

4. NUMBER

positive and negative numbers in the range 10^-130 to 10^126; can specify the *precision* (total number of digits to the left and right of the decimal point) and the *scale* (the number of digits to the left and right of the decimal point) NUMBER(5) - specifies integer field with max 5 digits NUMBER(5, 2) - specifies a field with no more than 5 digits and exactly 2 digits to the right of the decimal point

View integration problems

problems that arise when integrating relations 1. Synonyms 2. Homonyms 3. Dependencies between nonkeys 4. Class/subclass relations

Partitioning

the capability to split a table into separate sections is possible with most relational database products

normalization

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

1. VARCHAR2

variable-length character data with maximum length of 4,000 characters; you must enter a maximum field length (e.g. VARCHAR2(30)) for a field with a maximum length of 30 characters). A value of less than 30 characters will only consume required space

3. Dependencies between nonkeys

when two 3NF relations are merged to form a single relation, dependencies between nonkeys may result