LIS569 – Database Systems Dr. Jianqiang Wang Introduction to SQL and MySQL

1. LIS569 – Database SystemsDr. Jianqiang WangIntroduction to SQL and MySQL Week 8, Mar. 4, 2013

2. Agenda • Team project update • SQL history, purposes, and benefits • Basic SQL syntax • MySQL environment and commands involving only one table

3. Objectives • Define terms • Interpret history and role of SQL • Define a database using SQL DDL • Write single table queries using MySQL commands

4. SQL Overview • Structured Query Language (SQL) • The standard for relational database management systems (RDBMS) • RDBMS: A database management system that manages data as a collection of tables in which all relationships are represented by common values in related tables

5. History of SQL • 1970: E. Codd develops relational database concept • 1974-1979: System R with Sequel at IBM • 1979–Oracle markets first relational DB with SQL • 1986–ANSI SQL standard released • 1989, 1992, 1999, 2003: Major ANSI standard updates • Current: SQL is supported by most major DB vendors

6. Purpose of SQL Standard • Specify syntax/semantics for data definition and manipulation • Define data structures and basic operations • Enable portability of database definition and application modules • Specify minimal (level 1) and complete (level 2) standards • Allow for later growth/enhancement to standard

7. Benefits of a Standardized Relational Language • Reduced training costs • Productivity • Application portability • Application longevity • Reduced dependence on a single vendor • Cross-system communication

8. SQL Environment • Catalog • A set of schemas that constitute the description of a database • Schema • The structure that contains descriptions of objects created by a user (base tables, views, constraints) • Data Definition Language (DDL) • Commands that define a database, including creating, altering, and dropping tables and establishing constraints • Data Manipulation Language (DML) • Commands that maintain and query a database • Data Control Language (DCL) • Commands that control a database, including administering privileges and committing data

9. A simplified schematic of a typical SQL environment

10. DDL, DML, DCL, and the DB development process

11. Introduction to MySQL • Most popular open source, cross-platform RDBMS • Commercial examples: WordPress, Drupal, Google, Facebook, Twitter, Wikipedia • Web application development: downloadable in many ways including LAMP and XAMPP • Owned by Oracle Corporation • Source code available under GNU General Public License • Many frontend tools to use MySQL • Graphical: Navicat, phpMyAdmin, … • Command line: mysql

12. MySQL on gsestudent6 Server • Gsestudent6 server has MySQL server, Apache Web server, and PHP environment • Hosts web-accessible MySQL database • Web publishing and PHP programming • will be covered in later weeks • Developing and accessing MySQL databases via Navicat Lite • Week 8-11

13. Using gsestudent6 MySQL via Navicat • Launch Navicat Lite • Assuming Navicat Lite has been installed on the local computer, look for icon on the desktop and double click it • This window will display

14. Using gsestudent6 MySQL via Navicat • Set up a connection • Select “Connection”  “MySQL” • Fill information in the dialogue window User name: your UBIT name Password: password • Click OK

15. Using gsestudent6 MySQL via Navicat • You’ll see the newly created connection • Double click it to open the connection

16. Using gsestudent6 MySQL via Navicat • You’ll see three databases, one of which is named after your UBIT name, and that’s the one you’ll use to practice SQL commands (Carolyn’s is used as the example here)

17. Change your Password • Select “Tools”“Console” to open a console (command line) window • In the console window, type in • SET PASSWORD = PASSWORD('mypass');(where mypass is the password you want to use) • Hit “Enter” key to run the command • Close the console • Select “File”  “Close Connection”

18. Test your New Password • Double click “gsestudent6” connection • You’ll see an error message • Mouse cursor on “gsestudent6” connection and right click to select “connection properties” • In the “Connection Properties” window, change the password to the one that you just set and click “OK” • Double click “gsestudent6” connection • You should be successful now

19. SQL Database Definition • Data Definition Language (DDL) • Major CREATE statements: • CREATE SCHEMA–defines a portion of the database owned by a particular user • CREATE TABLE–defines a new table and its columns • CREATE VIEW–defines a logical table from one or more tables or views • Other CREATE statements: CHARACTER SET, COLLATION, TRANSLATION, ASSERTION, DOMAIN

20. Steps in table creation: • Identify data types for attributes • Identify columns that can and cannot be null • Identify columns that must be unique (candidate keys) • Identify primary key–foreign key mates • Determine default values • Identify constraints on columns (domain specifications) • Create the table and associated indexes Table Creation

21. An Enterprise Data Model

22. Corresponding Relations Customer_T (CustomerID, CustomerName) Product_T (ProductID, ProductStandardPrice) Order_T (OrderID, CustomerID, OrderDate) OrderLine_T (OrderID, ProductID, OrderedQuantity)

23. Open a Database in Navicat • Double click the database named after your UBIT name to open it • You have to select and open a database before you can do anything with it • Click Tables, Views, etc. under it • There is nothing since the database is empty at this point • Select “Tools”  “Console” • You’ll practice MySQL commands in the Console window

24. A Navicat Console Looks Like

25. MySQL Commands to Create Tables • Type in the following command in the Console window to create Customer_T table and hit “enter” CREATE TABLE Customer_T (CustomerID INT(10) NOT NULL, CustomerName VARCHAR(25) NOT NULL, CONSTRAINT Customer_PK PRIMARY KEY (CustomerID)); If the command is run successfully, you’ll see the following message: Query OK, 0 rows affected

26. Command Explained Non-nullable specification CREATE TABLE Customer_T (CustomerID INT(10) NOT NULL, CustomerName VARCHAR(25) NOT NULL, CONSTRAINT Customer_PK PRIMARY KEY (CustomerID)); Identifying primary key Primary keys can never have NULL values

27. A few Things to Keep in Mind • SQL is usually case-insensitive on Windows • So ‘NOT NULL’ is the same as ‘not null’ • Could be different on other OS’s or with different implementations of SQL • Punctuations are important • Line breaks are not required but they make your SQL easier to read by humans • It’s a good habit NOT to use space in names • White space in names can easily confuses the system • Use “-” or “_” instead

28. Edit/Save Queries in Navicat • Queries (i.e., MySQL commands) can be created, edited, and saved in Navicat’s Query Editor • Click “Query” on the top of the window and then select “New Query”

29. Edit/Save Queries in Navicat • In the newly opened Query Editor, you can type in your query and Run or Save it. To save it, give a name in the pop-up window and click “OK”. You’ll see the new query appears in the main Navicat query window.

30. Create Product_T Table CREATE TABLE Product_T (ProductID INT(10) NOT NULL, ProductStandardPrice DECIMAL(6,2), CONSTRAINT Product_PK PRIMARY KEY (ProductID));

31. Create Order_T Table CREATE TABLE Order_T (OrderID INT(10) NOT NULL, CustomerID INT(10), OrderDate DATE, CONSTRAINT Order_PK PRIMARY KEY (OrderID), CONSTRAINT Order_FK1 FOREIGN KEY (CustomerID) REFERENCES Customer_T(CustomerID));

32. Create OrderLine_T Table CREATE TABLE OrderLine_T (OrderID INT(10) NOT NULL, ProductID INT(10) NOT NULL, OrderedQuantity INT(10) , CONSTRAINT OrderLine_PK PRIMARY KEY (OrderID, ProductID), CONSTRAINT OrderLine_FK1 FOREIGN KEY (OrderID) REFERENCES Order_T(OrderID), CONSTRAINT OrderLine_FK2 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID));

33. Find Information of Tables • To see what tables are included in the database: SHOW tables; Or click icon in Navicat window. • To see the structure of a table: DESCRIBE table_name; Or first select a table and then click “Design Table” • To see the content of a table: SELECT * FROM table_name; Or double click on a table name.

34. Data Integrity Controls • Referential integrity: constraint that ensures that foreign key values of a table must match primary key values of a related table in 1:M relationships • Restricting: • Deletes of primary records • Updates of primary records • Inserts of dependent records

35. Ensuring data integrity through updates Relational integrity is enforced via the primary-key to foreign-key match

36. Changing Tables • ALTER TABLE statement allows you to change column specifications: • Table Actions:

37. Add/Delete One Column • Add CustomerState column to Customer_T ALTER TABLE Customer_T ADD CustomerState CHAR(2); • Check the structure of Customer_T DESCRIBE Customer_T; • Delete CustomerState from Customer_T ALTER TABLE Customer_T DROP CustomerState;

38. Add/Delete Multiple Columns • Add columns to Customer_T table ALTER TABLE Customer_T ADD COLUMN CustomerAddress VARCHAR(30), ADD COLUMN CustomerCity VARCHAR(20), ADD COLUMN CustomerState CHAR(2), ADD COLUMN CustomerPostalCode VARCHAR(10); • delete columns from Customer_T table ALTER TABLE Customer_T DROP COLUMN CustomerAddress, DROP COLUMN CustomerCity, DROP COLUMN CustomerState, DROP COLUMN CustomerPostalCode; • Add them back

39. In-Class Exercise: Add columns Add the following two columns to Product_T table ProductDescription VARCHAR(50), ProductFinish VARCHAR(20), Answer: ALTER TABLE Product_T ADD COLUMN ProductDescription VARCHAR(50), ADD COLUMN ProductFinish VARCHAR(20);

40. Removing Tables • DROP TABLE statement allows you to remove tables from your schema • Example • DROP TABLE Customer_T; • Be careful with DROP command • You may not be allowed to delete a table due to integrity constrains, e.g., it is involved in a relationship

41. In-Class Exercise: Delete tables • Delete OrderLine_T table • Verify it is deleted • Recreate the table

42. In-Class Exercise: Answers DROP TABLE OrderLine_T; SHOW tables; CREATE TABLE OrderLine_T (OrderID INT(10) NOT NULL, ProductID INT(10) NOT NULL, OrderedQuantity INT(10) , CONSTRAINT OrderLine_PK PRIMARY KEY (OrderID, ProductID), CONSTRAINT OrderLine_FK1 FOREIGN KEY (OrderID) REFERENCES Order_T(OrderID), CONSTRAINT OrderLine_FK2 FOREIGN KEY (ProductID) REFERENCES Product_T(ProductID));

43. Insert Statement • Adds one or more rows to a table • Inserting one complete row to a table INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState, CustomerPostalCode) VALUES (1, 'Contemporary Casuals', '1355 S Hines Blvd', 'Gainesville', 'FL', '32601-2871'); or INSERT INTO Customer_T VALUES (1, 'Contemporary Casuals', '1355 S Hines Blvd', 'Gainesville', 'FL', '32601-2871');

44. Insert Statement • Inserting a record that has some null attributes requires identifying the fields that actually get data INSERT INTO Customer_T (CustomerID, CustomerName, CustomerAddress, CustomerCity, CustomerState) VALUES (2, 'Value Furniture', '15145 S.W. 17th St.', 'Plano', 'TX'); • Inserting multiple rows with one command INSERT INTO Customer_T VALUES (3, 'Home Furnishings', '1900 Allard Ave.', 'Albany', 'NY', '12209-1125'), (4, 'Eastern Furniture', '1925 Beltline Rd.', 'Carteret', 'NJ', '07008-3188');

45. In-Class Exercises: Add Records • For Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) • (1, 'End Table', 'Cherry', 175); • (2, 'Coffee Table', 'Natural Ash', 200); • For OrderLine_T • (1001, 1, 2) What happened? Why? How to fix it?

46. In-Class Exercises: Answers INSERT Product_T (ProductID, ProductDescription, ProductFinish, ProductStandardPrice) VALUES (1, 'End Table', 'Cherry', 175), (2, 'Coffee Table', 'Natural Ash', 200); You won’t be able insert any record into OrderLine_T table because of a referential constraint, i.e., the foreign key OrderID has not had any value in Order_T table. You’ll have to insert records in that table first.

47. Delete Statement • Remove row(s) from a table • Delete certain rows DELETE FROM Customer_T WHERE CustomerState = 'TX'; • Delete all rows DELETE FROM Customer_T;

48. In-Class Exercises: Delete Records Delete the record of the product with an ID of "1" from Product_T table Answer: DELETE FROM Product_T WHERE ProductID = '1';

49. Update Statement • Modifies data in existing rows UPDATE Product_T SET ProductStandardPrice = 220 WHERE ProductID = '2';

50. In-Class Exercise: Update Records Change the ProductDescription of the product with an ID of “2” into “Tea Table” Answer: UPDATE Product_T SET ProductDescription = 'Tea Table' WHERE ProductID = '2';