Chapter 9 Designing Databases

DATE

Any date from january1, 4712 b.c. to December 31 4712a.d. Data stores the century, year, month,day, hour, minute, and second

Relation

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

Third Normal Form (3NF)

A relation is in third normal form (3NF) if it is in second normal form (2NF) and there are no functional (transitive) dependencies between two (or more) nonprimary key attributes.

Primary-Keys Well-structured Relation (or table)

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

Deliverable and Outcomes Physical database design

Converts relations into database tables Programmers and database analysts code the definitions of the database Written in Structure Query Language(SQL)

Databases

Forms and reports Dialogues and Interfaces Finalizing Design Specifications Distributed and Internet Systems

File and database design occurs in two steps

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

Normalization Second Normal Form (2NF)

Each nonprimary key attribute is identified by the whole key (called full functional dependency)

Relations have several properties that distinguish them from nonrelational tables

Entries in cells are simple Entries in columns are from the same set of values Each row is unique The sequence of columns can be interchanged without changing the meaning or use of the relation The rows may be interchanged or stored in any sequence

View Integration Problems Synonyms

two different names used for the same attribute (part, segment, piece) When merging, get agreement from users on a single, standard name

Transforming E-R Diagrams into Relations

It is useful to transform the conceptual data model into a set of normalized relations. Steps Represent entities. Represent relationships (primary key foreign key or a separate relationship). Normalize the relations. Merge the relations.

Designing Physical Tables Denormalization

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

Choosing Data Types Selecting a data type balances four objectives

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

Deliverable and Outcomes Logical database design

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

View Integration Problems

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

Normalization Third Normal Form (3NF)

Nonprimary key attributes do not depend on each other (i.e. no transitive dependencies)

A relation is in second normal form (2NF) if any of the following conditions apply:

Put simply, a table is in 2NF if and only if, it is in 1NF and every non-primary key attribute of the table is dependent on the whole of a candidate key To convert a relation into 2NF, you decompose the relation into new relations using the attributes, called determinants, that determine other attributes. The determinants are the primary key of the new relation. 2NF would allow transitive functional dependencies

Normalization First Normal From (1NF)

Unique rows, no multivalued attributes All relations are in 1NF

VARCHAR2

Variable-length character datawith a maximum length of 4000 characters;you must enter a max field lenght (e.g. VARCHAR2(30) for a field w/ a min length of 30 characters). A value less than 30 characters will consume only the requires space

Designing Fields Data Type

a coding scheme recognized by system software for representing organizational data

Calculated Fields

a field that can be derived from other database fields It is common for an attribute to be mathematically related to other data. The calculate value is either stored or computed when it is requested.

Designing Physical Tables Physical Table

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

Third Normal Form (3NF) (Cont.) 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

Third Normal Form (3NF) (Cont.) Referential Integrity

an integrity constraint specifying that the value (or existence) of an attribute in one relation depends on the value (or existence) of the same attribute in another relation or in the same relation

Controlling Data Integrity Range Control

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

Designing Physical Tables Denormalization

optimizes certain data processing activities at the expense of others.

Designing Fields Field

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

Functional Dependency

: a particular relationship between two attributes For a given relation, attribute B is functionally dependent on attribute A if, for every valid value of A (Emp_Id), that value of A uniquely determines the value of B (Name). The functional dependence of B on A is represented by A→B. No mathematical calculation dependence, rather it means that there can be only one value of B for each value of A

Referential Integrity

: an integrity constraint specifying that the value (or existence) of an attribute in one relation depends on the value (or existence) of the same attribute in another relation

Primary-Keys Primary Key

An attribute whose value is unique across all occurrences of a relation

BLOB

Binary large object, capable of storing up to four gigabytes of binary data

LONG

Capable of storing up to two gigabytes of one variable-length character data field

Relational Database Model

Data represented as a set of related tables or relations

CHAR

Fixed-length character data with a maximum length of 225 characters; default legnth is 1 character

NUMBER

Positive and negative numbers in the range 10-^130 to 10^126, can specify the precision (total number of digits to the left anf right of the decimal point)

Representing Entities

The primary key must satisfy the following two conditions. The value of the key must uniquely identify every row in the relation. The key should be nonredundant. The entity type label is translated into a relation name.

Normalization

The process of converting complex data structures into simple, stable data structures The result of normalization is that every nonprimary key attribute DEPENDS upon the whole primary key.

Primary-Keys All relations have a primary key

This is how rows are ensured to be unique A primary key may involve a single attribute or be composed of multiple attributes

Designing Physical Tables Relational database

a set of related tables

View Integration Problems (Cont.) Homonyms

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

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 Organizations

a technique for physically arranging the records of a file Physical file: a named set of table rows stored in a contiguous section of secondary memory

Controlling Data Integrity Default Value

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

View Integration Problems Dependencies between nonkeys

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