Using pgAdmin 4 ERD Tool

Task:

  1. Using pgAdmin 4 ERD Tool, create a new ERD project.
  2. Create the tables as defined in the schema, specifying appropriate data types and primary keys.
  3. Establish the relationships between the tables by creating foreign keys as indicated. Use the “one-to-many” relationship tool for each foreign key constraint.
  4. Auto-align the tables for better readability.
  5. Generate the SQL DDL script for this database schema.
  6. Save your ERD project.
Variant 1: Library Management System

Scenario: Design a database for a small library to manage its collection of books, authors, borrowers, and loans.

Database Schema:

  • Tables:
    • Books:
      • book_id (integer, Primary Key) - Unique identifier for each book.
      • title (character varying) - Title of the book.
      • isbn (character varying) - International Standard Book Number.
      • publication_year (integer) - Year of publication.
      • genre (character varying) - Genre of the book.
    • Authors:
      • author_id (integer, Primary Key) - Unique identifier for each author.
      • first_name (character varying) - Author’s first name.
      • last_name (character varying) - Author’s last name.
    • Book_Authors: (Intersection table for many-to-many relationship between Books and Authors)
      • book_id (integer, Foreign Key referencing Books(book_id))
      • author_id (integer, Foreign Key referencing Authors(author_id))
      • (Composite Primary Key: book_id, author_id)
    • Borrowers:
      • borrower_id (integer, Primary Key) - Unique identifier for each borrower.
      • first_name (character varying) - Borrower’s first name.
      • last_name (character varying) - Borrower’s last name.
      • address (character varying) - Borrower’s address.
      • phone_number (character varying) - Borrower’s phone number.
    • Loans:
      • loan_id (integer, Primary Key) - Unique identifier for each loan.
      • book_id (integer, Foreign Key referencing Books(book_id))
      • borrower_id (integer, Foreign Key referencing Borrowers(borrower_id))
      • loan_date (date) - Date when the book was borrowed.
      • return_date (date, Nullable) - Date when the book was returned (can be null if not yet returned).
  • Relationships:
    • One-to-many relationship between Authors and Book_Authors (One Author can write multiple books).
    • One-to-many relationship between Books and Book_Authors (One Book can be written by multiple authors).
    • One-to-many relationship between Books and Loans (One Book can be loaned multiple times).
    • One-to-many relationship between Borrowers and Loans (One Borrower can take out multiple loans).
Variant 2: Online Bookstore Database

Scenario: Design a database for an online bookstore to manage books, authors, customers, orders, and publishers.

Database Schema:

  • Tables:
    • Books:
      • book_id (integer, Primary Key)
      • title (character varying)
      • isbn (character varying)
      • publication_year (integer)
      • price (numeric)
      • publisher_id (integer, Foreign Key referencing Publishers(publisher_id))
    • Authors:
      • author_id (integer, Primary Key)
      • first_name (character varying)
      • last_name (character varying)
    • Book_Authors: (Intersection table)
      • book_id (integer, Foreign Key referencing Books(book_id))
      • author_id (integer, Foreign Key referencing Authors(author_id))
      • (Composite Primary Key: book_id, author_id)
    • Customers:
      • customer_id (integer, Primary Key)
      • first_name (character varying)
      • last_name (character varying)
      • email (character varying)
      • address (character varying)
    • Publishers:
      • publisher_id (integer, Primary Key)
      • publisher_name (character varying)
      • publisher_address (character varying)
    • Orders:
      • order_id (integer, Primary Key)
      • customer_id (integer, Foreign Key referencing Customers(customer_id))
      • order_date (date)
    • Order_Items: (Intersection table for many-to-many relationship between Orders and Books)
      • order_item_id (integer, Primary Key) - Unique identifier for each item in an order.
      • order_id (integer, Foreign Key referencing Orders(order_id))
      • book_id (integer, Foreign Key referencing Books(book_id))
      • quantity (integer)
      • price (numeric) - Price of the book at the time of order (could be different from current book price).
  • Relationships:
    • One-to-many relationship between Authors and Book_Authors.
    • One-to-many relationship between Books and Book_Authors.
    • One-to-many relationship between Publishers and Books.
    • One-to-many relationship between Customers and Orders.
    • One-to-many relationship between Orders and Order_Items.
    • One-to-many relationship between Books and Order_Items.
Variant 3: University Course Enrollment System Database

Scenario: Design a database for a university to manage students, courses, professors, departments, and course enrollments.

Database Schema:

  • Tables:
    • Students:
      • student_id (integer, Primary Key)
      • first_name (character varying)
      • last_name (character varying)
      • major (character varying)
    • Courses:
      • course_id (integer, Primary Key)
      • course_name (character varying)
      • course_code (character varying)
      • credits (integer)
      • department_id (integer, Foreign Key referencing Departments(department_id))
    • Professors:
      • professor_id (integer, Primary Key)
      • first_name (character varying)
      • last_name (character varying)
      • department_id (integer, Foreign Key referencing Departments(department_id))
    • Departments:
      • department_id (integer, Primary Key)
      • department_name (character varying)
      • building (character varying)
    • Enrollments: (Intersection table - represents a student enrolled in a course taught by a professor)
      • enrollment_id (integer, Primary Key) - Unique identifier for each enrollment record.
      • student_id (integer, Foreign Key referencing Students(student_id))
      • course_id (integer, Foreign Key referencing Courses(course_id))
      • professor_id (integer, Foreign Key referencing Professors(professor_id))
      • semester (character varying) - e.g., “Fall 2024”, “Spring 2025”.
      • grade (character varying, Nullable) - Grade received by the student (can be null if course is in progress).
  • Relationships:
    • One-to-many relationship between Departments and Courses.
    • One-to-many relationship between Departments and Professors.
    • One-to-many relationship between Students and Enrollments.
    • One-to-many relationship between Courses and Enrollments.
    • One-to-many relationship between Professors and Enrollments.
Variant 4: Music Streaming Service Database

Scenario: Design a database for a music streaming service to manage artists, albums, songs, users, and playlists.

Database Schema:

  • Tables:
    • Artists:
      • artist_id (integer, Primary Key)
      • artist_name (character varying)
      • genre (character varying)
    • Albums:
      • album_id (integer, Primary Key)
      • album_name (character varying)
      • artist_id (integer, Foreign Key referencing Artists(artist_id))
      • release_year (integer)
    • Songs:
      • song_id (integer, Primary Key)
      • song_name (character varying)
      • album_id (integer, Foreign Key referencing Albums(album_id))
      • track_number (integer)
      • duration (integer) - Duration in seconds.
    • Users:
      • user_id (integer, Primary Key)
      • username (character varying, unique)
      • email (character varying, unique)
      • registration_date (date)
    • Playlists:
      • playlist_id (integer, Primary Key)
      • playlist_name (character varying)
      • user_id (integer, Foreign Key referencing Users(user_id))
      • creation_date (date)
    • Playlist_Songs: (Intersection table)
      • playlist_id (integer, Foreign Key referencing Playlists(playlist_id))
      • song_id (integer, Foreign Key referencing Songs(song_id))
      • (Composite Primary Key: playlist_id, song_id)
      • added_date (timestamp)
  • Relationships:
    • One-to-many relationship between Artists and Albums.
    • One-to-many relationship between Albums and Songs.
    • One-to-many relationship between Users and Playlists.
    • One-to-many relationship between Playlists and Playlist_Songs.
    • One-to-many relationship between Songs and Playlist_Songs.
Variant 5: E-commerce Product Catalog Database

Scenario: Design a database for an e-commerce platform to manage products, categories, brands, suppliers, and product reviews.

Database Schema:

  • Tables:
    • Products:
      • product_id (integer, Primary Key)
      • product_name (character varying)
      • description (text)
      • price (numeric)
      • category_id (integer, Foreign Key referencing Categories(category_id))
      • brand_id (integer, Foreign Key referencing Brands(brand_id))
      • supplier_id (integer, Foreign Key referencing Suppliers(supplier_id))
    • Categories:
      • category_id (integer, Primary Key)
      • category_name (character varying)
    • Brands:
      • brand_id (integer, Primary Key)
      • brand_name (character varying)
    • Suppliers:
      • supplier_id (integer, Primary Key)
      • supplier_name (character varying)
      • supplier_contact (character varying)
    • Product_Reviews:
      • review_id (integer, Primary Key)
      • product_id (integer, Foreign Key referencing Products(product_id))
      • rating (integer) - e.g., 1 to 5 stars.
      • comment (text, Nullable)
      • review_date (timestamp)
  • Relationships:
    • One-to-many relationship between Categories and Products.
    • One-to-many relationship between Brands and Products.
    • One-to-many relationship between Suppliers and Products.
    • One-to-many relationship between Products and Product_Reviews.
Variant 6: Event Ticketing System Database

Scenario: Design a database for an event ticketing system to manage events, venues, tickets, customers, and orders.

Database Schema:

  • Tables:
    • Events:
      • event_id (integer, Primary Key)
      • event_name (character varying)
      • event_date (timestamp)
      • venue_id (integer, Foreign Key referencing Venues(venue_id))
    • Venues:
      • venue_id (integer, Primary Key)
      • venue_name (character varying)
      • venue_address (character varying)
      • capacity (integer)
    • Tickets:
      • ticket_id (integer, Primary Key)
      • event_id (integer, Foreign Key referencing Events(event_id))
      • ticket_type (character varying) - e.g., “General Admission”, “VIP”.
      • price (numeric)
      • quantity_available (integer)
    • Customers:
      • customer_id (integer, Primary Key)
      • first_name (character varying)
      • last_name (character varying)
      • email (character varying)
    • Orders:
      • order_id (integer, Primary Key)
      • customer_id (integer, Foreign Key referencing Customers(customer_id))
      • order_date (timestamp)
    • Order_Tickets: (Intersection table)
      • order_ticket_id (integer, Primary Key) - Unique identifier for each ticket item in an order.
      • order_id (integer, Foreign Key referencing Orders(order_id))
      • ticket_id (integer, Foreign Key referencing Tickets(ticket_id))
      • quantity (integer)
      • price (numeric) - Price of the ticket at the time of order.
  • Relationships:
    • One-to-many relationship between Venues and Events.
    • One-to-many relationship between Events and Tickets.
    • One-to-many relationship between Customers and Orders.
    • One-to-many relationship between Orders and Order_Tickets.
    • One-to-many relationship between Tickets and Order_Tickets.

Submission Instructions:

Step 1: Capture a High-Quality Screenshot of Your ER Diagram

  1. Maximize ERD Canvas: In pgAdmin 4, ensure your entire ER Diagram is visible on the screen. You might need to zoom out slightly or use the “Auto Align” feature again to ensure all tables and relationships are clearly displayed and not overlapping or cut off.

  1. Take a Screenshot: Capture a screenshot of your entire pgAdmin 4 window, clearly showing the ER Diagram you have created.
  2. Review Screenshot Quality: Open the screenshot and verify that:
    • The entire ER Diagram is visible.
    • Table names, column names, data types, primary keys, foreign keys, and relationships are clearly readable.
    • The screenshot is not blurry or pixelated. If it’s unclear, retake the screenshot.
  3. Save the Screenshot: Save the screenshot as an image file (e.g., .png, .jpg). Name the file informatively, such as Variant[Variant Number]_ERD_Screenshot_[YourLastName]_[YourFirstName].png (e.g., Variant1_ERD_Screenshot_Smith_John.png).

Step 2: Upload Screenshot to Google Drive

  1. Open Google Drive: Go to your Google Drive account in a web browser.
  2. Navigate to Submission Folder (Optional but Recommended): You may want to create a new folder in your Google Drive specifically for this Databases course or assignment submissions to keep things organized.
  3. Upload File: Click the “+ New” button (or “Upload”) and select “File upload”. Choose the screenshot image file you saved in Step 7. Wait for the upload to complete.

Step 3: Set Share Permissions for the Screenshot on Google Drive

  1. Locate Uploaded File: Find the screenshot file you just uploaded in your Google Drive.
  2. Get Shareable Link: Right-click on the screenshot file and select “Get link” (or “Share”).
  3. Change Link Settings: In the sharing dialog that appears, click on “Change” next to “Restricted” (or the current access setting).
  4. Select “Anyone with the link”: Choose the option “Anyone with the link” from the dropdown menu.
  5. Set Permission to “Viewer”: Ensure the permission is set to “Viewer” (it should be by default).
  6. Copy Link: Click the “Copy link” button. The link to your screenshot is now copied to your clipboard.
  7. Click “Done”: Close the sharing dialog.

Step 4: Submit the Google Drive Link in Google Sheets

  1. Open the Google Sheet: google sheets for this task
  2. Find Your Row: Locate your name or student ID in the first column of the Google Sheet. Your submission cell will be in the corresponding row.
  3. Insert Link as a Comment: * Click on the cell designated for your submission. * Right-click on the cell and select “Insert comment” (or “Comment”). * Paste the Google Drive link you copied in Step 3 into the comment box. * Click “Comment” or the “Post” button to save the comment with the link.
  4. Verify Submission: Double-check that your comment is visible in the cell and that you’ve pasted the link correctly.

Important Reminders:

  • Ensure “Anyone with the link can view” access is set on Google Drive. If this is not set correctly, your teacher will not be able to see your screenshot.
  • Test your link! Open the link in a private browsing window or on a different device to confirm it works as expected and displays your screenshot.
  • Submit by the deadline. Check the assignment instructions for the submission deadline.