Chapter 7 - Web & Database

Index

• Chapter 7 - Web & Database
  • Index
  • 7.1 Introduction to SQL and DBMS.
    • DBMS
      • MySQL
      • Pseudo Code
    • System Architecture
    • Database Connection
      • MySQLi
      • PDO
    • SQL (Structured Query Language)
      • DML && DDL
      • SELECT Statement
      • WHERE Clause
      • DISTINCT
      • JOIN
      • ORDER BY
      • INSERT
      • UPDATE
      • DELETE
  • 7.2 Design and model database
    • Database Model
    • Data Dictionary
      • Table
      • Column
      • Row
      • Primary Key
        • Example
      • Foreign Key
        • Example:
      • Relationship
        • One-to-Many (1:1)
        • One-to-Many (1:M)
        • Many-to-Many (M:N)
      • Normalization

7.1 Introduction to SQL and DBMS.

DBMS

1. collection of programs that manages the database structure and controls access to the data stored in the database.
2. MySQL
3. Microsoft SQL Server
4. Oracle
5. PostgreSQL
6. SQLite
7. Microsoft Access

MySQL

1. Free, open-source relational DBMS
2. A component of LAMP (Linux, Apache, MySQL, PHP) stack.
3. Runs on Linux/Unix, Windows, and Mac OS.
4. Access to data based on SQL (Structured Query Language).
5. Can be used in many different programming languages.

Pseudo Code

1. Connect to host server.
2. Select database.
3. Write SQL statement
4. Execute SQL statement
5. Close connection

System Architecture

Database Connection

1. Required Information:
   1. Hostname
   2. Username
   3. Password
   4. Database Name
2. PHP can work with a MySQL database using the following methods:
   1. MySQL extension: deprecated in 2012.
   2. MySQLi extension: improved version of MySQL.
   3. PDO (PHP Data Objects): a data-access abstraction layer.
3. PDO works on various DBMS, whereas MySQLi works only with MySQL.

MySQLi

1. The mysqli extension supports the following approaches:
   1. Procedural approach
   2. Object-oriented approach
2. Reference to a complete list of the properties and methods of the mysql:
3. MySQLi Reference
4. Procedural Example:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = mysqli_connect($servername, $username, $password, $dbname);

if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
echo "Connected successfully";

$query = "SELECT * FROM table";
$result = mysqli_query($conn, $query);

while ($row = mysqli_fetch_array($result)) 
{
    // use row to fetch the elements of each column
}

mysqli_close($conn);
?>

5. Object-oriented Example:

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

echo "Connected successfully";

$query = "SELECT * FROM table";
$result = $conn->query($query);

$row = $result->fetch_assoc();

echo $row["column_name"];

$conn->close();
?>

PDO

1. PDO Example:

<?php
$servername = "localhost";
$username = "username";
$password = "password";

try {
    $conn = new PDO("mysql:host=$servername;dbname=myDB", $username, $password);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connected successfully";
}
catch(PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}

$conn = null;
?>

SQL (Structured Query Language)

1. A standard language for specifying accesses and modifications to relational databases.
2. Supported by DBMS of major vendors.
3. Reserved words are not case-sensitive, but table names and table columns may/may not be case-sensitive depending on vendor.

DML && DDL

1. SQL can be divided into TWO parts:
2. DML (Data Manipulation Language):
   1. SELECT: extracts data from a database.
   2. INSERT: inserts new data into a database.
   3. UPDATE: updates existing data within a database.
   4. DELETE: deletes all records from a table.
3. DDL (Data Definition Language):
   1. CREATE DATABASE: creates a new database.
   2. ALTER DATABASE: modifies a database.
   3. CREATE TABLE: creates a new table.
   4. ALTER TABLE: modifies a table.
   5. DROP TABLE: deletes a table.
   6. CREATE INDEX: creates an index (search key).
   7. DROP INDEX: deletes an index.

SELECT Statement

1. The SELECT statement is used to select data from a database.
2. The SELECT statement returned ALL the results from the queried database table.
3. A simple SQL SELECT statement, requires:
   1. Column(s) Name
   2. Table Name
4. Syntax:

   SELECT column1, column2, ...
   FROM table_name;
   

WHERE Clause

1. The WHERE clause not only used in SELECT statement, but it UPDATE, DELETE statement etc.
2. The WHERE clause is used to FILTER records that satisft a given condition.
3. The WHERE clause returned MATCHING results from the queried database table restricted to a specific condition.
4. "condition" is the filter to be applied on the results. The filter could be a range, single value or sub query.
5. With the WHERE clause, the following operators can be used:

   1. Operator	Description
      =	Equal
      <>	Not equal
      >	Greater than
      <	Less than
      >=	Greater than or equal
      <=	Less than or equal
      BETWEEN	Between an inclusive range
      LIKE	Search for a pattern
      IN	To specify multiple possible values for a column

####　AND, OR, NOT

1. The AND, OR, NOT operators are used to filter records based on more than one condition.
2. Exmaple:

   SELECT * FROM table_name WHERE column1 = value1 AND column2 = value2;
   

DISTINCT

1. some of the columns may contain duplicate values. This is not a problem, however, sometimes there is a need to list only the different (distinct) values in a table.
2. The DISTINCT keyword can be used to return only distinct (different) values.
3. The SQL DISTINCT keyword is used in conjunction with SELECT statement to eliminate all the duplicate records and fetching only unique records.
4. Example:

   SELECT DISTINCT column1, column2, ...
   FROM table_name;
   

JOIN

1. The JOIN keyword is used in an SQL statement to query data from TWO or more tables, based on a relationship between certain columns in these tables.
2. The INNER JOIN keyword return rows when is at least ONE match in BOTH tables.
3. IF there are rows in table1 do not have matches in table2, these rows will NOT be listed.
4. Example:

   SELECT column_name(s)
   FROM table1
   INNER JOIN table2
   ON table1.PK = table2.FK;
   

ORDER BY

1. The ORDER BY keyword is used to sort the query result by a specified column.
2. Example:

   SELECT column1, column2, ...
   FROM table_name
   ORDER BY column1, column2, ... ASC|DESC;
   

INSERT

1. The INSERT INTO statement is used to insert new records in a table.
2. Example:

   INSERT INTO table_name (column1, column2, column3, ...)
   VALUES (value1, value2, value3, ...);
   

UPDATE

1. The UPDATE statement is used to modify the existing records in a table.
2. Example:

   UPDATE table_name
   SET column1 = value1, column2 = value2, ...
   WHERE condition;
   

DELETE

1. The DELETE statement is used to delete existing records in a table.
2. Example:

   DELETE FROM table_name
   WHERE column1 = value1;
   

Back to Top

7.2 Design and model database

Database Model

1. An entity represents a particular type of object in the real-world.
2. An attribute is a characteristic of an entity.
3. A relationship is an association among entities.
   1. One-to-many (1:M)
   2. Many-to-many (M:N)
   3. One-to-one (1:1)
4. A constraint is a restriction placed on the data.

Data Dictionary

Table

1. Databases are usually organized into one or more tables.
2. A table is a collection of related records regarding an entity.
3. A table stores data in a series of columns and rows.

Column

1. Columns are the attributes or quanlities of the entity described by the table.
2. Example:
   1. Entity: Student
   2. Attribute (column): student_id
3. Each column contains a different type of attribute.
4. known as fields.
5. A field is an area (within a record) reserved for a specific piece of data.
6. Example:
7. Fields are defined by field name, data type, and field size.
8. Field name
   1. Each column needs a heading to identify the data it contains.
   2. These headings are called field names.
   3. Field names are used to ensure that the data for each record is entered in the same sequence.
9. Data type
   1. Data type ensures that all the data in a column is entered using the same format.
   2. Example:
      1. Character: text including such things as telephone numbers, zip codes.
      2. Numeric: numbers.
      3. Date: dates.
      4. Logical: true/false or yes/no.
10. Field Size
    1. Field size are the amount of space reserved for storing data

Row

1. Rows are knows as records.
2. A record is the collection of values for all the fields pertaining to one entity.
3. Every record in a table has exactly the same structure, but different data.
4. Each row corresponds to a single record.
5. Rows hold the actual data, with one (or zero) items for each column of the table.
6. Each row is identified by the values appearing in a particular column which has been identified as a unique INDEX.

Primary Key

1. Primary key is a special column or combination of columns that uniquely identifies each record (row) in a table.
2. The value that primary key hold must be unique for each record (row), and must not contain any nulls (non-values).
3. If it's possible that TWO or more records (past, present, or future) may share the same value for an attribute, it's a poor choice for a primary key.

Example

Name	Department	Position
John	Customer management	Clerk
Paul	Customer management	Manager
Bill	Human Resource	Accountant

1. No field is going to be guaranteed unique.
2. There are many people that share the smae name / department / position that may be added to the database in ther future.

Solution:

1. To avoid the uncertainty of using a data column as a primary key, many developers will create their own column which contains a computer generated unique value.
2. The Auto Increment Data type automatically increments the field each time a new record is created/
3. Hence, a better choice might be to use a unique employee ID nummber that you assign to each employee record when they're created.
4. Once you decide upon a primary key and set it up in the database, the database management will enforce the uniqueness (entity integrity - PK must be unique and not be null) of the primary key.
5. If a record to be inserted into a table has a primary key that duplicates an existing record, the insertion will fail.

Foreign Key

1. Foreign keys are columns in a table which provide a link to another table.
2. It is used to create relationships (correlate information) between tables.
3. Primary key uniquely defines a record, while a foreign key is used to reference the same record from another table.
4. When a table's primary key field is added to another table in order to relates the two tables, it is called a foreign key in the referenced table.
5. It is not necessarily that a primary key has any corresponding value in the referenced table.
6. Similarly, the value used in a Foreign Key column is not necessarily unique to the table it is in but must be unique to the table it is referring to (Referencial Integrity).

Example:

cust_id (PK)	cust_name	cust_gender
001	John	Male
002	Paul	Male
004	Lilly	Female

order_id (PK)	item	cust_id (FK)
001	book	001
002	shirt	002
003	pen	002

1. We don't need to store all the other customer information (name, gender, etc.) in the order table.
2. This is the elegance of the relational model.
3. Referential integrity is a database concept that ensures that relationships between tables remain consistent.
4. When one table has a foreign key to another table, the concept of referential integrity states that you may not add a record to the table that contains the foreign key unless there is a corresponding record in the linked table.
5. It also includes the techniques known as cascade delete and cascade update, which ensure that changes made to the linked table are reflected in the primary table.
6. Refertial integrity enforces the following three rules:
   1. The Order's FK attribtue must points to a valid record in the Customer table.
   2. If the primary key for a record in the Customer table changes, all corresponding records in the Order table must be modified using a cascading update.
   3. If a record in the Customer table is deleted, all corresponding records in the Order table must be deleted using a cascading delete.

Relationship

1. Relationships are they very core of relational databases.
2. The primary key, together with the closely related foreign key concept, are the main way in which relationships are defined.
3. Relationships allows description of the connections between different database tables in powerfull ways.
4. Once the relationships between your tables are described, that information can be used to perform powerful cross-table queries, known as joins.
5. In relational databases, a relationship exists between two tables when one of them has a foreign key that references the primary key of the other table.
6. There are three types of relationships:
   1. One-to-one (1:1)
   2. One-to-many (1:M)
   3. Many-to-many (M:N)
7. Each relationship are named according to the number of tables rows that may be involved in the relationship.

One-to-Many (1:1)

1. Occur when each entry in the first table has one, and only one, counterpart in the second table.
2. One-to-one relationships are rarely used because it's often more efficient to simply put all of the information in a single table.
3. It would be possible, although not really desirable, to store all the information in one table in this case.

One-to-Many (1:M)

1. This is the most common type of database relationship.
2. It occur when each record in the first table corresponds to one or more records in the second table but each record in the second table corresponds to only one record in the first table.
3. Example:
   1. Each student has only one teacher, but each teacher may have multiple students.

Many-to-Many (M:N)

1. This relationship occur when each record in the first table corresponds to one or more records in the second table and each record in the second table corresponds to one or more records in the first table.
2. Example:
   1. Each Teacher may instruct more than one course and each course may be taught by more than one teacher.
   2. an order can contain many products, and a product can be included in many orders.

Normalization

1. Process of evaluating and correcting table structures to minimize data redundancy and reduce the likelihood of data anomalies.

Back to Top

Next Page Back to README