Exploring SQL Programming and External Data Models in IST359

1. SQL ProgrammingAnd The External Model IST359 M005 Yang Wang ywang@syr.edu 342 Hinds http://blackboard.syr.edu

2. Acknowledgements and caveat • These slides draw liberally, with permission, from the following sources: • IST359 materials by Prof. Michael Fudge Jr. • Slides from Murach’s SQL Server 2008 book • Caveat (beware): At best, PowerPoint slides are only a pale limitation of the entirety of a class meeting. In IST359 in particular, the lectures will cover topics beyond what appears in these slides. Don’t rely on them as a substitute for attending class.

3. 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 T-SQL

4. Internal –vs- External Data Models INTERNAL EXTERNAL Mimics user functionality Abstracted & simplified in nature Implemented with views, stored procedures, functions • Optimal design for storing data • Complex & atomic in nature • Implemented with Tables, keys, constraints

5. The External Model == Abstractions • Views – abstractions for the SELECT statement. Simplifies complex queries with joins, etc. • Stored Procedures – abstractions for DML. Simplifies a complex series of INSERTS, UPDATES or DELETES. • User-defined Functions – abstractions for custom program logic where values need to be returned.

6. The External Model

7. Create and set a variable DECLARE @today as DATETIME SET @today = getdate()

8. Create a procedure CREATE PROCEDURE dbo.p_add_zipcode ( @zip as char(5), @city as varchar(50), @state as char(2))ASBEGIN INSERT INTO zipcodetable ( ZIPCD, CITY, ST) VALUES ( @zip, @city, @state); RETURN @@ROWCOUNT;END

9. Call a procedure EXECUTEp_add_zipcode ‘13039’, ‘Cicero’, ‘NY’ EXECUTE@success = p_add_zipcode ‘90210’, ‘Bev Hills’, ‘CA’ IF @success=1 PRINT ‘Zip code Added!’

10. Create a function CREATE FUNCTION dbo.lookup_zip( @city as varchar(50),@state as char(2)) RETURNS char(5)ASBEGINRETURN (SELECT ZIPCD FROM zipcodetableWHERE CITY=@city AND ST=@state)END

11. Call a function SELECT lookup_zip(‘Clay’,’NY’) as ‘zip code’

12. Let’s explore the external data model and T-SQL programming by example.  QUICK DEMO Fudge Fictitious Credit Union

13. Basic Data Model (Internal Model)

14. FFCU – Simplified User Stories • When a personsigns up, then they become a new customer. All new customers have an opening balance in their savings account. • A customer can depositfunds into their account. • A customer can withdrawalfunds from their account • A customer can have view balancestheir 2 types of accounts, Checking and Savings • A customer can transfer funds between their accounts. (checking to savings and vice-versa)

15. 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

16. p_signup • INSERT INTO Customers... • INSERT INTO Accounts… • (Savings with initial starting amount) • INSERT INTO Accounts… • (Checking)

17. p_transfer • UPDATE Accounts… • (Remove amount from account) • UPDATE Accounts… • (Add amount to account)

18. v_myaccounts • Show detailed account information for Customer, Account(s), and Type(s) of Accounts. • Create a view ffcu_vMyAccounts • Read the view select * from ffcu_vMyAccounts