A Complete Guide to Views

Chapter 1: Introduction

In this article, we'll be exploring every aspect of views - virtual tables that allows us a way to present and organize the data. It is like looking through a lens that shows only the aspect of data we're interested in seeing. Doesn't matter whether you are a newbie to databases or an expert data nerd, this article offers everything you need to know about views, from simplifying queries and data security to organizing data.

Chapter 2: What are Views

• A view is a virtual table that is derived from the result of a query.

• It is created from two or more tables or views which represent a logical subset of data. One important point to note here is:

This is a very common misconception many data nerds aren't aware of. However, there are materialized views that don't behave this way. Well, we'll be looking at it in further sections of this article.

Chapter 3: Why Views

Let's understand it with a real-world scenario faced by businesses and how they tackle it using views.

By creating views, we can create a view that exposes only the necessary information to each team in the organization.

Chapter 3: Types Of Views

There are several types of views used in Database Management Systems. Some of the most commonly used are:

1. Simple Views: Simple Views are based on a single underlying table.

2. Complex Views: Complex Views involve two or more tables and often contain joins, subqueries and calculations.

3. Materialized Views: They store the actual data they represent. They should be refreshed periodically to keep the data up to date.

Chapter 4: Getting Hands Dirty on Views

Enough talk about views !!! Now let's get our hands dirty on views.

Let's use a Harry Potter-themed example to explain the concept of views. Are you ready?

Imagine you manage the database of Hogwarts, a database that keeps track of the students and their respective houses. You have two tables with you:

Table: Students
Columns: StudentID, StudentName, HouseID, WandType

Table: Houses
Columns: HouseID, HouseName

Let's create the tables and insert the data first:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    StudentName VARCHAR(100),
    HouseID INT,
    WandType VARCHAR(100),
    FOREIGN KEY (HouseID) REFERENCES Houses(HouseID)
);

INSERT INTO Students (StudentID, StudentName, HouseID, WandType)
VALUES
    (1, 'Harry Potter', 1, 'Phoenix Feather 11" Holly'),
    (2, 'Hermione Granger', 3, 'Dragon Heartstring 10¾" Vine'),
    (3, 'Ron Weasley', 1, 'Unicorn Hair 14" Willow'),
    (4, 'Draco Malfoy', 4, 'Unicorn Hair 10¼" Hawthorn'),
    (5, 'Luna Lovegood', 3, 'Thestral Tail Hair 12" Cherry');

CREATE TABLE Houses (
    HouseID INT PRIMARY KEY,
    HouseName VARCHAR(100)
);

INSERT INTO Houses (HouseID, HouseName)
VALUES
    (1, 'Gryffindor'),
    (2, 'Hufflepuff'),
    (3, 'Ravenclaw'),
    (4, 'Slytherin');

Now Professor McGonagall has asked you to display all the data of students but she doesn't want to see their wand type as it is unnecessary for her.

CREATE VIEW StudentDetails AS
SELECT
    S.StudentID,
    S.StudentName,
    H.HouseName
FROM Students S
JOIN Houses H ON S.HouseID = H.HouseID;

Now whenever she needs to have a look at the student's data, she doesn't need to join the tables and choose the desired columns for her. She just needs to write a simple query like:

SELECT * FROM StudentDetails;

Now let's have a look at some basic operations that can be performed on views.

Inserting Data

Note that you cannot directly insert data into views if it involves data from multiple tables.

INSERT INTO Students (StudentID, StudentName, HouseID, WandType)
VALUES 
(6, 'Neville Longbottom', 2, 'Cherry Wood 13" Unicorn Hair');

Updating Data

Again you cannot directly update views.

UPDATE Student
SET HouseID = 1
WHERE StudentID = 3;

Deleting Data

Similar to updating, we cannot directly delete data from views.

DELETE FROM Students
WHERE StudentID = 6;

Chapter 5: Advantages

1. Data Abstraction: They help us present the required data and hide the irrelevant ones.

2. Data Transformation: Views can be used to transform and clean the data before presenting it to the users.

Chapter 6: Disadvantages

1. Manipulation Limitations: If the view contains data from multiple tables, we cannot update, insert or delete data directly from the views.

2. Performance Overhead: Views that involve multiple tables introduces performance overhead.

Chapter 7: Best Practices

1. Avoid creating complex views.

2. Use meaningful names.

3. Use them to control data access.

Final Chapter: Quiz

Now let's see whether you're as attentive as Hermione was in the 'Potions Class'.

1. What is the best practice when designing views in a database?

a) Include complex calculations in views to offload processing from applications.

b) Use views to break down complex security access controls.

c) Design views with clear, meaningful names and use them to abstract complex logic.

d) Create views for all possible report variations to avoid redundant queries.

1. What is the primary purpose of using views in a database?

a) To replace the need for base tables.

b) To store large amounts of raw data for analytics.

c) To provide a simplified and customized way of presenting data.

d) To eliminate the need for indexes in a database.

1. Which of the following is a potential disadvantage of using views in a database?

a) Improved query performance for complex queries.

b) Enhanced security through data abstraction.

c) Increased complexity for developers working with underlying queries.

d) Reduced data consistency due to direct manipulation of views.

So that was all about views. Don't forget to share your answers in the comment section. Let's see how many of them you got correct.
Best of luck with you data journey ^_^