Software Installation

• Install mySQL for Windows or Ubuntoo from here. Please ensure that you download sakila and world sample databases offered by mySQL as part of core installer.
• Install mySQL Workbench for Windows or Ubuntoo from here. This a GUI for mySQL database software that helps the database user to review and analysis the datasets available in the database.

Example

Follow below commands and try them on you local machine!

Create a database named "Campus" in MySQL, you can use the following SQL query:

CREATE DATABASE IF NOT EXISTS Campus;

SHOW DATABASES;

This will list all databases, including "Campus" if it was successfully created.

Create the tables "Students", "Faculty", and "Course" under the "Campus" database in MySQL, you can use the following SQL queries. Each table includes relevant columns and data types.

USE Campus;

CREATE TABLE IF NOT EXISTS Students (
    StudentID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    DateOfBirth DATE,
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE IF NOT EXISTS Faculty (
    FacultyID INT AUTO_INCREMENT PRIMARY KEY,
    FirstName VARCHAR(50) NOT NULL,
    LastName VARCHAR(50) NOT NULL,
    Department VARCHAR(100),
    Email VARCHAR(100) UNIQUE
);

CREATE TABLE IF NOT EXISTS Course (
    CourseID INT AUTO_INCREMENT PRIMARY KEY,
    CourseName VARCHAR(100) NOT NULL,
    Credits INT CHECK (Credits > 0),
    FacultyID INT,
    FOREIGN KEY (FacultyID) REFERENCES Faculty(FacultyID)
);

Explanation

• USE Campus;: This command sets the context to the "Campus" database.
• AUTO_INCREMENT: Automatically generates a unique value for each new record.
• PRIMARY KEY: Uniquely identifies each record in the table.
• VARCHAR(n): A variable character string with a maximum length of n.
• DATE: Data type for storing date values.
• UNIQUE: Ensures that all values in a column are different.
• CHECK (Credits > 0): Ensures that credits must be greater than zero.
• FOREIGN KEY: Establishes a relationship between the "Course" and "Faculty" tables.