Lesson Access Intro  ( Intro to databases )

Learning Objectives

  • Can Describe what a relational database is and compare it to a flat database
  • Can use Table, Query and Report commands - Understanding of SQL
  • User Check
    Can understand the advantages of a relation DB and able to draw an ER Diagram and understand relationships

Data Redundancy

Table

Relationship

ERD Entity Relationships Diagram

Fields

Primary Key

Foreign Key

SQL

One to One

One to many

Many to Many

Record


Instructions

1 Creating a Query (15 mins)

Play and follow the first screen cast creating a query across multiple tables click here. The music store database has been uploaded to google drive so you may download to desktop and import into Access. Save your query with name OrdersQuery

Comprehension Task : Create a new Query to show Artist and Album information for your entire inventory. Run and Save Query as InventoryQuery

2 SQL ( Structured Query Language ) ( 10 mins )

When you make a query using the drag and drop design tool an SQL query is created in the background and it is this that creates the output. See example of  SQL by clicking here

3 Forms : Adding a New Order ( 15 mins )

The sample test database has Forms set up for adding orders : Imagine this could be a web page front end form using PHP to connect to database on the server - Add a new order following screen cast click here

4 Reports ( 5 mins )

Run a report from the sample database


Relationships Types

  • One to Many ( one customer can have many orders )

  • One to One ( one student has one homeroom teacher )

  • Many to Many

Example orders and products . One order can contain many products. One product can be on many orders 

Further Reading for Understanding of this important concept ( if part of your AI )

A Level IGCSE Click Here

Lesson 1PHP and MySQL

XAMPP Down Load Link  Version 7.2.  Click Here

Create a folder within the htdocs folder as in example below which will store your php programs


Download Notepad++ or an Editor of your choice


Connect to MySQL and Create a Database  

View Screen Cast For Help Click Here

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

// Create connection
$conn = new mysqli($servername, $username, $password);
// Check connection
if ($conn->connect_error) {
   die("Connection failed: " . $conn->connect_error);
}

// Create database
$sql = "CREATE DATABASE mynewDB";
     if ($conn->query($sql) === TRUE) {
        echo "Database created successfully";
   } else  {
      echo "Error creating database: " . $conn->error;
   }

$conn->close();
?> 


Create a Table with the Data Base

Need help View Screen Cast For Help Click Here

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

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

// sql to create table
$sql = "CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
)";

if ($conn->query($sql) === TRUE) {
echo "Table MyGuests created successfully";
} else {
echo "Error creating table: " . $conn->error;
}

$conn->close();
?>


Create an entry in the Table 

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


// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO myguests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
echo "New record created successfully";
} else {
echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Leave a Comment: