Creating & Modifying Database tables
Download
Report
Transcript Creating & Modifying Database tables
Creating Database Tables
1/21/2015
© Abdou Illia
MIS 4200 - Spring 2015
Objectives
Use structured query language (SQL) commands
to create, modify, and drop database tables
Explain Oracle 10g user schemas
Define Oracle 10g database tables
Create database tables using SQL*Plus
View information about your database tables using
Oracle 10g data dictionary views
2
Introduction to SQL
Structured 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
3
Personal DBMS
With 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
4
CH2:42-58
Oracle 10g Enterprise edition
A Client/server DBMS
User account
– Created for each user
– Identified using unique username and password
User schema
– Area of database belonging to user
Database objects
– Example: tables, forms, reports, …
– Also called schema objects
– Objects created by user reside in their user schema
5
CH2:42-58
Defining Oracle Database Tables
Tables
– Primary data objects in relational database
– When you create a new table, you must specify…
• Table name
• Each field data type/size
Each field (i.e. column) name
● 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
6
Defining Oracle Database Tables (cont.)
CREATE TABLE SQL syntax
CREATE 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
7
CH2:42-58
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
coding
Q: s_last CHAR(20) was used to define the data type for s_last in the Student table. How many
8
characters will Oracle save to the disk if the user enters illia as the student’s last name?
Character Data Types (continued)
Unicode coding
– Standardized technique that provides way to encode
data in diverse languages
NVARCHAR2
– Counterpart of VARCHAR2
– Uses Unicode coding
NCHAR
– Counterpart of CHAR
– Uses Unicode encoding
9
CH2:42-58
Number Data Types
NUMBER
– Used for all numeric data
– Syntax
# of digits both to left and
right of decimal point
• columnname NUMBER [([precision,]
[scale])]
# of digits on the right side of decimal point
Example:
– s_balance NUMBER (5,2)
– s_gpa NUMBER (3,2)
10
CH2:42-58
Number Data subtypes
Integer number syntax
Just the precision. No scale specified
– 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.)
No precision, no scale
Syntax: columnname NUMBER
Example: s_gpa NUMBER
11
CH2:42-58
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
12
CH2:42-58
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
If omitted, default is 6 decimal place
– Syntax: columnname TIMESTAMP
(fractional_seconds_precision)
– Example: shipment_date TIMESTAMP(2) 13
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
14
CH2:42-58
Date And Time Data Types (continued)
INTERVAL DAY TO SECOND
– Stores time interval expressed in days, hours, minutes,
and seconds using the following syntax:
Max allowed number of digits used to
express the elapsed days
Columnname INTERVAL DAY [(leading_precision)]
TO SECOND [(fractional_seconds_precision)]
Max allowed number of digits used to
express the elapsed seconds
15
CH2:42-58
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
16
Constraints
Table 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 rows
Types of constraints:
– Integrity constraints
– Value constraints
17
CH2:42-58
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 definition
Example:
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);
Q: Is there any syntax error (or something missing in the above CREATE TABLE statements?
18
Integrity Constraints
Primary key
– Syntax (within table definition)
• CONSTRAINT constraint_name PRIMARY
KEY
CREATE TABLE location
(loc_id NUMBER(6)
CONSTRAINT location_loc_id_pk PRIMARY KEY),
Room VARCHAR2(6));
– 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));
CH2:42-58
19
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)
20
CH2:42-58
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)
21
CH2:42-58
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));
22
CH2:42-58
Value Constraints
Value constraints
– Column-level constraints
– Restrict data values that users can enter
– Commonly used value constraints
• CHECK conditions
• DEFAULT constraint
CONSTRAINT student_s_class_cc
CHECK ((s_class = ‘FR’) OR (s_class = ‘SO’)
OR (s_class = ‘JR’) OR (s_class = ‘SR’))
NOT NULL constraint
● UNIQUE constraint
●
s_state CHAR(2) DEFAULT ‘FL’
CONSTRAINT course_credits_cc
CHECK ((credits > 0) AND (credits < 12))
S_last VARCHAR2(30)
CONSTRAINT student_s_last_nn NOT NULL
23