SQL, Data Storage Technologies, and Web-Data Integration

1. SQL, Data Storage Technologies, and Web-Data Integration Week 3

2. Today’s Agenda • Questions? Review • Physical Database Design • Connecting to MySQL • Introduction to SQL

3. Week 2 Review • 2nd Normal Form • Attributes must be dependent on the unique attributes • 3rd Normal Form • Non unique attributes cannot be dependent on each other • Physical database design • Entities become tables • Attributes become columns • Unique IDs become primary keys • Relationships become foreign keys

4. Data Types • Each database has its own data types • Most share a common core of data types, including integers, character strings, and dates. • MySQL has 36 different data types

5. Data Types • Numeric Types • store numeric data such as integers and floating point numbers • Modifiers: • UNSIGNED: 0 to 255 instead of -128 to 127 • AUTO_INCREMENT: integers only, one per table

6. Data Types • Numeric Types Cont.

7. Data Types • String Types • store textual data. • Modifiers: • BINARY: allows case-sensitive searching

8. Data Types • String Types Cont.

9. Data Types • Date types • store dates and times

10. Data Types • Complex data types • Enumerations (ENUM) • list of predefined strings, value must be one of them. • Sets (SET) • list of predefined strings, value can be any combination of them.

11. Current Physical Database Design

12. New Physical Database Design • Donor table: includes types!

13. Physical Database Design • Choosing Column Options • Column options help enforce data integrity • Can make the programmer’s job easier • Which makes the DBA’s job easier • Column Options • NULLs allowed, default values, auto incrementing values, and keys

14. Column Options • NOT NULL • By default, columns can contain a NULL instead of a value; this overrides that behavior • Requires that some value always exists in that column for any given row of data. • Will cause a database error if the programmer tries to add a NULL to that column. • What should be NOT NULL in our donation ER diagram?

16. Column Options • DEFAULT value • If a user doesn’t supply a value for a column, you can specify a default value • Example: For a local organization, State and Country might default to WA, USA • Should there be any DEFAULT columns in our donation database?

17. Column Options • AUTO_INCREMENT • Provides a default value to an INTEGER column • The value will automatically be incremented for each insert • Only one column per table can have this option. • Great option for an internal (meaning not shown to a external user) primary key

18. Column Options • PRIMARY KEY • Creates an index on the column • Forces each column entry to be unique from all other column entries • Automatically is NOT NULL • UNIQUE • Just like PRIMARY KEY, without the special name.

19. Physical Database Design • Now includes column options!

20. Relational Database Schema • We now have our database schema • Whereas our E/R Diagram was very abstract, we now have a very concrete, relational design

21. In Class Exercise • Turn your Data Models into Database Schemas: • Recipes • Dating Service • Bookstore • Photo Sharing • Movie Collection

22. The MySQL RDBMS • Connecting to MySQL • Using SQL commands

23. Client/Server Architecture Client Server • A distributed system of computing between two or more programs • Clients initiate requests, servers respond to requests • MySQL is an example of a server • The World Wide Web is an example of a vast client/server application

24. Multiple Clients • Most servers, such as MySQL, support multiple clients connecting at the same time

25. An example GUI Client

26. Connecting to MySQL • Log in to the course server: • SSH homer.u.washington.edu • Username: student8 • Select ‘O’ then ‘W’ • Log in to MySQL • $ mysql/bin/mysql –u uwnetid –p

27. Using the mysql Client • We are now connected as a client of the MySQL server. • The mysql prompt acts just the Unix prompt (almost) • Commands must end with a semicolon • MySQL can hold more than one database • How do we know what they are all called? • mysql> show databases;

28. Using Your Database • Each one of you has your own database in the MySQL server, named with your UW NetID • Tell MySQL which database you want to use for your commands • mysql> use database_name; • where database_name is your UW NetID

29. Conventions • Table names in StudlyCaps • case sensitive in newer versions of MySQL • Columns names in all_lower_case • except for PRIMARY_KEY_ID and FOREIGN_KEY_ID • not case sensitive • In general, SQL user defined names must: • follow the file naming rules of the underlying OS • should only contain only letters, underscores, or digits • not be a SQL reserved word (no columns named “null”, for example)

30. Creating a Table • SQL Syntax • CREATE TABLE TableName (create_clause, …) • Where create_clause = column_name DATATYPE [MODIFIER ]*

31. Creating a Table mysql> CREATE TABLE Donor ( DONORID INT UNSIGNED PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, email VARCHAR(255), phone_number VARCHAR(14), address VARCHAR(255) );

32. Creating a Table • If everything went smoothly, you should see: • Query OK, 0 rows affected (0.00 sec) • If something went wrong, you’d see something like: • ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'your SQL statement here' at line 1

33. Describing and Destroying Tables • Listing all the tables • mysql> SHOW TABLES; • Describing an existing table • DESCRIBE TableName; • mysql> DESCRIBE Donor; • Destroying a table • DROP TABLE TableName; • mysql> DROP TABLE Donor;

34. Basic SQL Commands • INSERT • SELECT • Being choosy • DELETE • UPDATE

35. Inserting Data • SQL Syntax • INSERT INTO TableName [ (column,…) ] VALUES ( values, … ); • INSERT INTO TableName SET column=value, ….; • Example • mysql> INSERT INTO Donor (name, email) VALUES (‘Jake Johnson’, ’jake@johnson.com’)

36. Inserting Data • All values must be quoted except for numerical data types • “Jake Johnson” • “2004-10-07” • Quotes must be escaped • “He said, \”yes,\” and continued along.” • ‘Jake\’s House of Blues’

37. Inserting Data • Inserting multiple rows • mysql> INSERT INTO Donor (name, email, phone_number) VALUES (‘John Smith’, ’jsmith@test.com’, ’206-555-1212’), (‘Betty Wilson’, ’betty@crocker.com’, NULL); • Use NULL when you don’t have the value • Not part of the ANSI SQL Standard • Why would this form be bad to use in an application?

38. Inserted Data • A row of data in a table is called a tuple. • Schema vs. Instance • Schema is a description of a table, such as name and attributes (fairly static) • Donor(DONORID, name, email, address, phone) • An instance is a tuple or set of tuples for that table (subject to frequent change) • (31, “Jake Johnson”, “jake@johnson.com”, NULL, NULL)

39. Selecting Data • Syntax • SELECT column_name,… FROM TableName • Use a “*” in place of the column_name list to retrieve all columns • Examples: • Show me all the data stored about our donors • mysql> SELECT * FROM Donor; • What are all the names of all our donors? • mysql> SELECT name FROM Donor;

40. Being More Specific • Syntax • SELECT column_name,… FROM TableName WHERE statement • Example • Show me all the donors with the name “Jake Johnson” • mysql> SELECT * FROM Donor WHERE name = ‘Jake Johnson’

41. Conditional Operators

42. The LIKE comparison • Uses wildcard characters to match column data • ‘_’ represent any one character • SELECT name FROM Donor WHERE name LIKE ‘_ob’ • Matches for “Bob”, “Rob”, “Job”, etc. • ‘%’ represents any number of characters • Select name FROM Fruit WHERE name LIKE ‘%apple’ • Matches for “Pineapple” and “Apple”

43. More LIKE • Find all donors whose name starts with a "J”: • mysql> SELECT * FROM Donor WHERE name LIKE ‘J%’; • Use “AND” or “OR” to add multiple restrictions in your WHERE clause • Find all donors whose name starts with a “J” and have a 206 area code • mysql> SELECT * FROM Donor WHERE name LIKE “J%” AND phone_number LIKE “206%”;

44. Other Constraints • ORDER BY • LIMIT • GROUP BY • Fun with aggregates • HAVING

45. Ordering Your Data • Syntax • SELECT column_name,… FROM TableName ORDER BY column_name, … • Example • List all donors in alphabetical order • mysql> SELECT * FROM Donor ORDER BY name;

46. More Ordering • Feel free to combine with other constraints, such as WHERE • mysql> SELECT * FROM Donor WHERE name like ‘J%’ ORDER BY name; • You can order by more than one column • SELECT * FROM Donor ORDER BY lastname, firstname • Swap the order with DESC or ASC • mysql> SELECT * FROM Donor WHERE name like ‘J%’ ORDER BY name DESC;

47. Limit Your Data • Syntax • SELECT FROM column_name,… FROM TableName LIMIT number_of_rows • Example • Show me just two “random” donors • mysql> SELECT * FROM Donor LIMIT 2;

48. More Limiting • You can use LIMIT start, number_of_rows to specify which rows from the result set to return • Show me all the donors whose name starts with J, in alphabetical order, one at a time • mysql> SELECT * FROM Donor WHERE name LIKE ‘J%’ ORDER BY name LIMIT 0,1 • mysql> SELECT * FROM Donor WHERE name LIKE ‘J%’ ORDER BY name LIMIT 1,1

49. Grouping your data • The GROUP BY clause groups data together so that aggregate functions can be performed on it. • Very common for reports and statistics • More interesting with large sets of data

50. Piping SQL commands to MySQL • Sometimes we have a big file of SQL commands that we want to run. • https://courses.washington.edu/wtcampus/spring/examples/sql/donation.sql • Download “donation.sql”, and SCP to your root dante folder • Quit your mysql client application • mysql> quit; • Use a Unix “pipe” to send the file of commands to MySQL • $ mysql/bin/mysql –u uwnetid –p uwnetid < donation.sql • The “<“ operator takes all the lines of text from donations.sql, and sends them to MySQL