Library Management System BCA 4th Semester Minor Project using SQL and PHP

ACKNOWLEDGEMENT

We truly resolve to acknowledge the opportunity provided to us by the Institution, respected teachers, and family.

We intend to show our gratitude for all the support and guidance we got from our teachers, classmates, and all individuals who helped us at any point in this project.

This whole process we went through in bringing out this project was a great source of knowledge and valuable experience, which would for sure, come in handy for us in any future we will have to bring out another such project.

This has been a period of time over which we learned about technological and logical aspects of the field where we are going to land soon, we feel indebted for being so well equipped with all the knowledge and experience we gathered here.

Introduction 

Surface details:

Our project entitled “Library Management System” is designed to bring ease to both the library staff and the students seeking for issuing books.

It is designed with features that reduce the effort for the staff and increase convenience for the readers, Being a Web Based application, it is easy to Run and adapt in almost all conventional environments.

Any moderate user can easily learn and operate this system 

Scope:

This System can work fine for all most every library, where desired,

This cuts off a lot of human resources required to maintain the library, hence saving money too.

It has fluid UI and UX which can be learned and adapted quite quickly by any category of user.

This system extends its use outside the library with is easy to adapt interfaces for readers

Objective

The Objective of this System is to reduce effort and increase convenience in the management of libraries.

It allows users to hold a book for 24 hours so that no one can get it issued before they reach the library.

It will save time in the calculation of fine

Students will always stay notified about generated fines via their online accounts which they can access from anywhere.

  • It will make it easier to search for books.
  • It will help keep track of bookholders.
  • It will keep Libraries less crowded.
  • It will save money as fewer human resources will be required.

About the existing system

Currently, libraries need to maintain bulky files to record books and the individuals occupying them.

Fines are calculated manually by going through bundles of records.

Need to manually check for availability of books in the library

The process of issuing a book involves designated officials making entries in registers manually, which takes plenty of time.

Disadvantages of the Present System:

The current system is full of flaws:

  • A lot of Human Resources is required to maintain such a system much cost and hard to operate and maintain.
  • A lot of money is wasted in maintaining these records
  • Paper is wasted
  • A lot of time is consumed in the process of searching a book, making its entry, and retrieving any previous record.
  • Availability of books cannot be assured without physically searching the library
  • People need to travel to the library to know whether a book is available. 

Proposed System

The System we developed comes with all solutions to the drawback of the previous system.

Some of them are listed below:

  • Search books from anywhere.
  • Put the book on hold for 24 hours so that no one else can take it before you
  • Release the book if you don’t want to hold it anymore
  • A panel for users to check their hold and issued books
  • Easy login and registration System.
  • Automated Fine System
  • One-click book Issue
  • Easily return book
  • List of trending books.

Feasibility Study

This document enables proper decisions to be made over the progress of the project, by studying various aspects of feasibility associated with it

Such are listed below:

  • Cost: as it is a web-based project it needs to be hosted over the server to be accessed by users, but the efficiency which comes with the product shadows this cost, as it reduces money being spent on human resources
  • Time: The time invested in this project is also justifiable as keeping the yield into consideration.
  • Technical: All technological tools used to make this project are conventional easy-to-use ones, and they are easy to maintain too.

This project can be easily upgraded and maintained as it is designed in a standard manner and is easy to understand. 

Hardware and software information 

HARDWARE CONFIGURATION

  • System: Multimedia PC
  • Processor:  Pentium 4 or above
  • Memory: 512MB RAM or above
  • Hard Disk:  500GB
  • Keyboard: 104 standards
  • Monitor: SVGA          

SOFTWARE CONFIGURATION

  • Front end: HTML, CSS, JavaScript
  • Back end: SQL, PHP
  • Operating System: Windows/Linux

Design Phase:

ER Diagram:

Context Level Diagram:

First Level Data Flow Diagram:

Second Level Data Flow Diagram for User:

Second Level Data Flow Diagram for Admin:

 

DATA DICTIONARY 

Users Table:

Name

Type

Field-Size

Description

Example

Uid

INT

100

Unique ID for Users

0

name

VARCHAR

100

Name of user

XYZ

password

varchar

100

Password

XYZ

credit

INT

100

No.of books currently Occupied

0-5

U_type

varchar

100

Reader/admin

admin

Pic

varchar

100

photograph

Xyx.jpg

Email

Varchar

100

Email for identification

[email protected]

 

Books Table:

Name

Type

Field-Size

Description

Example

bookid

INT

100

Unique id of book

1

bname

Varchar

100

Name of book

Life of pie

stock

INT

100

Available copies

66

Author

Varchar

100

Name of author

ABC

Publication

Varchar

100

Publication name

BBC

Edition

Varchar

100

Year of publication

2013

Price

Int

100

Price of the book

2000

 Transactions Table:

Name

Type

Field-Size

Description

Example

Tid

INT

100

Unique id of the transaction

1

bid

INT

100

The ID of User Involved

1

uid

INT

100

The ID of Book Involved

1

Time

timestamp

100

Time of transaction

2018-07-02 19:55:11

Returned

INT

100

State(returned or not)

0

T_type

Varchar

100

Hold or issue

issued

Download the complete project on College Library Management System using SQL and PHP.

Online Shopping Store Website Project using C# and SQL

MY ONLINE SHOPPING STORE 

We have a physical shopping store, which is doing well in terms of revenue and margin. We want to increase the business by extending the availability of our products through online mechanisms. Quick time-to-market is the main criterion;

we are adding some features: 

  1. Home Page 
  2. Product Search facility 
  3. Product Detail Page 
  4. Add to Cart 
  5. Place Order

Use Case Diagram-Online Shopping Website:

The use case diagram is usually referred to as a behavior diagram used to describe the actions of all users in a system.

All users described in the use case are actors and the functionality is the action of the system.

The use case for an online  shopping website:

Specification of Use Cases:

Use Case                                      Home page

Home Page

Element

Details

Actor

User

Trigger

With or without authentication

Pre Conditions

Various Categories of the items are displayed

Post Conditions

On clicking each category, various sub-items are to be displayed

Normal course

1.      On hovering various categories are displayed

2.      On each Category, the subcategories are displayed

 

Use Case                      Search Product

Search Product

Element

Details

Actor

User

Trigger

On clicking the search bar for the item to be searched.

Pre Conditions

 Users can check the items they needed

Post Conditions

The various items of the searched product are displayed

Normal course

1.      User enters the Product name or product category

2.      The various items of the product are displayed

Use Case                                      GetAllProducts

GetAllProducts

Element

Details

Actor

User

Trigger

With or without authentication

Pre Conditions

The user needs to enter a product name

Post Conditions

The sub-list of all the items should be displayed.

Normal course

1.      The various products should be displayed varying in different perspectives

Use Case                                      GetProductDetails

Get Product Details

Element

Details

Actor

User

Trigger

On clicking a particular item the info about the product is to be displayed.

Pre Conditions

The user needs to enter the product name or category

Post Conditions

The total info regarding the product is to be displayed

Normal course

1.      User enters the Product name and then submits

2.      The fetching records will be displayed

Use Case                                      AddProduct

Add Product

Element

Details

Actor

Admin

Trigger

When the User Quantity is not available

Pre Conditions

 Checks whether the searched item is available in the inventory or not

Post Conditions

The user can search for the prescribed item

Normal course

1.      Admin checks in the inventory stock

2.      During the period of offers or when the inventory is not available to the users, he adds to the database.

Use Case                                      Delete Product

Delete Product

Element

Details

Actor

Admin

Trigger

When the offer period is completed

Pre Conditions

 Refactoring the product as per the daily market price

Post Conditions

The product is not available after the deletion.

Normal course

1.      Admin login using his credentials

2.      Search for the product to be deleted

3.      Deletes the particular products

Use Case                                      Edit Product

Edit Product

Element

Details

Actor

Admin

Trigger

When the product needs to be updated during the season sale

Pre Conditions

 The product may vary in its cost and delivery

Post Conditions

The product has been updated regarding the information

Normal course

1.      Admin logins using his credentials

2.      Updates the selected products in the inventory.

Use Case                                      Add To Cart

Add To Cart

Element

Details

Actor

User

Trigger

When a user needs to buy the item at a later point in time

Pre Conditions

 If the user likes a particular product he can choose to buy it by clicking the Add To Cart

   

Post Conditions

The selected items are available later until the selected product is available

Normal course

1.      User selects the particular product

2.      He then clicks on Add To Cart.

3.      The liked items are available for later usage

 Use Case                                     BuyNow

BuyNow

Element

Details

Actor

User

Trigger

With  authentication

Pre Conditions

The total amount should be displayed.

Post Conditions

On Clicking the order needs to be placed.

Normal course

1.      Click on Buy Now, select the items and then go to payment

2.      The order should be placed after payment.

Shopping cart System ER Diagram:

ER Diagram:

Flow Chart For Online Shopping system:

Database Table Design:

ProductCategory:

Field Name

Type

Remark

CategoryId

Int

PK

CategoryName

Nvarchar(50)

 

Products:

Field Name

Type

Remark

Id

int

PK

CategoryId

int

FK

Price

Float

 

Quantity

Int

 

Description

Nvarchar(Max)

 

Picture

Nchar(100)

 

CartItem: 

FieldName

Type

Remark

Id

Int

FK

Price

Float

 

Quantity

Int

 

 

DBMS Project on Student Management System

Student Management System is purely developed by using DBMS queries. The main functions involved to develop this project is developing ER diagram, DDL/DML, SQL constraints,  Retrieving data using the select function, Restrictions, and sorting functions,  Aggregate, and grouping, Single row function, Joins, and finally Subquery.

ER DIAGRAM OF STUDENT MANAGEMENT SYSTEM

DDL AND DML

1) Create a table name student and attribute of s_id, first last and middle name, address, email, city, and state.

2) Display the datatype of all attributes in the student table?

3) Insert values into the table student?

4) Change the datatype of s_id in the student table?

5) Update the city of s_id=001 to bby?

SQL CONSTRAINTS

1) Create a table name student with an attribute s_id that is not null?

2) Create a table name student with an attribute s_id that is unique?

3) Create a table name student with an attribute s_id which is a primary key?

4) Create a table name teacher with an attribute s_id which is a foreign key and team name?

5) Create a table name student and with an attributes city which a default constraints “KOLKATA”?

RETRIEVING DATA USING SELECT

1) Display all the columns of the table name course?

2) Display the columns c_name aliases course name of table name course?

3) Display the columns c_fee and c_name using concatenation and aliases course details of table name course?

4) Display the course fee and increase in course fee by 3000 where the course duration is 45_hr?

5) Remove the duplicate value from course duration with a student ID from the table name course?

RESTRICTIONS AND SORTING

1) Display the student ID and the first name from the table name student whose state is westbengal?

2) Display all the data of the table course whose course ID is ‘11’.

3) Display all the data of the table course whose course fee range is less than 100000?

4) Display the Lastname and city of the table student where the student’s first name starts with ‘a’ and consists of 4 characters?

5) Display the course fee of the table name course which is between 10000 to 50000?

AGGREGATE AND GROUPING

1) Display the average course fee for all the courses in the given table?

2) Display the minimum and maximum course fees in the given table?

3) Display the count number of course names from the table name course?

4) Display the total course fee in the table name course?

5) Display the student ID and minimum course fee from the course table and group by student ID having a course fee less than 102000 and sort the minimum course fee in descending order?

SINGLE ROW FUNCTION

1) Display the student ID and student first name and state from the student table where convert student first name and state into uppercase?

2) Display the student ID and student first name and state from the student table where convert student first name and state into lowercase?

3) Display the student ID and student state from table student and change the first character of all states to uppercase?

4) Display the student ID and student first name middle name last name together from the table student use the character-manipulation function?

5) Display the student ID and the length of the first name and the last name from the table student using the character-manipulation function.

JOINS

1) Display the registration number for the user’s table and the student’s first name on the table student use inner join?

2) Display the registration number for the user’s table and the student’s first name on the table student use left join and order by the first name.

3) Display the registration number for the user’s table and the student’s first name and last name from the table student use the right join and order by registration number.

4) Select all teachers and all courses in the given table using full join and order by teacher ID.

5) Select all students’ first names and last names and city in the given table using self-join and order by the city?

SUBQUERY

1) Display the first middle and last name of the student where the course fee is minimum?

2) Display the teacher ID and first middle and last name of the teacher where the course name is DBMS?

3) Display the phone number of the student of who’s registration number is 20?

4) Display the registration number who live in Jaipur?

5) Display teacher ID first name and phone number whose qualification is b.tech?

Covid-19 Testing Management System Python Project

Covid-19 Testing Management System is a small project developed using Python programming. Here are the application features below.

Project features:

• Adding new testing centers
• Search for available testing centers by locality
• Update and delete testing centers
• Show all testing centers by city, state
• Shows the number and type of testing kits available at each center.

Software to be used to develop this application:

• Python
• SQL for creating a database
• Library to interface between Python and SQL
• Either Django (to create a web app) or a GUI library to create the UI (not decided yet)

Implementation

1. Login page (This has a simple login prompt designed using a GUI library that asks for the user id and password.)
2. Menu:
a. Add a testing center
B. Delete testing center
C. Edit the number of testing kits available
D. Search for the testing center by district/city/state
E. Show all testing centers
3. Separate sections for:
a. Adding a new center
b. Deleting center
c. Updating center information
d. Searching for center
e. Nationwide data

Covid-19 Testing Management System can be developed by using PHP & MySQL Server with different functionalities below.

User Characteristics

The Covid 19 Testing Management system has 2 types of users they are Admin and user(patient)

General Constraints

The tools and technologies that are used to develop this project are:
The language used in this project is PHP5.6 and PHP7.x.
The database used in this project is MySQL 5.x.
The web browsers that are used in this project are Mozilla, Google Chrome, IE8, and OPERA.

Operational Scenarios

Scenario A:
How your application starts
Our application covid19 testing management system starts by login into the application, if the user is a new user, the user needs to register by providing the needed credentials.
New users need to provide testing information. A registered user needs to provide test information.

Scenario B:
Usage Scenarios like Customer Check-out
When a customer visits our application, he/she needs to log in by providing their credentials according to their role i.e., admin and user.
If the user is new, he needs to register if he is an old user, he can directly enter the login details and login directly.

Scenario C:
Database

The data that we are going to store in the database
1) Admin Login
Username
Password

2) New admin login
Admin name
Username
Password

3) User login
Username
Password

4) New user login
Name
Phone number
Username
Password

5) User Registration
Name
Current address
Gender
D.O.B
E-mail id
Phone number
Age
Aadhar card number

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
created a Database Named “BBS” in the server

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 1st SP first to get the transaction details and amount

Pass those in 2nd 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.

School Case Study Database Design Queries and Answers

database design:

  1. Create a DATABASE: SCHOOL 

TABLES

Create the following three tables with the same names and data types as provided below:

CourseMaster

Column Name

Data Type

Remarks

CID

Integer

Primary Key

course name

Varchar(40)

NOT NULL

Category

Char(1)

NULL, Basic/Medium/Advanced

Fee

Small money

NOT NULL; Fee can’t be negative

StudentMaster

Column Name

Data Type

Remarks

SID

TinyInt

Primary Key

StudentName

Varchar(40)

NOT NULL

Origin

Char(1)

NOT NULL, Local/Foreign

Type

Char(1)

NOT NULL, Undergraduate/Graduate

 EnrollmentMaster

Column Name

Data Type

Remarks

CID

Integer

NOT NULL Foreign Key

SID

Tinyint

NOT NULL Foreign Key

DOE

DateTime

NOT NULL

FWF (Fee Waiver Flag)

Bit

NOT NULL

Grade

Char(1)

O/A/B/C

 Using the above table layouts as schema, write T-SQL Statements for the following requirements:

  1. List the course-wise total no. of Students enrolled. Provide the information only for students of foreign origin and only if the total exceeds 10.
  2. List the names of the Students who have not enrolled in the Java course.
  3. List the name of the advanced course where the enrollment by foreign students is the highest.
  4. List the names of the students who have enrolled for at least one basic course in the current month.
  5. List the names of the Undergraduate, local students who have got a “C” grade in any basic course.
  6. List the names of the courses for which no student has enrolled in the month of May 2020.
  7. List name, Number of Enrollments, and Popularity for all Courses. Popularity has to be displayed as “High” if the number of enrollments is higher than 50, “Medium” if greater than or equal to 20 and less than 50, and “Low” if the no.  Is less than 20.
  8. List the most recent enrollment details with information on Student Name, Course name, and age of enrollment in days.
  9. List the names of the Local students who have enrolled for exactly 3 basic courses.
  10. List the names of the Courses enrolled by all (every) students.
  11. For those enrollments for which fees have been waived, provide the names of students who have got ‘O’’ grades.
  12. List the names of the foreign, undergraduate students who have got a grade of ‘C’ in any basic course.
  13. List the course name and total no. of enrollments in the current month.

stored procedure

Using the above table layouts as schema, write a stored procedure for the following specifications:

Input Parameters:

Date From (Mandatory), Date To (optional, if not specified, take the current date), &Student ID (Mandatory) 

Requirements:

Course-wise,enrollment-wise in ascending order of course name to be printed. If no enrollment exists for a given course for the period specified, print the course name and the remarks ‘No enrollment for this period’

——————————————————————————————

Enrollment Details of<Student Name > from <FromDate> To <ToDate>

Origin :                                                          Type:

SL.No       Course Name    Date of Enrollment    Fee Waiver? Grade 

                                                                  (Yes/No)

  …             …               ……                              ……      ……

  …             …               ……                              ……      ……

                       Total No. of Courses Enrolled:

The Development of the above-Stored Procedure below using SQL Server:

–Create the database

create database School_DB

go

–To exchange the context

USE School_DB
go

–create CourseMaster table

create table CourseMaster
( CID int Primary Key,
CourseName varchar(40) NOT NULL,
Category char(1) NULL CHECK(Category='B' or Category='M' or Category='A'),
Fee smallmoney NOT NULL CHECK(Fee>0)
)
go

–To see schema of table

sp_help 'CourseMaster'
go

–To view the table

select * from CourseMaster
go

–Insert the values in CourseMaster table

insert into CourseMaster values(1,'SQL Server','M',10000)
insert into CourseMaster values(2,'MSBI','A',20000)
insert into CourseMaster values(3,'Python','B',5000)
insert into CourseMaster values(4,'Data Science using Python','M',90000)
insert into CourseMaster values(5,'C#.NET','B',15000)
insert into CourseMaster values(6,'Java','M',15000)
insert into CourseMaster values(7,'Power BI','A',30000)
insert into CourseMaster values(8,'Oracle','A',30000)
insert into CourseMaster values(9,'MySQL','B',15000)
insert into CourseMaster values(10,'Cobol','B',16000)
insert into CourseMaster values(11,'Cognos','M',40000)
GO

–Create Student table

create table StudentMaster
(
SID TinyInt Primary key,
StudName varchar(40) NOT NULL,
Origin char(1) NOT NULL CHECK(Origin='L' OR Origin='F'),
Type char(1) NOT NULL CHECK(Type='U' OR Type='G')
)
GO

–Insert the values in StudentMaster table

insert into StudentMaster values(100,'Arman','L','U')
insert into StudentMaster values(101,'Jhon','L','G')
insert into StudentMaster values(103,'David','F','G')
insert into StudentMaster values(104,'Rosy','L','U')
insert into StudentMaster values(105,'Mike','F','G')
insert into StudentMaster values(106,'Jessica','L','U')
insert into StudentMaster values(107,'Abhi','L','G')
insert into StudentMaster values(108,'Srinu','L','U')
insert into StudentMaster values(109,'Venkat','F','G')
insert into StudentMaster values(110,'Ram','L','U')
insert into StudentMaster values(111,'Khan','F','G')
insert into StudentMaster values(112,'Laya','F','U')
insert into StudentMaster values(113,'Naresh','F','G')
insert into StudentMaster values(114,'Naveen','F','G')
insert into StudentMaster values(115,'Rahman','F','U')
insert into StudentMaster values(116,'Abosode','F','G')
insert into StudentMaster values(117,'Manikanta','F','G')
insert into StudentMaster values(118,'Lalitha','F','U')
go

–create EnrollMaster

create table EnrollMaster
(
CID INT NOT NULL FOREIGN KEY REFERENCES CourseMaster(CID),
SID TinyInt NOT NULL FOREIGN KEY REFERENCES StudentMaster(SID),
DOE DateTime NOT NULL,
FWF Bit NOT NULL,
Grade char(1) NULL CHECK(Grade='O' OR Grade='A' OR Grade='B' OR Grade='C')
)

–USING THE ABOVE TABLE LAYOUTS AS SCHEMA, WRITE T-SQL STATEMENTS FOR THE FOLLOWING REQUIREMENTS:

1. List the course wise total no. of Students enrolled. Provide the information only for students of foreign origin and only if the total exceeds 10.
2. List the names of the Students who have not enrolled for Java course.
3. List the name of the advanced course where the enrollment by foreign students is the highest.
4. List the names of the students who have enrolled for at least one basic course in the current month.
5. List the names of the Undergraduate, local students who have got a “C” grade in any basic course.
6. List the names of the courses for which no student has enrolled in the month of May 2020.
7. List name, Number of Enrollments and Popularity for all Courses. Popularity has to be displayed as “High” if number of enrollments is higher than 50, “Medium” if greater than or equal to 20 and less than 50, and “Low” if the no. Is less than 20.
8. List the most recent enrollment details with information on Student Name, Course name and age of enrollment in days.
9. List the names of the Local students who have enrolled for exactly 3 basic courses.
10. List the names of the Courses enrolled by all (every) students.

–11. For those enrollments for which fee have been waived, provide the names of students who have got ‘O’ grade.

select StuName,grade
from StudentMaster SM
inner join EnrollMaster EM on SM.sid=EM.sid
where EM.Grade='O' and FWF = 1

–12. List the names of the foreign, undergraduate students who have got grade ‘C’ in any basic course.

select StudName,Origin,Type,Grade,Category
from CourseMaster as CM
inner join EnrollMaster as EM ON CM.CID=EM.CID
inner join StudentMaster as SM ON SM.SID=EM.SID
where SM.Origin='F' and SM.Type='U' and EM.Grade='C' and
CM.Category='B'

–13. List the course name, total no. of enrollments in the current month.

select CourseName,count(*) as Total_No_of_Enroll
from CourseMaster as cm
inner join EnrollMaster as em on cm.cid=em.cid
where datediff(mm,DOE,getdate())=0
group by cm.CourseName

Pharmacy Management System DBMS Project

Introduction

Pharmacies are an essential component of healthcare and handle the function of selling medicines. Even though the pharmacies do not seem different than any other shop, their functioning is very different due to various laws regarding medicines. For example, most of the medicines available in a pharmacy cannot be purchased without a prescription.

Even with a signed prescription, there is a limit on the quantity that can be purchased. Additionally, pharmacists can do a background check on customers’ medical history to ensure that they are not involved in medical abuse. In addition, there are other laws on the operations of pharmacies like a requirement for the safe disposal of expired medicine and the requirement of license for employees that mix/prepare the medicines.

Thus, preparing a Database Management System for a pharmacy not only requires a study of how things are handled from a customer or employee point of view but also the relevant rules and regulations.

With this Pharmacy Management System DBMS Project, we aimed to develop a comprehensive system that could deal with challenges faced in day to day operation of a modern pharmacy.

ER Modelling

The final ER diagram and UML diagram are shown below with explanations.

ER Diagram

UML Diagram:

1. A single customer can have multiple prescriptions. Thus, the relationship between them is one too many.
2. A prescription consists of multiple medicines, so the relation is one too many. In the case of refills, a prescription can generate multiple orders. So, this relation is one too many as well.
3. A single order can contain multiple medicines, thus the relationship is one too many. One order, however, can generate only one bill. Thus, the relation between bill and order is one-to-one.
4. A customer can make multiple purchases and hence, the relationship between the customer and the bill is one too many. This is because every bill has only one customer.
5. In the medicine table (stock), the medicine’s name and batch numbers can uniquely identify every medicine we have in inventory. The batch number is assumed to be unique among manufacturers.
6. Disposed medicines are weak entities and use foreign key medicine’s Name and Batch Number as their primary key.
7. One employee can receive multiple notifications and one notification can be sent to multiple employees, thus the relationship is many to many.
8. Multiple employees can dispose of the same medicines. Similarly, one employee can dispose of multiple medicines. Hence, the relationship is many to many.
9. One employee can prepare multiple orders. However, a specific order can only be prepared by one employee. Thus, the relationship is one too many. Relations and Normalization

Conclusion

The Pharmacy Management System DBMS Project was a good learning experience for implementing a real-world DBMS and helped us understand the nuances of full implementation. The most interesting part was the experience of starting from the real world and then translating the concepts into the terms of a DBMS. Paired with a capable application front end, it can handle day-to-day operations for a pharmacy.

Download the Complete Pharmacy Management System DBMS Project Code, Report, and deployment help for free.

Car Showroom Management System JAVA & SQL Project

The Car Showroom Management System consists of four major fields of management i.e. store cars, store Sales Persons, store admins, store customers and store total sales progress. Here the store admin manages all the activities such as viewing and adding car details, salesperson details, and admin details of different stores. Each store has a Manager and the Manager’s task here is to fill in the details of the car, customer, sales, and workers of a particular store.

The main goal of this application is to maintain the records of different stores which are visible only to the admin and can only be updated by him. It helps to achieve ease of access in searching car details of the selected store, salesperson’s details of the selected store, and sales details of the selected store.

CAR Showroom Management System is a software application to maintain day-to-day activities in the store, This JAVA & SQL project helps to maintain the record of the bike, customers, workers, and sales information Admin manages adds, updates, and deletes the cars, salespersons, and admin records. Admin also can add new admins. The software is designed to handle, the daily activities of all the stores. Search the details when required.

PROJECT PURPOSE

Provides a simple way of accessing the database. Allows the admin to make a search and find certain bikes, customers, and sales records that are linked through a TIN of different stores located at different locations. Provides centralized control of store database. Holds complete information about the customer.

PROJECT SCOPE

The Car Showroom Management System application is designed and developed so that there will be the elimination of storing the records and other details in a hardcopy thereby supporting its eco-friendly nature. The Bike Showroom Management System application is quite simple and easy to use. This application has not had many Complications. It has a very simple way of maintaining the store records such as storing the bike Details of a certain store, customers visiting a certain store, and the sales details of the store that the admin chooses to gain access to.

PROJECT GOAL

This CAR Showroom Management System project aims to show the shadow implementation of JAVA & SQL which includes a better storage facility, update, and view of data. The application provides a user-friendly interface so that the admin can access it with ease.

Download Car Showroom Management System JAVA & SQL Project Source Code, Project Report.

Insurance Policy Maintenance and Claim Management Process .Net Project using C# and SQL Server

An insurance management system is a personal intranet-based web application developed by a group of insurance broker agents to make your insurance policy and claim review process easier and more flexible.

The web application is used to check and monitor the status of customers who have adopted new policies in the context of distributed access in your organization, and to notify them of the terms of the policy’s premium payments and requirements. In fact, the system is designed to accelerate the process in your business process to increase customer satisfaction and keep the overall flow of customers in this domain smooth and stable.

The main problem of manual operation is the registration of holders of existing insurance policies and their regular maintenance of insurance policy payments. Providing regular services also takes a lot of time by searching the registers of the current customer base. If it is possible to automate the data storage process and increase search control, there is always the opportunity to get additional customers in turn, which will increase the profitability of the organization.

The real purpose of choosing this system is to speed up the organizational process. Agents can have the expected information at any time, in any instance. Creating the required reports in accordance with operational requirements will be much easier and information will be available. The system is not only false evidence but also has a high level of customer satisfaction.

The application is also security-oriented and is expected to be fast as a shared access structure because it communicates within the system.

Project Characteristics:

  • The agent can view the required information at any time, whether it is a policy or a customer, with the click of a mouse and the example the second.
  • If planned in an organized manner, customers can be provided with an online terminal where they can receive information with their own hands without major intervention.
  • Customer or policyholder alerts can be generated at lightning speed by asking specific customers.
  • Data can be shared via a single-window link in their respective databases, saving time entering multiple data while they are being collected.
  • The status of the premium payment on the clients’ policies can be systematically discussed by the Agents to check for non-payment.
  • A specific policy can be clearly monitored by the detainee and checked before the claim is resolved.
  • Most importantly, the overall system can provide consistent and reliable authenticated information about your transactions at any time.

Current System:

In general, if a user plans to create an insurance plan manually, they will need to perform a number of activities.

It is a very tedious process for the agent to keep records of what is happening through the system and the required information that may be generally needed at all stages.

In increasing the customer base, the manual search process is very tedious and time-consuming, which repeatedly frustrates the customer with the agent.

Recommended System:

With the new system, the following activities are gaining momentum.

The actual process of the organization can be modulated into three different independent views

  • Agent Appearance
  • Opinion of the policy owner
  • System Administrator View
  • Agent

At any time you can view the required information or policies, or customers with an example of a click of a mouse and a second.
If planned in an organized manner, customers can be provided with an online terminal where they can receive information with their own hands without major intervention.

Modules:

The actual process of an organization can be modulated into three different independent perspectives

  • insurance company information
  • Agency Information
  • customer information
  • Policy Information
  • Policy payments
  • Political demands
  • Security information

Download Insurance Policy Maintenance and Claim Management Process .Net Project Source code, Report, PPT using C# and SQL Server

Bug Tracking System .Net Project with SQL Server & ASP.Net

To create a new Bug Tracking System, which enables the user to detect the bugs and notify colleagues and administrators to get the rectification from them immediately.

This project “Bug Tracking System” handles the Organization details in the database in an efficient manner. This system is more innovative to use, any user can make up easier and faster transactions between employees. This site can be used under any private concern, for flexibly handling the databases, to get dispatches the data to process them for individual usages. Thus it makes the system more flexible, and more reliable for use.

For all the above this site is the perfect place for users, especially employees who preferred to save time and entered into the current trend.  Thus the entire project of the Bug Tracking System is clearly tested; all the modules are working correctly, as well as the output is verified.

Modules

  • Employee Details
  • Priority Master
  • Project Details
  • Status Fixation
  • Fast Search
  • Add Bugs

Module description

Employee Details:

The employee details module allows the administrator to make the entries for the employee with their proper identities such as name, email-id, mobile number and etc.
 
Priority Master:

The priority master module allows the administrative people to fix up the priority for the occurrences of the fore coming errors, which are assigned or created as high, low, medium, and so on.
 
Project Details:

The Project detail module allows the administrator to create the details about the ongoing projects, their issues, the status of the project, and its responsible person details.
 
Status Fixation:

The status of the project and the position of the bug is fixed by the user with its completion status, affection-causing range, prioritization, and so on.

Fast Search:

The fast search module allows the user to search the current project records in a simpler manner with details such as project name, respective person details, and so on.
 
Add Bugs:

The add bugs module allows the user to create the bugs which they are facing from their projects, and publish that problem to the general users for clarification, which leads other users to carry on the bugs and rectify them with their own ideas.

Download the Complete Bug Tracking System project source code, Report, and PPT.