Databases and SQL

1. Databases and SQL Exploring database design Designing a database with tables and primary keys Creating your own SQL Server tables Linking tables

2. Introduction • VWD allows you to have ACCESS (.mdb) or SQL-Server (.mdf) files. • SQL-Server provides better scalability and supports a greater number of simultaneous users. • ACCESS has a front-end. SQL-Server does not. It is just a server • DATA= collection of information in a structured way. • Field (entity) Record (collection of fields)Table (collection of records)Database (Collection of files)

3. View of Database Tables • Database Explorer • All tables automatically created start with aspnet_ • To see what the tables contain : • Right click Show table data • Don’t add or delete data manually to any of the aspnet_ files ALWAYS use the WEB SITE ADMINISTRATION TOOL to manage Membership Data

4. ASPNET TABLES

5. SQL-SERVER • SQL-Server is an RDBMS (Data + Relationships among them) • One-to-many, many-to-many • Example : One user can buy many products and one product can be bought by many users • QUESTION : How can I connect the two tables (i.e. Users and Items)? • ANSWER : By creating a new table (i.e. Transactions) • In real life we don’t draw lines between tables . We actually don’t even look at the tables. INSTEAD we create queries to manage them, update them, view them, insert records into them, delete records from them etc.

6. TABLE KEYS • Any time you extract data from the tables (i.e. Users, Items, Transactions) your query must contain fields of all 3 tables • The primary keys and the foreign keys that link the tables must be connected by join-lines in a query

7. Rules to many connections between tables • Every table has a primary key • The transaction table must contain at least 2 fields whose names and data types must be exactly the same as the fields in the tables Users and Items • This is because we have to realize the one-to-many association

8. SQL Server Tables (USERS) • Right Click on the tables ASPNET_USERS • Choose Open Table Definition

9. SQL – DATA TYPES • BASIC DATA TYPES • Text • Number (Scalar) • DateTime • Boolean • Binary (Pictures, sounds etc) • Other (Specialized Data Types : UniqueIdentifier, xml, timestamp, sql-variant) • Unicode Text (16-bit / character) • Non-Unicode Text (8-bit/character)

10. TEXT • char(n): Fixed-length non-Unicode text where n defines the actuallength of each string. • nchar(n): Fixed-length Unicode text, where n defines the actual lengthof each string. • varchar(n): Variable-length non-Unicode text where n defines the maximumlength of a string stored in the field, up to 8,000 characters. • nvarchar(n): Variable-length Unicode text, where n defines the maximumlength of each string, up to 4,000 characters.

11. Number data types • Integers: These are whole numbers with no decimal point. • Floating-point numbers: These types of scalar values can contain a decimalpoint as in 0.10 or 123.45 or 12,345,678.987654321. • There are also currencydata types specifically used for storing dollar amounts like $99.99.

12. INTEGERS • tinyint: From 0 to 255 • smallint: From –32,768 to 32,767 • int: From –2,147,483,648 to 2,147,483,647 • bigint: From –9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

13. MONEY • smallmoney: From about –$214,748.00 to about $214,748.00. • money: From about –$922,337,203,685,477.00 to about$922,337,203,685,477.00.

14. FLOATING POINT NUMBERS • Decimal • Precision (total # of numeric digits)- Maximum Precision is 38-digits • Scale (total # of digits after the decimal point) • Float • Numeric • Real

15. Summary of SQL-DATA TYPES

16. Summary of SQL-DATA TYPES

17. Summary of SQL-DATA TYPES

18. Summary of SQL-DATA TYPES

19. UniqueIdentifier • Defines a field that can store a GUID (Globaly Unique ID) • Example : 8a116cb2-4503-af83-870e-4f450bee923a • Watch the ASPNET_USERS Table • The ApplicationID is an ID that defines the whole WebSite • The UserID is created automatically by the CreateUserControl • To automatically assign new values • Go to the property Default Value or Binding  it is set to newid()

20. CREATE YOUR OWN TABLES • You don’t touch the aspnet_tables • But you want to create your own tables • Database ExplorerRight Click on the Tables FolderChoose Add New Table  Define the fields • Give each field a name • Choose the data type of the field • Define the primary key • SOS  You can let the SQL-Server to manage the primary key for you and keep it hidden from everyone

21. How to Set Primary Keys • Right Click on the field name • Choose  Set Primary Key or • Click the field nameChoose Table Designer  Set Primary Key from the menu • To have the records automatically numbered-- PROPERTIES • Click the + sign to the Identity Specification • Set the (Is Identity) to YES • Set The Identity Increment to 1 • Set the Identity Seed to (example 10000)

22. CREATING THE ITEMS TABLE • ItemID (7 char fixed length) 3 char-3 digit • ItemName (varchar) • ItemDescription (varchar) • ItemPrice (don’t include $ sign when insert records)

23. THE TRANSACTIONS TABLE • Create the table • SOS  Foreign Keys ( these are primary keys of other tables) • Insert the UserID field  Don’t make it to get newid() Let the property empty • Insert the ItemID  Don’t make it auto-numbered i.e. The Identity Specification property must be set to NO • Insert the DateOfTransaction field • Insert the SellingPrice field (to keep track of old prices) • Insert the IsPaid field • Insert the TransactionID (for the transaction code) • Do it automatically numbered i.e. IsIdentity YES, Increment  (1), Seed  (20000) • SOS the table does not need a PRIMARY KEY

24. POPULATING TABLES • When you first create a table, it will be empty. So when you right-click thetable name and choose Show Table Data, you’ll see an empty record with theword NULL in each field. • For example, right-clicking an empty Items table andchoosing Show Table Data displays that table’s contents. • But because thetable is brand new and empty, its contents consist of a single empty recordthat has the word NULL (which means, in effect, blank) in each field, as shown

25. POPULATING TABLES--Continue • You don’t want to manually enter records to a table • TIPS if you do insert them manually • When you enter data into a record that contains an automaticallygeneratedprimary key (for example, the ItemId field in the Itemstable), you must leave that field empty (NULL). The field gets filled witha value automatically, after you’ve filled in other fields in the record. • When you’re typing data into table fields, do not press Enter when youcomplete your entry. Instead, when you’ve finished filling one field andwant to move on to the next, press the Tab key. Or just click the nextfield in which you want to enter or edit data. • The little pencil that appears after you fill in a field isn’t part of the datain the field. It’s only there to tell you the field has been edited since thetable was first opened. • When typing dollar amounts, don’t type the dollar sign. There’s no needto bother typing commas, either; they’ll just be removed. For example, ifyou want to enter $1,234.56 into a money field, type 1234.56. In theDesign surface, all dollar amounts have four digits to the right of thedecimal point and no dollar sign. Don’t let that bother you. As long asyou’re working in Database Explorer, how the data looks isn’t important.

26. TIPS CONTINUE • If you don’t have data to fill a field yet, you can leave the field empty(NULL), provided you allowed nulls in the design of the table. If youdidn’t, and you find that the table really needs to allow nulls in a field, goback to the table’s definition and select the Allow Nulls check box forthat field. • After you’ve filled in all the fields for a record, you can press Tab orEnter to complete that record. SQL Server validates the data and savesthe record. If there are any problems, the record won’t be saved. Instead,you’ll see an error message (stating No row was updated) with adescription of the problem. You have to click OK in that message boxand fix the problem. This fix often involves clicking the faulty value andthen pressing Escape to “undo” the entry currently in the column.

27. Linking Tables • Although the tables in a database store data, the way you get exactly the datayou need, and only the data you need, when you need it, is through StructuredQuery Language, abbreviated SQL • Examples • SELECT * FROM Items • SELECT UserId, UserName FROM aspnet_Users • SELECT * FROM Items WHERE ItemId = 10002 • SELECT ItemName, ItemDescription FROM ItemsTable WHERE ItemId = 10002

28. Don’t Worry about SQL • You don’t actually have to write the SQL statements yourself.And that’s a good thing — SQL statements can be far more complex than theexamples shown here. • To avoid all that tedious and error-prone typing, youcan use the Query Builder to create your complex SQL statements. TheQuery Builder lets you pick and choose what you want to extract from yourtables with ease — and with a more intuitive graphical user interface. • Choose options, the Query Builder then writes the appropriate SQLstatement for you.The Query Builder appears automatically whenever you perform some actionthat requires getting data from a database. • For now, we use the Query Builder tolink tables together into a view.

29. Creating a view • In SQL Server, a view is a saved query. • When you set up your site forMembership, VWD automatically created several views. They are listed underViews in Database Explorer. The name of each automatically-created viewstarts with vw_aspnet_ as shown in Figure

30. Creating a view - Continue

31. Creating a view - Continue • Never delete, change, or rename any view whose name starts with vw_aspnet_.Those views are created by the membership system, for the membershipsystem, and they really don’t like being monkeyed around with. • To illustrate using the Query Builder to link users, transactions, and items, Let’s create a view named UsersAndItemsView. • You create a view as you wouldany other object: Right-click the Views folder in Database Explorer andchoose Add New View. A dialog box named Add Table opens. • The first step in building the query is to choose the tables from which thequery will get data. If there’s a many-to-many relationship among selectedtables, the view must also include the Transactions table that provides thelink between tables. • To add a table to the query, click its name in the AddTable dialog box, and then click OK. • (For this example, add theaspnet_Users, Transactions, and Items tables to the view.) Click the Closebutton in the Add Tables dialog box after choosing your tables.

32. Creating a view - Continue

33. Creating a view - Continue • Choose which fieldsyou want to see from these tables.You choose which fields you want the viewto display by clicking the check box next to each desired field name. As youdo so, each field name you check appears in the Criterion pane, just belowthe Diagram pane. • Below the Criterion pane is the Show SQL pane, which shows the actual SQLstatement that the Query Builder creates as you go.

35. Testing a Query • While you’re designing a query, you can test it out at any time using any ofthese methods: • Right-click some empty space in the Design surface and chooseExecute SQL. • Press Ctrl+R. • Click the Execute Query button (red exclamation mark) in the toolbar. • Choose Query Designer➪Execute Query from the menu bar. • The results of executing the query appear in the Results pane at the bottomof everything else.