1 / 31

Database Implementation & SQL Basics Workshop

Learn database fundamentals, schema design, SQL implementation, and basic SQL functions hands-on with PHP and MySQL. Understand how to build, query, and update relational databases effectively.

dhummel
Download Presentation

Database Implementation & SQL Basics Workshop

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. INFM 603: Session 6Database Implementation Using PHP and MySQL Paul Jacobs The iSchool University of Maryland Wednesday, Mar. 13, 2019 This work is licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 United StatesSee http://creativecommons.org/licenses/by-nc-sa/3.0/us/ for details

  2. Today’s Topics • Review of Database Fundamentals • How to Build/Query/Update Online Relational Databases • Hands-on Practice

  3. Database Fundamentals - Review • What is a database? A computer mechanism for online persistent storage, organization and access to information (a technology) • Why do we need them? To maintain information in a consistent, convenient, reliable way that enables applications (a tier or architectural component) • How do we use them? Building a (usually) relational model called a schema that organizes data in tables and supports needed functionality; writing code for interfaces (implementation) Database Fundamentals Implementation Hands-on Practice

  4. Database Implementation – Types of Database Problems • Design and Document a Database • Figure out what data has to be in there • How is the data structured? What are the types of data (e.g., names, dates, strings, etc.)? • Build the schema • Document it (so that you or someone else can implement it) • Build a Database • Create the database (tables), users, security, etc. (usually done by designer and/or DBA – administrator using various interfaces and/or ODBC/SQL) • Validate that it works • Populate the database (enter records) Database Fundamentals Implementation Hands-on Practice

  5. Database Implementation –Database Problems (cont’d) • Program a Database • Develop interfaces for end users (e.g. to search, access information from databases – e.g., Testudo) • Develop interfaces for other systems (e.g., to place orders, perform updates, etc. – e.g., Canvas, Amazon) • Query a Database • Access data (records) already in the database • Can be done using all kinds of interfaces, or by programs (often using ODBC/SQL) • Very often done by users who are not allowed to update or change the data themselves Database Fundamentals Implementation Hands-on Practice

  6. Documenting a Database • Usually specifies the schema, especially the tables (or object classes in an object-oriented format), but can be at various levels and description and abstraction • Can be used to communicate among different parts of a technical project team (e.g., user groups, programmers, database designers, etc.) • Can use any number of documentation methods • E-R diagrams • Class diagrams with annotations • Sketches and drawings of all sorts (such as what we used last time) • Actual database output from some stage of implementation • Best done relatively early in project because of need to build and test code to interface with the database Database Fundamentals Implementation Hands-on Practice

  7. Database Implementation - Introduction to SQL

  8. What is SQL (Structured Query Language)? • The “interlingua” of relational databases (e.g., Oracle, SQLServer, MySQL, Postgres, DB2, etc.) • An international standard (ISO 9075) • A common method of building and querying databases • A common method of building database interfaces (e.g., using ASP, JSP, PHP) • A common method of building connectors from applications to databases (e.g., using XML, SOAP and ODBC/JDBC) Database Fundamentals Implementation Hands-on Practice

  9. Basic SQL functions (on records) • SELECT – get records from a database that meet certain criteria • INSERT – add records to a database • UPDATE • DELETE Database Fundamentals Implementation Hands-on Practice

  10. Basic SQL functions - SELECT SELECT CustomerAddress FROM Customers WHERE CustomerName = ‘Will E. Coyote’; SELECT * FROM SalesOrders LIMIT 0,30; Database Fundamentals Implementation Hands-on Practice

  11. PHPMyAdmin Output - SELECT Database Fundamentals Implementation Hands-on Practice

  12. SELECT Using JOIN SELECT SalesOrders.SalesOrderNo, Customers.CustomerName FROM SalesOrders JOIN Customers ON (SalesOrders.CustomerNo = Customers.CustomerNo) ; Database Fundamentals Implementation Hands-on Practice

  13. PHPMyAdmin Output - Join Database Fundamentals Implementation Hands-on Practice

  14. Basic SQL functions - INSERT INSERT INTO `inclassdemo`.`OrderItems` ( `SalesOrderNo` , `ItemNo` , `ItemQuantity` , `ItemUnitPrice` )VALUES ( '1', '1', '12', '10.50'); Database Fundamentals Implementation Hands-on Practice

  15. Basic SQL functions - UPDATE UPDATE `inclassdemo`.`SalesOrders` SET `CustomerNo` = '5', `ClerkNo` = '2' WHERE `SalesOrders`.`SalesOrderNo` =1 LIMIT 1 ; Database Fundamentals Implementation Hands-on Practice

  16. Basic SQL functions - DELETE DELETE FROM Customers WHERE CustomerName = 'Peter Piper'; Database Fundamentals Implementation Hands-on Practice

  17. Basic SQL functions (on tables) • CREATE – make a new table • DROP – delete a table • ALTER – change (e.g., fields) of a table Database Fundamentals Implementation Hands-on Practice

  18. Basic SQL functions - CREATE CREATE TABLE `SalesOrders` ( `SalesOrderNo` INT( 6 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,`Date` DATE NOT NULL ,`CustomerNo` INT NOT NULL ,`ClerkNo` INT NOT NULL ,`Total` FLOAT( 9, 2 ) NOT NULL ) [in MySQL requires separate constraint PRIMARY KEY (SalesOrderNo)] Database Fundamentals Implementation Hands-on Practice

  19. Basic SQL functions – DROP AND ALTER DROP TABLE Pets; ALTER TABLE SalesOrders ADD Season VARCHAR (10); Database Fundamentals Implementation Hands-on Practice

  20. Building the Sample Database Using MySQL

  21. Example Database (Final Tables) Table: SalesOrders • SalesOrderNo • Date • CustomerNo • ClerkNo • Total Table: OrderItems • SalesOrderNo • ItemNo • ItemQuantity • ItemUnitPrice Table: InventoryItems • ItemNo • ItemDescription Table: Customers • CustomerNo • CustomerName • CustomerAddress Table: Clerks • ClerkNo • ClerkName Database Fundamentals Implementation Hands-on Practice

  22. Building the Sample With PHPMyadmin – Create Tables Database Fundamentals Implementation Hands-on Practice

  23. Building the Sample With PHPMyadmin – Add Data Database Fundamentals Implementation Hands-on Practice

  24. Building the Sample With PHPMyadmin – Add Data (SQL) Database Fundamentals Implementation Hands-on Practice

  25. Building the Sample With PHPMyadmin – Build Queries Database Fundamentals Implementation Hands-on Practice

  26. Hands On Practice Build and populate the sample database

  27. SQL is Quite Powerful • Data types – Most common are INT (Integer), VARCHAR (Variable length string), and DATE, but there are many other related types, e.g. TIMESTAMP, BLOB (binary large object), FLOAT (floating point) • Functions – There are functions for almost any data type, e.g. – • Date conversion and formatting, such as DATE_FORMAT(Date, ‘%b . %m, %Y) TIMESTAMPDIFF(YEAR, BirthDate,CURDATE()) • All kinds of arithmetic, like SQRT() and POW() • Numerous tools and tricks for manipulating, sorting, comparing, etc. Database Fundamentals Implementation Hands-on Practice

  28. Example: Typical Contact DB Database Fundamentals Implementation Hands-on Practice

  29. Example: Age Query Database Fundamentals Implementation Hands-on Practice

  30. Example: Birthday Query Database Fundamentals Implementation Hands-on Practice

  31. Database Implementation - Recap • Reviewed the role of databases in [web] applications • Introduced SQL as the “interlingua” of relational databases • Presented and tried out one type of database server using PHPMyAdmin/MySQL • Next up: Integrating with web pages via PHP and other advanced methods, tying things together Database Fundamentals Implementation Hands-on Practice

More Related