DRAFT: This module has unpublished changes.

*~Applications Software ~*

Standard Query Language - Database

 

For this portion I designed a database that was for a false company called MiniQuest’s PRIME TIME SYSTEM.  This database tracks all customers, the types of subscription they purchased and incorporated a billing system as well.

  

Data Dictionary: DataDictionary.xlsx

 

Entity Relationship Diagram: ERD.docx

 

Script: This is the entire script for the database and incorporates a few reports: Final_Script.txt

What I've learned and displaying the learning through creating a VIEW using SQL.

 

When I first took this course I was not aware of the time and focus that was necessary to create a simple database. What is necessary to understand is that each step is critical and one error in the planning phase can lead to errors throughout the program. It is essential to stay focused and to debug after each stage to ensure each submission is working as it should. 

 

You can use the CREATE statement to create other types of objects such as VIEWS, SEQUENCES, INDEXES and USERS.

There are many reasons for creating VIEWS. Two of the most common are

  1. To provide quick access to multiple columns of data from multiple tables
  2. To hide the underlying data structure from users

To demonstrate this, let’s first look at a simple view of customers in the Leebooks database.

CREATE VIEW vwCustomer1 AS

SELECT Customer#, LastName, FirstName,

Address, City, State, Zip

FROM Book_Customer;

 

In the above statement, I have used the CREATE command followed by the database object (VIEW) that I want to create. I then give the object a name. In this case, I prefaced my object with the initials “vw” so it can easily be distinguished from a base table. That is because a view is considered to be a virtual table. That means you can run SELECT statements against it just like the actual tables that are contained in the view.

 

After the name of my view, I have to use the keyword “AS”  to define my view. The Select statement that follows is the view definition.

 

SELECT * FROM vwCustomer1;

 

This will return the all records from my view.  Because it acts like a base table, I can also use it to insert a new record.

 

INSERT INTO vwCustomer1

VALUES(1021,'Zane', 'Ibarra', '203 S. Mill Ave.','Tempe', 'AZ',85281);

 

When I do another select, I will see the new record I added. That is because vwCustomer1 is an updateable view.  Not all views are updateable.  If there is a GROUP BY or an ORDER BY, it is not updateable. Also, if primary key values are not unique, it is also not updateable.

 

DRAFT: This module has unpublished changes.