' Develop the Access database of Project (including tables,…

Question

' Develop the   Access database   of Project (including tables,…

Manu · Accepted Answer

Step 1: Create Tables : Customer Table : Fields: Customer ID , CName , Address , Classification Service Table : Fields: Service ID , Type , Description , Customer ID , Consumed Units Per Month , Due Date , Pay Date , Rate Per Unit , Min Monthly Charge Sign Table : Fields: Service ID , Customer ID , Sign In Date Establish Relationships : Create relationships between the tables using Customer ID and Service ID as the foreign keys. Create Queries : Query 1: List customer name, customer’s total consumption (by service), and total $ amount due. Query 2a: List the total number of new connections set per month during the last year. Query 2b: List the monthly income collected from domestic, commercial, and industrial customers during the last year. Query 2c: List the monthly income collected by electric, natural gas, water, and telecommunication services during the last year. Query 3: List the customers that always pay late. Step 2: Setting Up the Database Create the Tables : Customer Table : CREATE TABLE Customer ( CustomerID INTEGER PRIMARY KEY, CName TEXT, Address TEXT, Classification TEXT ); 2 .Service Table : CREATE TABLE Service ( ServiceID INTEGER PRIMARY KEY, Type TEXT, Description TEXT, CustomerID INTEGER, ConsumedUnitsPerMonth INTEGER, DueDate TEXT, PayDate TEXT, RatePerUnit REAL, MinMonthlyCharge REAL, FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ); 3. Sign Table: CREATE TABLE Sign ( ServiceID INTEGER, CustomerID INTEGER, SignInDate TEXT, PRIMARY KEY (ServiceID, CustomerID), FOREIGN KEY (ServiceID) REFERENCES Service(ServiceID), FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID) ); Screenshot of Implementation: Insert Sample Data Insert sample data into the tables to test your queries. Insert Sample Data into Customer Table : INSERT INTO Customer (CustomerID, CName, Address, Classification) VALUES (1, 'John Doe', '123 Elm St', 'Domestic'), (2, 'Jane Smith', '456 Oak St', 'Commercial'), (3, 'Bob Johnson', '789 Pine St', 'Industrial');  2 . I n sert Sample Data into Service Table : INSERT INTO Service (ServiceID, Type, Description, CustomerID, ConsumedUnitsPerMonth, DueDate, PayDate, RatePerUnit, MinMonthlyCharge) VALUES (1, 'Electric', 'Electric Service', 1, 150, '2024-05-01', '2024-05-10', 0.12, 30.00), (2, 'Gas', 'Gas Service', 2, 200, '2024-05-01', '2024-05-11', 0.15, 25.00), (3, 'Water', 'Water Service', 3, 100, '2024-05-01', '2024-05-09', 0.10, 20.00); 3. I nsert Sample Data into Sign Table : INSERT INTO Sign (ServiceID, CustomerID, SignInDate) VALUES (1, 1, '2023-06-01'), (2, 2, '2023-07-01'), (3, 3, '2023-08-01'); Screenshot of Implementation: Create the Queries Query 1 : Customer's total consumption (by service) and total $ amount due. SELECT c.CName, s.Type, SUM(s.ConsumedUnitsPerMonth * s.RatePerUnit) AS TotalConsumption, SUM(s.ConsumedUnitsPerMonth * s.RatePerUnit) + s.MinMonthlyCharge AS TotalAmountDue FROM Customer c JOIN Service s ON c.CustomerID = s.CustomerID GROUP BY c.CName, s.Type; Query 2a : Total number of new connections set per month during the last year. SELECT strftime('%Y-%m', SignInDate) AS Month, COUNT(*) AS NewConnections FROM Sign WHERE SignInDate >= date('now', '-1 year') GROUP BY strftime('%Y-%m', SignInDate); Query 2b : Monthly income collected from domestic, commercial, and industrial customers during the last year. SELECT c.Classification, strftime('%Y-%m', s.DueDate) AS Month, SUM(s.ConsumedUnitsPerMonth * s.RatePerUnit) AS MonthlyIncome FROM Customer c JOIN Service s ON c.CustomerID = s.CustomerID WHERE s.DueDate >= date('now', '-1 year') GROUP BY c.Classification, strftime('%Y-%m', s.DueDate); Query 2c : Monthly income collected by service type during the last year. SELECT s.Type, strftime('%Y-%m', s.DueDate) AS Month, SUM(s.ConsumedUnitsPerMonth * s.RatePerUnit) AS MonthlyIncome FROM Service s WHERE s.DueDate >= date('now', '-1 year') GROUP BY s.Type, strftime('%Y-%m', s.DueDate); Query 3 : List customers that always pay late. SELECT c.CName, COUNT(*) AS LatePayments FROM Customer c JOIN Service s ON c.CustomerID = s.CustomerID WHERE s.PayDate > s.DueDate GROUP BY c.CName HAVING COUNT(*) > 0; Explanation: Database Tables Customer Table : Stores customer information. Service Table : Records details of services used by customers. Sign Table : Logs sign-in details of services by customers. Queries Query 1 : Calculates customer's total consumption and total amount due by service. Query 2a : Counts new connections per month in the past year. Query 2b : Sums monthly income from different customer classifications in the past year. Query 2c : Sums monthly income by service type in the past year. Query 3 : Lists customers who consistently pay late. Final Answer: This project effectively organizes customer, service, and sign-in data, enabling detailed analysis and reporting. Through carefully designed tables and relationships, we can execute comprehensive queries to monitor total consumption and billing, track new connections, and analyze

AI tools

Study help

Free tools

Earn & more

Computer science

Computer science

Manu

Answer

Extracted Transcript from Images

AI

Study help

Earn & more

Company