Lab
Lab #2: Create Tables
Learn about records, tables, Data Definition Language (DDL) commands, and database constraints.
Objectives
The lab teaches you how to work SQL commands to create tables. It requires that you have completed Lab 1 because you will need to edit and run both unit and integration test scripts.
You are provided with scripts that create ten tables. All the tables have dependencies on one another. You generally create tables in a specific order. The order starts with the table that has the least number of dependencies. The second table has the next least number of dependencies and the third through the last follows the same pattern until you get to the table with the most dependencies.
You will work with these ten tables in this and subsequent labs. A single script file should create each table. The SQL script files are unit test programs. Each of the unit test programs writes its own log file.
An eleventh SQL script is an integration script, and it calls all the unit tests. You will download eleven SQL script files for this lab from github.com and put them into a new /lib2 directory in your Linux instance.
After you download and position the script files, you will test them by calling the integration script file. Then, you will copy these unit test programs into the lab2 directory where you will edit each of the unit test scripts to create ten nearly duplicate tables. The last step requires you to copy the base integration program into the lab2 directory where you will edit it to run your modified copies in the lab2 directory.
The lab teaches you how to edit and re-inforces Lab 1 where you learned how to run unit and integration test scripts in SQL*Plus (sqlplus). Working with these scripts should help you learn the following:
- How to edit a unit test script
- How to set a local bind variable
- How to conditionally drop a table
- How to create a table with database constraints
- How to create a sequence for a table’s surrogate primary key
- How to create an index for a table
- How to change the definition of an existing table
- How to query the data dictionary to discover table definitions
Business Scenario
Building data-centric application software requires a place to put the data. You need to create tables to hold the data whether you choose to use a relational, document, or columnar database.
The lab is designed to teach you to create tables in a relational database. Tables should hold a single subject. Well designed tables in domain modeling should include two unique keys, where a unique key is a one or more columns that uniquely define each row in the table. One unique key is a natural key made up of columns that define each instance of data stored in a table. The other unique key is a surrogate key, which is a unique ID. The unique ID is typically populated by a number generated by a sequence that increments by one. The surrogate key acts like an indirect reference to the natural key of the table.
Domain modeling adds the overhead of a surrogate (second unique) key because that key lets you evolve the natural key when you discover that table doesn’t uniquely describe a subject. The natural key provides a set of meaningful information that would let a business user find data about a customer, order, or purchase.
For example, you may want to query a customer by their membership number, credit card number and type, or full name. The following form would let you enter the data to search for the customer.
A form like the preceding forms the basis for a query that uses natural key components from more than one table to identify a correct customer record. The customer service team member may query a record like this and then change the customer’s credit card number or type of card fter querying the results. In today’s world, it’s much more likely the customer would query their own data and change their own credit card information.
The customer would never use the surrogate key value because it’s designed to organize table relationships and only meaningful toward resolving queries with natural key data values.
Table design and creation also requires you to identify whether columns are mandatory or optional. Mandatory tables are constrained by a NOT NULL constraint. Optional tables are not constrained, which lets you write a row without a middle name because not everyone has a middle name. Likewise, tables require UNIQUE constraints for the set of columns that defines a table’s natural key; and PRIMARY KEY constraints for surrogate key columns so that you can create FOREIGN KEY constraints to map relationships between rows of data.
