Oracle

In Oracle, the CREATE TABLE command is used to create a new table in the database. This is an essential part of the Data Definition Language (DDL), which focuses on defining and managing database structures. For beginners, understanding how to define a table and specify its columns, data types, and constraints is crucial. The following example will demonstrate how to create tables for a library system, focusing on authors, books, libraries, memberships, and rentals.

Here’s a step-by-step guide with code samples for creating these tables:

1. Creating the author Table

   • The author table will store information about authors such as their ID, name, and nationality.

   CREATE TABLE author (
       author_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
       first_name  VARCHAR2(50) NOT NULL,
       last_name   VARCHAR2(50) NOT NULL,
       nationality VARCHAR2(50),
       birth_date  DATE,
       PRIMARY KEY (author_id)
   );
   

2. Creating the books Table

   • The books table will store details about books such as title, genre, publication date, and the author who wrote them.

   CREATE TABLE books (
       book_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
       title        VARCHAR2(100) NOT NULL,
       genre        VARCHAR2(50),
       publication_date DATE,
       author_id    NUMBER,
       PRIMARY KEY (book_id),
       FOREIGN KEY (author_id) REFERENCES author(author_id)
   );
   

3. Creating the library Table

   • The library table represents different library branches with details such as branch name and location.

   CREATE TABLE library (
       library_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
       branch_name  VARCHAR2(100) NOT NULL,
       location     VARCHAR2(100),
       PRIMARY KEY (library_id)
   );
   

4. Creating the membership Table

   • The membership table will store information about library members including their name, contact details, and join date.

   CREATE TABLE membership (
       membership_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
       first_name    VARCHAR2(50) NOT NULL,
       last_name     VARCHAR2(50) NOT NULL,
       join_date     DATE DEFAULT SYSDATE,
       email         VARCHAR2(100),
       phone_number  VARCHAR2(20),
       PRIMARY KEY (membership_id)
   );
   

5. Creating the rentals Table

   • The rentals table will track the borrowing of books by library members, including when a book was borrowed and returned.

   CREATE TABLE rentals (
       rental_id   NUMBER GENERATED BY DEFAULT AS IDENTITY,
       book_id     NUMBER NOT NULL,
       membership_id NUMBER NOT NULL,
       rental_date DATE DEFAULT SYSDATE,
       return_date DATE,
       PRIMARY KEY (rental_id),
       FOREIGN KEY (book_id) REFERENCES books(book_id),
       FOREIGN KEY (membership_id) REFERENCES membership(membership_id)
   );
   

Additional Notes:

• In Oracle, the NUMBER data type is used for numeric values, and VARCHAR2 is used for variable-length strings.
• The PRIMARY KEY constraint ensures that each row in a table can be uniquely identified.
• The FOREIGN KEY constraint establishes a link between two tables, enforcing referential integrity.

By following these examples, students can begin creating well-structured tables that form the foundation of a relational database.

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