Creating Database Tables

© Abdou Illia MIS 4200 - Spring 2015

1/21/2015

2

Objectives

Use structured query language (SQL) commands to create, modify, and drop database tables

Explain Oracle 10g user schemasDefine Oracle 10g database tablesCreate database tables using SQL*PlusView information about your database tables using

Oracle 10g data dictionary views

3

Introduction to SQLStructured query language (SQL)

– Standard query language for relational databases

– Consists of about 30 commands

– Enables users to create database objects and manipulate and view data

– SQL-99, SQL-2003, and SQL-2008• Most recent versions

• Most vendors do not fully comply with SQL-2008 (but comply with SQL-92, SQL-99)

– Basic categories for SQL commands• Data definition language (DDL)

• Data manipulation language (DML)CH2:42-58

4

Personal DBMSWith personal DBMS …

– You are usually the only user

– You start the database application

– You create a new database

– The DBMS saves the database file in your workstation’s file system

– You create database objects (tables, etc.)Examples:

– MS Access

– Oracle Personal edition. But this DBMS derives most of its features from the Oracle C/S version

CH2:42-58

5

Oracle 10g Enterprise edition

A Client/server DBMSUser account

– Created for each user

– Identified using unique username and passwordUser schema

– Area of database belonging to userDatabase objects

– Example: tables, forms, reports, …

– Also called schema objects

– Objects created by user reside in their user schema

CH2:42-58

6

Defining Oracle Database Tables Tables

– Primary data objects in relational database

– When you create a new table, you must specify…• Table name ● Each field (i.e. column) name

• Each field data type/size ● You may specify constraints

Constraints

– Restrictions on data values that column can store Oracle naming standard

– Rules established by Oracle corp. for naming objects

– One to 30 characters long

– Contain letters, numbers, and special symbols $, _, and #

– Begin with character

– Example: S_ID, f_id, PRICE, PRICE$, Not #ITEM.CH2:42-58

7

Defining Oracle Database Tables (cont.)

CREATE TABLE SQL syntaxCREATE TABLE tablename

(columnname1 data_type,

columnname2 data_type, …);Example:

CREATE TABLE student

(s_id CHAR(5),

s_first VARCHAR2(20));Basic data types

• Character ● Number ● Date/time ● Large object

CH2:42-58

8

Character Data Types VARCHAR2

– Variable-length character data (up to 4000 characters)

– Syntax: columnname VARCHAR2(maximum_size)– If user enters data value less than maximum_size, DBMS

only stores actual character values CHAR

– Fixed-length character data (default = 2000)

– Syntax: columnname CHAR(maximum_size)– If user enters data value less than maximum_size, DBMS

adds trailing blank spaces to the end of entry Oracle stores CHAR and VARCHAR2 data using the ASCII

codingQ: s_last CHAR(20) was used to define the data type for s_last in the Student table. How many

characters will Oracle save to the disk if the user enters illia as the student’s last name?

9

Character Data Types (continued)

Unicode coding– Standardized technique that provides way to encode

data in diverse languagesNVARCHAR2

– Counterpart of VARCHAR2

– Uses Unicode codingNCHAR

– Counterpart of CHAR

– Uses Unicode encoding

CH2:42-58

10

Number Data Types

NUMBER– Used for all numeric data

– Syntax•columnname NUMBER [([precision,] [scale])]

Example:– s_balance NUMBER (5,2)

– s_gpa NUMBER (3,2)

# of digits both to left and

right of decimal point

# of digits on the right side of decimal point

CH2:42-58

11

Number Data subtypes

Integer number syntax– columnname NUMBER(precision)

Fixed-point number – Contains specific number of decimal places

– Column declaration specifies both precision and scale

– Example: price NUMBER(5,2)Floating-point number

– Contains variable number of decimal places

– Decimal point may appear anywhere (.005, 2.34, etc.)

– Syntax: columnname NUMBER– Example: s_gpa NUMBER

Just the precision. No scale specified

No precision, no scale

CH2:42-58

12

Date And Time Data Types

Datetime data subtypes– Store actual date and time values

– DATE

– TIMESTAMP Interval data subtypes

– Store elapsed time interval between two datetime values

– INTERVAL YEAR TO MONTH

– INTERVAL DAY TO SECOND

CH2:42-58

13

Date And Time Data Types (continued)

DATE– Stores dates from Dec 31, 4712 BC to Dec 31, AD 4712– Default date format: DD-MON-YY– Default time format: HH:MI:SS AM– Syntax: columnname DATE

TIMESTAMP – Stores date values similar to DATE data type – Also stores fractional seconds– Syntax: columnname TIMESTAMP

(fractional_seconds_precision)– Example: shipment_date TIMESTAMP(2)

If omitted, default is 6 decimal place

14

Date And Time Data Types (continued)

INTERVAL YEAR TO MONTH – Stores time interval expressed in years and months

using the following syntax:

+|– elapsed_years-elapsed_months– Example: +02-11 specifies 2 years and 11 months

– Example:time_enrolled INTERVAL YEAR TO MONTH

CH2:42-58

15

Date And Time Data Types (continued)

INTERVAL DAY TO SECOND – Stores time interval expressed in days, hours, minutes,

and seconds using the following syntax:

Columnname INTERVAL DAY [(leading_precision)] TO SECOND [(fractional_seconds_precision)]

Max allowed number of digits used to express the elapsed days

Max allowed number of digits used to express the elapsed seconds

CH2:42-58

16

Large Object (LOB) Data Types

Store binary data such as: – Digitized sounds or images

– References to binary files from word processor or spreadsheet

General syntax– columnname Lob_data_type

17

ConstraintsTable constraint

– Restricts data value with respect to all other values in table like primary key must be unique, not NULL.

Column constraint – Limits value that can be placed in specific column

– Irrespective of values that exist in other table rowsTypes of constraints:

– Integrity constraints

– Value constraints

CH2:42-58

CREATE TABLE location (loc_id NUMBER(6)CONSTRAINT location_loc_id_pk PRIMARY KEY),Room VARCHAR2(6);

18

Constraints (cont.)

Constraint naming convention– tablename_columnname_constraintid

Constraint definitions should be placed either:– At end of CREATE TABLE command after table columns

declared

– Within each column definitionExample:

CREATE TABLE location (loc_id NUMBER(6),Room VARCHAR2(6),CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id);

Q: Is there any syntax error (or something missing in the above CREATE TABLE statements?

19

Integrity Constraints

Primary key– Syntax (within table definition)

•CONSTRAINT constraint_name PRIMARY KEY

– Syntax (at end of table definition)•CONSTRAINT constraint_name PRIMARY KEY (columnname)

CREATE TABLE location (loc_id NUMBER(6),Room VARCHAR2(6),CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id));

CREATE TABLE location (loc_id NUMBER(6)CONSTRAINT location_loc_id_pk PRIMARY KEY),Room VARCHAR2(6));

CH2:42-58

20

Integrity Constraints (continued)

Foreign key– Column constraint

– Specifies that value user inserts in column must exist as primary key in referenced table

– Syntax (placed at end of table definition)CONSTRAINT constraint_name

FOREIGN KEY (columnname)

REFERENCES primary_key_tablename

(primary_key_columnname)

– Example of foreign key defined in the Faculty table:CONSTRAINT faculty_loc_id_fk

FOREIGN KEY (loc_id)

REFERENCES location (loc_id)

CH2:42-58

21

Integrity Constraints (continued)

Foreign key (continued)– Syntax (placed within table definition)

CONSTRAINT constraint_name

REFERENCES primary_key_tablename

(primary_key_columnname)

– Example:loc_id NUMBER(6) CONSTRAINT faculty_loc_id_fk

REFERENCES location (loc_id)

CH2:42-58

22

Integrity Constraints (continued)

Composite key– Syntax

CONSTRAINT constraint_name

PRIMARY KEY (columnname1, columnname2 …)

– Example:

CREATE TABLE enrollment (s_id NUMBER(5) CONSTRAINT enrollment_s_id_fk REFERENCES student(s_id),c_sec_id NUMBER(8) CONSTRAINT enrollment_c_sec_id_fk REFERENCES course_section(c_sec__id),CONSTRAINT enrollment_s_id_c_sec_id_pk PRIMARY KEY (s_id, c_sec_id));

CH2:42-58

23

Value Constraints

Value constraints – Column-level constraints

– Restrict data values that users can enter

– Commonly used value constraints• CHECK conditions ● NOT NULL

constraint

• DEFAULT constraint ● UNIQUE constraintCONSTRAINT student_s_class_cc CHECK ((s_class = ‘FR’) OR (s_class = ‘SO’) OR (s_class = ‘JR’) OR (s_class = ‘SR’))

CONSTRAINT course_credits_cc CHECK ((credits > 0) AND (credits < 12))

S_last VARCHAR2(30)CONSTRAINT student_s_last_nn NOT NULL

s_state CHAR(2) DEFAULT ‘FL’