Drop View Customer Unshipped Products 338787

Drop View Customer Unshipped Productsdrop Table Order Itemsdrop

Drop View Customer Unshipped Productsdrop Table Order Itemsdrop

DROP VIEW CUSTOMER_UNSHIPPED_PRODUCTS; DROP TABLE ORDER_ITEMS; DROP TABLE ORDERS; DROP TABLE SHOPPING_CART_ITEMS; DROP TABLE CUSTOMERS; DROP TABLE INVENTORY_ITEMS; DROP TABLE PRODUCT_FEATURES; DROP TABLE PRODUCTS; DROP TABLE CATEGORIES; CREATE TABLE CATEGORIES ( ID NUMBER PRIMARY KEY, Name VARCHAR2(20) NOT NULL); CREATE TABLE PRODUCTS ( ID NUMBER PRIMARY KEY, CatID NUMBER, Name VARCHAR2(20) NOT NULL, Price NUMBER NOT NULL, FOREIGN KEY (CatID) REFERENCES CATEGORIES(ID)); CREATE TABLE PRODUCT_FEATURES ( ProdID NUMBER NOT NULL, Name VARCHAR2(20) NOT NULL, Value VARCHAR(50) NOT NULL, FOREIGN KEY (ProdID) REFERENCES PRODUCTS(ID)); CREATE TABLE INVENTORY_ITEMS ( ProdID NUMBER PRIMARY KEY, Quantity NUMBER NOT NULL, FOREIGN KEY (ProdID) REFERENCES PRODUCTS(ID)); CREATE TABLE CUSTOMERS ( ID NUMBER PRIMARY KEY, Name VARCHAR2(50) NOT NULL, Email VARCHAR2(50)); CREATE TABLE SHOPPING_CART_ITEMS ( CustID NUMBER NOT NULL, ProdID NUMBER NOT NULL, Quantity NUMBER NOT NULL, WishList CHAR(1) DEFAULT 'N' CHECK (WishList IN ('Y','N')), LastUpdate DATE DEFAULT SYSDATE, PRIMARY KEY (CustID, ProdID), FOREIGN KEY (CustID) REFERENCES CUSTOMERS(ID), FOREIGN KEY (ProdID) REFERENCES PRODUCTS(ID)); CREATE TABLE ORDERS ( ID NUMBER PRIMARY KEY, CustID NUMBER NOT NULL, DatePlaced DATE DEFAULT SYSDATE, FOREIGN KEY (CustID) REFERENCES CUSTOMERS(ID)); CREATE TABLE ORDER_ITEMS ( OrderID NUMBER NOT NULL, ProdID NUMBER NOT NULL, Quantity NUMBER DEFAULT 1, DateShipped DATE, FOREIGN KEY (OrderID) REFERENCES ORDERS(ID), FOREIGN KEY (ProdID) REFERENCES PRODUCTS(ID)); INSERT INTO CATEGORIES VALUES (1, 'Books'); INSERT INTO CATEGORIES VALUES (2, 'Electronics'); INSERT INTO CATEGORIES VALUES (3, 'Outdoors'); INSERT INTO PRODUCTS VALUES (1, 1, 'The Lost Years', 15); INSERT INTO PRODUCT_FEATURES VALUES (1, 'Author', 'Mary Higgings Clark'); INSERT INTO PRODUCT_FEATURES VALUES (1, 'Category', 'Suspense'); INSERT INTO PRODUCTS VALUES (2, 1, 'iPhone Development', 24); INSERT INTO PRODUCT_FEATURES VALUES (2, 'Author', 'David Mark'); INSERT INTO PRODUCT_FEATURES VALUES (2, 'Author', 'Jack Nutting'); INSERT INTO PRODUCT_FEATURES VALUES (2, 'Author', 'Jeff LaMarche'); INSERT INTO PRODUCT_FEATURES VALUES (2, 'Category', 'Technology'); INSERT INTO PRODUCTS VALUES (3, 1, 'A Raisin in the Sun', 11); INSERT INTO PRODUCT_FEATURES VALUES (3, 'Author', 'Lorraine Hansberry'); INSERT INTO PRODUCT_FEATURES VALUES (3, 'Category', 'Drama'); INSERT INTO PRODUCTS VALUES (4, 1, 'Lone Wolf', 17); INSERT INTO PRODUCT_FEATURES VALUES (4, 'Author', 'Jodi Picoult'); INSERT INTO PRODUCT_FEATURES VALUES (4, 'Category', 'Contemporary'); INSERT INTO PRODUCTS VALUES (5, 1, 'Harry Potter', 50); INSERT INTO PRODUCT_FEATURES VALUES (5, 'Author', 'J.K. Rowling'); INSERT INTO PRODUCT_FEATURES VALUES (5, 'Category', 'Children'); INSERT INTO PRODUCTS VALUES (6, 2, 'USB Flash Drive', 25); INSERT INTO PRODUCT_FEATURES VALUES (6, 'Brand', 'Kingston'); INSERT INTO PRODUCT_FEATURES VALUES (6, 'Capacity', '16 GB'); INSERT INTO PRODUCTS VALUES (7, 2, 'Laptop Mouse', 35); INSERT INTO PRODUCT_FEATURES VALUES (7, 'Brand', 'Logitech'); INSERT INTO PRODUCT_FEATURES VALUES (7, 'Connection', 'Wireless'); INSERT INTO PRODUCTS VALUES (8, 2, 'Laser Printer', 250); INSERT INTO PRODUCT_FEATURES VALUES (8, 'Brand', 'HP'); INSERT INTO PRODUCT_FEATURES VALUES (8, 'Print Speed', '15 ppm'); INSERT INTO PRODUCT_FEATURES VALUES (8, 'Color', 'Yes'); INSERT INTO PRODUCTS VALUES (9, 2, 'Speaker System', 65); INSERT INTO PRODUCT_FEATURES VALUES (9, 'Brand', 'Logitech'); INSERT INTO PRODUCT_FEATURES VALUES (9, 'Power', '200 W'); INSERT INTO PRODUCTS VALUES (10, 2, 'Broadband Router', 75); INSERT INTO PRODUCT_FEATURES VALUES (10, 'Brand', 'Linksys'); INSERT INTO PRODUCT_FEATURES VALUES (10, 'Number of Ports', '4'); INSERT INTO PRODUCT_FEATURES VALUES (10, 'Connection', 'Wireless'); INSERT INTO PRODUCTS VALUES (11, 3, 'Student Backpack', 35); INSERT INTO PRODUCT_FEATURES VALUES (11, 'Capacity', '31 liters'); INSERT INTO PRODUCT_FEATURES VALUES (11, 'Color', 'Black'); INSERT INTO PRODUCT_FEATURES VALUES (11, 'Brand', 'Jan Sport'); INSERT INTO PRODUCTS VALUES (12, 3, 'Flash Light', 80); INSERT INTO PRODUCT_FEATURES VALUES (12, 'Weight', '6.2 onces'); INSERT INTO PRODUCT_FEATURES VALUES (12, 'LifeTime', '100000 hours'); INSERT INTO PRODUCT_FEATURES VALUES (12, 'Waterproof', 'Yes'); INSERT INTO PRODUCTS VALUES (13, 3, 'Men''s Jacket', 99); INSERT INTO PRODUCT_FEATURES VALUES (13, 'Brand', 'Columbia'); INSERT INTO PRODUCT_FEATURES VALUES (13, 'Color', 'Red'); INSERT INTO PRODUCT_FEATURES VALUES (13, 'Waterproof', 'No'); INSERT INTO PRODUCTS VALUES (14, 3, 'Binocular', 60); INSERT INTO PRODUCT_FEATURES VALUES (14, 'Brand', 'Celestron'); INSERT INTO PRODUCT_FEATURES VALUES (14, 'Magnification', '8x'); INSERT INTO PRODUCTS VALUES (15, 3, 'Swiss Army Knife', 20); INSERT INTO PRODUCT_FEATURES VALUES (15, 'Number of Blades', '5'); INSERT INTO PRODUCT_FEATURES VALUES (15, 'Size', '2 1/4 inches'); INSERT INTO INVENTORY_ITEMS VALUES (1, 10); INSERT INTO INVENTORY_ITEMS VALUES (2, 15); INSERT INTO INVENTORY_ITEMS VALUES (3, 5); INSERT INTO INVENTORY_ITEMS VALUES (4, 25); INSERT INTO INVENTORY_ITEMS VALUES (5, 30); INSERT INTO INVENTORY_ITEMS VALUES (6, 10); INSERT INTO INVENTORY_ITEMS VALUES (7, 15); INSERT INTO INVENTORY_ITEMS VALUES (8, 20); INSERT INTO INVENTORY_ITEMS VALUES (9, 25); INSERT INTO INVENTORY_ITEMS VALUES (10, 5); INSERT INTO INVENTORY_ITEMS VALUES (11, 3); INSERT INTO INVENTORY_ITEMS VALUES (12, 5); INSERT INTO INVENTORY_ITEMS VALUES (13, 12); INSERT INTO INVENTORY_ITEMS VALUES (14, 15); INSERT INTO INVENTORY_ITEMS VALUES (15, 9); INSERT INTO CUSTOMERS VALUES (1, 'Johh M Newton', ' [email protected] '); INSERT INTO CUSTOMERS VALUES (2, 'David S Harrington', ' [email protected] '); INSERT INTO CUSTOMERS VALUES (3, 'Bob R Hume', ' [email protected] '); INSERT INTO CUSTOMERS VALUES (4, 'Jeff A Newman', ' [email protected] '); INSERT INTO CUSTOMERS VALUES (5, 'Bill G Davidson', ' [email protected] '); INSERT INTO SHOPPING_CART_ITEMS VALUES (1, 1, 1, 'Y', '31-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (1, 6, 2, NULL, '25-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (1, 12, 1, NULL, '10-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (2, 3, 1, 'Y', '30-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (2, 9, 1, NULL, '22-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (2, 11, 4, NULL, '17-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (2, 14, 2, NULL, '12-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (3, 2, 2, 'N', '29-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (3, 7, 1, NULL, '21-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (3, 3, 1, 'Y', '9-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (3, 12, 2, NULL, '18-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (5, 4, 2, 'N', '24-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (5, 5, 1, NULL, '26-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (5, 8, 2, 'N', '9-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (5, 10, 3, NULL, '11-MAR-2012'); INSERT INTO SHOPPING_CART_ITEMS VALUES (5, 13, 1, NULL, '1-MAR-2012'); INSERT INTO ORDERS VALUES (1, 1, '25-MAR-2012'); INSERT INTO ORDERS VALUES (2, 2, '26-MAR-2012'); INSERT INTO ORDERS VALUES (3, 3, '27-MAR-2012'); INSERT INTO ORDERS VALUES (4, 5, '28-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (1, 1, 2, '25-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (1, 3, 1, '25-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (1, 5, 3, '25-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (1, 9, 2, '25-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (2, 11, 1, '27-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (2, 13, 4, '27-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (2, 15, 2, '27-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (3, 2, 1, '28-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (3, 4, 1, '28-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (3, 14, 2, '28-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (3, 7, 2, NULL); INSERT INTO ORDER_ITEMS VALUES (4, 5, 2, '29-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (4, 8, 2, '29-MAR-2012'); INSERT INTO ORDER_ITEMS VALUES (4, 6, 3, NULL); INSERT INTO ORDER_ITEMS VALUES (4, 9, 2, NULL);

Paper For Above instruction

The provided SQL script outlines a comprehensive setup of an e-commerce database system, including the creation, population, and management of various critical tables. These tables facilitate the management of categories, products, product features, inventory, customers, shopping carts, orders, and order items. This case study exemplifies typical relational database design principles, normalization, and referential integrity enforcement through foreign keys, which are essential for maintaining data consistency and integrity in a transactional environment.

The database schema begins with dropping existing views and tables to ensure a clean environment for reinitialization. The sequence of drop commands prevents foreign key constraint errors by removing dependent objects in the correct order. Following this, the schema initializes with the creation of multiple core tables, each with appropriate primary keys, foreign keys, and constraints. For instance, the 'PRODUCTS' table references 'CATEGORIES', establishing a clear hierarchical classification of items, while 'PRODUCT_FEATURES' links features directly to specific products, enabling flexible attribute management.

The schema includes essential attributes for online retail operations such as product names, prices, categories, and features like brand, capacity, or author, depending on the product. Inventory levels are tracked in 'INVENTORY_ITEMS', facilitating stock management and availability checks. Customer information, including names and emails, is stored in the 'CUSTOMERS' table, supporting personalized user interactions. The 'SHOPPING_CART_ITEMS' table captures the dynamic state of users' shopping carts, including purchase intent and wishlist status, with a composite primary key ensuring each product per customer is unique.

Order management is handled through 'ORDERS' and 'ORDER_ITEMS' tables, representing transactional records and their detailed line items respectively. Each 'ORDER' is linked to a customer, with 'ORDER_ITEMS' detailing purchased products, quantities, and shipment dates. The initial data population demonstrates typical product, feature, inventory, customer, shopping cart, and order data, reflecting real-world scenarios. Notable data entries include products from various categories, diverse inventory levels, and multiple customer transactions, simulating a typical online store.

This schema and data setup provide a robust foundation for developing complex queries, reports, and application logic. For example, queries can retrieve current shopping cart contents, compute total cart values, or analyze sales trends by category or individual products. The detailed foreign key relationships and constraints ensure data consistency across operations, critical for reliable e-commerce management. Overall, this script exemplifies good database design practices suitable for instructional purposes and real-world applications.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
  • Hoffer, J. A., Venkataraman, R., & Topi, H. (2016). Modern Database Management (12th ed.). Pearson.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management (11th ed.). Cengage Learning.
  • Ramkrishnan, R., & Gehrke, J. (2003). Database Management Systems (3rd ed.). McGraw-Hill.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2020). Database System Concepts (7th ed.). McGraw-Hill.
  • Coronel, C., & Morris, S. (2012). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Budd, J. M. (2014). Database and Data Communication Networks (2nd ed.). Wiley.
  • Kim, W. (2014). Introduction to Database Systems (8th ed.). Pearson.
  • Date, C. J. (2004). An Introduction to Database Systems (8th ed.). Pearson Education.

Related Assignments