Guide to Oracle 10g

1. Guide to Oracle 10g Chapter 2: Creating and Modifying Database Tables

2. The Oracle 10g Client/Server Database • Oracle 10g • Latest release of Oracle Corporation’s relational database • Client/server database • Server side • DBMS server process • Oracle Net • Utility that enables network communication between client and server Guide to Oracle 10g

3. Client/Server Architecture for Oracle 10g DBMS Guide to Oracle 10g

4. The Oracle 10g Client/Server Database (continued) • Oracle Application Server • Used to create World Wide Web pages that allow users to access Oracle databases • Oracle client products: • SQL*Plus • Oracle 10gDeveloper Suite • Enterprise Manager Guide to Oracle 10g

5. Database Objects • An Oracle database consists of multiple user accounts • Each user account owns database objects • Tables • Views • Stored programs • Etc.

6. Database Queries • Query: command to perform operation on database object • Create • Modify • View • Delete • Structured Query Language (SQL) • Standard query language for relational databases

7. SQL Command Types • Data Definition Language (DDL) • Used to create and modify the structure of database objects • Data Manipulation Language (DML) • Used to insert, update, delete, and view database data

8. DDL Commands • Used to create and modify the structure of database objects • CREATE • ALTER • DROP • DDL commands execute as soon as they are issued, and do not need to be explicitly saved

9. 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

10. User Accounts • Each Oracle database user has a user schema • Area in the database where the user’s database objects are stored • Identified by a unique username and protected by a password • Each user schema is granted specific privileges

11. Types of Database Privileges • System Privileges • Control the operations that the user can perform within the database • Connecting to the database (Create Session), 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

12. Break Time: SQL Plus • Oracle SQL command line utility for issuing SQL commands • Starting SQL Plus LOGON to YOUR Oracle Account

13. How to Access Your Oracle Account 1. Click the START button, point to Programs 2. Select Oracle –Oracle10g, then 3. Click Application Development, then 4. Select SQL PLUS User Name: Password: Host string:

14. Creating New User Accounts • Done by DBA • Syntax: CREATE USER username IDENTIFIED BY password;

15. Oracle Naming Standard • Oracle database objects must adhere to the Oracle Naming Standard • 1 to 30 characters long • Must begin with a character • Can contain characters, numbers, and the symbols $, _, and #

16. Defining Database Tables • To create a table, you must specify: • Table name • Field names • Field data types • Field sizes • Constraints

17. Table and Field Names • Must follow the Oracle Naming Standard • Each table in a user schema must have a unique name within that user schema • Each field in a table must have a unique name within that table

18. Creating a Table CREATE TABLEtablename (fieldname1 data_type, (fieldname2 data_type, …)

19. Oracle Data Types • Data type: specifies type of data stored in a field • Date, character, number. • LONG, RAW, LONG RAW, BLOB • Uses • Error checking • Efficient use of storage space

20. Oracle Character Data Types • VARCHAR2 columnname VARCHAR2(max_size) • Variable-length character strings • Max_size can be between 1 and 4,000 characters • Must specify the size • No trailing blank spaces are added • If more than max_size data is inserted, an error occurs. • Example declaration: student_name VARCHAR2(30)

21. Character Data Types • CHAR columnname CHAR(max_size) • Fixed-length character data • Max_size can be between 1 and 2000 characters • Max_size is optional. Default is 1. • Adds trailing blank spaces to pad width • If more than max_size data is inserted, an error occurs. • Example declaration: student_gender CHAR(2)

22. Character Subtypes • Examples: • VARCHAR2(5) ‘Smith’ or ‘Smi’ • CHAR(5) ‘Smith’ or ‘Smi ’

23. Question: Which query will possibly generate student information? s_last CHAR(15); SELECT s_last, s_first, s_address FROM student WHERE s_last = ‘Smith’; s_last VARCHAR2(15); SELECT s_last, s_first, s_address FROM student WHERE s_last = ‘Smith’; • What data type should be used if there is any chance that all column spaces will NOT be filled? • Answer: VARCHAR2

24. Character Data Types • 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 (Japanese).

25. Number Data Type • NUMBER • stores negative, positive, fixed, and floating point numbers values between 10-130 and 10126 • General declaration format: variable_name NUMBER(precision, scale)

26. NUMBER Data Types • Number type (integer, fixed point, floating point) specified by precision and scale • Precision: total number of digits on either side of the decimal point. It does not include the decimal point itself or any commas or any formatting symbols. • Scale: number of digits to right of decimal point

27. Integer Numbers • Whole number with no digits to right of decimal point • Precision is maximum width • Scale is omitted • Sample declaration: s_age NUMBER (2)

28. Fixed Point Numbers • Contain a specific number of decimal places • Precision is maximum width • Scale is number of decimal places • Sample declaration: item_price NUMBER(5, 2) • 259.99 33.89 (decimal point is not included)

29. Floating Point Numbers • Contain a variable number of decimal places • Precision and scale are omitted • Sample declaration: s_GPA NUMBER

30. Date Data Type • DATE • Stores dates from 1/1/4712 BC to 12/31/4712 AD • Stores both a date and time component • Default date format: DD-MON-YY HH:MI:SS AM • example: 05-JUN-03 12:00:00 AM • Sample declaration: s_dob DATE

31. Specifying Date and Time Values • If no time value is given when a new date is inserted, default value is 12:00:00 AM • If no date value is given when a new time is inserted, default date is first day of current month

32. TIMESTAMP Data Type • The same as Date DT, but it stores also fractional seconds. • Field Timestamp(Fr_Se_Precision) • E.g: ship_dt Timestamp(2) • Fractional Seconds Precision default value is 6 (If omitted).

33. Interval Year to Month Data Type • Field Interval Year(Y_Pr) To Month. • Y_Pr: Year Precision(Default: 6). • E.g: elapsed Interval Year(2) To Month. • Possible Values: +02-11 :add 2 years and 11 months to a known date. -11-4:subtract 11 years and 4 months.

34. Interval Day to Second Data Type • Field Interval Day(D_Pr) To Second(Fr_Se_pr). • D_Pr: Day Precision(Default : 2). • Fr_Se_Pr: Fractional Seconds Precision (Default : 6). • Possible value: -04 03:20:32.00 (Days Hours:Minutes:Seconds.Fractions)

35. Large Object (LOB) Data Types • Binary Large Object (BLOB) • Stores up to 4 GB of binary data • Character Large Object (CLOB) • Stores up to 4 GB of character data • BFILE • Stores a reference to a binary file maintained in the operating system • NCLOB • Character LOB that supports 16-bit character code

36. 6. Large Object (LOB) Data Types Ex: f_image BLOB;

37. Declaring LOB Data Fields • Item size is not specified • Examples: item_image BLOB item_image BFILE

38. Creating a Database Table • Syntax: CREATE TABLE table_name ( fieldname1 datatype, fieldname2 datatype, …); • Example: CREATE TABLE my_students ( s_id NUMBER(6), s_name VARCHAR2(30), s_dob DATE, s_class CHAR(2));

39. Constraints • Rules that restrict the values that can be inserted into a field • Types of constraints • Integrity: define primary and foreign keys • Value: specify values or ranges of values that can be inserted

40. Constraint Levels • Table constraint • Restricts the value of a field with respect to all other table records • Example: primary key value must be unique for each record • Column constraint • Restricts values in a specific column • Example: values in an S_GENDER field must be ‘M’ or ‘F’

41. Constraint Names • Internal name used by DBMS to identify the constraint • Each constraint name in a user schema must be unique • If you do not name a constraint, the system will automatically generate an unintuitive name

42. Constraint Names • Constraint naming convention: tablename_fieldname_constraintID • Constraint ID values: • Primary key: pk • Foreign key: fk • Check condition: cc • Not NULL: nn • Unique: uk • Example constraint name: my_students_s_id_pk

43. Primary Key Constraints • Table-level • Defining a primary key: CONSTRAINT constraint_name PRIMARY KEY • Example: s_id NUMBER(6) CONSTRAINT student_s_id_pk PRIMARY KEY

44. Primary Key Constraints • Can be defined when field is declared

45. Primary Key Constraints • Can also be defined after all table field definitions are completed

46. Composite Primary Keys • Syntax: CONSTRAINT constraint_name PRIMARY KEY (field1, field2) • Must be defined after fields that compose key are defined

47. Foreign Key Constraints • 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)

48. Foreign Key Constraints • Can be defined when field is declared

49. Foreign Key Constraints • Can also be defined after all table field definitions are completed

50. Value Constraints • Column-level • Restricts data values that can be inserted in a field • In general, avoid value constraints because they make the database very inflexible