Transcript Database Concepts - Syracuse University

SQL PROGRAMMING
AND THE EXTERNAL MODEL
Fact of the Week:
According to the 2010 outlook from the
BLS 286,600 new Computer, Network, and
Database administrator jobs will be added
in the next decade.
Learning Objectives




Understand the rationale and purpose of SQL
programming
Understand and explain the external data model
Learn how to create views, stored procedures, and
functions
Learn how to perform program flow control in TSQL
Where are we?
Internal –vs- External Data Models
INTERNAL



Optimal design for
storing data
Complex & atomic in
nature
Implemented with
Tables, keys,
constraints
EXTERNAL



Mimics user
functionality
Abstracted &
simplified in nature
Implemented with
views, stored
procedures, functions
Fudge Fictitious Credit Union
Let’s explore the external data model and T-SQL
programming by example. 
Basic Data Model (Internal Model)
FFCU – Uber-Simplified User Stories
1.
2.
3.
4.
5.
When a person signs up, then they become a new
customer. All new customers have an opening
balance in their savings account.
A customer can deposit funds into their account.
A customer can withdrawal funds from their
account
A customer can have view balances their 2 types
of accounts, Checking and Savings
A customer can transfer funds between their
accounts. (checking to savings and vice-versa)
External Model





p_signup – sign a user up for an account
p_deposit – deposit funds into user’s account
p_withdrawl – take out funds from a user’s account
p_transfer – transfer funds from one user account to
another
v_myaccounts – show balances for the user’s
account
p_signup


INSERT INTO Customers...
INSERT INTO Accounts…
 (Savings

with initial starting amount)
INSERT INTO Accounts…
 (Checking)
p_transfer

UPDATE Accounts…
 (Remove

amount from account)
UPDATE Accounts…
 (Add
amount to account)
v_myaccounts

Show detailed account information for Customer,
Account(s), and Type(s) of Accounts.