728x90 AdSpace

Latest Article

Accounts Management System Database Project using MS SQL C#




Radio City Accounts Management System Database Project using MS SQL C# application  with detail database design article and ERD Explanation



Radio City is one of the most popular and one of the largest digital audio/ video stores in Lahore. The store sells a vast collection of DVDs, CDs and LDs to a large number of customers. The approximate monetary value of the average daily sales of this store is more than many of its smaller competitors. Offering people the widest range to choose from, Radio City evidently monopolizes the market and is the first choice of most of the customers.
We plan to make an automatic accounts monitoring and inventory management system for this store which would cover both, the sales and stock aspect of this quite successful business.



 Source Code Link Here 

The objectives behind the design and implementation of this system are as follows:
Ø  An efficient and easy to use system that aids the store management in smooth running.
Ø  An easy way to record daily sales
Ø  A system that could automatically adjust stock levels according to sales transactions
Ø  Automatic display and calculation of the total amount per transaction
Ø  Alerts to indicate required re-ordering of the products that fall below the stock threshold levels
Ø  Collective and individual views of payments due and those received.
Ø  A summarized view of sales of the shop to access sales performance
Ø  Contact details of all the suppliers as well as management and status of the orders made.
Ø  A system that offers different levels of access to different employee ranks to minimize the chances of fraud.
On the accounts side, the system would handle deductions from the stock of items sold as well as calculation of bills and accumulated daily, monthly sales etc. On the stock side the system would be responsible for alerting when products need to be re-ordered and maintain status of order and payments to suppliers. Over all it would present various views to assess sales performance over different time spans

System Module

 Stock Management System

The stock system would deal with all aspects of the store management related to stock handling. It would comprise of the following sub-modules:

Suppliers:
This would allow the manager to add details of new suppliers and hold contacts of all suppliers.
Payments:
This would comprise of all payments due to suppliers for stocks bought.
Orders:
This module would cover orders made to suppliers as well as status of those orders and their payment status as well
Stock:
The module would comprise of all items in stock, it would allow the manager to add new items to stock and to monitor all items already in stock

Sales Management System

This part of the system would deal with the front end sales of the system. It would consist of the following sub-modules:

Billing:
This would allow sales transactions to be added to the system along with automatic bill calculation and stock deductions.

Sales Trends:
This module would allow the manager to see various sales trends and accumulated sales according to different time durations. It would generate various reports for the owner to assess sales performance.

Users Management System

This part of the system would deal with adding/ deleting and editing main users of the system so different views and rights can be provided for them.

Users/Roles of the System

The system would operate in three separate dimensions for the three different users.

Sales Person:
The sales person would be able to only add sales transactions into the system. The products sold would be deleted from the stock and whenever a product is out of stock its sale would be blocked
Store Keeper:
The store keeper would be able to place orders to the suppliers. S/he would be able to add new supplier details as well as enter new products into the stock. The stock keeper would also be able to see the stock details.
Owner:
The owner of the store would be able to see value of daily transactions as well as payments due. The daily profit would also be accessible to the owner. In addition to the access rights of the store keeper and sales person the owner would be able to generate sales reports to view trends and make better purchase order decisions.


 Forms, Queries and Reports

Forms:
  • Add\Edit a Product
  • User Management
  • Add a Product
  • Stock Management
  • Add\Edit a Supplier
  • Supplier Management
  • Purchase Order
  • Stock Purchases
  • Sales Order
  • Stock Sales
  • Add\Edit User
  • User Management
  • Add\Edit Category
  • Add\Edit Type

Queries:
·        Products low in stock
·        Suppliers supplying a particular product
·        Accounts Payable
·        General Ledger: credits and debits
·        Sales Trends: per product, daily/weekly/monthly/yearly aggregations
·        Product demand: by frequency or proportion of sales made comparative to other products
Reports:
  • Daily Sales Report
  • Monthly Sales Report
  • Yearly Sales Report
  • General Sales Report
  • Daily Purchases Report
  • Monthly Purchases Report
  • Yearly Purchases Report
  • General Purchases Report
Entity Relationship Diagram 



Enhanced Entity Relationship Diagram

Tables Description


Examples are given below:

User
Table Name:  User
Primary Key:   Username
Purpose: Record details of every user in the system (User entity)

Column Name
Data Type and Domain
Null Constraint
Key Constraint
Application based Constraint
Username
varchar (100)
Not Null
Primary Key

FullName
varchar (100)
Not Null


Password
varchar (50)
Not Null

Must be at least 3 characters
UserRole
varchar (10)
Not Null

Must be one of “Owner”, “Store Keeper”, “Sales Person”

 Product
Table Name:  Product
Primary Key:   ProductID
Purpose: To record details of various Products in the stock (Product entity)

Column Name
Data Type and Domain
Null Constraint
Key Constraint
Application based Constraint
ProductID
int (4)
Not Null
Primary Key

Name
varchar (100)
Not Null


Description
varchar (200)
Null


Type
varchar (20)
Not Null

Must be one of “CD”, “DVD”, “VHS”, “LD”, “Cassette”, “Other”
Category
varchar (20)
Not Null

Must be one of “Games”, “Movies”, “Songs”, “Software”, “Other”
Price
Float(8)
Not Null

Not less than 0
Amount
Int(4)
Not Null

Not less then 0 or greater then 1000

Supplier
Table Name:  Supplier
Primary Key:   SupplierID
Purpose: To record details of suppliers of stock products (Supplier entity)

Column Name
Data Type and Domain
Null Constraint
Key Constraint
Application based Constraint
SupplierID
int (4)
Not Null
Primary Key

Name
varchar (100)
Not Null


Address
varchar (100)
Not Null


City
varchar (50)
Not Null


Country
varchar (100)
Not Null


Phone
varchar (25)
Null


Email
varchar (50)
Null


Contact Person
varchar (100)
Null



SalesOrder
Table Name:  SalesOrder
Primary Key:   TransactionID
Purpose: To record details of Sales order transactions made

Column Name
Data Type and Domain
Null Constraint
Key Constraint
Application based Constraint
TransactionID
int (4)
Not Null
Primary Key

SaleDate
Date
Not Null

Must be in DD-MM-YYYY format
TotalCost
double (8)
Not Null

Not less than 0

SalesOrderDetail
Table Name:  SalesOrderDetail
Primary Key:   TransactionID & ProductID (composite key)
Purpose: To record item details of every Sales Order transaction

Column Name
Data Type and Domain
Null Constraint
Key Constraint
Application based Constraint
TransactionID
int (4)
Not Null
Foreign Key

ProductID
int (4)
Not Null
Foreign Key

Quantity
long(8)
Not Null

Quantity not less then 0 or greater then 100,000
UnitPrice
double (8)
Not Null

Not less than 0
NetCost
double (8)
Not Null

Not less than 0

PurchaseOrder
Table Name:  PurchaseOrder
Primary Key:   TransactionID
Purpose: To record details of Purchase order transactions made

Column Name
Data Type and Domain
Null Constraint
Key Constraint
Application based Constraint
TransactionID
int (4)
Not Null
Primary Key

PurchaseDate
Date
Not Null

Must be in DD-MM-YYYY format
SupplierID
int (4)
Not Null
Foreign Key

TotalCost
double (8)
Not Null

Not less than 0
Status
varchar (20)
Not Null

Must be one of “Payment Pending”, “Shipment Pending”, “Completed”



PurchaseOrderDetail
Table Name:  PurchaseOrderDetail
Primary Key:   TransactionID & ProductID (composite key)
Purpose: To record item details of every Purchase Order transaction

Column Name
Data Type and Domain
Null Constraint
Key Constraint
Application based Constraint
TransactionID
int (4)
Not Null
Foreign Key

ProductID
int (4)
Not Null
Foreign Key

Quantity
long(8)
Not Null

Quantity not less then 0 or greater then 100,000
UnitPrice
double (8)
Not Null

Not less than 0
NetCost
double (8)
Not Null

Not less than 0

ProductCategory
Table Name:  ProductCategory
Primary Key:   CategoryID
Purpose: To record the different categories of products

Column Name
Data Type and Domain
Null Constraint
Key Constraint
Application based Constraint
CategoryID
int (4)
Not Null


CategoryName
Varchar(100)
Not Null



 ProductType
Table Name:  PurchaseType
Primary Key:   TypeID
Purpose: To record different types of products

Column Name
Data Type and Domain
Null Constraint
Key Constraint
Application based Constraint
TypeID
int (4)
Not Null


         TypeName
Varchar(100)
Not Null




Source Code Link Here 


Accounts Management System Database Project using MS SQL C#
  • Title : Accounts Management System Database Project using MS SQL C#
  • Posted by :
  • Date : 10:16
  • Labels :





  • Blogger Comments
  • Facebook Comments

6 comments:

  1. Where is the db script or Database file ?? can you please send me that thanks
    rahman.qau80@gmail.com

    ReplyDelete
  2. Where is the db script or Database file ?? can you please send me that thanks Rumeel2004@hotmail.com

    ReplyDelete
  3. send me sql file at haidereng2gmail.com

    ReplyDelete
  4. please email me sql file arsalan_leo2020@hotmail.com

    ReplyDelete
  5. You people are all asking for db script. I would like to ask a question all of you what is your aim to get these script? Just copy paste or want to learn. If you would like to learn so the best way to ask for some tutorials specially based on SQl Server or .Net which ever language you are learning. VB, C++, C# ?

    ReplyDelete

Top