Outline

• Databases
• Quiz

Database Management Systems and Databases

Last Day, we learned about the MVA design pattern. Models in this pattern often interact with a database so today we are going to have a whirlwind tour of databases to learn how to do this.

• For our purposes, a database management systems (DBMS) is used to manage a collection of databases.
• It manages how the data is stored in each database, and who can access it when.
• A given database consists of a collection of tables.
• Each table has a type for each of its columns and then has rows of data where the data values match the values of the column types.
• For example, car(make VARCHAR(10), model VARCHAR(10), year DATE) is an example table schema (specification). A possible row of car is:

  ('Toyota', 'Corolla', '1970-01-01')
  

MySQL Databases

• Let's learn how to write to databases now, so we have a good infrastructure to write models.
• To manage MySQL databases you need to know how to:
  • Create and manage users
  • Create new databases and tables
  • Do lots of other stuff I won't talk about
  • Many of these operations can be done using phpMyAdmin.
  • Alternatively, you can use the command line.

MySQL Command Line

• I will assume you have set your path so that you can directly run mysqladmin and mysql.
• To set the root password you can go to the command prompt and type:

  mysqladmin -uroot password what_password_is
  

• You can get a mysql shell by then typing:

  mysql -uroot -pwhat_password_is
  

MySQL Command Line

• To see what databases are available you can use the command:

  show databases;
  

• To use a particular database type:

  use dbname;
  

• To see what tables the currently use database has type:

  show tables;
  

• To add a new user you can type at the prompt:

  GRANT SELECT, INSERT, UPDATE, DELETE, LOCK TABLES 
    on dbname.* TO username@127.0.0.1 IDENTIFIED BY 'password';
  

• To quit mysql type \q or quit. For help type \h or help.
• To login using this new user type at the command prompt:

  mysql -uusername -ppassword
  

Quiz

Which of the following statements are true?

1. Web sessions are implemented using the HTTP-SESSION header.
2. By default global defined PHP variables are visible within a function's scope.
3. The $_SERVER PHP super global variable can be used to see what fields were POST'd from a web form.

Basic SQL

• SQL (Structured Query Language) is the most common language for issuing commands to a database.
• These commands can usually be given either from the mysql prompt or from within a PHP transaction that is connecting to the database.

Database, Table Creation Deletion

• To create a database one uses the CREATE DATABASE command:

  CREATE DATABASE my_nifty_db;
  

• Then one can type use my_nifty_db; to use this database.
• To create a table in the currently used database one can use a command like:

  CREATE TABLE customer (
      cust_id int(5) NOT NULL,
      name VARCHAR(50),
      title CHAR(3),
      PRIMARY KEY(cust_id)
  );
  

• To delete a table type:

  DROP TABLE customer;
  

• To delete a database

  DROP DATABASE my_nifty_db;
  

SQL Query

• Some basic SQL commands are:

  select col1, col2, col3
  from table1, table2, table3
  where condition_holds; 
  

• Returns rows consisting of given columns of tables such that the given condition holds. For example,

  /* To Get First Names and Last Names of 
     Employees with Salary's bigger than $10000
   */
  SELECT FNAME, LNAME
  FROM EMPLOYEE
  WHERE SALARY > 10000;
  
  /*
     To get triples (FNAME, LNAME, PNAME) of Employees
     who work on projects with names containing
     the string "SECRET", sorting result in descending
     alphabetical order, showing results 10 through 60.
   */
  SELECT E.LNAME AS LNAME,
     E.FNAME AS FNAME,    
     P.PROJECT_NAME AS PNAME
  FROM EMPLOYEE E, PROJECT P, WORKS_ON W
  WHERE E.EID=W.EID AND P.PID=W.PID
     AND P.PROJECT_NAME LIKE '%SECRET%'
  ORDER BY E.LNAME DESC, E.FNAME DESC, 
     P.PROJECT_NAME DESC
  LIMIT 10,50; /* start at 10 plus next 50 */
  
  /* Number of classes each student is taking
   */
  SELECT STUDENT.NAME, COUNT(*) /* AVG, COUNT, SUM are aggregate functions*/
  FROM STUDENT, COURSE
  WHERE STUDENT.ID=COURSE.SID
  GROUP BY STUDENT.NAME;
  

• The general form of a select statement looks like:

   
  SELECT T1.FIELD_NAME_1 AS NAME1, ..., T1.FIELD_NAME_N AS NAME_N,
   ..., TM.FIELD_NAME_N AS SOME_NAME
  FROM T1, T2, ..., TM
  WHERE T1.FIELD_NAME_j = some value [OR|AND] ... T1.FIELD_NAME_k = TJ.FIELD_NAME_w
  [GROUP BY ...]
  [ORDER BY ...]
  [LIMIT ...]
  

  The above are called SELECT statements and consist of three main lines (plus optional ones). The first line lists the columns we want, the second line lists the tables to use, the last line lists the conditions that must be met by the returned row (join conditions).
• We have tried to format the SQL nicely but there is no requirement to do so.

SQL Insert, Delete, Update

• You can insert a row into table one uses a command like:

  INSERT INTO table_name VALUES ('col1_value', 'col2_value', 'col3_value');
  

• To delete a set of rows from a table you can use a command like:

  DELETE FROM table_name WHERE cond;
  

• To update a set of rows you can use a command like:

  UPDATE table_name SET column='value' WHERE cond;
  

Connecting to a database from PHP

• PHP can connect with many different database systems: DB2, Oracle, MariaDB, Mysql, Postgres, SQLite, etc.
• To start we are going to look at how to do this for Mysql/MariaDB because this is what comes with XAMPP.
• As we'll see there is usually more than one API to connect to the same DBMS in PHP: the "native" API, and the "generic" API using PDO.
• The former tends to expose more DBMS specific abilities, the later tends to help your code portable if you need to switch which DBMS you are using.
• For some DBMS's, there are actually two "native" APIs rather than one. For example, for mysql there are functions mysql_ and mysqli_, the former being the very common original way to connect to a Mysql database from PHP and the latter the more modern.
• For new code, you should use mysqli_ as the mysql_ functions have been removed in PHP 7.
• mysql_ is also a function only interface; whereas, mysqli_ can be used in a function (almost the same call as mysql_ but using mysqli_ ) or object-oriented approach (where create an object and use its methods).
• Another situation of this type occur with the Sqlite and Sqlite3 APIs.

Connecting to a database from PHP

• To start a connection to a MySQL database one can issue the command: $db = mysqli_connect();
• This function actually can take up to six parameters: the host, the username, the password, a database, a port, and a socket.
• These default to localhost, the default mysql user and password, blank, default port and socket. A slightly more advanced, connection call might be:

  $db = mysqli_connect($host, $uname, $pword, $db); 	
  

• Depending on how mysql is configured, the first example above might work and saves some typing.
• This function returns false if a connection is not made.
• To close a database connection, one can call mysqli_close();

Selecting a Database and queries

• To select a database after a connection has been made (sometimes useful) one calls:

  mysqli_select_db("cars");
  

• One can then do a query with code like:

  $query ="SELECT * FROM Corvettes";
  $result = mysqli_query($query);
  $num_rows = mysqli_num_rows($result);
  $num_fields = mysqli_num_fields($result);
  for($j = 1; $j <= $num_rows; $j++)
  {
  	$row = mysqli_fetch_array($result);
  	print $row[0].$row["some_attr"]. "<br />";
  }
  

• mysqli_query can also be used to do inserts, etc.

Accessing More Advanced Features of Mysql

• Last week, we started discussing interacting with a database using PHP.
• As we mentioned before, mysqli can be used in both a procedural and object-oriented fashion.
• We illustrate this by showing another way we could do queries in mysql: Using prepared statements.
• When one does a mysqli_query, the database has to compile the query each time it gets it, it can't compile the query, optimize it, and store it for the next time. This is because the argument to the mysql query might completely change between calls.
• Using prepared statements, we can reuse a query we have already compiled and set up with fixed slots that may change. For example:

  <?php
  $mysqli = new mysqli("localhost", "my_user", "my_password", "my_db"); 
  // note: here we are using the OO interface to mysqli
  // we'll talk more about OO in PHP later today; for now, pretend its like Java/C++
  $stmt =  $mysqli->stmt_init();
  if ($stmt->prepare("SELECT FNAME FROM EMPLOYEE WHERE LNAME=?")) {
      $lnames = ["Smith", "Jones", "Pollett"];
      foreach($lnames as $lname) {
         $stmt->bind_param("s", $lname); //s == string, i == int, d==double
         $stmt->execute();
         $stmt->bind_result($fname);
         $stmt->fetch();
         print("The first person I found with last name $lname was  $fname\n");
      }
      $stmt->close();
  }
  $mysqli->close();
  

• Another advantage of the above is that there is less of an issue about properly escaping the parameters.

Other Database Interfaces in PHP

• PHP has support for the usual database suspects: Oracle, DB2, Postgres, Sybase, etc, each having their own suite of functions orcl_, db2_, pg_, sybase_ ...
• PHP's default build has also includes in it support for Sqlite 2 and Sqlite 3 databases.
• Sqlite is a tiny highly optimized and tested C program to allow you to create SQL databases.
• It stores databases in a flat file and is used as an embedded persistence layer in a wide variety of platforms: Firefox, iPhone, Android, Adobe, etc.
• Sqlite database files created on one of these platforms can be read by any other. Further, since the database is stored in a flat file, it can be very useful for storing default data needed in deploying an app.
• Sqlite2 in PHP uses a function style interface: $dbh = sqlite_open($filename, $write_mode); $result = sqlite_query($query); sqlite_fetch_array($result); sqlite_close($dbh);
• Sqlite3 in PHP uses an OO style interface: $dbh = new SQLite3($filename, $mode); $results=$dbh->query($query); $data = $results->fetch_array(); $dbh->close();
• Finally, PHP has an OO interface known as PDO, which can be used to access any DBMS database for which PDO drivers have been written. This includes all the major databases listed above.

Some Quick Remarks on Database Design

• It is possible to store all of your data in a big table with lots of columns.
• This is typically not done because it is wasteful of space. For instance, if we had a big table with Employee names, ssns, and children of employee names, we'd have to repeat the full Employee name for each child name; whereas, if we had a separate table called dependents we could just have columns in it: Employee ssn, child names, and not have to repeat the Employee name for each child of that employee.
• One goal in DB design is to split the database into tables to store the data as efficiently as possible; yet, on the other hand, be sure we are preserving the original information, and are not introducing any false information when we join tables together.
• An artistic approach to this problem can be done by trying to figure out the classes/entities that need to be stored in the database and the corresponding relationships between them.
• After doing this modeling phase, people typically map each of the entities and relationships that were found to tables.
• To check the result, a notion called functional dependency is used. A set of columns Y functionally depends on a set of columns X, if when we know the values for X we know the values of Y.
• A dependency like X fixes X (or some subset of X's columns) is considered trivial.
• We would like our tables to have the property that the only nontrivial dependencies X fixes Y on our tables involve X's which fix whole rows. (Boyce-Codd Normal Form).

Example

• As an example, consider the table EMP_PROJ(SSN, PNUMBER, HOURS, ENAME, PNAME, PLOCATION).
• Suppose the functional dependencies are:

  SSN, PNUMBER--> HOURS
  SSN --> ENAME
  PNUMBER --> PNAME, PLOCATION.
  

• Some drawbacks of this table are that:
  • To insert an employee before they have a project means, null padding rows.
  • Deleting the last employee from a project might end up deleting the project.
  • When we add an employee to a second project we are duplicating ENAME unnecessarily, as once we know the SSN we've already fixed the ENAME.
• The above table is not in BCNF as SSN --> ENAME is a non-trivial FD, yet knowing the SSN does not fix the whole row as someone might work on multiple projects.
• We can make a database schema with the above attribute which is in BCNF using the following tables: EMPLOYEE(SSN, ENAME), PROJECT(PNUMBER, PNAME, PLOCATION), WORKS_ON(SSN, PNUMBER, HOURS).
• Notice this database schema avoids the three issues above, giving evidence to the idea that putting things into BCNF avoids the kind of DB anomalies we don't want.