Skip to content

The purpose of this assignment is to use SQL to insert, update, and delete data in a table. We will also be creating the tables and relationships with SQL. The database below is based off the quilt fabric industry. I happen to be familiar with this industry, since my wife and I own and operate SouthernFabric.com. When you are adding data to the your database, the data does not have to be exact or correct. You do not need to worry about what designers work for what vendors and so forth.

SQL 1 – Create the Database with Relationships

Create the database using an SQL statement. Write the correct SQL statements to create the appropriate tables with all of the correct attributes, data types, and keys. All foreign key should cascade update and delete.

  • A designer can only work for one supplier (vendor), but a vendor can have lots of designers working for them. For example, Tula Pink is a designer and she works for Free Spirit a supplier. She is only allowed to work for one vendor. Free Spirit has lots of designers in design department.
  • A collection can only have one designer, but a designer can have many collections. Tula Pink comes out with a new collection every three months.
  • A specific product can be part of a collection, but a collection can have multiple products. Tula Pink created a new collection that had 5 different product types. In the Linework collection, she created a charm pack (5 in square), a layer cake (10 in square), a jelly roll (2.5 in by 44 in strip), a fat quarter bundle (18 in by 22 in) and fat eighth bundle (9 in by 22 in). Tula Pink has lots of collections.
  • A product in a collection can only have one product type, but there can be lots of products of a particular product type. The Linework collection can only have one charm pack (5 in square) as a product type, but not all collections are required to have a charm pack.

https://www.techonthenet.com/sqlite/foreign_keys/foreign_delete.php

SQL 2 – Insert the Data

Using an SQL statement insert at least 5 data points into each table.

To make this easier, the basic product types include:

  • Charm Pack (5 in square)
  • Layer Cake (10 in square)
  • Jelly Roll (2.5 in by 44 in strip)
  • Fat Quarter Bundle (18 in by 22 in)
  • Fat Eighth Bundle (9 in by 22 in)

Some prominent suppliers include:

  • Moda
  • Free Spirit
  • Riley Blake
  • Art Gallery
  • Andover
  • Robert Kaufman

Some of the best designers include:

  • Tula Pink
  • Lori Holt
  • AGF Designers
  • Bonnie and Camille
  • Bonnie Christine

Make sure you insert into the tables that are required for use as foreign keys first. For example, create the Tula Pink designer before you add her collections so you will know the correct ID to use with your relationships. You might want to start with the vendor first.

SQL 3 – Update the Data

You just found out the two of your designers have decided to switch to a different vendor. Write the correct SQL statements to 2 update the designers vendor.

SQL 4 – Remove the Data

Lately it has been so frustrating to sell charm packs on Amazon. The profit margins are just not there anymore. Write the sql statement that will delete the product type “charm pack”. Be sure the cascade delete works, and all charm packs are removed from the product table.

Turn In

Put all your sql statements into one .sql file and upload to GeorgiaView Assignment 6. I should be able to create a new SQLite DB without any tables and run your SQL script. It should create the tables (with relationships), add the data, update the data, and delete some data.