Advance Database System Project

Introduction………………………………………………………………………………….. 1 2 ER Diagram………………………………………………………………………………….. 3 3 Relational Design diagrams …………………………………………………………………. 4 4 Convert Database in 3NF ……………………………………………………………………. 5 5 SQL Statement………………………………………………………………………………. 10 5.1 Create Statement…………………………………………………………………………… 10 5.2 Insert Statement……………………………………………………………………………… 13 5.3 Select Statement……………………………………………………………………………... 17 5.4 Views………………………………………………………………………………………… 20 5.5 Trigger……………………………………………………………………………………….. 24 6 Testing & Validation. ………………………………………………………………………… 25 6.1 Constraint ……………………………………………………………………………………. 25 6.2 Database ……………………………………………………………………………………... 28 7 Conclusion…………………………………………………………………………………… 30 2 INTRODUCTION Problem Domain: The database is for a retail company that sells various products to customers through their website. The database includes information about customers, products, orders, invoices, payments, employees, sales, categories, and suppliers. Requirements: ● Customers should have a unique identifier, name, address, contact number, and email address. 32 ● Products should have a unique identifier, category, name, description, price, weight, UPS code, and inventory level. ● Orders should have a unique identifier, customer identifier, product identifier, order date, and quantity. ● Invoices should have a unique identifier, order identifier, payment identifier, discount identifier, customer identifier, invoice date, due date, total amount, discount amount, tax amount, paid amount, and payment status. ● Payments should have a unique identifier, order identifier, payment date, payment amount, and payment method. ● Employees should have a unique identifier, user name, password, name, and job title. ● Sales should have a unique identifier, customer identifier, employee identifier, date and time, and total amount. ● Categories should have a unique identifier, name, and description. ● Suppliers should have a unique identifier, name, address, city, state, country, contact information, and TIN number. ● Discounts should have a unique identifier, name, description, and discount amount. ● The inventory level of a product should be an integer that is required and cannot be negative. ● The weight of a product should be a real number that is required and cannot be negative. ● The quantity of an order should be an integer that is required and cannot be negative. ● The total amount of an order should be a real number that is required and cannot be negative. ● The total amount of an invoice should be a real number that is required and cannot be negative. ● The discount amount of an invoice should be a real number that is required and cannot be negative. ● The tax amount of an invoice should be a real number that is required and cannot be negative. ● The paid amount of an invoice should be a real number that is required and cannot be negative. ● The payment status of an invoice should be a text field that is required. 1 ● The payment amount of a payment should be a real number that is required and cannot be negative. Functional Dependencies: ● Products: product_id -> category_id, name, description, price, weight, ups, inventory_level, supplier_id ● Orders: order_id -> customer_id, product_id, order_date, quantity, total_amount ● Invoices: invoice_id -> order_id, payment_id, discount_id, customer_id, invoice_date, due_date, total_amount, discount_amount, tax_amount, paid_amount, payment_status ● Payments: payment_id -> order_id, payment_date, payment_amount, payment_method ● Employees: employee_id -> user_name, password, name, job_title, contact_info ● Sales: sale_id -> customer_id, employee_id, date_time, total_amount ● Category: category_id -> name, description ● Suppliers: supplier_id -> name, address, city, state, country, contact_info, TIN_number Multivalued Dependencies: ● None identified in the given schema.