Drop Table Order Items, Orders, And Customers

Drop Table Order Itemsdrop Table Ordersdrop Table Customersdr

Drop Table Order Itemsdrop Table Ordersdrop Table Customersdr

Drop Table Order Itemsdrop Table Ordersdrop Table Customersdr

DROP TABLE ORDER_ITEMS; DROP TABLE ORDERS; DROP TABLE CUSTOMERS; 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 CUSTOMERS ( ID NUMBER PRIMARY KEY, Name VARCHAR2(50) NOT NULL, DOB DATE NOT NULL, Email VARCHAR2(50), ZipCode CHAR(5) NOT NULL); 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 PRODUCTS VALUES (2, 1, 'iPhone Development', 24); INSERT INTO PRODUCTS VALUES (3, 1, 'A Raisin in the Sun', 11); INSERT INTO PRODUCTS VALUES (4, 1, 'Lone Wolf', 17); INSERT INTO PRODUCTS VALUES (5, 1, 'Harry Potter', 50); INSERT INTO PRODUCTS VALUES (6, 2, 'USB Flash Drive', 25); INSERT INTO PRODUCTS VALUES (7, 2, 'Laptop Mouse', 35); INSERT INTO PRODUCTS VALUES (8, 2, 'Laser Printer', 250); INSERT INTO PRODUCTS VALUES (9, 2, 'Speaker System', 65); INSERT INTO PRODUCTS VALUES (10, 2, 'Broadband Router', 75); INSERT INTO PRODUCTS VALUES (11, 3, 'Student Backpack', 35); INSERT INTO PRODUCTS VALUES (12, 3, 'Flash Light', 80); INSERT INTO PRODUCTS VALUES (13, 3, 'Men''s Jacket', 99); INSERT INTO PRODUCTS VALUES (14, 3, 'Binocular', 60); INSERT INTO PRODUCTS VALUES (15, 3, 'Swiss Army Knife', 20); INSERT INTO CUSTOMERS VALUES (1, 'John M.

Newton', '11-JAN-1980', ' [email protected] ', 21001); INSERT INTO CUSTOMERS VALUES (2, 'David S. Harrington', '21-JUL-1985', ' [email protected] ', 21002); INSERT INTO CUSTOMERS VALUES (3, 'Bob R. Hume', '21-JAN-1990', ' [email protected] ', 21003); INSERT INTO CUSTOMERS VALUES (4, 'Jeff A. Newman', '12-FEB-1975', ' [email protected] ', 21004); INSERT INTO CUSTOMERS VALUES (5, 'Bill G. Davidson', '21-DEC-1989', ' [email protected] ', 21005); INSERT INTO CUSTOMERS VALUES (6, 'Joe A.

Huntsman', '10-MAR-1965', ' [email protected] ', 21006); INSERT INTO CUSTOMERS VALUES (7, 'Chris W. White', '22-MAR-1955', ' [email protected] ', 21007); INSERT INTO CUSTOMERS VALUES (8, 'Mark A. Black', '13-JAN-1973', ' [email protected] ', 21008); INSERT INTO CUSTOMERS VALUES (9, 'Vlad B. Johnson', '16-DEC-1997', ' [email protected] ', 21009); INSERT INTO CUSTOMERS VALUES (10, 'Matt C. Allen', '04-OCT-1977', ' [email protected] ', 21010); INSERT INTO ORDERS VALUES (1, 1, '25-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (1, 1, 2, '25-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (1, 3, 1, '25-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (1, 5, 3, '25-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (1, 9, 2, '25-MAR-2015'); INSERT INTO ORDERS VALUES (2, 2, '26-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (2, 11, 1, '27-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (2, 13, 4, '27-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (2, 15, 2, '27-MAR-2015'); INSERT INTO ORDERS VALUES (3, 3, '27-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (3, 2, 1, '28-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (3, 4, 1, '28-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (3, 14, 2, '28-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (3, 7, 2, NULL); INSERT INTO ORDERS VALUES (13, 3, '07-APR-2015'); INSERT INTO ORDER_ITEMS VALUES (13, 12, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (13, 14, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (13, 8, 1, NULL); INSERT INTO ORDERS VALUES (4, 5, '28-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (4, 5, 2, '29-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (4, 8, 2, '29-MAR-2015'); INSERT INTO ORDER_ITEMS VALUES (4, 6, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (4, 12, 1, NULL); INSERT INTO ORDERS VALUES (14, 4, '08-AUG-2014'); INSERT INTO ORDER_ITEMS VALUES (14, 2, 1, '29-AUG-2014'); INSERT INTO ORDER_ITEMS VALUES (14, 10, 2, '29-AUG-2014'); INSERT INTO ORDER_ITEMS VALUES (14, 6, 3, NULL); INSERT INTO ORDER_ITEMS VALUES (14, 9, 2, NULL); INSERT INTO ORDERS VALUES (5, 6, '18-APR-2015'); INSERT INTO ORDER_ITEMS VALUES (5, 1, 2, NULL); INSERT INTO ORDER_ITEMS VALUES (5, 3, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (5, 15, 3, NULL); INSERT INTO ORDER_ITEMS VALUES (5, 5, 4, NULL); INSERT INTO ORDERS VALUES (6, 6, '01-APR-2015'); INSERT INTO ORDER_ITEMS VALUES (6, 11, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (6, 13, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (6, 5, 2, NULL); INSERT INTO ORDER_ITEMS VALUES (6, 7, 1, NULL); INSERT INTO ORDERS VALUES (7, 7, '07-SEP-2014'); INSERT INTO ORDER_ITEMS VALUES (7, 1, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (7, 2, 2, NULL); INSERT INTO ORDER_ITEMS VALUES (7, 9, 1, NULL); INSERT INTO ORDERS VALUES (8, 7, '07-OCT-2014'); INSERT INTO ORDER_ITEMS VALUES (8, 4, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (8, 8, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (8, 14, 1, NULL); INSERT INTO ORDERS VALUES (9, 2, '14-NOV-2014'); INSERT INTO ORDER_ITEMS VALUES (9, 13, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (9, 3, 2, NULL); INSERT INTO ORDER_ITEMS VALUES (9, 5, 1, NULL); INSERT INTO ORDERS VALUES (10, 8, '24-DEC-2014'); INSERT INTO ORDER_ITEMS VALUES (10, 15, 3, NULL); INSERT INTO ORDER_ITEMS VALUES (10, 1, 2, NULL); INSERT INTO ORDER_ITEMS VALUES (10, 9, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (10, 8, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (10, 11, 1, NULL); INSERT INTO ORDERS VALUES (11, 8, '04-JAN-2015'); INSERT INTO ORDER_ITEMS VALUES (11, 15, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (11, 12, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (11, 5, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (11, 2, 3, NULL); INSERT INTO ORDER_ITEMS VALUES (11, 6, 1, NULL); INSERT INTO ORDERS VALUES (12, 9, '24-DEC-2014'); INSERT INTO ORDER_ITEMS VALUES (12, 1, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (12, 7, 2, NULL); INSERT INTO ORDER_ITEMS VALUES (12, 9, 4, NULL); INSERT INTO ORDER_ITEMS VALUES (12, 12, 3, NULL); INSERT INTO ORDERS VALUES (15, 10, '05-NOV-2013'); INSERT INTO ORDER_ITEMS VALUES (15, 13, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (15, 7, 2, NULL); INSERT INTO ORDER_ITEMS VALUES (15, 8, 1, NULL); INSERT INTO ORDER_ITEMS VALUES (15, 2, 1, NULL);

Paper For Above instruction

Drop Table Order Itemsdrop Table Ordersdrop Table Customersdr

The provided script demonstrates a comprehensive approach to managing relational database tables for an e-commerce-like system. The script begins with the imperative command to drop existing tables: ORDER_ITEMS, ORDERS, and CUSTOMERS, which are likely to be dependent on other tables or contain existing data. This step ensures a clean state for creating fresh table structures and avoids conflicts or duplication when rerunning the script. Properly dropping tables is essential in database management for maintenance, updates, or resetting environments for testing purposes.

Creating the Database Schema: Tables and Relationships

The script proceeds to create multiple tables: CATEGORIES, PRODUCTS, CUSTOMERS, ORDERS, and ORDER_ITEMS, each serving a specific role within the database schema. These tables are interconnected via foreign key constraints, forming a normalized relational structure suitable for an online retail environment.

Categories Table

The CATEGORIES table comprises an ID and a Name, with the ID serving as the primary key. This table categorizes products into groups such as 'Books', 'Electronics', and 'Outdoors'. The essential role of this structure is to facilitate product classification and filtering.

Products Table

The PRODUCTS table contains an ID, CatID (foreign key referencing CATEGORIES), Name, and Price. By linking products to categories via CatID, the design maintains data integrity and enables efficient querying of products by category. The inclusion of product prices supports features like price-based filtering or calculations in sales reports.

Customers Table

The CUSTOMERS table records customer information with an ID, Name, Date of Birth (DOB), Email, and ZipCode. This structure allows for customer profiling and segmentation based on geographic or demographic data, useful for marketing and personalized communication.

Orders Table

The ORDERS table tracks individual orders, tying each one to a customer through CustID, and recording the date placed. The default value of the current system date (SYSDATE) automates timestamping when an order is created. This table serves as the core transactional record for purchases.

Order Items Table

The ORDER_ITEMS table details which products are included in each order, with fields for OrderID, ProdID, Quantity, and optional DateShipped. Foreign key constraints establish relationships to the ORDERS and PRODUCTS tables, ensuring referential integrity. This table enables detailed tracking of order contents, quantities, and shipping status.

Data Insertions and Their Significance

The SQL INSERT statements populate the database with sample data for categories, products, customers, orders, and order items. These data entries simulate real-world data, providing a foundation for testing queries, reporting, and application development.

For example, the CATEGORIES table includes 'Books', 'Electronics', and 'Outdoors', representing diverse product sectors. The PRODUCTS table contains specific items with associated prices, reflecting typical pricing strategies. The CUSTOMERS table lists individuals with relevant contact and demographic information, enabling personalization and marketing efforts. The ORDERS and ORDER_ITEMS tables model actual purchase transactions, showcasing various order sizes, product combinations, and shipping dates.

Data Relationships and Integrity Constraints

The foreign key constraints in the schema are crucial for maintaining data consistency. For instance, the PRODUCTS.CatID references CATEGORIES.ID, preventing the insertion of products under nonexistent categories. Similarly, ORDER_ITEMS.OrderID and ProdID constraints enforce valid associations between orders and their items. These relationships enable robust and reliable data retrieval, such as generating sales reports per category or customer purchase history.

Conclusion: Implications for Database Design

The script exemplifies best practices in relational database design—normalization, referential integrity, and comprehensive data modeling—tailored for an e-commerce system. By carefully defining tables and relationships, and populating them with representative data, developers and database administrators create a scalable foundation for application development, analytics, and business intelligence. Proper handling of table drops and data insertion fosters a clean environment for iterative development and testing.

References

• Elmasri, R., & Navathe, S. B. (2015). Database System Concepts (6th ed.). Pearson.