Creating, Modifying, and Deleting Tables in SQL

Variant 1: Online Bookstore Database

Variant 1: Online Bookstore Database

Scenario: You are tasked with setting up a database for an online bookstore. This database will initially manage books and their authors.

Table Schemas:

  1. books table:
    • book_id: Unique identifier for each book (auto-incrementing integer, primary key).
    • title: Title of the book (text, not null, maximum length 100 characters).
    • isbn: International Standard Book Number (text, unique, maximum length 20 characters).
    • publication_year: Year of publication (integer).
    • genre: Genre of the book (text, maximum length 50 characters).
  2. authors table:
    • author_id: Unique identifier for each author (auto-incrementing integer, primary key).
    • author_name: Name of the author (text, not null, maximum length 100 characters).
    • nationality: Nationality of the author (text, maximum length 50 characters).
    • birth_date: Date of birth of the author (date).

Tasks:

  1. Create the books and authors tables in your database using the CREATE TABLE statement.
  2. Add two new columns to the books table: price (decimal) and stock_quantity (integer, default value 0).
  3. Modify the genre column in the books table to have a maximum length of 75 characters and change the data type of publication_year to SMALLINT.
  4. Rename the author_name column in the authors table to name and rename the nationality column to country.
  5. Drop the birth_date column from the authors table and the genre column from the books table.
  6. Delete the authors table from the database using the DROP TABLE statement.
Variant 2: Event Management Database

Variant 2: Event Management Database

Scenario: You are building a database to manage events and venues for an event planning company.

Table Schemas:

  1. events table:
    • event_id: Unique identifier for each event (auto-incrementing integer, primary key).
    • event_name: Name of the event (text, not null, maximum length 100 characters).
    • event_date: Date of the event (date, not null).
    • start_time: Time the event starts (time).
    • category: Category of the event (text, maximum length 50 characters).
  2. venues table:
    • venue_id: Unique identifier for each venue (auto-incrementing integer, primary key).
    • venue_name: Name of the venue (text, not null, maximum length 100 characters).
    • address: Address of the venue (text, maximum length 200 characters).
    • capacity: Maximum capacity of the venue (integer).
    • venue_type: Type of venue (e.g., ‘hall’, ‘stadium’, ‘park’) (text, maximum length 50 characters).

Tasks:

  1. Create the events and venues tables in your database.
  2. Add two new columns to the events table: description (text) and is_cancelled (boolean, default false).
  3. Modify the category column in the events table to be NOT NULL and change the data type of capacity in the venues table to BIGINT.
  4. Rename the event_name column in the events table to title and rename the venue_name column in the venues table to name.
  5. Drop the start_time column from the events table and the venue_type column from the venues table.
  6. Delete the venues table from the database.
Variant 3: Music Library Database

Variant 3: Music Library Database

Scenario: You are creating a database to manage a personal music library, tracking songs and artists.

Table Schemas:

  1. songs table:
    • song_id: Unique identifier for each song (auto-incrementing integer, primary key).
    • song_title: Title of the song (text, not null, maximum length 100 characters).
    • duration_seconds: Duration of the song in seconds (integer).
    • release_year: Year the song was released (integer).
    • genre: Genre of the song (text, maximum length 50 characters).
  2. artists table:
    • artist_id: Unique identifier for each artist (auto-incrementing integer, primary key).
    • artist_name: Name of the artist (text, not null, maximum length 100 characters).
    • origin_country: Country of origin of the artist (text, maximum length 50 characters).
    • formation_year: Year the artist was formed (integer).

Tasks:

  1. Create the songs and artists tables in your database.
  2. Add two new columns to the songs table: album_name (text, maximum length 100 characters) and play_count (integer, default 0).
  3. Modify the genre column in the songs table to have a maximum length of 75 characters and change the data type of duration_seconds to REAL.
  4. Rename the song_title column in the songs table to title and rename the artist_name column in the artists table to name.
  5. Drop the release_year column from the songs table and the formation_year column from the artists table.
  6. Delete the artists table from the database.
Variant 4: Simple Blogging Platform Database

Variant 4: Simple Blogging Platform Database

Scenario: You are designing a database for a simple blogging platform to manage blog posts and categories.

Table Schemas:

  1. posts table:
    • post_id: Unique identifier for each post (auto-incrementing integer, primary key).
    • post_title: Title of the blog post (text, not null, maximum length 200 characters).
    • content: Content of the blog post (text).
    • publication_date: Date the post was published (date, default current date).
    • is_published: Status of publication (boolean, default false).
  2. categories table:
    • category_id: Unique identifier for each category (auto-incrementing integer, primary key).
    • category_name: Name of the category (text, not null, unique, maximum length 50 characters).
    • description: Description of the category (text, maximum length 200 characters).
    • created_at: Timestamp when the category was created (timestamp with time zone, default current timestamp).

Tasks:

  1. Create the posts and categories tables in your database.
  2. Add two new columns to the posts table: author_name (text, maximum length 100 characters) and view_count (integer, default 0).
  3. Modify the post_title column in the posts table to have a maximum length of 255 characters and change the data type of publication_date to TIMESTAMP.
  4. Rename the post_title column in the posts table to title and rename the category_name column in the categories table to name.
  5. Drop the is_published column from the posts table and the description column from the categories table.
  6. Delete the categories table from the database.
Variant 5: Recipe Database

Variant 5: Recipe Database

Scenario: You are creating a database to store recipes and their ingredients for a recipe application.

Table Schemas:

  1. recipes table:
    • recipe_id: Unique identifier for each recipe (auto-incrementing integer, primary key).
    • recipe_name: Name of the recipe (text, not null, maximum length 100 characters).
    • instructions: Cooking instructions (text).
    • cuisine_type: Type of cuisine (e.g., ‘Italian’, ‘Mexican’, ‘Indian’) (text, maximum length 50 characters).
    • preparation_time_minutes: Preparation time in minutes (integer).
  2. ingredients table:
    • ingredient_id: Unique identifier for each ingredient (auto-incrementing integer, primary key).
    • ingredient_name: Name of the ingredient (text, not null, unique, maximum length 100 characters).
    • is_vegetarian: Indicates if the ingredient is vegetarian (boolean, default true).
    • is_vegan: Indicates if the ingredient is vegan (boolean, default false).

Tasks:

  1. Create the recipes and ingredients tables in your database.
  2. Add two new columns to the recipes table: servings (integer, default 1) and rating (numeric, precision 2, scale 1, e.g., 4.5).
  3. Modify the cuisine_type column in the recipes table to have a maximum length of 75 characters and change the data type of preparation_time_minutes to SMALLINT.
  4. Rename the recipe_name column in the recipes table to name and rename the ingredient_name column in the ingredients table to name.
  5. Drop the instructions column from the recipes table and the is_vegan column from the ingredients table.
  6. Delete the ingredients table from the database.
Variant 6: Simple Task Management Database

Variant 6: Simple Task Management Database

Scenario: You are building a database for a simple task management application to manage tasks and projects.

Table Schemas:

  1. tasks table:
    • task_id: Unique identifier for each task (auto-incrementing integer, primary key).
    • task_name: Name of the task (text, not null, maximum length 100 characters).
    • description: Description of the task (text).
    • due_date: Date the task is due (date).
    • status: Status of the task (e.g., ‘To Do’, ‘In Progress’, ‘Completed’) (text, maximum length 50 characters, default ‘To Do’).
  2. projects table:
    • project_id: Unique identifier for each project (auto-incrementing integer, primary key).
    • project_name: Name of the project (text, not null, maximum length 100 characters).
    • start_date: Date the project started (date).
    • end_date: Date the project is expected to end (date).
    • priority: Priority of the project (e.g., ‘High’, ‘Medium’, ‘Low’) (text, maximum length 50 characters, default ‘Medium’).

Tasks:

  1. Create the tasks and projects tables in your database.
  2. Add two new columns to the tasks table: created_at (timestamp with time zone, default current timestamp) and is_urgent (boolean, default false).
  3. Modify the status column in the tasks table to have a maximum length of 75 characters and change the data type of due_date to TIMESTAMP.
  4. Rename the task_name column in the tasks table to title and rename the project_name column in the projects table to name.
  5. Drop the description column from the tasks table and the end_date column from the projects table.
  6. Delete the projects table from the database.

Submission Instructions

1. Perform the Tasks:

  • For your selected variant, complete all the tasks outlined. This involves writing and executing SQL commands in pgAdmin 4 using PostgreSQL.

2. Document Your Work in a Google Doc:

  • Create a new Google Document. example
  • At the top of the document, clearly write:
    • Your Name:
    • Variant Number: (e.g., Variant 1: Online Bookstore Database)
  • For each task within the variant, document the following:
    • Task Number: (e.g., Task 1, Task 2, Task 3, etc.)
    • SQL Command: Copy and paste the exact SQL command you executed in pgAdmin 4 for that task.
    • Confirmation/Screenshot: Include a screenshot from pgAdmin 4 (showing the table created in the Object Browser) to visually confirm the successful execution of your command.

3. Submit Your Google Doc Link:

  • Once you have completed documenting all tasks for your chosen variant in your Google Doc, ensure the document is shared so that your teacher can access it. Important: Set the sharing permissions to “Anyone with the link can Comment”.
  • Go to the Google Sheet provided by your teacher for assignment submissions. Google Sheet
  • Locate the column corresponding to your name.
  • In the appropriate cell for this Database Assignment, paste the shareable link to your Google Doc.
Playful GIF