Software - Database Design

Database Design & SQL

This section requires practical application of database management

• Sample Task 1: Design a simple database schema for a Library with Books and Authors. An author can write multiple books, and a book can have one author (for simplicity). Create the tables using SQL and insert one sample author and two sample books written by that author. Then, write a query to retrieve all books written by that specific author.

• Sample Task 2: Expand Schema & Query with Multiple Joins

  • Task: Add a Publishers table to the Library schema (publisher_id PK, publisher_name Varchar, city Varchar). Modify the Books table to include a publisher_id (Foreign Key referencing Publishers). Insert one sample publisher. Update the existing books to reference this publisher. Write an SQL query to retrieve the book.title, author.author_name, and publisher.publisher_name for all books.
  • Skills Tested: ALTER TABLE ADD COLUMN, ALTER TABLE ADD FOREIGN KEY (or include in initial CREATE), UPDATE, SELECT with multiple JOIN clauses.

• Sample Task 3: Data Modification and Aggregation

  • Task: Write an SQL UPDATE statement to change the nationality of 'Jane Doe' to 'Canadian'. Write an SQL query to count the number of books published in each publication_year, displaying the year and the count. Write an SQL DELETE statement to remove the author 'Jane Doe' and any books associated with her (you might need to consider ON DELETE CASCADE or delete books first depending on constraints). Note: Explain the potential issue with foreign keys when deleting.
  • Skills Tested: UPDATE, COUNT aggregate function, GROUP BY, DELETE, understanding foreign key constraints.

• Sample Task 4: Filtering with LIKE and Ordering

  • Task: Insert a few more authors and books with varied names and years. Write an SQL query to find all books where the title contains the word "Secret". Write another SQL query to list all authors whose names start with the letter 'J'. Finally, write a query to retrieve all book titles and their publication years, ordered first by publication_year (descending) and then by title (ascending).
  • Skills Tested: INSERT, WHERE with LIKE operator (using % wildcard), ORDER BY with multiple columns and directions (ASC/DESC).

• Sample Answer for Task 1 (Schema Design):

  • Authors Table:
    • author_id (Primary Key, Integer, Auto-increment)
    • author_name (Varchar)
    • nationality (Varchar)
  • Books Table:
    • book_id (Primary Key, Integer, Auto-increment)
    • title (Varchar)
    • publication_year (Integer)
    • author_id (Foreign Key referencing Authors table, Integer)

• Sample Answer (SQL Code):

  -- Create Authors Table
  CREATE TABLE Authors (
      author_id INT AUTO_INCREMENT PRIMARY KEY,
      author_name VARCHAR(100) NOT NULL,
      nationality VARCHAR(50)
  );
  
  -- Create Books Table
  CREATE TABLE Books (
      book_id INT AUTO_INCREMENT PRIMARY KEY,
      title VARCHAR(255) NOT NULL,
      publication_year INT,
      author_id INT,
      FOREIGN KEY (author_id) REFERENCES Authors(author_id)
  );
  
  -- Insert Sample Author
  INSERT INTO Authors (author_name, nationality) VALUES ('Jane Doe', 'British');
  
  -- Get the author_id of the inserted author (Assuming it's 1 for this example)
  -- In a real scenario, you might use LAST_INSERT_ID() or query by name if needed. Let's assume author_id is 1.
  
  -- Insert Sample Books for Jane Doe (author_id = 1)
  INSERT INTO Books (title, publication_year, author_id) VALUES
  ('The First Adventure', 2020, 1),
  ('Secrets of the Old Library', 2022, 1);
  
  -- Query to retrieve all books by 'Jane Doe'
  SELECT b.title, b.publication_year
  FROM Books b
  JOIN Authors a ON b.author_id = a.author_id
  WHERE a.author_name = 'Jane Doe';