Which structure BEST describes the pоlаrity оf а H—O bоnd? h-bond_polаrity.png
Scenаriо:Yоu hаve been hired аs a database designer fоr a retail company called ShopMart, which operates both online and in physical stores. ShopMart wants to develop a new database system to manage their daily business transactions, including customer orders, inventory management, and supplier details. They currently use spreadsheets to track these details, but they need a more robust database system to improve efficiency, handle large volumes of data, and support their analytics. Below is a simplified list of requirements for the new database: Customers: The company needs to store customer information, including customer_id, name, email, and address. Each customer may place multiple orders. Orders: The company needs to track orders placed by customers. Each order is identified by order_id, order_date, customer_id (who placed the order), and order_total. Products: The company sells different products. Each product is identified by product_id, name, category, and price. Order Items: Each order may contain one or more products. You need to track details such as order_id, product_id, quantity, and subtotal. Suppliers: Each product is supplied by one supplier. The supplier details include supplier_id, name, contact_number, and address. Final Exam Question: Part 1: Database Design (25 points) Design an ER Diagram for the above system using the Entity-Relationship Model. Your diagram should include: Entities and their primary keys. Relationships between the entities. Cardinalities for each relationship. Make sure your design is normalized to 3NF. Justify briefly why the design avoids redundancy. Part 2: SQL Queries (25 points) Based on the ER Diagram you designed, write SQL DDL statements to create the tables, with appropriate primary keys, foreign keys, and constraints. Include the tables for Customers, Orders, Products, Order Items, and Suppliers. Add any necessary constraints such as NOT NULL, UNIQUE, or CHECK to ensure data integrity. Part 3: Advanced SQL Query (20 points) Write an SQL query to find the top 5 customers (by name and email) who have spent the most on ShopMart orders. Include the total amount spent by each customer and order the result in descending order by the total amount spent. Part 4: Transactions (15 points) Consider that a customer places an order. Write a transaction script that performs the following actions: Insert a new order into the Orders table. Update the Order Items table with the products in the order. Update the Products table to decrease the stock for each product ordered. Assume you need to ensure that all steps are completed successfully or none at all (Atomicity). Provide the SQL script, including the transaction control statements (BEGIN, COMMIT, ROLLBACK). Scenario: Analyzing Customer Churn for a Telecom Company A telecom company, TeleCo, wants to understand customer behavior to reduce customer churn. The company has collected data on its customers over the past two years. The dataset contains the following attributes: customer_id: Unique identifier for each customer. tenure: Number of months the customer has stayed with the company. monthly_charges: Monthly amount billed to the customer. total_charges: Total amount billed to the customer over their tenure. contract_type: Type of contract (e.g., month-to-month, one year, two years). payment_method: The method used for payment (e.g., electronic check, credit card, bank transfer). internet_service: The type of internet service (e.g., Fiber optic, DSL, None). churn: Indicates whether the customer left the company (Yes/No). Question: Part 1: Exploratory Data Analysis (5 Marks) Perform an exploratory data analysis (EDA) on the given dataset. Explain briefly what steps you would take to understand the following aspects: Data Quality: How would you identify and handle missing values or inconsistencies in the data? Part 2: Predictive Modeling (10 Marks) You need to build a predictive model to determine which customers are at risk of churning. Answer the following: Model Selection: Which type of machine learning model (e.g., decision tree, logistic regression, random forest) would you choose and why? Consider the dataset features and the target variable (churn). Model Evaluation: Explain two metrics that you would use to evaluate the performance of your model, specifically for predicting customer churn. Why are these metrics suitable?