Designing and Creating Databases

Overview

In this lab we will practice creating a database and tables.

EXERCISES

Create a new database named 'myDatabase'
Connect to the newly created database (PostgreSQL has no USE statement - open a new query tool connected to 'myDatabase' instead)
Create a 'students' table with columns:

student_id (numeric, primary key)

first_name (string)

last_name (string)

date_of_birth (date)

Create a 'courses' table with columns:

course_id (numeric, primary key, auto increment)

course_name (string)

credit_hours (numeric)

Add a unique constraint to the course_name column in the 'courses' table
Create a 'professors' table with columns:

professor_id (numeric, primary key, auto increment)

first_name (string)

last_name (string)

subject (string)

Add a not null constraint to the subject column in the 'professors' table
Create a 'classrooms' table with columns:

classroom_id (numeric, primary key, auto increment) classroom_name (string) capacity (numeric)

Add a check constraint to the capacity column in the 'classrooms' table such that capacity should be greater than 0
Create a 'enrollment' table to track which student is taking which course. The table should have the following columns:

enrollment_id (numeric, primary key, auto increment)

student_id (reference to the students table)

course_id (reference to the courses table)

Click here to view the sample solution

CREATE DATABASE myDatabase;

-- Connect to the myDatabase database in a new query tool before running the rest of this script

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    date_of_birth DATE
);

CREATE TABLE courses (
    course_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    course_name VARCHAR(50),
    credit_hours INT
);

ALTER TABLE courses ADD CONSTRAINT uc_course UNIQUE (course_name);

CREATE TABLE professors (
    professor_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    subject VARCHAR(50)
);

ALTER TABLE professors ALTER COLUMN subject SET NOT NULL;

CREATE TABLE classrooms (
    classroom_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    classroom_name VARCHAR(50),
    capacity INT
);

ALTER TABLE classrooms ADD CONSTRAINT chk_classrooms_capacity CHECK (capacity > 0);

CREATE TABLE enrollment (
    enrollment_id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id),
    FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

PostgreSQL has no USE statement (connect to the new database from a fresh query tool instead), no AUTO_INCREMENT (use GENERATED ALWAYS AS IDENTITY), and no MODIFY clause on ALTER TABLE (use ALTER COLUMN ... SET NOT NULL).

Overview​

EXERCISES​

Overview

EXERCISES