CS3319B Introduction to Databases – Assignment 2

Overview

This assignment involved performing and verifying a series of SQL operations on a relational restaurant database (assign2db). The task covered:

1. Data Updates
2. Data Inserts
3. Data Queries
4. Views and Deletion with Referential Integrity

The assignment demonstrates proficiency in SQL including joins, nested queries, view creation, data manipulation, and handling foreign key constraints.

Contents

• 69_script2.sql: SQL script file containing all parts of the assignment.
• 69_outputscript2.txt: Output captured from running the script on MySQL, including query results and errors.

Assignment Breakdown

Part 1: SQL Updates

• Changed dish name from “Pasta alla Brenda” to “Pasta alla Norma” and marked it vegetarian.
• Updated all orders delivered by Marge to have a delivery rating of 3.

Part 2: SQL Inserts

• Added fictional characters:
  • Walter White as a customer
  • Jesse Pinkman as a driver
  • Favorite dish: Los Pollos Hermanos Chicken
• Attempted to insert a new order which failed due to:
  • Delivery address too long
  • Foreign key constraint violation due to previous failure

Part 3: SQL Queries

Executed various SELECT queries to retrieve information such as:

• Driver and customer statistics
• Most popular vegetarian items
• Customers who only ordered vegetarian dishes
• Order costs
• Delivery durations
• Driver assignment gaps

Part 4: Views and Deletes

• Created a view driver_orders to track deliveries.
• Attempted deletion of drivers:
  • Successfully deleted D666 (no associated orders)
  • Deletion of D333 failed due to referential integrity with cusorder table

Highlights & Issues

• Success: Most queries produced correct outputs; joins and subqueries were well-formed.
• Issue: The cusorder insertion failed due to an overly long address exceeding column limits.
• Insight: Deleting drivers requires checking referential integrity constraints first.

How to Run

To execute the script on MySQL:

mysql -u [username] -p < 69_script2.sql

Make sure the assign2db database is properly set up with required tables and constraints.

License

This submission is part of CS3319B – Introduction to Databases at Western University. For academic use only.