Lesson Access Intro ( Intro to databases )
Learning Objectives
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
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();
?>