# data[].attributes.body
C.R.U.D. stands for Create, Read, Update, Delete

C.R.U.D. represents the four basic operations that can be performed on data

C.R.U.D. operations are fundamental to interacting with databases in web applications

# data[].attributes.body
Create: Add new records to database using SQL INSERT INTO

Read: Retrieve and display records from database using SQL SELECT

Update: Modify existing records in database using SQL UPDATE

Delete: Remove existing records from database using SQL DELETE

# data[].attributes.body
SLC stakeholders have proposed development of a video game rental web application to allow staff and students within the College to rent video games. The new web application must utilise a backend relational database and

allow staff and students to:

browse and rent available video games

return rented video games

allow administrators to:

create, update, and delete user accounts

view a list of all rentals (who has what)

# data[].attributes.attachment_caption
A relational schema defines the tables, fields, and relationships between them, and provides a clear and organized way to access and manipulate the data stored 

# data[].attributes.body
One user can create many rentals (rent many games) (1:N)

One game can be rented many times (1:N)

# data[].attributes.body
1. Open AMPPS.

2. Click the 'home' icon (opens localhost/ampps).

3. Find phpMyAdmin and login using below credentials:

username: root

password: mysql

4. Click the 'SQL' tab in MySQL.

# data[].attributes.body
1. Copy and paste the SQL into MySQL to create and populate the VideoGameRental database.

# data[].attributes.attachment_link.readable_text
-- Create the database
CREATE DATABASE VideoGameRental;

-- Use the created database
USE VideoGameRental;

-- Create the tblUsers table
CREATE TABLE tblUsers (
    UserID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    Surname VARCHAR(50) NOT NULL,
    Email VARCHAR(255) NOT NULL,
    Password VARCHAR(255) NOT NULL,
    Role VARCHAR(20) NOT NULL
);

-- Insert sample data into tblUsers
INSERT INTO tblUsers (FirstName, Surname, Email, Password, Role)
VALUES
    ('Master', 'Chief', 'masterchief@example.com', 'masterchief', 'Student'),
    ('Cortana', '', 'cortana@example.com', 'cortana', 'Student'),
    ('Arbiter', '', 'arbiter@example.com', 'arbiter', 'Student'),
    ('Lord', 'Hood', 'lordhood@example.com', 'lordhood', 'Staff'),
    ('Thel', 'Vadam', 'thelvadam@example.com', 'thelvadam', 'Staff'),
    ('343', 'Guilty Spark', 'guiltyspark@example.com', 'guiltyspark', 'Admin');

-- Create the tblGames table
CREATE TABLE tblGames (
    GameID INT AUTO_INCREMENT PRIMARY KEY,
    Name VARCHAR(255) NOT NULL,
    Genre VARCHAR(50),
    Platform VARCHAR(50)
);

-- Insert sample data into tblGames
INSERT INTO tblGames (Name, Genre, Platform)
VALUES
    ('Halo: Combat Evolved', 'First-Person Shooter', 'Xbox'),
    ('Halo 2', 'First-Person Shooter', 'Xbox'),
    ('Halo 3', 'First-Person Shooter', 'Xbox 360'),
    ('Halo 4', 'First-Person Shooter', 'Xbox 360'),
    ('Halo 5: Guardians', 'First-Person Shooter', 'Xbox One'),
    ('Halo Infinite', 'First-Person Shooter', 'Xbox Series X/S'),
    ('Halo Wars', 'Real-Time Strategy', 'Xbox 360'),
    ('Halo: Reach', 'First-Person Shooter', 'Xbox 360'),
    ('Halo: ODST', 'First-Person Shooter', 'Xbox 360'),
    ('Halo: The Master Chief Collection', 'First-Person Shooter', 'Xbox One');

-- Create the tblRentals table
CREATE TABLE tblRentals (
    RentalID INT AUTO_INCREMENT PRIMARY KEY,
    GameID INT,
    UserID INT,
    RentDate DATE NOT NULL,
    ReturnDate DATE,
    Returned BOOLEAN DEFAULT FALSE,
    CONSTRAINT FK_Rental_Game FOREIGN KEY (GameID) REFERENCES tblGames(GameID),
    CONSTRAINT FK_Rental_User FOREIGN KEY (UserID) REFERENCES tblUsers(UserID)
);

-- Insert sample data into tblRentals
INSERT INTO tblRentals (GameID, UserID, RentDate, ReturnDate, Returned)
VALUES
    (1, 1, '2023-01-01', '2023-01-10', FALSE),
    (3, 2, '2023-02-01', NULL, FALSE),
    (5, 3, '2023-03-01', NULL, FALSE),
    (7, 1, '2023-04-01', '2023-04-15', FALSE);

# data[].attributes.body
1. On your web server, create a folder 'rentals'. This is where your web application will live.

# data[].attributes.attachment_caption
database.php establishes a connection to the database.

# data[].attributes.body
1. Create a new file in your text editor.

2. Save the file as ‘database.php’ to your web server.

3. Add the code.

# data[].attributes.attachment_caption
login.php allows authorised users to login.

# data[].attributes.body
1. Create a new file in your text editor.

2. Save the file as ‘login.php’ to your web server.

3. Add the code.

4. Test the file.

# data[].attributes.attachment_caption
index.php provides a dashboard for logged in users.

# data[].attributes.body
1. Create a new file in your text editor.

2. Save the file as ‘index.php’ to your web server.

3. Add the code.

4. Test the file.

# data[].attributes.attachment_caption
logout.php handles logging the user out.

# data[].attributes.body
1. Create a new file in your text editor.

2. Save the file as ‘logout.php’ to your web server.

3. Add the code.

4. Test the file.

# data[].attributes.attachment_caption
games.php allows users to see a list of games available to rent.

# data[].attributes.body
1. Create a new file in your text editor.

2. Save the file as ‘games.php’ to your web server.

3. Add the code.

4. Test the file.

# data[].attributes.attachment_caption
return.php allows users to see and return their rented games.

# data[].attributes.body
1. Create a new file in your text editor.

2. Save the file as ‘return.php’ to your web server.

3. Add the code.

4. Test the file.

# data[].attributes.attachment_caption
rentals.php allows administrators to see a list of all rental information.

# data[].attributes.body
1. Create a new file in your text editor.

2. Save the file as ‘rentals.php’ to your web server.

3. Add the code.

4. Test the file.

# data[].attributes.attachment_caption
accounts.php allows administrators to C.R.U.D. user accounts.

# data[].attributes.body
1. Create a new file in your text editor.

2. Save the file as ‘accounts.php’ to your web server.

3. Add the code.

4. Test the file.

# data[].attributes.body
1. Style the website.

2. Can rent same game twice?

3. Should teachers be able to approve rental requests?

4. Returned column has 0 or 1, not yes or no.

5. If same game rented twice, returning once triggers both.

6. No navigation bar?