chapter 2: creating and modifying database tables
DESCRIPTION
Chapter 2: Creating and Modifying Database Tables. Objectives. Become acquainted with Structured Query Language (SQL) Learn about Oracle10g user schemas Learn how to define Oracle10g database tables Create database tables using SQL*Plus. Objectives (cont.). - PowerPoint PPT PresentationTRANSCRIPT
![Page 1: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/1.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 1
Chapter 2:Creating and ModifyingDatabase Tables
![Page 2: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/2.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 2
Objectives
Become acquainted with Structured Query Language (SQL)
Learn about Oracle10g user schemas Learn how to define Oracle10g
database tables Create database tables using SQL*Plus
![Page 3: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/3.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 3
Objectives (cont.)
Learn how to debug Oracle10g SQL commands and use Oracle Corporation online help resources
Learn how to view information about your database tables using Oracle10g data dictionary views
Modify and delete database tables using SQL*Plus
![Page 4: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/4.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 4
Database Objects and Queries An Oracle database consists of multiple user accounts Each user account owns database objects
Tables Views Stored programs Etc.
• Query: command to perform operation on database object
• Structured Query Language (SQL) Industry standard query language for most of relational databases
![Page 5: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/5.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 5
Basic SQL Concepts and CommandsSQL (Structured Query Language) is used to manipulate the database.
There are two basic types of SQL commands:
DDL commands work with the structure of the objects (tables, indexes, views) in the database.
DML commands work with the data in the database (i.e.,manipulate the data).
Data Definition Language (DDL)
Data Manipulation Language (DML)
![Page 6: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/6.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 6
DDL Commands
Used to create and modify the structure of database objects CREATE ALTER DROP GRANT REVOKE
DDL commands execute as soon as they are issued, and do not need to be explicitly saved
![Page 7: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/7.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 7
DML Commands
Used to insert, view, and modify database data INSERT UPDATE DELETE SELECT
DML commands need to be explicitly saved or rolled back COMMIT ROLLBACK SAVEPOINT
![Page 8: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/8.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 8
Security -Granting Table Privileges Security is the prevention of unauthorized access to
the database. Within an organization, the database administrator determines the types of access various users need for the database.
Some users might be able to retrieve and update data in the database. Other users might be able to retrieve any data from the database but not make any changes to it. Still other users might be able to access only a portion of the database.
![Page 9: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/9.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 9
Oracle10g User Accounts
User account - identified by a unique username and password
User schema - all of the objects that the user creates and stores in the database
Object owner has privileges to perform all possible actions on an object
![Page 10: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/10.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 10
Oracle SQL command line utility for issuing SQL commands
Starting SQL Plus
Break Time: SQL Plus
LOGON to YOUR Oracle Account
![Page 11: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/11.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 11
How to Access Your Oracle Account
User Name:
Password:
Host string:
1. Click the START button, point to Programs2. Select Oracle –Oracle10g, then3. Click Application Development, then4. Select SQL PLUS
![Page 12: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/12.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 12
Types of Database Privileges
System Privileges Control the operations that the user can perform within the
database Create user accounts Connecting to the database, creating new tables, shutting
down the database, etc. Object Privileges
Granted on individual database objects Controls operations that a user can perform on a specific
object (insert data, delete data, etc.) When you create an object in your user schema, you can
then grant object privileges on that object to other database users
![Page 13: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/13.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 13
Creating New User Accounts
Done by DBA Syntax:
CREATE username IDENTIFIED BY password;
![Page 14: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/14.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 14
Defining Oracle10g Database Tables To create a table, you must specify:
Table name Field names Field data types Field sizes Constraints
restrictions on the data values that a field can store
![Page 15: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/15.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 15
Creating a Table
CREATE TABLE tablename
(fieldname1 data_type,
(fieldname2 data_type,
…)
![Page 16: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/16.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 16
Oracle Naming standers and Conventions
• From 1 to 30 characters
• Only alphanumeric characters, and special characters ($ , _, #)
• Must begin with a letter and can not contain blank spaces or hyphens Are the following names valid? Why?
customer ordercustomer-order#order
Naming standards are Series of rules Oracle Corporation established for naming all database objects
![Page 17: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/17.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 17
Oracle10g Data Types
Data type: specifies the kind of data that a field stores
Assigning a data type provides a means for error checking
Data types enable the DBMS to use storage space more efficiently by internally storing different types of data in different ways
![Page 18: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/18.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 18
Data Types
• Built-in provided by the system
• Library built by the software
vendor or a third party
• User-defined built by users
![Page 19: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/19.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 19
• Data type: specifies type of data stored in a field Date, character, number, etc.
• Uses/Purposes Error checking Efficient use of storage space
Oracle Data Types
L
![Page 20: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/20.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 20
Basic Built-In Data Types
• Character VARCHAR2 CHAR NVARCHAR2 / NCHAR
• Numeric NUMBER
• Date/Time
• Others: LONG, RAW, LONG RAW, BLOB
![Page 21: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/21.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 21
Character Data Types
1. VARCHAR2 Stores variable-length character data up
to a maximum of 4,000 characters Values in different records can have a
different number of characters fieldname VARCHAR2(maximum_size)
(e.g.) emp_name VARCHAR2(20); an instance: ‘Jason Chen’
![Page 22: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/22.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 22
Character Data Types (cont.) 2. CHAR
Fixed-length character data (<= 2000 characters) default is 1 if no field size is specified Data values for different records all have the same number of
characters DBMS adds trailing blank spaces to the end of the entry to make the
entry fill the maximum_size value Data longer than maximum_size causes an error fieldname CHAR[(maximum_size)]
pros: use data storage space more efficiently and processed faster cons: causes inconsistent query results in other Oracle applications
e.g. s_class CHAR(2); ‘SR’ ‘JR’ ‘SO’ ‘FR’ ‘GR’
s_state CHAR(2) DEFAULT ‘WI’; student_gender CHAR;
[optional]
![Page 23: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/23.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 23
Character Subtypes
Examples:
VARCHAR2(5) ‘Smith’ or ‘Smi’
CHAR(5) ‘Smith’ or ‘Smi ’
![Page 24: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/24.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 24
Question: Which query will possibly generate student information?
What data type should be used if there is any chance that all column spaces will NOT be filled?
Answer: VARCHAR2
s_last VARCHAR2(15);
SELECT s_last, s_first, s_addressFROM studentWHERE s_last = ‘Smith’;
s_last CHAR(15);
SELECT s_last, s_first, s_addressFROM studentWHERE s_last = ‘Smith’;
L
![Page 25: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/25.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 25
When use Query:
SELECT s_last, s_first, ssn, telephone FROM student
WHERE s_last = ‘Smith’;
Case is sensitive within the single
quotation. SQL Plus commands are NOT case
sensitive, but Query within the single quotation are case sensitive.
![Page 26: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/26.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 26
Character Data Types (cont.)
3. NVARCHAR2 and NCHAR Analogous to VARCHAR2 and
CHAR but use Unicode rather than ASCII
Used to hold character data in languages other than English
![Page 27: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/27.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 27
4. Number Data Types Stores negative, positive, fixed, and floating point
numbers between 10 -130 and10 +125 with precision up to 38 decimal places
General Syntax: fieldname NUMBER [([precision,] [scale])]
Integer: fieldname NUMBER(precision) Fixed point: fieldname NUMBER[([precision],[scale])] Floating point: fieldname NUMBER
![Page 28: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/28.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 28
Number Data Types (examples) a) Integer: Number (n)
e.g. s_id NUMBER(5) 12345
b) Fixed-point numbers e.g. current_price NUMBER (5, 2);
259.99 33.89 c) Fixed-point numbers (cont.)
e.g. total_mileage NUMBER (5, 1); 259.9 33.8
d) Floating-point Number – with a variable number of decimal places e.g. s_gpa NUMBER;
3.89 2.7569 3.2
![Page 29: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/29.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 29
5. Date and Time Data Types DATE
Dates from December 31, 4712 BC to December 31, 4712 AD
Stores the century, year, month, day, hour, minute and second.
Default format DD-MON-YY Default time format HH:MI:SS A.M.
fieldname DATE Sample declaration: s_dob DATE;Use one of the following format masks:
TO_DATE (‘ ’, ‘MM/DD/YY’) TO_DATE (‘ ‘, ‘DD-MON-YYYY’) TO_DATE (‘ ‘, ‘HH:MI AM’)
![Page 30: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/30.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 30
Date and Time Data Types (cont.) TIMESTAMP
Similar to DATE but stores fractional seconds
fieldname TIMESTAMP (fractional_seconds_precision)
If you omit the fractional_seconds_precision specification, the default value is 6 decimal places.
E.g: s1_date_received TIMESTAMP(2);
![Page 31: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/31.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 31
Date and Time Data Types (cont.)
INTERVAL YEAR TO MONTH Time interval, expressed in years and months +02-11 specifies a positive time interval of 2 years and 11 months fieldname INTERVAL YEAR[(year_precision)] TO MONTH e.g., Software Expert database (p.48) elapsed_time INTERVAL YEAR(2) TO MONTH;
INTERVAL DAY TO SECOND Time interval, expressed in days, hours, minutes, and seconds -04 03:20:32.00: 4 days, 3 hours, 20 minutes, and 32 seconds fieldname INTERVAL DAY[(leading_precision)] TO
SECOND[(fractional_seconds_precision)] e.g. DDL:
elapsed_time INTERVAL DAY(6) TO SECOND, DML:
TO_DSINTERVAL('53 00:00:00.00')
![Page 32: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/32.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 32
6. Large Object (LOB) Data Types
![Page 33: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/33.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 33
What is a Constraint?
A rule that restricts the values that can be inserted into a field
A mechanism used to protect the relationship between data within an Oracle
table, or the correspondence between data in two different
tables. For example, the state entered must be one of the 50
states in the U.S.
![Page 34: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/34.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 34
Types of Constraints
Integrity constraints: define primary and foreign keys
Value constraints: define specific data values or data ranges that must be inserted into columns and whether values must be unique or not NULL
Table constraint: restricts the data value with respect to all other values in the table
Field constraint: limits the value that can be placed in a specific field, irrespective of values that exist in other table records
![Page 35: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/35.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 35
Creating a Table
CREATE TABLE tablename(fieldname1 data_type [CONSTRAINT constraint_name]
CONSTRAINT_TYPE,fieldname2 data_type [CONSTRAINT constraint_name]
CONSTRAINT_TYPE,,…)
CREATE TABLE tablename(fieldname1 data_type,fieldname2 data_type,
[CONSTRAINT constraint_name] CONSTRAINT_TYPE(coulmn_name));
![Page 36: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/36.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 36
I. Naming conventions for constraints
<tablename>_<fieldname>_<constraint id>
Where <constraint id> is:• pk PRIMARY KEY• fk REFERENCES <tablename> (pk)• • cc CHECK <condition to be checked>
(note that cc stands for CHECKCONDITION)
• nn NOT NULL• uk UNIQUE
Inte
grai
ty
con
stra
int
Val
ue
con
stra
int
e.g., s_id NUMBER (6) CONSTRAINT student_s_id_pk PRIMARY KEY;
![Page 37: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/37.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 37
Oracle Constraint Naming Convention tablename_ fieldname_constraintID
![Page 38: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/38.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 38
Integrity Constraints
Define primary key fields
Specify foreign keys and their corresponding table and column references
Specify composite keys
![Page 39: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/39.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 39
Table-level Can be defined when field is declared Syntax:
CONSTRAINT constraint_name PRIMARY KEY
Primary Key Constraints
SQL> CREATE TABLE my_students2 (s_id NUMBER(6) CONSTRAINT my_students_s_id_pk PRIMARY KEY,3 s_name VARCHAR2(30),4 s_class CHAR(2),5 s_dob DATE);
![Page 40: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/40.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 40
Can also be defined after all table field definitions are completed
Primary Key Constraints (cont.)
SQL> CREATE TABLE my_students2 (s_id NUMBER(6), 3 s_name VARCHAR2(30),4 s_class CHAR(2),5 s_dob DATE),6 CONSTRAINT student_s_id_pk PRIMARY KEY (s_id));
SQL> CREATE TABLE my_students2 (s_id NUMBER(6) CONSTRAINT my_students_s_id_pk PRIMARY KEY,3 s_name VARCHAR2(30),4 s_class CHAR(2),5 s_dob DATE);
![Page 41: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/41.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 41
Primary Key Constraints (cont.)
You will learn how to create the following SQL (DDL) command:
CREATE TABLE location(loc_id NUMBER(6),bldg_code VARCHAR2(10),room VARCHAR2(6),capacity NUMBER(5),CONSTRAINT location_loc_id_pk PRIMARY KEY (loc_id));
![Page 42: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/42.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 42
Table-level Can only be defined after field is defined as a
primary key in another table Syntax:
CONSTRAINT constraint_name
REFERENCES primary_key_table_name (field_name)
Foreign Key Constraints
![Page 43: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/43.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 43
Can be defined when field is declared
Foreign Key Constraints
N
CREATE TABLE faculty(f_id NUMBER(6) CONSTRAINT faculty_f_id_pk PRIMARY KEY(f_id),f_last VARCHAR2(30),f_first VARCHAR2(30),f_mi CHAR(1),loc_id NUMBER(5) CONSTRAINT faculty_loc_id_fk REFERENCES location(loc_id)f_phone VARCHAR2(10),f_rank VARCHAR2(8),f_pin NUMBER(4),f_image BLOB, );
SQL> SELECT TABLE_NAME FROM USER_TABLES;
![Page 44: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/44.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 44
Can also be defined after all table field definitions are completed
Foreign Key Constraints (cont.)
CREATE TABLE faculty(f_id NUMBER(6),f_last VARCHAR2(30),f_first VARCHAR2(30),f_mi CHAR(1),loc_id NUMBER(5),f_phone VARCHAR2(10),f_rank VARCHAR2(8),f_pin NUMBER(4),f_image BLOB, CONSTRAINT faculty_f_id_pk PRIMARY KEY(f_id),CONSTRAINT faculty_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id));
SQL> SELECT TABLE_NAME FROM USER_TABLES;
![Page 45: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/45.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 45
Column-level Restricts data values that can be inserted in a
field In general, avoid value constraints because
they make the database very inflexible
Value Constraints
![Page 46: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/46.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 46
Types of Value Constraints Check condition: restricts to specific values
CONSRAINT 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 <=18));
s_gender (M or F)CONSTRAINT my_students_s_gender_cc CHECK (s_gender = ‘M’) OR (s_gender = ‘F’);
Not NULL: specifies that a field cannot be NULL Must be created in column declaration. s_last VARCHAR2(30) CONSTRAINT
student_s_last_nn NOT NULL; CONSTRAINT my_students_s_dob_nn NOT NULL;
![Page 47: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/47.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 47
Types of Value Constraints (cont.) Unique
Table constraint Specifies that a non-primary key field must have a unique
value CONSTRAINT term_term_desc_uk UNIQUE (term_desc);
Default: specifies a default value that is inserted automatically Must be created in the column declaration s_state CHAR(2) DEFAULT ‘WI’;
![Page 48: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/48.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 48
Summary on Value Constraints (cont.)
Check conditions: field value must be a specific value or fall within a range of values
NOT NULL constraints: specify whether a field value can be NULL
Default constraints: specify that a field has a default value that the DBMS automatically inserts for every record, unless the user specifies an alternate value
Unique constraints: specify that a field must have a unique value for every table record
![Page 49: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/49.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 49
Creating Database Tables Using SQL*Plus Type SQL commands at the SQL prompt
End each command with a semicolon (;)
Not case sensitive
![Page 50: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/50.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 50
Log On to SQL*Plus
Oraclelab
![Page 51: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/51.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 51
Create a Table
![Page 52: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/52.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 52
Using Notepad
Useful to use Notepad to edit sql commands Commands can be edited without
retyping Commands can be saved Saving multiple sql commands in a
file creates a script
![Page 53: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/53.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 53
Study …
Can you create TABLE student now?
![Page 54: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/54.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 54
Table Creation Sequence
When creating tables with foreign key references, create referenced tables first
Always create tables without foreign keys before those with foreign keys
![Page 55: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/55.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 55
Errors - Oracle Help Resources
When an error occurs error information is displayed: Line number Position on line Error code Description of error
Error codes 3 letter prefix (I.e. ORA) 5 digit code More information on errors can be found at
http://otn.oracle.com
![Page 56: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/56.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 56
Syntax:CONSTRAINT constraint_name PRIMARY KEY (field1, field2) Must be defined after fields that composite key are
defined
Composite Primary Keys
CREATE TABLE ENROLLMENT(s_id NUMBER(6),c_sec_id NUMBER(6),grade CHAR(1), CONSTRAINT enrollment_pk PRIMARY KEY (s_id, c_sec_id),CONSTRAINT enrollment_sid_fk FOREIGN KEY (s_id) REFERENCES student(s_id),CONSTRAINT enrollment_csecid_fk FOREIGN KEY (c_sec_id) REFERENCES course_section (c_sec_id));
![Page 57: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/57.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 57
Integrity and Value Constraints: Other Examples (cont.)
CREATE TABLE faculty(f_id NUMBER(6) CONSTRAINT faculty_f_id_pk PRIMARY KEY,f_last VARCHAR2(30) CONSTRAINT faculty_f_last_nn NOT NULL,f_first VARCHAR2(30) CONSTRAINT faculty_f_first_nn NOT NULL,f_mi CHAR(1),loc_id NUMBER(5) CONSTRAINT faculty_loc_id_fk REFERENCES location(loc_id),f_phone VARCHAR2(10),f_rank VARCHAR2(8) CONSTRAINT faculty_f_rank_ccCHECK ((f_rank = ’INST') OR (f_rank = ’ASST')OR (f_rank = 'ASSO') OR (f_rank = ’FULL')),f_pin NUMBER(4) CONSTRAINT faculty_f_pin_uk UNIQUE,f_image BLOB);
![Page 58: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/58.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 58
Exiting SQL*Plus
Three ways to exit SQL*Plus: Type exit at the SQL prompt lick File on the menu bar, and then click Exit Click the Close button on the program window title
bar Database session ends when SQL*Plus exits
![Page 59: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/59.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 59
Viewing Information About Tables describe tablename: displays short description about
tablename; column names, NOT NULL columns and data types.
Data dictionary: tables that contain information about the structure of the database. When the DBA creates a new DB, the system creates the data
dictionary in a user schema named SYS. Oracle10g DBMS automatically updates the data dictionary
tables as users create, update, or delete database objects. Users do not directly interact with data dictionary tables, They interact with the data dictionary views.
View: is a db object that the DBMS bases on an actual db table and which enables the DBMS to present the table data in a different format based on user needs. It can serve to hide some table columns in which the user has no interest or doesn’t have privileges to view.
![Page 60: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/60.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 60
Viewing Information About Tables Categories of Data Dictionary Views:
USER: shows the objects in the current user’s schema ALL: shows both objects in the current user’s schema
and objects that the user has privileges to manipulate DBA: allows users who are database administrators to
view information about all database objects
General command:SELECT view_columnname1, view_columnname2, …FROM prefix_object;
![Page 61: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/61.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 61
Database Objects with Data Dictionary View Object NameObject Type
OBJECTSAll database objects
TABLESDatabase tables
INDEXESTable indexes created to improve query performance
VIEWSDatabase views
SEQUENCESSequences created to automatically generate surrogate key values
USERSDatabase users
CONSTRAINTSTable constraints
CONS_COLUMNSTable columns that have constraints
IND_COLUMNSIndexed columns
TAB_COLUMNSAll table columns
![Page 62: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/62.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 62
Viewing Tables in the Database
![Page 63: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/63.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 63
Viewing Constraints on One Table
![Page 64: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/64.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 64
Modifying and Deleting Database Tables Modify existing database tables by:
Changing the name of a table Adding new columns Deleting columns that are no longer needed Changing the data type or maximum size of an existing
column Unrestricted action: some specifications can always
be modified Restricted action: specifications modified only in
certain situations
![Page 65: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/65.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 65
Deleting and Renaming Tables To delete:
DROP TABLE [tablename] Use with caution To delete foreign key constraints, add
“CASCADE CONSTRAINTS”
To rename: RENAME old_tablename TO new_tablename DBMS automatically transfers to the new table
integrity constraints, indexes, and privileges that referenced the old table.
Views and stored program units that reference the old table name become Invalid.
![Page 66: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/66.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 66
Adding Columns to Existing Tables To add a field:
ALTER TABLE tablename ADD(fieldname data_declaration constraints);
Example:ALTER TABLE faculty ADD (start_date DATE);
![Page 67: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/67.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 67
Modifying Existing Column Data Definitions Can only change datatype to compatible data
type (i.e. varchar2 to char)
ALTER tablename
MODIFY(fieldname new_data_declaration);
Example:ALTER TABLE faculty MODIFY (f_rank VARCHAR2 (4));
![Page 68: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/68.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 68
Deleting a Column To delete a field
ALTER TABLE tablename
DROP COLUMN fieldname;
Examples:ALTER TABLE faculty DROP COLUMN f_rank;
ALTER TABLE faculty ADD (faculty_rank VARCHAR2(4));
![Page 69: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/69.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 69
Renaming a Column To rename a field
ALTER TABLE tablename
RENAME COLUMN old_fieldname TO new_fieldname;
![Page 70: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/70.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 70
Adding and Deleting Constraints Add a constraint:
ALTER TABLE tablename
ADD CONSTRAINT constraint_name constraint_definition;
Remove a constraint:ALTER TABLE tablename
DROP CONSTRAINT constraint_name;
Examples:ALTER TABLE facultyADD CONSTRAINT faculty_f_pin_uk UNIQUE (f_pin);
ALTER TABLE facultyDROP CONSTRAINT faculty_f_pin_uk;
![Page 71: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/71.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 71
Enabling and Disabling Constraints When modifying a database it can be useful to disable
constraints Constraints are enabled by default To disable a constraint:
ALTER TABLE tablename
DISABLE CONSTRAINT constraint_name; To enable a constraint:
ALTER TABLE tablename
ENABLE CONSTRAINT constraint_name;
ALTER TABLE facultyDISABLE CONSTRAINT faculty_loc_id_fk;
ALTER TABLE facultyENABLE CONSTRAINT faculty_loc_id_fk;
DROP TABLE faculty CASCADE CONSTRAINTS;
EXIT;
![Page 72: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/72.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 72
SQL Commands
CREATE TABLE <tablename> … ; DROP TABLE <tablename>; RENAME <tablename>TO<newtablename>;
Use the following commands to check your tables SELECT table_name FROM user_tables; DESCRIBE <tablename>;
![Page 73: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/73.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 73
SQL Commands
SELECT constraint_name
FROM user_constraints;
SELECT constraint_name
FROM user_constraints
WHERE TABLE_NAME =‘<tablename>’;
‘case sensitive’ within the quotation
![Page 74: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/74.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 74
SQL Commands (cont.)
ALTER TABLE <tablename>
ADD <fieldname> <data declaration>; ALTER TABLE <tablename>
ADD/MODIFY <fieldname> <data declaration>
CONSTRAINT <integrity constraints>
CONSTRAINT <value constraints>;
EXIT; or QUIT;
![Page 75: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/75.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 75
Summary SQL commands include:
Data description language (DDL) commands: create, modify, Deleted database objects
Data manipulation language (DML) commands: insert, update, delete, view database data
To create a table: specify the table name, the name of each data field,
and the data type and size of each data field Data types ensure correct data values Constraints restrict values of database fields SQL*Plus commands are not case sensitive
![Page 76: Chapter 2: Creating and Modifying Database Tables](https://reader035.vdocuments.us/reader035/viewer/2022081419/56813825550346895d9fd283/html5/thumbnails/76.jpg)
IS421 DB Applications Mr. Ashraf Yaseen 76
Summary (cont.)
Errors include line number, position, error code
Use DESCRIBE command to display a table’s fieldnames and data types
Tables can be modified or deleted but some changes are restricted