T4 Assessment

T4 Weighting: 40%

Practical Class Practical Test End-of-term test

Activities
5% 10% 25%
 T4 Assessment

T4 Weighting: 40%

Practical Class Practical Test End-of-term test

Activities
5% 10% 25%

• Assignment topics: Module 3 and 4

• Save and upload as a script file.
• Demonstrate the execution of your script file statements
in class during your timetabled lab sessions.
• Zero marks allocated if absent for demo.
A Guide to S Q L, Tenth Edition
Chapter Seven
Database Administration

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021
Cengage. All Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible
website, in whole or in part.
 Objectives (1 of 2)

• Understand, create, and drop views

• Recognize the benefits of using views
• Use a view to update data

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Introduction

• Database administration
• Process of managing a database
• Database administrator
• Person or entire group in a business organization charged with
managing the database

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Creating and Using Views (1 of 6)

• View
• A program’s or individual user’s picture of the database
• Base tables
• Existing, permanent tables in a relational database
• View is a derived table because the data in it is retrieved
from one or more base tables

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Creating and Using Views (2 of 6)

• Usually includes less information than the full database

• Simplifies data processing for the user
• Provides a measure of security by omitting sensitive information
• Unavailable to the user
• Defined by creating a defining query
• Indicates rows and columns to include in the view
• Use the CREATE VIEW command
• CREATE VIEW, followed by the name of the view, A S, and then a
query

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
Figure 7-1: Creating the DOGS View

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Table 7-2: Creating the DOGS Query

DOGS
ITEM_ID DESCRIPTION ON_HAND PRICE
AD72 Dog Feeding Station 12 $79.99
DT12 Dog Toy Gift Set 27 $39.99
LD14 Locking Small Dog Door 14 $49.99
LP73 Large Pet Carrier 23 $59.99
UF39 Underground Fence System 7 $119.99

• Data shown in Figure 7-2 does not exist in this form

• Query is executed and produces some sort of temporary
table
• To query a view, merge the query that created the view
with the query to select specific data
Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
Figure 7-3: Martina’s View of the ITEM Table (1
of 3)
ITEM
ITEM_ID DESCRIPTION ON_HAND CATEGORY LOCATION PRICE

AD72 Dog Feeding 12 DOG B $79.99

Station
BC33 Feathers Bird 10 BRD B $79.99
Cage
(12×24×18)
CA75 Enclosed Cat 15 CAT C $39.99
Litter Station
DT12 Dog Toy Gift Set 27 DOG B $39.99
FM23 Fly Mask with 41 HOR C $24.95
Ears

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
Figure 7-3: Martina’s View of the ITEM Table (2
of 3)
ITEM
ITEM_ID DESCRIPTION ON_HAND CATEGORY LOCATIO PRICE
N
FS39 Folding Saddle 12 HOR C $39.99
Stand
FS42 Aquarium (55 5 FSH A $124.99
Gallon)
KH81 Wild Bird Food 24 BRD C $19.99
(25 lb)
LD14 Locking Small 14 DOG A $49.99
Dog Door
LP73 Large Pet 23 DOG B $59.99
Carrier

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
Figure 7-3: Martina’s View of the ITEM Table (3
of 3)
ITEM
ITEM_ID DESCRIPTION ON_HAND CATEGORY LOCATION PRICE

PF19 Pump & Filter 5 FSH A $74.99

Kit
QB92 QB92 Quilted 32 HOR C $119.9
Stable Blanket 9
SP91 Small Pet 18 CAT B $39.99
Carrier
UF39 Underground 7 DOG A $199.9
Fence System 9
WB49 Insulated Water 34 HOR C $79.99
Bucket

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
Figure 7-4: Using the DOGS View

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Creating and Using Views (4 of 6)

Actual query executed by S Q L

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Creating and Using Views (5 of 6)

• Can assign column names that are different from those in

the base table
• Include new column names in parentheses, following the
name of the view
• Output will display new column names

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
Figure 7-5: Renaming Columns When Creating
a View

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
Figure 7-7: Creating the REP_CUST View

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
Figure 7-9: Creating the CRED_CUST View

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Creating and Using Views (6 of 6)

• Benefits of views
• Provide data independence
• Can often be used even after database structure changes
• Different users can view same data differently
• Customize display to meet each user’s needs
• A view can contain only those columns required by a given user
• Simplifies user’s perception of database
• Provides a measure of security

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Using a View to Update Data

• Benefits of views are for retrieval purposes only

• Updating data through a view is dependent on the type of
view

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Updating Row-and-Column Subset Views

• Row-and-column subset view that contains the primary

key of the underlying base table is updatable
• Cannot update when the primary key is not included

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Updating Views Involving Joins

• Can update when a view is derived by joining two tables

on the primary key of each table
• Cannot update when a view involves joining two tables by
matching the primary key of one table with a column that
is not the primary key
• Encounter more severe problems if neither of the join
columns is a primary key

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Updating Views Involving Statistics

• Most difficult to update

• Cannot add rows to a view that includes calculations

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Dropping a View

• Can remove a view that is no longer needed

• DROP VIEW command

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.
 Summary

• Views
• CREATE VIEW command
• Benefits
• Update issues
• DROP VIEW command

Mark Shellman, Hassan Afyouni, Philip J Pratt & Mary Z Last, A Guide to S Q L, 10th Edition. © 2021 Cengage. All
Rights Reserved. May not be scanned, copied or duplicated, or posted to a publicly accessible website, in whole or
in part.