Database Concepts: Data Dictionary, Modeling, DDL, and DML

Data Dictionary

  • A data dictionary is a data structure that stores metadata (definitions) about data.
  • For a database, it defines the structure: list of files, number of records in each file, field names and types, constraints, and relationships between data elements.
  • Crucial for DBMS to access data; often hidden to prevent accidental destruction.

The Importance of Data Modeling

  • Data model serves as the "blueprint" for the physical database.
  • Ensures all necessary data objects are present.
  • Ensures accurate definition of data objects, including all attributes.
  • Ensures accessibility of data objects within and across database tables (relations).

Data Modeling Design Considerations

  • Identify the purpose of data modeling: determine required data and their purpose.
  • Identify entities/tables.
  • Choose table attributes that are necessary and sufficient for the purpose.
  • Identify keys for accessing data in the tables.
  • Identify relationships among tables (type and connected fields).
  • Normalize the database to remove redundancy.

Consequences of Poor Data Modeling

  • Incomplete or inaccurate representation/missing data objects.
  • Incorrect, incomplete, or inconsistent results.
  • Inability to perform complex queries across multiple tables.
  • Difficulty or impossibility to make changes.
  • Creation/presence of redundant data.
  • Assignment of wrong data type to a field, affecting queries/searches.

Data Definition Language (DDL)

  • DDL is a syntax (similar to a programming language) for defining data structures, especially database schemas.
  • DDL statements build and modify the structure of tables and constraints in the database.

CREATE Statements (DDL)

  • CREATE statement makes a new database, table, index, or stored procedure.
  • Example:
    sql CREATE TABLE employees ( id INTEGER PRIMARY KEY, first_name VARCHAR(50) NOT NULL, last_name VARCHAR(75) NOT NULL, fname VARCHAR(50) NOT NULL, dateofbirth DATE NULL );

ALTER Statements (DDL)

  • ALTER statement modifies an existing database object.
  • Examples to add or remove a column:
    sql ALTER TABLE employees ADD age INTEGER; ALTER TABLE employees DROP COLUMN age;
  • ALTER TABLE statement may specify primary and foreign key constraints (can also be specified in CREATE TABLE).

DROP Statements (DDL)

  • DROP statement destroys an existing database, table, index, or view.
  • Removes an object from an RDBMS; object types depend on the RDBMS.
  • Example:
    sql DROP TABLE employees;
  • DROP destroys the database object.

Data Manipulation Language (DML)

  • DML is a family of syntax elements (similar to a programming language) for selecting, inserting, deleting, and updating data in a database.
  • SELECT statement is considered part of DML.
  • DML statements work with data in tables, modifying stored data but not the schema or database objects.

SELECT Statement (DML)

  • SELECT statement looks at the data in tables.
  • The result is a new table (view) that can be viewed or used with programming languages.
  • The resultant table isn't stored but can be part of other select statements.

SELECT Syntax

  • Basic syntax (not case sensitive):
    sql SELECT <attribute names> FROM <table names> WHERE <condition to pick rows> ORDER BY <attribute names>;
  • Only SELECT and FROM clauses are required.

How Queries Provide a View of a Database

  • When a query runs, it matches each record from a table with each record from other tables (the whole data space).
  • Restrictions (using WHERE) display only combinations that satisfy each condition.
  • Table connections display only combinations where connection conditions are fulfilled (through primary key-foreign key pairs).

Simple vs. Complex Queries

  • Simple queries gather data from a single table; aggregate functions (MAX(), COUNT(), etc.) cannot be used.
  • Simple queries do not contain subqueries.

INSERT Statement (DML)

  • INSERT statement adds new rows to a table.
  • Example:
    sql INSERT INTO employees(first_name, last_name, fname) VALUES ('John', 'Capita', 'xcapit00');
  • Comma-delimited list of values must match the table structure exactly (number of attributes and data types).
  • A separate INSERT statement is required for every row.

UPDATE Statement (DML)

  • UPDATE statement changes values already in a table.
  • Syntax:
    sql UPDATE <table name> SET <attribute> = <expression> WHERE <condition>;
  • <expression> can be a constant, computed value, or result of a SELECT statement that returns a single field.
  • Omitting the WHERE clause sets the specified attribute to the same value in every row of the table.

DELETE Statement (DML)

  • DELETE statement deletes rows from a table.
  • Syntax:
    sql DELETE FROM <table name> WHERE <condition>;
  • Omitting the WHERE clause deletes every row of the table.

Other Statements

  • COMMIT makes DML changes visible to other users in a multi-user system; may be automatic upon logout.
  • ROLLBACK restores a private copy of the database to its state before changes (only works if COMMIT hasn't been used).

Other Statements (Grants)

  • GRANT statement allows others to view or manipulate data by granting privileges (select, insert, update, delete) for each table.
  • Commonly used for tables accessed by scripts on a web server.
  • Example:
    sql GRANT select, insert ON customers TO webuser;