Special Topics in Database

1. Special Topics in Database

2. Database-stored code

3. Accessing Databases from Application Programs • Options include: • Script Files  already covered • APIs  will cover end of course • Database-Stored Code  this topic

4. Database-Stored Code • Program code stored in database • T-SQL • CLR Languages (C#, VB.net, …) • Good for: • sharing code • performance • Enforcing security, business rules, audits Database Application Program ------------------- execute A ------------------- execute B Procedure A ------------------- SQL… SQL...... ------------------- end A

5. Database-Stored Code, con’t... • Stored Procedure • T-SQL block • Procedural programming features • stored in database • * invoked by name • * accepts arguments • Database Trigger • T-SQL block • Procedural programming features • stored in database • * invoked by DML (Insert, Update, or Delete) • * accepts no arguments

6. Create Stored Procedure: Template CREATE PROCEDURE <procedure_name> [parameter_information] AS DECLARE /* program variables defined here */ BEGIN /* program logic goes here */ END

7. Example 1: Parameters, Variables, Program logic, Printing • /* retrieves a specific customer record based on customer ID */ • /* if the customer lives in the state of FL, then add “-1111” after the zip code & print a message to user */ • CREATE PROCEDURE add_ones_to_zip_and_print • @desired_customer numeric(5) = null • AS • DECLARE • @ID numeric(4), • @state varchar(2), • @pcodevarchar(12); • BEGIN • SELECT @ID = customer_id, @pcode= postal_code, @state = state • FROM customer_t • WHERE customer_id = @desired_customer; • IF @state = 'fl' • BEGIN • SET @pcode = @pcode + '-1111' • UPDATE customer_t SET postal_code = @pcodeWHERE customer_id = @desired_customer; • print 'cust#: ' + convert(varchar, @desired_customer)+' zipcode changed to: ' + @pcode; • END; • END; • GO

8. Example 1: Invoke and Verify /* statement below included ONLY so I can undo changes after demo. You DO NOT need to do this */ Set implicit_transactions on GO /* show customer 9's record before calling procedure */ Select * from customer_t where customer_id = 9; exec add_ones_to_zip_and_print@desired_customer = 9; -or- exec add_ones_to_zip_and_print 9 GO /* check to see if procedure updated customer 9's record */ Select * from customer_t where customer_id = 9; GO /* statement below included ONLY so I can undo changes after demo. You DO NOT need to do this */ Rollback; Set implicit_transactionsoff

9. Multiple-Record Retrievals • Require cursors • Declare CURSOR • OPEN cursor • FETCH data from cursor • CLOSE & DEALLOCATE cursor • Use control structures to process each record • WHILE <expression> … [BREAK can be used to force exit from WHILE loop] END; • IF <expression> <statement> ELSE <statement> • IF <expression> BEGIN <statements> END; ELSE BEGIN <statements> END;

10. Example 2: Cursor /* code continued here… */ WHILE @@FETCH_STATUS = 0 BEGIN -- loop thru each customer record IF @cstate = 'fl' BEGIN -- processing and displaying old/new zip PRINT 'id: ' + convert(varchar, @cid) + ' , name: ' + @cname; PRINT 'old zip: ' + @pcode; SET @pcode= @pcode + '-1111' UPDATE customer_t SET postal_code = @pcode WHERE customer_id = @cid; PRINT 'new zip: '+@pcode; PRINT ' ======================= '; END; -- processing and displaying of zip FETCHNEXT FROMcustcursorINTO @cid,@cname,@caddr,@ccity,@cstate,@pcode, @cowner,@corders; END; -- looping through customer records CLOSE custcursor; DEALLOCATEcustcursor; END; -- end stored procedure GO /* retrieve customer records based on NAME; if customer(s) live in FL, ‘-1111’ is added to zip */ CREATE PROCEDURE getcust @name varchar(25) AS DECLARE custcursorCURSOR FOR SELECT * from customer_t WHERE customer_name like '%' + @name + '%'; DECLARE @cid numeric (11,0), @cnamevarchar(25), @caddrvarchar(30), @ccityvarchar(20), @cstatevarchar(2), @pcodevarchar(12), @cowner numeric(11), @corders numeric(4); BEGIN -- logic OPENcustcursor; --find customers meeting criteria IF @@CURSOR_ROWS = 0 BEGIN RAISERROR ('no customer found', 10,1); RETURN; END; FETCHFROMcustcursorINTO @cid, @cname, @caddr, @ccity, @cstate,@pcode, @cowner, @corders;

11. Example 2: Invoke and Verify Set implicit_transactions on GO Select * from customer_t where customer_name like '%furn%'; Exec getcust'furniture' GO Select * from customer_t where customer_name like '%furn%'; GO Rollback; Set implicit_transactions off

12. Summary: Steps to Create / Invoke Procedures • Store code that creates procedure in a script • Run and debug script in SSMS • Creates & stores procedure in database • Invoke procedure • Issue "execute" command • Include parameter values where required

13. Triggers • Common Uses: • Implementing RI • Complex defaults • Interrelation constraints/updates • Updating views • Auditing business activities • 2 Kinds: • After • Instead of

14. Create Trigger: Template CREATE TRIGGER <trigger_name> ON <table_name> <AFTER | INSTEAD OF> <INSERT [,] UPDATE [,] DELETE> AS DECLARE /* program variables defined here */ BEGIN /* program logic goes here */ END

15. Example 1: Maintain Counter /* maintain the orders placed counter in the customer table */ /* add 1 to the counter each time a customer places a new order */ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'add_order_placed' AND type = 'TR') DROP TRIGGER add_order_placed; GO CREATE TRIGGER add_order_placed ON order_t AFTER INSERT AS DECLARE @customer AS numeric(11,0) BEGIN SELECT @customer = customer_ID FROM INSERTED; UPDATE customer_t SET orders_placed = orders_placed+1 WHERE customer_t.customer_ID = @customer; END; GO

16. Example 1: Fire/Verify Trigger Set implicit_transactions on GO /* check status of customer’s orders_placed counter before placing new orders */ select customer_id, orders_placed from customer_t where customer_id=10; /* fire insert trigger by placing orders for customer #10 */ insert into order_t values (9900, '10-OCT-01', 10); insert into order_t values (9901, '10-OCT-01', 10); insert into order_t values (9902, '10-OCT-01', 10); /* issue select statement to verify that trigger updated orders_placed counter */ select customer_id, orders_placed from customer_t where customer_id=10; GO Rollback; Set implicit_transactions off

17. Example 2: Setting Default Value /* when a new product is added, if the standard price is NOT given, then set the product’s standard price to either: */ /* the highest standard price of a product in the same product line if one exists, OR to $300 */ IF EXISTS (SELECT name FROM sysobjects WHERE name = 'set_standard_price' AND type = 'TR') DROP TRIGGER set_standard_price; GO CREATE TRIGGER set_standard_price ON product_t AFTER INSERTAS DECLARE @newproductlineid as numeric(11,0), @newstandardprice as numeric(6,2), @higheststandardprice as numeric(6,2), @productID as numeric (11,0) BEGIN SELECT @productID=product_ID, @newproductlineid = product_line_id , @newstandardprice = standard_price FROM INSERTED; IF @newstandardprice IS NULL BEGIN /* find the highest standard price for existing products in the product line of the new product */ SELECT @higheststandardprice = MAX(standard_price) FROM product_t WHERE product_line_id = @newproductlineid; /* if there was a value resulting from the above query, use that value for the price of the new product */ IF @higheststandardprice IS NOT NULL SET @newstandardprice = @higheststandardprice /* if there was no value retrieved from the above query, then set the price of the new product to $300 */ ELSE SET @newstandardprice = 300; UPDATE product_t SET standard_price = @newstandardprice WHERE product_id = @productID; END --end IF END; -- end program GO

18. Example 2: Fire/Verify Trigger Set implicit_transactions on GO /* issue select statement to view product table before adding new products */ select * from product_t; /* fire insert trigger adding new products to the product_t table*/ insert into product_t values (999,null,null,null,null,20001); insert into product_t values (998, null, null, 123, 3, 33333); Insert into product_t values (997,null,null,null,null,44444); /* issue select statement to verify that trigger updated standard prices for products */ select product_id, product_line_id, standard_price from product_t where product_id>900; GO Rollback; Set implicit_transactions off

19. Web-database connectivity

20. Active Server Pages • Server-side technology • Enables dynamic, interactive, data-driven web pages • Scripting OR compiled languages • VBScript, VB, C# • Jscript, Java • Perl • Etc… • HTMLfor display/gathering of data • ADOfor access to data

21. ASP vs. ASP.net

22. Basic Steps • Create New ASP.net project • (Optional) Create or Customize Site.Master template • (Optional) Create or Customize Default.aspx page • Create New webpages (.aspxfiles) • Labels • Data Controls • Buttons • Create Code-Behind (.vb) files • Database access • Data manipulation • Test/Debug/Deploy

23. Database Access Steps • Define program variables for data access objects • Connection Dim connObject = New SQLClient.SQLConnection(…..) • Query / Command Dim cmdObject AS New SQLClient.SQLCommand (….., …..) • Data Container Dim contObject AS SQLClient.SQLDataReader • Data Reader • Data Adapter… • Create connection to the database • Open connObject.Open() • Run query/command and store data in container • Execute contObject = cmdObject.ExecuteReader • Display, Maintain data via Data “Container” Controls • Grid View • Details View • Form View • List View… • Release objects • ClosecontObject.close() connObject.close()

24. Create New ASP.net Project

25. Example 1: View Student Info: Textbox, Gridview • Prompt user for student search criteria • Use text boxes for entering Student search criteria • Display student information based on search criteria • Use GridView data control for displaying Student data

26. Example 1: Create Webpage

27. Example 1: Create Webpage, cont…

28. Example 1: Configure the Gridview Control

29. Database Access Code 'THIS CODE GOES IN THE VB FILE for the View Student Information button lblStatus.Visible= False '(1) create an ADO connection object to establish connection to the database 'first define a text string that describes database connection information Dim connectionstring As String = "server=hsb-mis-sql; database=mis4340; integrated security=sspi" 'create the ADO connection object that will establish a connection to the database Dim myConnection = New SqlClient.SqlConnection(connectionstring) '(2) make the connection to the database myConnection.Open() '(3) setup queries that get student data from database based on values the user entered Dim querystring1 As String = "select * from student where stud_id='" & txtStudentID.Text & "';" Dim querystring2 As String = "select * from student where firstname like '%" & txtFirstName.Text & "%' and lastname like '%" & txtLastName.Text & "';" Dim querystring 'Decide which query to use based on which values the user entered If (txtLastName.Text <> String.Empty Or txtFirstName.Text <> String.Empty) Then querystring = querystring2 Else querystring = querystring1 End If '(4) create an ADO command object that will contain the query command to be executed by the database Dim myCommand As New SqlClient.SqlCommand(querystring, myConnection) '(5) create an ADO container object that will hold data retrieved from database Dim myDatareader As SqlClient.SqlDataReader '(6) execute the command and store results in the datareader container object myDatareader = myCommand.ExecuteReader 'if datareader container is empty, display error message If Not myDatareader.HasRows Then lblStatus.Text = "No Student Found" lblStatus.Visible = True myDatareader.Close() Exit Sub End If '(7) bind control to data source, then display data to user StudentGrid.DataSource = myDatareader StudentGrid.DataBind() StudentGrid.Visible = True '(8) close objects myDatareader.Close() myDatareader = Nothing myCommand = Nothing myConnection.Close() myConnection = Nothing

30. Data Bind/Display Code 'THIS CODE GOES IN THE SAME VB CODE for View Student Information button '(7) bind control to data source, then display data to user StudentGrid.DataSource = myDatareader StudentGrid.DataBind() StudentGrid.Visible = True

31. Example 2: View Student Info: add Dropdown • Prompt for search criteria • Use Dropdown web control for specifying Student ID • See http://www.dotnetcurry.com/ShowArticle.aspx?ID=221 for how to implement “cascading dropdowns” for Lastname and Firstname

32. Wizard: Configure the Database Connection

33. Wizard: Configure the SQL Command Will give us a field which is the concatenation of student ID and first/last name for ease of lookup Will give us student ID as the key field

34. Wizard: Bind Data to Dropdown Name of the data source (connection and query) we just created The field from the query that we want displayed in the dropdown The field from the query that we want to use as the selected value from the dropdown

35. Result: ASP.net Generates "Code" to Access Data Will be used as the Data Container object and dropdown control will be set to its values Will be used by a Command object Used DropdownList control & configured its data source via Wizard Will be used by Connection object Ensure DropDownList control has this name

36. Modify Database Access Code • In VB Code for View Student Information button, retrieve the selected student from the new dropdown instead of from the text box • i.e., replace txtStudentID.Text with SelectedStudentID.Text

37. Example 3: View Student Info: add Data Controls, cont… • Add dropdown option for "All Students"

38. Modify Database Access Code • Add BOLD code below to the aspx file: <asp:DropDownList ID="SelectedStudentID" runat="server" DataSourceID="SqlDataSource1" DataTextField="studentinfo" DataValueField="stud_id" AppendDataBoundItems = "true"> <asp:ListItem Selected="True" Value="-1">All Students</asp:ListItem> </asp:DropDownList> • In the “View Student Information” button VB code, add third querystring to select all rows and columns from student table Dim querystring3 As String = "select * from student;" • Also in the “View Student Information” button VB code, update IF-Then-Else in VB code to check for "-1" value (i.e., user selected “All Students”) and assign appropriate query If (txtLastName.Text <> String.Empty Or txtFirstName.Text <> String.Empty) Then querystring = querystring2 ElseIf (SelectedStudentID.Text = "-1") Then querystring = querystring3 Else querystring = querystring1 End If

39. Data integration

40. Metadata Management • System Catalog • Part of DBMS • Data Dictionary • Typically passive • Extension of catalog metadata • Information Repository • Master Data Management

41. Master Data Management • "Ensuring the currency, meaning, and quality of reference data within and across various subject areas" • Identify • Common Data Subjects • Common Data Elements • Sources of "the truth" • Cleanse • Update applications to reference Master Data repository • Ensures consistency of key data (not ALL data) throughout organization

42. Cloud computing

43. Cloud Computing • Business Model • Computing resources on demand • Need-based architectures • Internet-based delivery • Pay as you go • History (VERY high-level and approximate) Cloud Computing Time-sharing Utility Computing Personal Computers Virtual Machines WWW Grid Computing 50's 60's 70's 80's 90's 2000's

44. Cloud Computing Services • Impacts to Data(base) Administration

45. Big data

46. Complex Data Landscape NOTE: This diagram is for effect ONLY—it is incomplete (e.g., no MDDB, no OODB) AND contains some inaccuracies

47. Big Data • Big Data = more than you're able to effectively process • Influenced by Mobile, Social Networking, Web analytics, RFID, Atmospheric, Medical Research data, … • Traditional RDMS Problems • Transaction-focus • Requires schema  maintenance issue • ACID-focus • Requires locks, db constraints, joins  performance & availability issues • "Relatively" small amounts of operational data • Exceptions require complex, $ actions  scalability issue Reference: http://www.slideshare.net/dondemsak/intro-to-big-data-and-nosql

48. Big Data Solutions • Hadoop • Good for storing and retrieving large amounts of (mostly unstructured) data • HDFS: data distributor • MapReduce: request distributor • Columnar Databases • Good for data warehouses, analytics • Files contain all column values vs. all row values • NewSQL Databases • Good when high scalability needed with relational DBMSs • Re-written, highly optimized storage engines • "Sharding" (i.e. horizontal partitioning across multiple DB instances) • Distributed query processing • In-memory databases • NoSQL Databases…

49. Big Data Solutions, cont… • NoSQL Databases • Focus (in most cases): • Scalability via Physical Distribution of data • No fixed schema • "Individual query systems" instead of SQL • Support for semi- and un-structured data • Some provide consistency • Apache's Hbase • Amazon's DynamoDB • Most provide "eventual consistency" • Google's BigTable • Facebook's Cassandra • Amazon's SimpleDB • Use a variety of data models…

50. Big Data/NoSQL, cont… • NoSQL Physical Data Models • Column (Family) Store • Key Value Store • Document Store • Advantages • Highly scalable • Support for semi- and un-structured data • Data Model (schema) does not have to be defined up-front • Many are open source • Cloud options available (e.g., Amazon's SimpleDB) • Disadvantages • No common query language • Data inconsistency • Reliance on client applications for data validation, consistency, etc…