Oracle

In Oracle, Data Definition Language (DDL) is used to define and manage database objects such as tables, indexes, and views. For beginners, understanding the data types used in Oracle is essential, as they dictate the kind of data that can be stored in each column of a table. Oracle supports a wide range of data types, which can be grouped into several categories, including numeric, character, date/time, and more. Below, we'll focus on typical data types that would be useful for creating tables related to authors, books, libraries, memberships, and rentals.

Data Types in Oracle:

1. Character Data Types
   These data types store alphanumeric values.

   • VARCHAR2(size): Variable-length character data (1 to 4000 bytes).
   • CHAR(size): Fixed-length character data (1 to 2000 bytes).
   • CLOB: Character large object, used for large text data (up to 4 GB).

2. Numeric Data Types
   These data types store numbers with or without decimal points.

   • NUMBER(p, s): Stores numbers with precision p and scale s.
   • INTEGER: Alias for NUMBER with no fractional part.
   • FLOAT: A floating-point number (precision up to 126).

3. Date/Time Data Types
   These data types store dates, times, and intervals.

   • DATE: Stores date and time (from Jan 1, 4712 BC to Dec 31, 9999 AD).
   • TIMESTAMP: Stores date and time with fractional seconds.
   • INTERVAL YEAR TO MONTH: Stores a period of time in years and months.
   • INTERVAL DAY TO SECOND: Stores a period of time in days, hours, minutes, and seconds.

4. Binary Data Types
   These data types store binary data, such as images or files.

   • BLOB: Binary large object, used for storing binary data (up to 4 GB).
   • BFILE: Points to an external file on the server, used for large binary data.

5. Other Data Types
   These are specialized types for storing specific kinds of information.

   • RAW(size): Stores binary or byte-oriented data.
   • ROWID: A unique identifier for each row in the database.
   • XMLType: Stores XML data.
   • JSON: Stores JSON data (newer versions of Oracle).

Sample Table Definitions:

1. Author Table

   CREATE TABLE author (
     author_id NUMBER(10) PRIMARY KEY,
     first_name VARCHAR2(50) NOT NULL,
     last_name VARCHAR2(50) NOT NULL,
     birth_date DATE,
     bio CLOB
   );
   

2. Books Table

   CREATE TABLE books (
     book_id NUMBER(10) PRIMARY KEY,
     title VARCHAR2(255) NOT NULL,
     author_id NUMBER(10) REFERENCES author(author_id),
     publish_date DATE,
     genre VARCHAR2(100),
     summary CLOB
   );
   

3. Library Table

   CREATE TABLE library (
     library_id NUMBER(10) PRIMARY KEY,
     library_name VARCHAR2(255) NOT NULL,
     location VARCHAR2(255),
     established_year NUMBER(4)
   );
   

4. Membership Table

   CREATE TABLE membership (
     membership_id NUMBER(10) PRIMARY KEY,
     member_name VARCHAR2(100) NOT NULL,
     membership_type VARCHAR2(50),
     join_date DATE,
     expiration_date DATE
   );
   

5. Rentals Table

   CREATE TABLE rentals (
     rental_id NUMBER(10) PRIMARY KEY,
     book_id NUMBER(10) REFERENCES books(book_id),
     member_id NUMBER(10) REFERENCES membership(membership_id),
     rental_date DATE NOT NULL,
     return_date DATE
   );
   

Detailed Breakdown of Data Types:

1. Character Data Types

   • VARCHAR2(255) in the Books table for title stores variable-length book titles.
   • CLOB in the Author and Books tables stores large text for bio and summary.

2. Numeric Data Types

   • NUMBER(10) is used for IDs such as author_id, book_id, library_id, and membership_id.
   • NUMBER(4) in the Library table for established_year stores the year the library was founded.

3. Date/Time Data Types

   • DATE is used for birth_date, publish_date, join_date, expiration_date, rental_date, and return_date to store different date-related information.

4. Foreign Keys

   • author_id in the Books table and book_id in the Rentals table reference other tables to maintain relationships.

By using these data types, you can effectively design your database tables to handle all necessary data related to authors, books, libraries, memberships, and rentals in a structured manner.

This setup is fully compatible with React Markdown, making it easier for students to follow through code examples and numbered lists.

To gain complete access, login with gmail or outlook, no need of signup. click here

Character Data Types

• CHAR(size): Fixed-length character data of length size.
• VARCHAR2(size): Variable-length character data with a maximum size of size bytes.
• NCHAR(size): Fixed-length Unicode character data of length size.
• NVARCHAR2(size): Variable-length Unicode character data with a maximum size of size bytes.

Numeric Data Types

• NUMBER(p, s): Fixed-point or floating-point number with precision p and scale s.
• INTEGER: Signed integer data in the range -2^31 to 2^31 - 1.
• DECIMAL(p, s): Fixed-point number with precision p and scale s.
• FLOAT(p): Floating-point number with binary precision p.

Date and Time Data Types

• DATE: Date and time data ranging from January 1, 4712 BC, to December 31, 9999 AD.
• TIMESTAMP: Date and time data including fractional seconds precision.
• INTERVAL: Time interval data.

Large Object Data Types

• BLOB: Binary large object for storing binary data.
• CLOB: Character large object for storing single-byte character data.
• NCLOB: National character large object for storing Unicode character data.

Row Identifier Data Types

• ROWID: Unique identifier for a row in a database table.
• UROWID: Universal row identifier, similar to ROWID but system-independent.

Other Data Types

• BOOLEAN: Logical data type with values TRUE, FALSE, and NULL.
• BINARY_INTEGER: Integer data type for arithmetic operations.
• PLS_INTEGER: Integer data type optimized for arithmetic operations in PL/SQL.
• RAW(size): Raw binary data of length size bytes.
• LONG: Variable-length character data of up to 2 GB.
• LONG RAW: Variable-length binary data of up to 2 GB.

SAMPLE IMPLEMENTATION OF ORACLE DATA TYPES

CREATE TABLE customer (
    customer_id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_name VARCHAR2(300),
    profile_picture BLOB,
    gender CHAR(1),
    birth_year DATE,
    complete_address VARCHAR2(4000), -- JSON data can be stored as VARCHAR2
    joining_date DATE,
    active_flag NUMBER(1), -- TINYINT equivalent
    salary NUMBER(7), -- MEDIUMINT equivalent
    credit_limit NUMBER(5), -- SMALLINT equivalent
    to_date_purchase_amount NUMBER(10,2), -- DECIMAL equivalent
    marital_status VARCHAR2(20), -- ENUM equivalent
    education VARCHAR2(100), -- SET equivalent
    feedback CLOB, -- TEXT equivalent
    record_creation TIMESTAMP

  );

CREATE TABLE users (
    id NUMBER PRIMARY KEY,
    username VARCHAR2(100),
    encrypted_security_question BLOB,
    encrypted_security_answer BLOB,
    password_hash RAW(64) -- Assuming 64-byte hash value (e.g., SHA-256)
);

CREATE TABLE simulation_results (
    id NUMBER  GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    simulation_id NUMBER,
    experiment_start_time TIMESTAMP,
    experiment_duration INTERVAL DAY TO SECOND, -- TIME equivalent
    pressure BINARY_DOUBLE, -- DOUBLE equivalent
    velocity BINARY_FLOAT, -- FLOAT equivalent
    energy BINARY_DOUBLE -- DOUBLE equivalent
);