Database Management/Data Definition Language

This lesson introduces data definition language (DDL).

Objectives and Skills

Objectives and skills for this lesson include:

• Understand advanced SQL concepts
• Create data definition language SQL queries using a database engine

Readings

1. Wikipedia: Database engine
2. Wikipedia: Data definition language
3. Wikipedia: Database dump
4. Wikibooks: Structured Query Language/Create Table
5. Wikibooks: Structured Query Language/Views
6. Wikibooks: Structured Query Language/Managing Indexes

Multimedia

DDL

1. YouTube: Constraints - SQL
2. YouTube: What Are SQL Views And How Can You Create Them?
3. YouTube: Advanced SQL Tutorial | Stored Procedures + Use Cases
4. YouTube: SQL Index
5. YouTube: Generating Scripts for Database Objects in SQL Server

DBMS Installation

1. YouTube: Install Microsoft SQL Server and Management Studio
2. YouTube: Want to know how to install SQL Server Express 2022?
3. YouTube: How To Install MySQL (Server and Workbench)
4. YouTube: How To Install SQLite3 On Windows and Mac OS

Activities

1. Select a database management system to use for this lesson. Options include Microsoft SQL Server, MySQL, Oracle Database, PostgreSQL, and SQLite.
2. Select a graphic user interface to connect with the database management system. Tools for specific database systems include Microsoft SQL Server Management Studio, MySQL Workbench, Oracle SQL Developer, Postgre GUI tools, and DB Browser for SQLite. Generic database administration tools that work with multiple database systems include DBeaver.
3. Use a Northwind script to create the Northwind database in your selected DBMS.
4. Complete the Constraints, Views, Stored Procedures, and Indexes activities below.
5. Dump your completed database schema to an SQL script and review the results.

Microsoft SQL Server

1. For Linux and Windows. Download a free edition of Microsoft: SQL Server DBMS.
2. Download and install Microsoft: SQL Server Management Studio (Windows) or Microsoft: Azure Data Studio (Linux).
3. Complete one or more of the following tutorials using SQL Server and SQL Server Management Studio:
   • SQL Server Tutorial
   • Microsoft: Database Engine Tutorials
   • Guru99: MS SQL Server Tutorial
4. Review Microsoft: Generate and Publish Scripts Wizard and practice dumping a database to SQL statements. Use the schema only option to exclude data.

MySQL

1. For Linux, macOS, and Windows. Download and install the free and open MySQL Community Server DBMS.
2. Download and install the free and open MySQL Workbench.
3. Complete one or more of the following tutorials using MySQL and MySQL Workbench:
   • MySQL: Tutorial
   • MySQLTutorial.org
   • Guru99: Introduction to MySQL Workbench
4. Review MySQL: mysqldump and practice dumping a database to SQL statements. Use the -d or --no-data options to exclude data. For example:
   macOS: /usr/local/mysql/bin/mysqldump -d -u root -p Northwind

SQLite

1. For Linux, macOS, and Windows. Download and install the free and open SQLite DBMS.
2. Download and install the free and open DB Browser for SQLite.
3. Complete one or more of the following tutorials using SQLite and DB Browser for SQLite:
   • SQLite Tutorial
   • Data Carpentry: Using DB Browser for SQLite
4. Review SQLite Tutorial: How To Use The SQLite Dump Command and practice dumping a database to SQL statements. Use the .schema command to exclude data. Alternatively, use DB Browser for SQLite and select File / Export to export the database to an SQL file. Practice exporting the schema only.

Constraints

Northwind

1. Review the Northwind SQL script for foreign key constraints in the CREATE TABLE statements. Create an INSERT statement to insert a new product using an invalid supplier and/or category ID to verify that foreign key constraints are being enforced. Create a DELETE statement to delete a supplier with existing products to verify that foreign key constraints are being enforced.
2. Review each of the Northwind database tables and modify the CREATE TABLE statements to enforce the listed column constraints (NOT NULL, > 0, etc.). Create INSERT statements to test column constraints and verify that they are being enforced.
3. Modify the CREATE TABLE statement for the Orders table to include a default value for Order Date. Create an INSERT statement to test that the default value is automatically added to new orders.

Pubs

1. Review the Pubs SQL script for foreign key constraints in the CREATE TABLE statements. Create an INSERT statement to insert a new sales order using an invalid store id and/or title ID to verify that foreign key constraints are being enforced. Create a DELETE statement to delete a store with existing sales orders to verify that foreign key constraints are being enforced.
2. Review each of the Pubs database tables and modify the CREATE TABLE statements to enforce the listed column constraints (NOT NULL, > 0, etc.). Create INSERT statements to test column constraints and verify that they are being enforced.
3. Modify the CREATE TABLE statement for the Sales table to include a default value for order date. Create an INSERT statement to test that the default value is automatically added to new orders.

Views and Stored Procedures

Northwind

1. Create views for each of the Northwind SQL and and Northwind Advanced SQL SELECT queries. Create stored procedures for each of the Northwind Advanced SQL INSERT, UPDATE, and DELETE statements.
   Note: SQLite doesn't support saving stored procedures. Saved SQL statements may be executed using the sqlite3 command line instead.

Pubs

1. Create views for each of the Pubs SQL and and Pubs Advanced SQL SELECT queries. Create stored procedures for each of the Pubs Advanced SQL INSERT, UPDATE, and DELETE statements.
   Note: SQLite doesn't support saving stored procedures. Saved SQL statements may be executed using the sqlite3 command line instead.

Indexes

Northwind

1. Review each of the Northwind database tables and create appropriate indexes for the indicated columns.
2. For the UNIQUE indexes, create an INSERT statement to insert a duplicate product name and verify that the UNIQUE constraints are being enforced.

Pubs

1. Review each of the Pubs database tables and create appropriate indexes for the indicated columns.
2. For the UNIQUE indexes, create an INSERT statement to insert a duplicate publisher name and verify that the UNIQUE constraints are being enforced.

Lesson Summary

• A database engine (or storage engine) is the underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.
• The term "database engine" is frequently used interchangeably with "database server" or "database management system". A "database instance" refers to the processes and memory structures of the running database engine.
• A database, while in operation, resides simultaneously in several types of storage, forming a storage hierarchy. This hierarchy includes the processor, processor cache, RAM, and non-volatile storage systems.
• Internal database storage structures include flat files, hash tables, B+ trees, ISAM, and heaps.
• A view is the result set of a stored query on the data, which the database users can query just as they would in a table.
• A stored procedure is a subroutine available to applications that access a relational database management system (RDBMS).
• Indexing is a technique some storage engines use for improving database performance.
• Data definition language (DDL) is a syntax for creating and modifying database objects such as tables, indices, and users. Common examples of DDL statements include CREATE, ALTER, and DROP.
• The format of the CREATE TABLE statement is:
  CREATE TABLE table_name (column_definitions) table_options
• Column definitions include:
  • column_name data_type {NULL|NOT NULL} {column options}
  • DEFAULT value
  • CONSTRAINT constraint_definition
  • PRIMARY KEY columns
• A check constraint is a type of integrity constraint in SQL which specifies a requirement that must be met by each row in a database table.
• The format of the CHECK constraint statement is:
  CHECK (expression)
• A foreign key is a set of attributes in a table that refers to the primary key of another table. Foreign key constraints prevent inserting, updating, or deleting records that would violate the referenced key value.
• The format of the FOREIGN KEY constraint statement is:
  FOREIGN KEY (columns) REFERENCES table_name (columns) [ON UPDATE option] [ON DELETE option]
• The format of the CREATE VIEW statement is:
  CREATE VIEW view_name AS select_statement
• The format of the CREATE PROCEDURE statement is:
  CREATE PROCEDURE procedure_name sql_statement
• The format of the CREATE INDEX statement is:
  CREATE [UNIQUE] INDEX index_name ON table_name(columns)

Key Terms

constraint

Restricts the domain of an attribute.

Data Definition Language (DDL)

A syntax for creating and modifying database objects such as tables, indices, and users.

database engine

The underlying software component that a database management system (DBMS) uses to create, read, update and delete (CRUD) data from a database.

foreign key

A set of attributes in a table that refers to the primary key of another table.

index

Provides quicker access to data.

stored procedure

Executable code that is associated with, and generally stored in, the database.

view

A stored query.

See Also

• Learning SQL Server/SSMS
• Create a Database- SQL Server
• Wikibooks: MySQL
• W3Schools: SQL Tutorial

References

← Advanced SQL

Database Management

Data Control Language →