Lesson 14	Building the Database
Objective	Explain how to use SQL to build a Database

Building Database using SQL

Once you have created the schema and populated it with tables, it is time to build the database itself. This involves a use of SQL that is beyond the scope of this course. The following diagram gives you a flavor of the commands involved in creating a table for the course-project database. (The code assumes a schema named StoriesOnCDOrders already exists.)

Creating Schema

To create a schema, you use the CREATE SCHEMA statement. In its simplest form it has the syntax:

CREATE SCHEMA schema_name

as in:

CREATE SCHEMA distributedNetworks

By default, a schema belongs to the user who created it (the user ID under which the schema was created). The owner of the schema is the only user ID that can modify the schema unless the owner grants that ability to other users. To assign a different owner to a schema, you add an AUTHORIZATION clause:

CREATE SCHEMA schema_name AUTHORIZATION
owner_user_ID

For example, to assign the schema of the Antique Opticals to the user ID DBA, someone could use:

CREATE SCHEMA antiqueOpticals AUTHORIZATION dba

When creating a schema, you can also create additional elements at the same time. To do so, you use braces to group the CREATE statements for the other elements, as in:

CREATE SCHEMA schema_name AUTHORIZATION
owner_user_id { 
// other CREATE statements go here 
}

This automatically assigns the elements with the braces to the schema

SQL code — SQL Commands in the diagram above are detalied below

CREATE TABLE CD: Tells the RDBMS to make a table named CD.

CDNo int: Creates a field named CDNo with the data type Integer.

CDTitle varchar (42): Creates a field named CDTitle that can contain up to 42 characters.

DistID int: Creates a field named DistID with the data type Integer.

CDPrice numeric (3, 2): Creates a field named CDPrice that can hold a numeric value with up to three digits to the left of the decimal and two to the right (e.g., 14.95).

CategoryNo int: Creates a field named CategoryNo with the data type Integer.

PRIMARY KEY (CDNo): Defines the CDNo field as the primary-key field for the table.

FOREIGN KEY (DistID): Identifies the DistID field as a foreign-key field.

FOREIGN KEY (CategoryNo): Identifies the CategoryNo field as a foreign-key field.

What is an Identifier in the context of a database object?

An identifier is a name that lets you refer to an object unambiguously within the hierarchy of database objects (whether a schema, database, column, key, index, view, constraint, or anything created with a CREATE statement). An identifier must be unique within its scope, which defines where and when it can be referenced. In general:

Database names must be unique on a specified instance of a database server.
Table and view names must be unique within a given schema (or database).
Column, key, index, and constraint names must be unique within a given table or view.

This scheme lets you duplicate names for objects whose scopes do not overlap. You can give the same name to columns in different tables, for example, or to tables in different databases.

SQL Commands used To Create Table
The next lesson examines the completed data dictionary.