Oracle: Creating a View from Multiple Tables

In this article, we will explore the concept of creating views in Oracle and how to use them effectively. Specifically, we will delve into creating a view that combines data from multiple tables.

Introduction to Views in Oracle

A view is a virtual table based on the result of a query. It can be used to simplify complex queries, provide an abstraction layer between the user and the underlying database structure, or make it easier for non-technical users to access data.

To create a view in Oracle, you use the CREATE VIEW statement followed by the name of the view and a SELECT statement that specifies the columns and conditions of the view.

Understanding the Schema

The question provides four tables: Engineers, Faculty, Classes, and ClassEnrollments. The relationship between these tables is as follows:

An engineer can be enrolled in multiple classes (one-to-many).
A class has multiple enrollments from different faculty members and engineers (many-to-many).

Analyzing the Existing Query

The question provides a query that attempts to create a view, Testers, which combines data from four tables: Engineers, Faculty, Classes, and ClassEnrollments. The query uses implicit joins with the comma-separated list of table names.

CREATE VIEW Testers AS
SELECT e.Lastname, e.Firstname, f.Lastname, f.Email, c.Subject, c.title, en.EnID
FROM Engineers e, Faculty f, Classes c, ClassEnrollments en
WHERE e.EID = en.EID AND f.FID = en.FID AND c.CID = en.CID 
ORDER By en.ENID;

Understanding the Issue with the Existing Query

The query provided in the question results in a duplicate column name error. This is because both f.Lastname and e.Lastname are selected as separate columns, which can lead to confusion when using the view.

Solved Query Using Aliases

To resolve this issue, we can use aliases for the columns that have the same name in different tables. We modify the query to give distinct names to each of these columns:

CREATE VIEW Testers AS
SELECT e.Lastname as EngLastname, e.Firstname as EngFirstname,
       f.Lastname as FacLastname, f.Email,
       c.Subject, c.title, en.EnID
FROM Engineers e, Faculty f, Classes c, ClassEnrollments en
WHERE e.EID = en.EID AND f.FID = en.FID AND c.CID = en.CID 
ORDER By en.ENID;

Solved Query Using JOIN Clause

Another approach is to use a JOIN clause instead of an implicit join with commas. This makes the query more readable and easier to understand:

CREATE VIEW Testers AS
SELECT e.Lastname as EngLastname,
       e.Firstname as EngFirstname,
       f.Lastname as FacLastname,
       f.Email,
       c.Subject, c.title,
       en.EnID    
FROM Engineers e
JOIN Faculty f ON Faculty.FID = ClassEnrollments.FID
JOIN Classes C ON ClassEnrollments.CID = Classes.CID
JOIN ClassEnrollments en ON ClassEnrollments.EID = Engineers.EID;

Using a Join with the `JOIN` Keyword

Using a JOIN clause is more efficient and clearer, but we need to make sure our database schema is set up for this. This can be done by using FOREIGN KEY constraints:

CREATE TABLE Classes (
    CID Integer Primary Key,
    Subject varchar(6),
    Catalognbr varchar(6),
    Title varchar(120)
);

CREATE TABLE ClassEnrollments (
    EnID Integer Primary Key,
    CID Integer,
    FID Integer, 
    FOREIGN KEY (CID) REFERENCES CLASSES(CID),
    FOREIGN KEY (FID) REFERENCES FACULTY(FID),
    FOREIGN KEY (EID) REFERENCES ENGINEERS(EID)
);

Best Practices

Use aliases for columns that have the same name in different tables.
Prefer using JOIN clauses over implicit joins with commas.
Make sure your database schema is set up to support efficient join operations.

Conclusion

Creating views in Oracle can be an effective way to simplify complex queries, provide abstraction between the user and the underlying database structure, or make it easier for non-technical users to access data. By understanding the concept of views, using aliases to resolve duplicate column name errors, and employing JOIN clauses for clarity and efficiency, we can effectively create and maintain our views in Oracle.

In addition to creating a view that combines columns from multiple tables like this one, we might also need to consider issues such as:

Handling null values
Implementing security measures to prevent unauthorized access to the data through the view.
Creating views based on other types of queries (like aggregate functions).

Views can serve many purposes in database design and are a fundamental concept that should be explored when working with relational databases.

Last modified on 2025-01-12