SchoolDB using Mockaroo

School Mockaroo is a mock database project aimed at simulating a school management system using PostgreSQL. It provides a sample database schema and data generated with Mockaroo testing purposes.

Features

• Database Schema: The project includes an Entity-Relationship (ER) diagram outlining the database schema.
• Mock Data: Sample data is generated using Mockaroo to populate the database tables.
• PostgreSQL: The database management system used for this project is PostgreSQL, ensuring compatibility and reliability.

ER Diagram

Steps

Step 1: Setup PostgreSQL and Create a Database

First, install PostgreSQL. Im using Fedora system:

sudo dnf install postgresql-server postgresql-contrib

Initialize the database cluster:

sudo postgresql-setup --initdb

Start the PostgreSQL service:

sudo systemctl start postgresql

Create a use or use the default user 'postgres'

CREATE ROLE username WITH LOGIN PASSWORD 'password';

# Example
CREATE ROLE liz WITH LOGIN PASSWORD 'password';

Create a database

sudo -u user_name createdb database_name

# Example
sudo -u liz createdb schooldb

Step 2: Login to Postgres Terminal

Now, Login to the Postgres Terminal

sudo -u user_name psql

To view the database

\l

To view the users

\du

To switch to the database

psql -U user_name -d your_database_name

# Example
psql -U liz -d schooldb

Step 3: Create Tables in the Database

Refering the ER Diagram, create tables in the database created.

CREATE TABLE student (
    id SERIAL PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    grade VARCHAR(50),
    dob DATE,
    age INTEGER
);

CREATE TABLE school (
    name VARCHAR(50),
    addr_city VARCHAR(50),
    addr_state VARCHAR(50),
    addr_pin INTEGER
);

CREATE TABLE faculty (
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    salary INTEGER,
    subject VARCHAR(50),
    phone_no VARCHAR(15)
);

Step 4: Add data to the tables

COPY command can be used to import data into PostgreSQL

COPY students FROM '/home/liz/schoolMockaroo/student.csv' DELIMITER ',' CSV HEADER;

COPY school FROM '/home/liz/schoolMockaroo/school.csv' DELIMITER ',' CSV HEADER;

COPY faculty FROM '/home/liz/schoolMockaroo/faculty.csv' DELIMITER ',' CSV HEADER;