Database Schema Fundamentals

Database Schema

• A schema is a description of the logical structure of a database.
• It is written in a Data Definition Language (DDL).
• A schema describes the following:

  Records

  • The different kinds of records it will hold.

  Record Composition

  • The data elements that will comprise each record.
  • The data types of each data element (e.g. number, string, etc.).

  Relationships

  • The relationship between records.

  Constraints

  • The constraints between records.

  Performance Optimization

  • Indexes are used to speed up access for typical uses.

Database Query Language

• Used to access and manipulate data
• Enables four main operations:
  • Read (find)
  • Create (insert)
  • Update
  • Delete

Types of Query Language

Procedural Query Language

• Users specify both:
  • Data
  • Procedures

Non-Procedural Query Language

• Users specify only:
  • Data

Database Query Language

• Used to access and manipulate data
• Enables four main operations:
  • Read (find)
  • Create (insert)
  • Update
  • Delete

Types of Query Language

Procedural Query Language

• Users specify both:
  • Data
  • Procedures

Non-Procedural Query Language

• Users specify only:
  • Data

Database Query Language

• Used to access and manipulate data
• Enables four main operations:
  • Read (find)
  • Create (insert)
  • Update
  • Delete

Types of Query Language

Procedural Query Language

• Users specify both:
  • Data
  • Procedures

Non-Procedural Query Language

• Users specify only:
  • Data

Data Management Challenges

• Data in the digital world is highly portable and can be easily moved, but its management is crucial to ensure its integrity and completeness.

Types of Enterprise Data

• Enterprise data can be classified into two main categories: internal and external data.
• Internal data includes generated, purchased, and borrowed data, as well as statutory and operating books and records.
• External data includes public data, social media data, and database contents from transactional systems.
• Other types of enterprise data include legal matters, intellectual property, email, documents in file systems and document libraries.

Data Attributes

• Data attributes include sensitivity, location, duplicates, usage, owner, and accountabilities.
• Data attributes also include destruction requirements, which ensure that data is disposed of securely.

Special Types of Data

• Personally Identifiable Information (PII) requires special handling and protection.
• Intellectual Property (IP) data needs to be protected from unauthorized access and use.
• Financial data, including regulated and confidential data, requires special handling and security measures.
• Trade secret data requires highest level of protection and confidentiality.
• Legal and privileged data, as well as audit and regulatory data, require special handling and access controls.
• Healthcare data is subject to specific regulations and requirements.

The Data Life Cycle (DLC)

• The Data Life Cycle is similar to the Systems Development Life Cycle (SDLC), but with more statutory and regulatory requirements.
• The DLC involves more stakeholders and third parties than the SDLC.
• The DLC has fewer controls regarding change management than the SDLC.
• The DLC does not typically involve staging, promotion, testing, and acceptance phases.

Phases of the Data Life Cycle

• Phase 1: Creation and Receipt of data.
• Phase 2: Distribution of data.
• Phase 3: Use of data.
• Phase 4: Maintenance of data.
• Phase 5: Disposition of data, which includes secure disposal and destruction.