Broadband Billing System Database Design SQL Project

Database design of Broadband Billing System application with the help of stored procedures and triggers in SQL Server.

ER Diagram for Broadband Billing System:

Data Model Diagram for Broadband Billing System:

Create Data Base:

For this purpose, created a Database Named “BBS” on the server.

• Right Click on Database in Object Explorer of SSMS
• Click on New
• And Provide the name and hit the ok button

Create Objects specified in Data Model & Add data to the “Subscription” table

Run the BBS_CreateTables script that will create the below data model in SQL.

Creation of SQL Procedures:

SP#1

Create a procedure that gets the customer’s name, customer mobile number, id proof, type, city, area, and password as inputs and registers the customer for choosing any suitable plan of broadband as per his locality.

Run the BBS_RegisterNewCustomer_prc Script to create Register New Customer

Testing Script:

USE BBS go — Register New Customer Exec BBS_RegisterNewCustomer_prc   @CustomerName='Suresh Kumar' , @CustomerMobile ='80043781' , @CustomerIDProof ='ARC5296P' , @CustomerType ='Regular' , @City ='HYDERABAD' , @Area ='HYDERABAD' , @Password ='iSuraj'

SP#2

Create a procedure that gets the customer’s name, customer mobile number, id proof, type, city, area, and password as inputs and registers the customer for choosing any suitable plan of broadband as per his locality.

Run the BBS_ShowCurrentSubscriptionPlans_prc script:

Testing Script:

use BBS  go  — Check Current Subscription Plans  Exec BBS_ShowCurrentSubscriptionPlans_prc @USerID=2 , @Password ='iSuraj'

SP#3

Create a procedure that gives the customer all the plans available and the customer can choose anyone plans out of them as per his need and locality by taking subscription id, customer id, and month as input.

Run the BBS_ShowAllPlansToCustomers_prc Script:

Testing Script:

Use BBS go  — Show all plans to customers  Exec [BBS_ShowAllPlansToCustomers_prc] @SubscriptionID =1 , @CustomerID =100001 , @Month ='JUNE'

SP#4

Create a procedure that will generate the bill for the customer’s chosen subscription plan by taking subscription id, month, and customer id as inputs.

Run the BBS_GenerageBillForCustomer_prc script:

Testing Script:

use BBS  go  — Generate Bill for Customer  Exec [BBS_GenerageBillForCustomer_prc] @SubscriptionID =2 , @CustomerID =100001 , @Month ='JUNE'

SP#5

Create a procedure to make a transaction for payment for the chosen transaction and will show the transaction details of the customer’s payment status.

Run the BBS_ShowPendingTranactions_prc script :

Run the script:

use BBS  GO  — Show Pending Transactions  exec BBS_ShowPendingTranactions_prc  @CustomerID =100001

Trigger:

Create a trigger that gets triggered when there is any update on the bill table after the successful transaction of payment and this trigger has to change the status of the bill payment from pending to paid.

Run the below Scripts:

BBS_Transaction_Details_Update_tr Script

BBS_MakeTranactionsForPayment_prc Script

Testing Script:

use BBS  GO  — Show Pending Transactions  exec BBS_ShowPendingTranactions_prc  @CustomerID =100001  — Make Transactions for payment   exec [BBS_MakeTranactionsForPayment_prc] @TransactionID=2 ,   @PaidAmount =700, @PayMethod ='CC'

Run the 1^st SP first to get the transaction details and amount

Pass those in 2^nd sp and execute it.

Then if you run the query you will see the status updated to Paid

Select * From Bills b where billno=2

 End

Download the Broadband Billing System application Stored Procedure Scripts Here.