Database Management Systems Assignment Due Date

ISMN 3830 Database Management Systems Assignment Due Date: Friday, March 13, 2015 at 11:59 p.m. (CST)

Read each question stated below and write SQL statements for this database as follows: 1) Find tables in page 237, 238 and 239. Write SQL CREATE TABLE and SQL INSERT statements data for: 1. SQL statements for Table 1 (p. 237) – QACS CUSTOMER 2. SQL statements for Table 2 (p. 238) – QACS EMPLOYEE 3. SQL statement for Table 3 (p. 240) – QACS ITEM 4. SQL statement for Table 4 (p. 241) – QACS SALE 2) Write SQL statements to list all columns for all tables. 3) Write an SQL statement to list ItemID and ItemDescription for all items that cost $1000 or more. 4) Write an SQL statement to list LastName, FirstName, and Phone of the customer who made the purchase with SaleID 1. Use a subquery. 5) Write an SQL statement to list LastName, FirstName, and Phone of the customers who made the purchase with SaleIDs 1, 2, and 3. Use a subquery. 6) Write an SQL statement to list LastName, FirstName, and Phone of customers who have made at least one purchase with SubTotal greater than $500. Use a subquery. 7) Write an SQL statement to list LastName, FirstName, and Phone of customers who have purchased an item that has an ItemPrice of $500 or more. Use a subquery. 8) Write SQL CREATE TABLE statement for table VENDOR (p. 239) and insert the data (the first three rows only) into the VENDOR table. 9) Write an SQL statement to modify the vendor with CompanyName of Linens and Things to Linens and Other Stuff.

Paper For Above instruction

This paper addresses the comprehensive SQL queries and database design steps necessary to model and interrogate a retail store database, exemplified by the Queen Anne Curiosity Shop scenario. The core objectives involve creating tables, inserting data, and retrieving information via complex queries—defining relationships among customers, employees, items, sales, and vendors, and illustrating advanced SQL techniques such as subqueries and table modification commands.

Introduction

The Queen Anne Curiosity Shop offers both antiques and non-antique household items, requiring a robust relational database to manage its diverse inventory, customer interactions, and sales transactions. Designing such a database entails establishing multiple tables with pertinent fields, inserting realistic sample data, and crafting SQL queries to extract meaningful insights. This case provides an excellent context for applying SQL Data Definition Language (DDL) commands to create and modify tables, as well as Data Manipulation Language (DML) commands to query and update data effectively.

Database Table Design and Data Insertion

The first step involves creating the necessary tables: CUSTOMER, EMPLOYEE, ITEM, SALE, and VENDOR. Each table contains attributes tailored to its entity, for example, CUSTOMER includes CustomerID, LastName, FirstName, Address, and Phone. Following creation, sample data is inserted to reflect the store’s real-world dataset.

The CUSTOMER table manages client information, capturing identifiable details, while the EMPLOYEE table records staff data responsible for sales. The ITEM table catalogs products available for sale, with attributes indicating description, cost, price, and vendor. The SALE table links customers, employees, sale dates, and subtotals, facilitating transaction tracking. Lastly, the VENDOR table associates suppliers or vendors with their contact information—creating a comprehensive schema to support complex queries.

Sample SQL statements for creating the CUSTOMER table are as follows:

CREATE TABLE CUSTOMER (

CustomerID INT PRIMARY KEY,


LastName VARCHAR(50),


FirstName VARCHAR(50),


Address VARCHAR(100),


City VARCHAR(50),


State VARCHAR(20),


ZIP VARCHAR(10),


Phone VARCHAR(20),


Email VARCHAR(50)


);

Sample INSERT statements for the CUSTOMER table include:

INSERT INTO CUSTOMER VALUES (1, 'Shire', 'Robert', '6225 Evanston Ave N', 'Seattle', 'WA', '98103', '(206) 555-1234', 'robert.shire@example.com');

INSERT INTO CUSTOMER VALUES (2, 'Goodyear', 'Katherine', '1234 Th Ave NE', 'Seattle', 'WA', '98105', '(206) 555-5678', 'katherine.goodyear@example.com');


-- Additional sample data continues similarly for other entities.

Similar SQL statements are crafted for EMPLOYEE, ITEM, SALE, and VENDOR tables, adhering to their schema requirements.

SQL Queries for Data Retrieval

Once tables are established and populated, various SELECT statements are crafted to retrieve specific data. For example, to list all columns for all tables, the following queries are used:

SELECT * FROM CUSTOMER;

SELECT * FROM EMPLOYEE;


SELECT * FROM ITEM;


SELECT * FROM SALE;


SELECT * FROM VENDOR;

To find all items costing $1000 or more:

SELECT ItemID, ItemDescription FROM ITEM WHERE ItemPrice >= 1000;

To identify the customer associated with SaleID 1:

SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID = (

SELECT CustomerID FROM SALE WHERE SaleID = 1


);

Similarly, obtaining details for customers with specific SaleIDs employs subqueries:

SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID IN (

SELECT CustomerID FROM SALE WHERE SaleID IN (1, 2, 3)


);

The queries also encompass listing customers with a purchase subtotal exceeding $500, or customers who bought items priced at $500 or more, both utilizing subqueries to filter accurately.

SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID IN (

SELECT CustomerID FROM SALE WHERE SubTotal > 500


);
SELECT LastName, FirstName, Phone FROM CUSTOMER WHERE CustomerID IN (

SELECT CustomerID FROM ITEM WHERE ItemPrice >= 500 AND ItemID IN (


SELECT ItemID FROM SALE_ITEM WHERE SaleID IN (


SELECT SaleID FROM SALE WHERE CustomerID = CUSTOMER.CustomerID


)


)


);

Modifying Vendor Records

Regarding database modifications, the task involves creating and inserting sample data into a VENDOR table:

CREATE TABLE VENDOR (

VendorID INT PRIMARY KEY,


CompanyName VARCHAR(100),


ContactLastName VARCHAR(50),


ContactFirstName VARCHAR(50),


Address VARCHAR(100),


City VARCHAR(50),


State VARCHAR(20),


ZIP VARCHAR(10),


Phone VARCHAR(20),


Email VARCHAR(50)


);


INSERT INTO VENDOR VALUES (1, 'Antique Supplies Co.', 'Smith', 'John', '123 Antique St', 'Seattle', 'WA', '98101', '(206) 555-1000', 'j.smith@antiques.com');


INSERT INTO VENDOR VALUES (2, 'Modern Home Goods', 'Doe', 'Jane', '456 Modern Rd', 'Bellevue', 'WA', '98004', '(425) 555-2000', 'j.doe@modernhome.com');


INSERT INTO VENDOR VALUES (3, 'Linens and Things', 'Brown', 'Lisa', '789 Linen Ave', 'Seattle', 'WA', '98102', '(206) 555-3000', 'l.brown@linens.com');

To modify the vendor with CompanyName 'Linens and Things' to 'Linens and Other Stuff':

UPDATE VENDOR

SET CompanyName = 'Linens and Other Stuff'


WHERE CompanyName = 'Linens and Things';

This structured approach ensures an operational, queryable database to support store functions and analytical needs.

Conclusion

Constructing and querying a relational database for a retail environment involves careful table design, data insertion, and crafting advanced SQL queries. Through this process, one can derive significant insights, ensure data integrity, and enable flexible data manipulation. The Queen Anne Curiosity Shop case exemplifies these principles, demonstrating how SQL facilitates effective data management and retrieval in a business context.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.

Related Assignments