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.

Learn about the basics of database schema, including records, record composition, relationships, and constraints. Test your understanding of database design and structure.