Database Design and Implementation

A collection of data an organization needs and owns, maintains, and uses jointly.

• Conceptual design models an implementation-independent conceptual schema.
  • Model transactions
    .
  • Create an E-R diagram.
• Logical design produces a logical schema for the selected data model.
  • Design transaction interfaces
    .
  • Normalize the schema
    .
  • Map the model to a logical database structure.
  • Evaluate and refine the schema.
• Physical design converts the logical design to a physical structure for the target DBMS.
  • Denormalize where appropriate
    .
  • Design objects, access methods, indexes, and view capacity.
  • Write transactions
    .
  • Design stored record formats, ordering, and access paths
    .

• Structure
  • The structural properties of the data.
  • Examples include format, length, keys, and relationships.
• Operations
  • Specifications for actions that process data.
  • Examples include insertion, deletion, updating, and retrieval.
• Constraints
  • Logical rules on what data can be stored in the database.
  • Examples include validity, consistency, and integrity rules.

• A relational database represents data in tables called relations.
• A relation consists of
  attributes
  (columns or fields) and
  tuples
  (rows or records).
• The
  degree
  is the number of attributes. The
  cardinality
  is the number of tuples.
• A
  domain
  is the set of atomic values an attribute can take.

• Tuples and attributes are unique and unordered.
• A subset of attributes serves as a key to identify tuples.
• Attributes can contain nulls, except for those that belong to the
  primary key
  .

A

procedural language

that defines how to retrieve the desired information.

1. Relational operators

   Symbol	Operator	Description
   σ	Select	Selects tuples that satisfy a condition.
   π	Project	Selects specific attributes from a set of tuples.
   ⋈	Join	Joins two relations based on common attributes.
   ÷	Division	Divides one relation by another.

2. General set operators

   Symbol	Operator	Description
   ∪	Union	Returns the union of two relations.
   ∩	Intersection	Returns the intersection of two relations.
   -	Difference	Returns the set difference of two relations.
   x	Cartesian product	Combines each tuple in one relation with each tuple in the other.

A

nonprocedural language

that describes the information to retrieve.

1. Logical operators

   Symbol	Operator	Description
   ∧	AND	True when both conditions are true.
   ∨	OR	True when at least one condition is true.
   ¬	NOT	Negates a condition.

2. Quantifiers

   Symbol	Operator	Description
   ∀	For all	The condition holds for every tuple.
   ∃	There exists	The condition holds for at least one tuple.

A process for minimizing redundancy and preserving data integrity.

• Minimize

  redundancy and dependencies

  that cause anomalies.
• During

  logical design

  , divide tables into relations with fewer attributes so they are easier to manage.
• Make the data structure more stable and reduce the need to restructure relations when inserting data.
• Types of anomalies
  • Insertion anomaly occurs when adding desired data requires adding unwanted data.
  • Deletion anomaly occurs when deleting desired data also removes information you wanted to keep.
  • Update anomaly occurs when only some tuples are updated, leaving inconsistent data.

1. First normal form requires every
   domain
   to contain only
   atomic values
   .
2. Second normal form requires non-key attributes to be
   fully functionally dependent
   on the primary key, eliminating
   partial dependencies
   .
3. Third normal form eliminates
   transitive functional dependencies
   of non-key attributes on the primary key.
4. Boyce–Codd normal form (BCNF) requires every determinant to be a
   candidate key
   .
5. Fourth normal form addresses
   multivalued dependencies
   by requiring the determinant of each nontrivial multivalued dependency to be a superkey.
6. Fifth normal form requires every
   join dependency
   to follow from the candidate keys.

System tables maintain definitions and specifications for all data objects.

• The catalog stores metadata, or data about the database.
• You can query it with SQL.
• The system creates and maintains it automatically.

  Ordinary users cannot update it directly

  .
• The data directory contains the location information needed to access the data dictionary.

An ordered data structure built separately to make records quicker to locate and retrieve.