Creating the Store Schema | Oracle Database 10g SQL (Osborne ORACLE Press Series)

Most of the examples in this book will use an example database schema that will be used to hold information about the customers, inventory, and sales of a simple store. This example store sells items such as books, videos , DVDs, and CDs. This schema will be named store, the definition of which is contained in the SQL*Plus script store_schema.sql , which is contained in the Zip file you can download from this book s web site. The store_schema.sql script contains the DDL and DML statements to create the store schema. Once you ve obtained the script, you may run it using SQL*Plus or have your DBA run it for you. You ll now learn how to run the store_schema.sql script.

Running the SQL*Plus Script to Create the Store Schema

Perform the following steps:

Open the store_schema.sql script using an editor and change the password for the system user if necessary. The system user has privileges to create new users and tables, among other items, and has a default password of manager . If that is not the correct password for the system user, ask your DBA for the correct password (or just have your DBA run the script for you).
Start the SQL*Plus tool.
Run the store_schema.sql script from within SQL*Plus using the @ command.

The @ command has the following syntax:

  directory_path  \store_schema.sql

where directory_path is the directory and path where your store_schema.sql script is stored.

For example, if the script is stored in a directory named SQL on the C partition of your Windows file system, then you would enter

 @C:\SQL\store_schema.sql

If you re using Unix (or Linux), and you saved the script in a directory named SQL on your tmp file system, for example, you would enter

 @/tmp/SQL/store_schema.sql

Note	Windows uses backslash characters (\) in directory paths, whereas Unix and Linux use forward slash characters (/).

When the store_schema.sql script has finished running, you ll be connected as the store user. If you want to, open the store_schema.sql script using a text editor like Windows Notepad and examine the statements contained in it. Don t worry too much about the details of the statements contained in this file ”you ll learn the details as you progress through this book.

Note

To end SQL*Plus, you enter EXIT . To reconnect to the store schema in SQL*Plus, you enter store as the user name with a password of store_password . While you re connected to the database, SQL*Plus maintains a database session for you. When you disconnect from the database, your session is ended. You can disconnect from the database and keep SQL*Plus running by entering DISCONNECT . You can then reconnect to a database by entering CONNECT .

Data Definition Language (DDL) Statements Used to Create the Store Schema

As mentioned earlier, Data Definition Language (DDL) statements are used to create users and tables, plus many other types of structures in the database. In this section, you ll learn how to use DDL statements to create the database user and tables for the store schema .

Note	The SQL statements you ll see in the rest of this chapter are the same as those contained in the store_schema.sql script. You don t have to type the statements in yourself, just run the store_schema.sql script as described earlier.

The following sections describe how to create a database user, followed by the commonly used data types used in the Oracle database, and finally the various tables used for the hypothetical store.

Creating a Database User

To create a user in the database, you use the CREATE USER statement. The simplified syntax for the CREATE USER statement is as follows :

 CREATE USER  user_name  IDENTIFIED BY  password;

where

user_name specifies the name you assign to your database user
password specifies the password for your database user

For example, the following CREATE USER statement creates the store user with a password of store_password :

 CREATE USER store IDENTIFIED BY store_password;

Next , if you want the user to be able to work in the database, the user must be granted the necessary permissions to do that work. In the case of store , the user must be able to log on to the database (which requires the connect permission) and create items like database tables (which requires the resource permission). Permissions are granted by a privileged user (the DBA, for example) using the GRANT statement.

The following example grants the connect and resource permissions to store :

 GRANT connect, resource TO store;

Once a user has been created, the database tables and other database objects can be created in the associated schema for that user. For most of the examples in this book, I ve chosen to implement a simple store; these tables will be created in the schema of store . Before I get into the details of the tables required for the store, you need to understand a little bit about the commonly used Oracle database types that are used to define the database columns .

Understanding the Common Oracle Database Types

There are many types that may be used to handle data in an Oracle database. Some of the commonly used types are shown in Table 1-1.

Table 1-1: Commonly Used Oracle Data Types
Oracle Type	Meaning
CHAR( length )	Stores strings of a fixed length. The length parameter specifies the length of the string. If a string of a smaller length is stored, it is padded with spaces at the end. For example, CHAR(2) may be used to store a fixed length string of two characters; if C is stored using this definition, then a single space is added at the end. CA would be stored as is with no padding.
VARCHAR2( length )	Stores strings of a variable length. The length parameter specifies the maximum length of the string. For example, VARCHAR2(20) may be used to store a string of up to 20 characters in length. No padding is used at the end of a smaller string.
DATE	Stores dates and times. The DATE type stores the century, all four digits of a year, the month, the day, the hour (in 24- hour format), the minute, and the second. The DATE type may be used to store dates and times between January 1, 4712 B.C. and December 31, 4712 A.D.
INTEGER	Stores integer numbers . An integer number doesn't contain a floating point: it is a whole number, such as 1, 10, and 115, for example.
NUMBER( precision , scale )	Stores floating point numbers, but may also be used to store integer numbers. precision is the maximum number of digits (in front of and behind a decimal point, if used) that may be used for the number. The maximum precision supported by the Oracle database is 38. scale is the maximum number of digits to the right of a decimal point (if used). If neither precision nor scale is specified, any number may be stored up to a precision of 38 digits. Numbers that exceed the precision are rejected by the database.
BINARY_FLOAT	New for Oracle10 g . Stores a single precision 32-bit floating point number. You'll learn more about BINARY_FLOAT later in the section The New Oracle10 g BINARY_FLOAT and BINARY_DOUBLE Types.
BINARY_DOUBLE	New for Oracle10 g . Stores a double precision 64-bit floating point number. You'll learn more about BINARY_DOUBLE later in the section The New Oracle10 g BINARY_FLOAT and BINARY_DOUBLE Types.

You can see all the data types in Appendix A. The following table illustrates a few examples of how numbers of type NUMBER are stored in the database:

Format	Number Supplied	Number Stored
NUMBER	1234.567	1234.567
NUMBER(6, 2)	123.4567	123.46
NUMBER(6, 2)	12345.67	Number exceeds the specified precision and is rejected by the database.

Examining the Store Tables

In this section, you ll learn how the tables for the store schema are created. The store schema will hold the details of the hypothetical store. Some of the information held in the store schema includes

Customer details
Types of products sold
Product details
A history of the products purchased by the customers
Employees of the store
Salary grades

The following tables will be used to store this information:

customers Stores customer details
product_types Stores the types of products stocked by the store
products Stores product details
purchases Stores which products were purchased by which customers
employees Stores the employee details
salary_grades Stores the salary grade details

Note
The store_schema.sql script creates other tables and database items not mentioned in the previous list. You ll learn about these items in later chapters.

In the next sections, you ll see the details of some of the store tables, and you ll see the CREATE TABLE statements included in the store_schema.sql script that creates these tables.

The customers Table The customers table is used to store the details of the customers of the hypothetical store. The following items are to be stored in this table for each one of the store s customers:

First name
Last name
Date of birth (dob)
Phone number

Each of these items requires a column in the customers table, which is created by the store_schema.sql script using the following CREATE TABLE statement:

 CREATE TABLE customers (customer_id INTEGER  CONSTRAINT customers_pk PRIMARY KEY,  first_name VARCHAR2(10) NOT NULL,  last_name VARCHAR2(10) NOT NULL,  dob DATE,  phone VARCHAR2(12));

As you can see, the customers table contains five columns, one for each item in the previous list, and an extra column named customer_id . The following list contains the details of each of these columns:

customer_id Stores a unique integer for each row in the table. Each table should have one or more columns that uniquely identifies each row in the table and is known as that table s primary key . The CONSTRAINT clause for the customer_id column indicates that this is the table s primary key. A CONSTRAINT clause is used to restrict the values stored in a table or column and, for the customer_id column, the PRIMARY KEY keywords indicate that the customer_id column must contain a unique number for each row. You can also attach an optional name to a constraint, which must immediately follow the CONSTRAINT keyword ”in this case, the name of the constraint is customers_pk . When a row is added to the customers table, a unique value for the customer_id column must be given, and the Oracle database will prevent you from adding a row with the same primary key value. If you try to do so, you will get an error from the database.
first_name Stores the first name of the customer. You ll notice the use of the NOT NULL constraint for the first_name column ”this means that a value must be supplied for first_name . If no constraint is specified, a column uses the default constraint of NULL and allows the column to remain empty.
last_name Stores the last name of the customer. This column is NOT NULL , and therefore you must supply a value.
dob Stores the date of birth for the customer. Notice that a NOT NULL constraint is not specified for this column, therefore the default NULL is assumed, and a value is optional.
phone Stores the phone number of the customer. This is an optional value.

The store_schema.sql script populates the customers table with the following rows:

 customer_id first_name last_name dob phone ----------- ---------- ---------- --------- ------------  1 John Brown 01-JAN-65 800-555-1211  2 Cynthia Green 05-FEB-68 800-555-1212  3 Steve White 16-MAR-71 800-555-1213  4 Gail Black 800-555-1214  5 Doreen Blue 20-MAY-70

Notice that customer #4 s date of birth is null, as is customer #5 s phone number.

You can see the rows in the customers table for yourself by executing the following SELECT statement using SQL*Plus:

 SELECT * FROM customers;

The asterisk (*) indicates you want to retrieve all the columns from the customers table.

The product_types Table The product_types table is used to store the names of the product types that may be stocked by the store. This table is created by the store_schema.sql script using the following CREATE TABLE statement:

 CREATE TABLE product_types (product_type_id INTEGER  CONSTRAINT product_types_pk PRIMARY KEY,  name VARCHAR2(10) NOT NULL);

The product_types table contains the following two columns:

product_type_id Uniquely identifies each row in the table; the product_type_id column is the primary key for this table. Each row in the product_types table must have a unique integer value for the product_type_id column.
name Contains the product type name. It is a NOT NULL column, and therefore a value must be supplied.

The store_schema.sql script populates this table with the following rows:

 product_type_id name --------------- ----------  1 Book  2 Video  3 DVD  4 CD  5 Magazine

This defines the product types for the store. Each product stocked by the store may be of one of these types.

You can see the rows in the product_types table for yourself by executing the following SELECT statement using SQL*Plus:

 SELECT * FROM product_types;

The products Table The products table is used to store detailed information about the products sold. The following pieces of information are to be stored for each product:

Product type
Name
Description
Price

The store_schema.sql script creates the products table using the following CREATE TABLE statement:

 CREATE TABLE products (product_id INTEGER  CONSTRAINT products_pk PRIMARY KEY,  product_type_id INTEGER  CONSTRAINT products_fk_product_types  REFERENCES product_types(product_type_id),  name VARCHAR2(30) NOT NULL,  description VARCHAR2(50),  price NUMBER(5, 2));

The columns in this table are as follows:

product_id Uniquely identifies each row in the table. This column is the primary key of the table.
product_type_id Associates each product with a product type. This column is a reference to the product_type_id column in the product_types table and is known as a foreign key because it references a column in another table. The table containing the foreign key (the products table) is known as the detail or child table, and the table that is referenced (the product_types table) is known as the master or parent table. When you add a new product, you should also associate that product with a type by supplying the product type ID number in the product_type_id column. This type of relationship is known as a master-detail or parent-child relationship.
name Stores the product name, which must be specified as the name column is NOT NULL .
description Stores an optional description of the product.
price Stores an optional price for a product. This column is defined as NUMBER(5, 2) ” the precision is 5, and therefore a maximum of 5 digits may be supplied for this number. The scale is 2, and so 2 of those maximum 5 digits may be to the right of the decimal point.

The following is a subset of the rows that are stored in the products table, populated by the store_schema.sql script:

 product_id product_type_id name description price ---------- ------------------ ------------ ------------ ----------  1 1 Modern A 19.95  Science description  of modern  science  2 1 Chemistry Introduction 30  to Chemistry  3 2 Supernova A star 25.99  explodes  4 2 Tank War Action movie 13.95  about a  future war

The first row in the products table has a product_type_id of 1, which means that this product represents a book. The product_type_id value comes from the product_types table, which uses a product_type_id value of 1 to represent books. The second row also represents a book, but the third and fourth rows represent videos.

You can see all the rows in the products table for yourself by executing the following SELECT statement using SQL*Plus:

 SELECT * FROM products;

The purchases Table The purchases table stores the purchases made by a customer. For each purchase made by a customer, the following information is to be stored:

Product ID
Customer ID
Number of units of the product purchased by the customer

The store_schema.sql script uses the following CREATE TABLE statement to create the purchases table:

 CREATE TABLE purchases (product_id INTEGER  CONSTRAINT purchases_fk_products  REFERENCES products(product_id),  customer_id INTEGER  CONSTRAINT purchases_fk_customers  REFERENCES customers(customer_id),  quantity INTEGER NOT NULL,  CONSTRAINT purchases_pk PRIMARY KEY (product_id, customer_id));

The columns in this table are as follows:

product_id Stores the ID of the product that was purchased. This must match a value in the product_id column for a row in the products table.
customer_id Stores the ID of a customer who made the purchase. This must match a value in the customer_id column for a row in the customers table.
quantity Stores the number of units of the product that were purchased.

The purchases table has a constraint named purchases_pk that spans multiple columns in the table. The purchases_pk constraint is also a PRIMARY KEY constraint and specifies that the table s primary key consists of two columns: product_id and customer_id . The combination of the two values in these columns must be unique for each row in the table.

The following is a subset of the rows that are stored in the purchases table, populated by the store_schema.sql script:

 product_id customer_id quantity ---------- ----------- ----------  1 1 1  2 1 3  1 4 1  2 2 1  1 3 1

As you can see, the combination of the values in the product_id and customer_id columns is unique for each row.

The employees Table The employees table stores the details of the employees of the store. The following information is to be stored:

Employee ID
If applicable , the employee ID of the employee s manager
First name
Last name
Title
Salary

The store_schema.sql script uses the following CREATE TABLE statement to create the employees table:

 CREATE TABLE employees (employee_id INTEGER  CONSTRAINT employees_pk PRIMARY KEY,  manager_id INTEGER,  first_name VARCHAR2(10) NOT NULL,  last_name VARCHAR2(10) NOT NULL,  title VARCHAR2(20),  salary NUMBER(6, 0));

The store_schema.sql script populates this table with the following rows:

 employee_id manager_id first_name last_name title salary ----------- ---------- ---------- ---------- ------------- ----------  1 James Smith CEO 800000  2 1 Ron Johnson Sales Manager 600000  3 2 Fred Hobbs Salesperson 150000  4 2 Susan Jones Salesperson 500000

The salary_grades Table The salary_grades table stores the different grades of salaries available to employees. The following information is to be stored:

Salary grade ID
Low salary boundary for the grade
High salary boundary for the grade

The store_schema.sql script uses the following CREATE TABLE statement to create the salary_grades table:

 CREATE TABLE salary_grades (salary_grade_id INTEGER  CONSTRAINT salary_grade_pk PRIMARY KEY,  low_salary NUMBER(6, 0),  high_salary NUMBER(6, 0));

The store_schema.sql script populates this table with the following rows:

 salary_grade_id low_salary high_salary --------------- ---------- -----------  1 1 250000  2 250001 500000  3 500001 750000  4 750001 999999