Category: Database Management Projects

Download Free Database Management System Projects on Oracle Database, MySQL Database, SQL Server Database. Stored Procedures, Functions, and Triggers can be used to download Database Management Projects.

Posted on

Car Rental System Database Design Project

Data Requirements

The Car Rental System Database involves around three main entities Car, User, and Reservation

The car can be reserved from a rental location with a specific address. It has a unique Rental_Location_ID, a phone number, and a contact email. A rental location keeps track of the address where the car belongs using the street name, state, and zip code.

A rental location has several cars for rental. Each car is described by VIN, Registration Number, Color, Model, Manufactured year, Seating capacity of the car, whether it has Disabled friendly feature and its Status marked ‘Available’ or ‘Unavailable’

The car has a parameter Car Type. It can be ‘Economy’, ‘Standard’, ’SUV’, ‘Premium’ and ‘Minivan’. Car Type defines the rental price per day. A user can take Insurance per day for the rental car. There are different types of Insurance each having different medical coverage, collision coverage, and bodily coverage. Insurance Types are ‘Liability’ and ‘Comprehensive’. Car type and Insurance Type drives the Insurance price per day.

A user can reserve a car for several days. He can use any valid promotional code which is maintained by status. When a user books a car he mentions the start date and end date for which he needs the car. The end date will be hypothetical at the time of reservation and updated with the actual end date when the car is returned. The total amount and net amount are calculated based on start date, end date, rental price per day, insurance price per day, and promotional code if any

A user is categorized as a guest or customer. The user can continue reserving the car as a guest as long as he has not registered as a a customer. A user is uniquely identified by his/her license number. User information consists of his name as first name, middle name and last name, email, address, date of birth, and contact number

A registered customer will be provided with a login id and password. A customer can save his credit/debit card details for future payment

Partial payment can also be made at the time of reservation and the balance must be paid by the user during car return when the actual end date is known. If the user is a customer, he/she can pay through saved debit/credit card details

A user can add any accessories as part of his/her reservation. Accessories can be ‘Car Seat’ and ‘GPS’. A user can add as many accessories he/she needs as part of the reservation

The additional driver can be added as part of his/her reservation. For each additional driver, there will be an additional charge

A user can cancel a reserved car before the car has been rented. A reservation can have status as ‘Reserved’, ‘Completed’, and ‘Cancelled’. When the car is reserved, the status will be in ‘Reserved’ Status. Once the car is returned and the total amount is paid, the status will be ‘Completed’

Functional Dependencies

In Rental car location, Rental_Location_ID is the primary key
Rental_Location_ID —> {Phone, Email, Street_Name, State, Zip_Code}

Type of the car defines the rental price of the car per day
Car_Type —> Price_Per_Day

Type of the insurance defines the insurance coverage
Insurance_Type —> {Bodily_Coverage, Medical_Coverage, Collision_Coverage}

Insurance Type and Car Type defines the Insurance price per day
{Car_Type, Insurance_Type} —> {Insurance_Price}

A user is defined by his/her License_No
{License_No} —> {FName, Mname, Lname, Email,Address, Phone,DOB, User_Type}

In a user credential, Login_ID defines the rest of the attributes in the entity
{Login_ID} —> {Password, Year_Of_Membership, License_No}

Login_ID and Card_No in Card_Details defines complete card information
{Login_ID, Card_No} —> {Name_On_Card, Expiry_Date, CVV, Billing_Address}

Reservation_ID drives all the other attributes in Reservation relation
Reservation_ID —> {Start_Date, End_Date, Meter_Start, Meter_End, Rental_Amount, Insurance_Amount, Status, License_No, VIN, Promo_Code, Additional_Amount, Tot_Amount, Insurance_Type}

Payment_ID is the primary key of Payment relation
Payment_ID —> {Amount_Paid, Card_No, Expiry_Date, Name_On_Card, CVV, Billing_Address, Reservation_ID, Login_ID, Saved_Card_No, Paid_By_Cash}

Promo_Code defines other attributes in Offer_Details relation
Promo_Code —> {Description, Promo_Type, Is_One_Time, Percentage, Discounted_Amount}

Additional_Drivers relation
{Reservation_ID,Name} —> DOB

In Accessories relation, Accessory_ID drives the type and amount of the accessory
Accessory_ID —> {Type, Amount}

Download the Car Rental System Database Design Project Code, Report, and Presentation.

Posted on

Restaurant Management System Oracle SQLPLUS DBMS Project

We have created a Database for a Restaurant as a part of our Database Management System project for the Database Management System. It has been developed using Oracle SQLPLUS software. In our database, we have considered all the major functionalities that are required in a restaurant. An organized system in any restaurant makes the job a lot easier to manage everything properly.

There are lots of different kinds of data to consider here. The various food details have to be stored. We should also have a record of all the customers who come to the restaurant. We need to maintain proper ordering data too to keep track of the various food items ordered by the customer. The feedback given to the customer and the table number where they sat may also be needed for future reference. Finance is one of the most important components here which has to be maintained well too. Then come the people who work in the restaurant and provide service to their customers. All of their details should also be kept properly.

The entire Restaurant Management System project is not only about just storing the data. The process of retrieving the data also should not be troublesome. The users should be able to access it easily and the process should be fast too. So, all the related data should be interlinked which will help in these cases. Many times, the users might also want to insert new data and update or delete them too. Those cases also we have considered. Even after these modifications, the database should work perfectly. Finally, in a nutshell, our work is useful for storing all the data in an organized and efficient manner, saving valuable time, and reducing the huge paperwork.

DATA REQUIREMENTS & CONSTRAINTS

The Restaurant Management System must have the following data requirements and constraints:

Customers who come to the restaurant are identified by their customer IDs and their names, ages, phone numbers, and addresses are recorded. The customer’s name should always be present. Customers give orders. Each Order is identified by order ID and has food ordered, bill no., and date. Bill no. and table no. should be unique. The maximum number of food items that can be ordered is 20. Payment has to be done for the order. Payment is identified by payment ID, it also contains the mode of payment (Cash, Debit card, Credit card, and UPI), total amount, amounts received, amounts returned, and date.

The total amount should be equal to the amount received – the amount returned and should always be greater than zero. There are three kinds of staff members in the Restaurant – Manager, Chef, and Waiter. Every staff member is identified by staff ID and has a name, date of birth, contact number, address, and salary. Staff name and contact should be present for every staff. Waiters’ salaries should be greater than ₹100. The chef’s salary should be greater than ₹500. The manager’s salary should be greater than ₹1000.

The chef cooks the food and the waiter takes and serves the orders. The order consists of varieties of food. Every food is identified by its food ID, it also has a name, description, and price. The price should be greater than ₹100 and lesser than ₹1000. Food names should be present for every food item. Customers are at tables. Tables are identified by table number and have several seats. Several seats should be more than 4. The customer gives feedback. Each feedback is identified by a Feedback ID and contains ratings (1-5) and comments.

FUNCTIONAL REQUIREMENTS

Insertion of Data

1. Details of customers are stored.
2. Details of the orders placed are stored.
3. Details of the dishes are stored.
4. Details of the tables are stored.
5. Details of the payments done are stored.
6. Feedback given by customers is also stored.
7. Details of the staff (manager, chef, waiter) of the restaurant are stored.
8. Details of which cooked food is stored
9. Details of which waiter served which order are stored
10. Details of which dishes are ordered in a particular order are stored

Removal of old data

1. A customer decides to delete his order on a particular date. So by his id and date of order, the staff id, food id, and order id should be deleted.
2. A customer decides to delete a food item from his order. So its food id needs to be deleted.
3. An order has been placed to cook which gets rejected at the last moment. So its food id for cook needs to be deleted.

Modification of data

1. A customer has changed his address. So, his address needs to be updated for delivery of his order by order_id.
2. The amount of payment needs to be updated for an order
3. A customer decides to change his feedback on the services of the restaurant
4. A customer decides to change his mobile number at the time of delivery of an order

Data Retrieval

1. Display the name of the customer who gave the order on the entered date.
2. Display the list of all the bills no for a particular food
3. Display the rating given by a particular customer
4. Display the name of the customer who did give a particular comment and rating
5. Display the table_no where a particular order was served

Posted on

Women Security Band – Smart and Handy Security Device for Women Project

Objective :

  • To design and develop a smart and handy security device for Women.
  • To alert people in its vicinity and relatives of the victim about the attack and also get help from emergency services.
  • To track the user using google maps under crucial situations.
  • The module developed should be capable to work automatically under crucial situations.

Introduction :

Women Security device is a device that will help women to walk freely in the streets anywhere and at any time. The goal of the women’s security device is to protect women from getting molested in different ways like rape, sexual harassment, abuse, etc. There are existing apps or devices to provide security to women but there are drawbacks to these existing apps or devices. So to overcome such drawbacks and provide security in an enhanced way we are going to propose the women’s security device.

Technical Details :

Following modules/sensors to detect the crucial situation:

1. Pulse rate sensor:

Pulse rate will be the major parameter for the security device because
whenever the person is in danger person’s heartbeat gets increased. So using this parameter we can consider that person can be in danger.

2. Temperature sensor:

This is the second parameter that can be considered when the person is in
danger its temperature starts decreasing so this can be considered as one of the parameters to detect that the person is in danger.

3. Motion sensor:

This is the third parameter we can consider as when the person is in danger due to
fear or anxiety person’s body starts shaking or shivering. So this can be considered as another parameter to send alarm message or call to relatives.

4. Voice Recognition.

We will be using this parameter as well to detect dangerous situations. We will be recording the “HELP” word and when the person shouts when is in danger then by detecting and comparing it with the already recorded word and if it crosses a certain limit of intensity range then we can consider it as a dangerous situation to send alarm message or call to the relatives of the person.

Using the above sensors will provide input to our device
Also to track the user and send emergency messages to listed
contacts and to the nearest police station two modules are used

Problem Statement

To develop an easy-to-carry system that will work automatically without any manual inputs to provide security for women.
Importance of the project.

The expected outcome of the project

A wearable security device that will be user-friendly and feature train the device according to different users. Module to add and remove authorized people for sending alerts to them. Location will be considered while sending alerts.

Functional Requirements:

The user of the Bracelet:-

1. System would Identify the critical situation and normal situation automatically.
2. System would get emergency help automatically when the victim is under attack.
3. System would Blink the LED for 10 secs when a critical situation is detected.
4. Track the nearby victim/friend/relative in an emergency using Google Maps.
5. System would alert the vicinity people.
6. System would get emergency help when the SOS button is pressed manually.
7. Add the contact numbers of friends/relatives on the application to ask help for in an emergency.
8. Login with the already provided unique number using the mobile application.
9. Save the button on the System to write those numbers into GSM.
10. Reset button on the System to reset all previously saved numbers into GSM.

Other than the victim/ User of the Bracelet:-

1.Log in with a verified mobile number.

2. Track the nearby victim/friend/relative in an emergency using Google Maps.

External Interface Specifications:

1. User Interface:-

The user of the Band:-

1. Login.
2. Add the contact numbers of friends/relatives.
3. Tracking the victim using Google Maps.

Other than victim/ User of the Band:-

1. Login.
2. Tracking the victim using Google Maps.

2. Database:-

Firebase Cloud will be used for real-time database storage.

3. Google Maps API will be used for tracking the user.

Technical Specification: There will be one android app and a smart band.

Languages & Technologies:

Languages: C, C++, JAVA, Firebase database
IDE: Arduino, Android Studio

Posted on

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.

Posted on

Human Resource Management System PHP & MySQL Web Application Project

The HR Management System is a simple academic web-based mini-project created with PHP & MySQL databases. The primary goal of this Human Resource Management System PHP project is to build a web application that helps the organization manages its human resources.

There will be the main module named the project module in the human resource management system application. It Records of all employees in the organization are stored in the application MySQL backend along with their skills and abilities. When a new company-based project is assigned to an employee team, the project module creates a new file. Details of project team members and the estimated completion date of the project or deadline will be set.

The project manager in the application should update the assessment of team members in the application. This rating is used by the application to make recommendations for promotions. The final decision on employee future promotions is made by the senior manager.

The second module of the application is the hiring module that can be used in the new employee hiring process in the company. This module provides information on future projects and progress in current projects, based on which it predicts the demand for talent.

The third and final module of the application is the Employees module, it will be available to all the employees to view their salaries and permitted leave details. Using this HRMS web application, all employees can easily check the information and ask questions at any time on the Human Resource Management System page. Top management can also use this system to access employee records and performance statistics of the employees to improve the organization’s standards.

HRMS Project

Download Human Resource Management System PHP & MySQL Web application mini project. This project was developed with HTML, CSS, Javascript and Bootstrap.

Posted on

Sales Management System for Hypermarkets PHP & MySQL Project

ABOUT THE PROJECT

A sales management system for hypermarkets is a web project developed with PHP & MySQL Database. It is a DBMS-based Project,  Supermarket store is an Indian retail store operating as a chain of hypermarkets. It is one of the largest and oldest hypermarkets in India, with more than 150 stores in more than 60 cities and towns across the country. It includes food, clothing and fashion, furniture, electronics, stationery, cosmetics, kitchen and kitchen utensils, and many other categories.

OBJECTIVES

  • Sales Details by Store / Region / State
  • Revenue/sales analysis
  • Details of the store, ie numbers, area, address
  • List of undelivered orders
  • List of payments made or expected to be distributed to distributors
  • Calculation of benefits by date/month/season
  • Maintains detailed inventory.
  • Track store order shipments
  • Purchased stock, if the number is less than a certain number and calculate the income for a month

ER Diagram:

FEATURES

  • Sales management
  • Income analysis
  • inventory management
  • Delivery management
  • Analytics by category
  • Advanced magazine search
  • Improved reports

PREREQUISITES

  • xampp installation
  • Cloning this repository within xampp / htdocs
  • Read the superstore.sql file to configure the database

MODULES

  • ADMINISTRATORS: Responsible for centralized management of stores and distributors
  • DISTRIBUTORS: Distributors are responsible for store orders.
  • STORE: Located in different parts of the country.
  • STORE ADMINISTRATOR: Participates in-store management and sales

APPLICATIONS

  • It can be used in a centralized management department for large pages.
  • Reseller Dashboard helps you manage store orders and update store order details such as invoices, deliveries, and payments.
  • The Store Manager dashboard contains basic apps for managing your store’s inventory while maintaining customer interaction.
  • The administration panel helps the management team of large stores to keep all stores in the region in one central interface.

CONCLUSION

In developing this Sales Management System for Hypermarkets PHP & MySQL Project, we learned a lot about working with HTML / CSS / JS / PHP / MySQL and database management, as well as how to make the app user-friendly (easy to use and manage) by hiding its complex parts.
During the development process, we carefully studied and understood the criteria for creating the required software, as well as the importance of maintaining a minimum error rate.

Download the complete Hypermarket Sales Management System PHP & MySQL Project Source code, Report.

For more details about the project visit this page

Posted on

Restaurant Management System Database Project using PHP, MySQL/MS Access

The aim of this project is to create a Restaurant Management Database (RMD) is an online application for restaurant management. This system wakes to provide service facilities to restaurants and to the customer. The services which are provided are food ordering, reservation of the table by the customer through the system online, menu information management, and report.

The main goal of this project is to make the customers satisfied to get the food from anywhere

  • To develop the online ordering and reservation system in restaurants.
  • To develop a user interface for an online restaurant management system to provide online menu information for customers to order

Project requirement

The basic requirement is to make the customers log in and order their favorite food online. To do so they need to look at the menu. Thus, there must be a menu with quantity and price options. Thus, these basic requirements are addressed for now.

Mission statement

The objective is to help the customer to order food online and get them delivered through an interactive application.

Objectives

  • The application should support customer registration
  • Registered customers should gain access through username and password
  • There must be an interactive menu with all details
  • Customers should be allowed to browse the menu
  • Customers can place an order by adding the menu item to the cart.

Interview Questions

  • What tables are needed for the system?
  • How will we ensure that there are no duplicate records in the database?
  • How will the customer know whether the item is available or not?
  • What navigational options are good for customers?
  • How do secure customer payment information and personal information?

ER Diagram

Tables

Product ID

Product name

Details

Status

Price

Int

char

char

char

Currency

 

user_id

username

password

address

mobileno

email id

int

char

varchar

varchar

number

varchar

 

cart_id

user_id

product details

product_id

quantity

price

int

int

char

int

int

currency

 

delivery_id

user_id

delviery status

duedate & time

int

int

varchar

date  time

 

Reservation_id

User_id

Name

date

table choice

time

int

int

Varchar

date

int

time

Output Results:

Admin Dashboard:

All Orders Details Page:

Restaurant Management Table booking Admin Page:

Popular Dishes Page:

Here you can freely download the complete Restaurant Management System project Source code with both MySQL and MSAccess database code, User Manual Report.

Posted on

Library Management System Database Project using PHP & MySQL/MS Access

EXECUTIVE SUMMARY

Libraries are popular places where there are numerous books to keep track of. Not only books but the librarian is also required to keep track of users, books that were taken, due dates, etc. Making manual entries and keeping track of due dates is not easy when the user’s size is more. It becomes complex when there are numerous books and when the members of the library are increasing. Tracking members and books details become time-consuming and are prone to error. Thus, the objective of the project is to come up with easy to use and user-friendly database management application that helps the librarian with all the tasks related to efficient library management.

The application is expected to be a secure, user-friendly, and easy-to-use database application for library management, that is capable of performing library tasks like adding books, searching books, issuing books, returning books, and generating fines and reports. The application is expected to simplify the librarian tasks, reduce errors in bookkeeping, and make the task interactive and interesting.  

The project management and development shall be iterative and shall employ the RAD application development methodology. The rapid Application Development methodology is a fast way of completing a project prototype with more emphasis on the design phase and application development. With RAD in use, the application development shall be broken down into smaller tasks that are easily manageable and monitored.

At the end of the project, there shall be a live web-based application with an easy-to-use and rich navigational front end designed with JavaScript and PHP and a back end with My SQL. Both platforms are open-free and thus making applications cost-effective.

PROJECT PROPOSAL

1.     Statement of Work

1.1  Project Background

Database management systems have become vital for organizations to manage large databases and to perform transactions upon such large data. These database applications not only store data, but also manage them, synchronize them, and help in information retrieval without errors. They reduce manual efforts and enhance the quality of information retrieval services. Due to this reason, they are widely used in almost all sectors. Libraries are popular places where there are numerous books to keep track of. Not only books but the librarian is also required to keep track of users, books that were taken, due dates, etc. Making manual entries and keeping track of due dates is not easy when the user’s size is more. Thus, this work implements a library management system database application that helps the librarian manage all tasks in an efficient and user-friendly manner.

1.2  Vision

The project vision is to come up with a library management system database application that does the jobs of the librarian like maintaining book records, maintaining user records, due dates, fines, etc. efficiently and in a quick time without errors.

1.3  Project Objective

The objective of the project is to come up with easy to use and user-friendly database management application that helps the librarian with all the tasks related to efficient library management.

1.4  Project Scope

The project scope is:

  • To have a user-friendly and easy-to-use database application for library management.
  • To have an application that secures data records.
  • To have an application that generates reports on books, due reports, fine reports, etc. easily.
  • To have an application that can track the user activities and their records easily.
  • To have an application that reduces the errors and efforts of the librarian.
  • The application shall have a login and password for allowing only authorized users to access the application.

1.5  Value proposition

This application shall reduce the manual errors and efforts of the librarian. It shall provide the end-user with the abstraction of all the database details and thus simplify the task as a set of small activities. It shall enhance the quality of the services of the library. It shall keep better track of book availability, dues, fines, etc., and enhance the quality of response to the user by the librarian. 

1.6  Technical details

Existing system:

Currently, the library system in use is a mixture of both file-based and manual-based work. Excel files are used to keep records of books, library users, and library user transactions. With such a system the key challenges are

  • More duplicate records as there is no control on duplicity.
  • inconsistent records are more as there is no referential integrity control
  • Time-consuming tasks as a simple tasks may require extensive search and a lot of entries.
  • such a system is prone to errors due to inconsistencies in data
  • Lack of user-friendly GUI: the system lacks a GUI that facilitates the end-user by providing an abstraction of the back end. This abstraction of the backend makes the end-user efficiently do his or her job without worrying about other details.
  • Data loss risk: if the data is lost it becomes impossible to recover the data from excel files.
  • Generating reports of books or users with fines becomes difficult and time-consuming as it requires multiple worksheets to be navigated and used. Due to the cumbersome job, the accuracy of the reports is low.

Thus, it is proposed to come up with an efficient, user-friendly, library management system that makes end-user tasks easy, error-free, and fast. The system shall follow the norms of a database management system ensuring integrity, consistency, and no duplicates of data.

Thus, the system shall have a simple to use GUI as the front end. Database application shall be developed using My SQL. The front-end JavaScript & PHP. The operating system is Microsoft Windows 7 and above with either 32 or 63 bits configuration.

1.7  Challenges

The Key challenges the project development may face are as follows:

  • The existing data from excel can be imported to MY SQL database. But to ensure referential integrity of the data it is essential that the database follows norms of having the primary key, removing duplicates ensuring consistency, etc. Thus, it may require the excel data to pre-process, convert and then migrate it to MY SQL.
  • The end-user may be new to GUI and API usage. Thus, there may be a need to train the end-user with the application.
  • Issues may arise from front-end and back-end connectivity. This can be managed through proper coding.
  • The system development may face hurdles if its development process is not managed and monitored properly. It is essential to have a Gantt chart with proper milestones to monitor the activities.

1.8  Organization of the proposal

This project proposal is divided into sections. The first section was a background of the project giving details on its vision, value proposition, objectives, and scope.

Sections 2 to 6 details are as follows:

  • Section 2: This section gives in detail the project methodology and the implementation plan. In this section the database design principles and methodology employed is elaborated, the way the application is developed is defined and the key areas of the application along with key features and functionalities are explained through various diagrams. It shall also contain the test plans for the application.
  • Section 3: This section defines the key deliverables from this project. It thus gives details on what the project shall give for a specified set of inputs and how the result is used.
  • Section 4: This section defines how the application project management shall be carried out. It shall give details of the milestones to be achieved and details on project monitoring and evaluation.
  • Section 5:This section gives details on the project team and their roles
  • Section 6: This section is about the tools and the supporting platforms that make the application implementation possible.

2.     Methodology and Implementation Plan

The database design and implementation process shall be done on the MySQL database management platform. The project management and development shall be iterative and shall employ the RAD application development methodology. The rapid Application Development methodology is a fast way of completing a project prototype with more emphasis on the design phase and application development. With RAD in use, the application development shall be broken down into smaller tasks that are easily manageable and monitored. With regular communication and feedback among the team, the application development becomes faster and more efficient with RAD. The key phases of application development thus are as follows:

  1. Requirement analysis
  2. Application Design
  3. Rapid construction & Testing
  4. Going live

2.1  Requirements Analysis

This is the first phase of the application development lifecycle. The objective of this phase is to gather the end-user requirements and the expectations of the stakeholders from the library management system. These are listed in the form of functional and non-functional requirements.

Functional requirements

  1. Login/logout: The application should provide user-based login and logout mechanism. Thus authorized users can login into the system.
  2. Add book: This shall allow the librarian to add the book record into the database.
  3. Manage book: This shall allow the librarian to edit or delete any book record in the database.
  4. Search book: This shall allow the user to search for a book through book name, author name, or both.
  5. View book: This shall allow viewing all the books, new books, old books, damaged books, lost books.
  6. Issue Book: This shall make an entry of the member with the books browed and the due to return.
  7. Accept Return book: This shall enter the return date and ensure that the book is successfully returned.
  8. View issued books: This shall list the books that have been borrowed with their member name and due date.
  9. View returned books: This shall consist of lists of returned books.
  10. Add member: This shall help the librarian to add a new member and categorize them.
  11. Manage members: This shall make changes in the member record like deactivating them, deleting them, and editing their information.
  12. Search member: this shall help in searching the library member record
  13. View fine details: This shall generate a fine for those borrowed books with member details that skipped their due dates.
  14. Generate report: this shall allow the system admin to generate user reports, book reports, borrower reports, fine list reports, etc., and download them.

Non-functional requirements

  1. Security: the application should endure data security and user access security.
  2. Navigational requirement: The navigational options of the system should be user-friendly and easy to use.
  3. Database requirements: The database should be accessed by only secure and authorized users. The database should be available, consistent, have no duplicate entries, and should give error-free output.
  4. Performance requirement: the system should be responding with a proper message thus giving an idea to the user of the interactions.

Use case diagram

Following is the use case diagram of the system

Figure 1: Use case diagram

Activity diagram: Consider the process of adding a book record

The steps of adding the book record to the database are as follows:

Figure 2: Activity diagram

1.1  Conceptual Design

ER diagram for the Library Management System

Figure 4: ER Diagram

1.2  DBMS cost/benefit analysis

The technology that shall be used for the proposed system is the MySQL database management system. It is used because it is simple to use, supports a large database system, and has compatibility with various programming languages that can support the front-end design.

The front-end technology relies on JavaScript and PHP. These are selected as they offer good design options, navigational options, making the user interface interactive and more appealing. Thus, with consideration to having a good and user-friendly interface, these technologies are selected.

The other options were to use the Oracle database management system and MS Access database management system. But MS access system was ruled out due to its failure to support large databases and its difficulty in providing front-end connection to programing and scripting languages like PHP. Oracle was ruled out as it is tough to use and is costly when compared to MY SQL which is free and open source.

Product

Cost

Option 1 Oracle

Standard Edition One – $5,800 per unit (sockets)

Option 2 MS Access

$109.99 per license

Option 3 My SQL

Free

 

Front end option 1 Java and swings

Free but requires programming expertise

Front end option 2 PHP and Javascript

Free requires scripting expertise

Front end option 3 ASP.NET

Free requires scripting expertise

 

Based on the above and the available expertise, the project is built using MY SQL, PHP ad Java Scripts is selected.

1.3  Logical Design

The main tables proposed for the system are as follows:

User table:

User_id

Username

password

firstname

Lastname

Primary key int

Varchar(100)

Varchar(100)

Varchar(100)

Varchar(100)

Book table

book_id

Book_title

Author

Book_copies

Book_pub

Publisher_name

Isbn

Copyright_year

status

Category_id

Primary key int

Varchar(100)

Varchar(100)

int

Varchar(100)

Varchar(100)

Varchar(50)

int

Varchar(30)

 

Borrow

borrow_id

Date_borrow

Due_date

Member_id

Primary key int

Date

date

Int

Member

member_id

Firstname

lastname

Gender

contact

address

type

Status

Primary key int

Varchar(100)

Varchar(100)

Varchar(100)

Varchar(100)

Varchar(100)

Varchar(100)

Varchar(100)

Return

Return_id

Book-id

Borrow_id

Borrow_status

Date_return

Fine

Primary key int

int

Int

Varchar(100)

Date

int

1.4  Physical Design

The proposed system shall have a user interface form designed and developed using JavaScript and PHP scripting languages. The key issue in this design is the special emphasis on navigational options, proper usage of arrows and icons so that the user can operate the system without any hurdles. The user navigation should give control freedom to the user. It should not be complex and should be user-friendly.

The database backend is designed and developed using MySQL. It can be problematic to establish front and back-end connectivity while using the application. Thus, in such a case proper commands for the connection must be established.

1.5  Prototype

A sample prototype is as follows:

Front end admin login screen

Front end admin login screen

Back end book table

Adding books

Adding members

1.6  Performance Evaluation

The Project is successful only if it is capable of giving expected results. Thus, the application is to be tested to ensure that it is executing as per expected objectives. The proposed application shall be using black-box testing wherein test cases shall be built and executed. The test cases shall test the user interfaces and each use case. If the expected results are achieved the application shall be assumed to be error-free and ready for launch. Otherwise, the test result recommendations shall be implemented.

2.     Expected Result

At the end of the project completion following things shall be expected:

  • A back end designed and developed in MySQL with tables for users, members, books, return records, borrowing records, searching capabilities, and report generation.
  • A front end that is user-friendly, rich with icons, and is appealing to use. The front end shall be designed so that the user can perform the actions without any issues.
  • A fully functional application with both front end and back end connectivity capable of doing all the said tasks of use cases.
  • An application manual describing the ways to use the application
  • A cost-benefit analysis describing the feasibility of the product in economic terms.
  • A requirement analysis documents and work breakdown structure.
  • A complete coding and scripting file
  • A test project report giving details of project implementation and test results.
  • Final project document

3.     Project Management Approaches & Milestones

The objective of project management is to ensure that the project development is planned well and executed properly to ensure quality and timely delivery.  For proper management of the application development, the project development process is broken down into small tasks. Each task is grouped as milestones. For each milestone, a deadline is assigned and an expected outcome checklist is prepared.

Once a milestone is reached, the checklist is evaluated and the reviews are done with the team members. The outcome of the checklist evaluations and the review are recommended and implemented to ensure that the project is meeting its quality standards.

To ensure the tasks are executed on time a Gantt chart is used. The Gantt chart for the project is as follows:

Figure 4: Gantt Chart

Here you can download the entire project source code, User Manual Report.

Posted on

Student School Record Keeping System Application Database Project

Project Background

Efficient management of student-related information becomes challenging for any educational organization when the volume of data and operations over that data increases. Information systems and database systems are the needed tools that help in the efficient management of such large volumes of data. These systems not only manage the data but even help in the easy retrieval and processing of useful information.

Vision

This project work vision is thus to come up with an efficient database management system that is capable of maintaining student records for an educational organization.

Project Objective

The objective of designing and implementing such a database management system is to manage the student records in a proper and organized manner, enable data flow and information flow among various activities related to student information processing, and secure the student data.

The project scope is as follows:

  • To come up with an efficient database management application for student record management.
  • The application shall help to add student records like their personal information, fees-related information, scholarship-related information, course-related information, and marks-related information.
  • the application shall allow to edit or delete any student record based on authorized permission
  • The application shall allow generating reports like marks statistics, feed duellist of students, etc.
  • The application shall have access based on authorization and role-based authorization shall be enabled for read and write processes.
  • The key information like payment, and a password shall be stored in encrypted form for security purposes.

Value proposition

This database application shall help the educational organization to keep track of all the student records and manage them in a more efficient manner. It shall reduce the number of manual errors, do the student record processing task in quick time and thus reduce human effort. It shall enhance the student and teacher experience in generating reports and getting key notifications.

Technical development plan

The existing system makes use of excel based records. These are file-based systems. The biggest challenge in this system is that there are more duplicate records due to the lack of any control mechanisms that control duplicates. Database management system makes use of a primary key that is unique and it is capable of identifying any unique record. Thus, it is a way of eliminating duplicates.

The existing system is not user-friendly to use as they lack user-friendly APIs. With supporting scripting languages like PHP, CSS, and Java Script it is possible to come up with an interactive GUI. Such GUI helps the user to do the tasks in a more efficient manner.

Thus, the proposed system shall make use of the following:

Front end: PHP

Back end: MySQL

Web Server: Wamp server

Operating system: Windows 7 and above.

Development Methodology

The application development shall adopt the Rapid application development methodology and develop the application in a small set of tasks. The objective shall be to come up with a rapid prototype and iterate it as per the testing results. The development process shall begin from starting from system requirements, system design, coding, testing & debugging implementation & maintenance. It shall be an iterative process.

The development tasks shall be small manageable tasks. This shall make project monitoring and management easier.

Requirements Analysis

The first functional requirement of the project is to have an application capable of adding and managing student records.

Thus, the application should allow Admin to do the following

  • Add student: to should allow the admin to add student details like name, enrolment id, contact address, course enrolled, subjects, date of birth, address, gender, and other details.
Activity diagram depicting adding or deleting student
Activity diagram depicting adding or deleting student

Figure 1: Activity diagram depicting adding or deleting student

  • Manage student: to allow the admin to view student records, delete existing student records, and edit or update any existing student records.
  • The application should allow the admin to add marks, manage marks and generate a result
  • The admin can add subject or course details.
  • The application should allow the admin to generate lists of students who have not paid fees and those who have availed of scholarships.
  • Manage user records and change passwords

Students can do the following:

  • The application should allow students to register,
  • view result and download result

Conceptual diagram

Student record system overall architectural diagram

Figure 2: Student record system overall architectural diagram

Tasks of Students and Admin are explained in the figure below:

Figure 3: Task in detail

Interview Questions

With front-end designer:

  • What entries are needed for a student form wherein details can be entered?
  • What entries are needed for the resulting form so that admin can add marks for the students?
  • What entries are needed for the course and subject form?

With back-end designer:

  • What columns should be used for a student table, result-in table, course table, user table, and fees table?
  • Which column should be the primary key to help in unique row identification?
  • How to connect the front end and back end?

Tables

Student table:

Student_id

first name

last name

gender

age

Date of birth

Contact no

address

City

CourseId

email

Password

Primary Key Varchar (10) Unique

Varchar (30)

Varchar (30)

Varchar (30)

int

date

int

Varchar (100)

Varchar (30)

Varchar (30)

Varchar (30)

Varchar (30)

Course table:

Course_id

Course name

Subjects

Primary Key Varchar (10) Unique

Varchar (30)

Varchar (30)

Subject table:

Subject_id

Subject name

Course_id

Primary Key Varchar (10) Unique

Varchar (30)

Varchar (30)

User table:

User_id

username

password

role

Primary Key Varchar (10) Unique

Varchar (30)

Varchar (30)

Varchar (30)

Result table:

Result_id

Student_id

first name

last name

Course_id

Subject_id

Marks obtained

Total marks

Grade

Primary Key Varchar (10) Unique

Varchar (30)

Varchar (30)

 

Varchar (30)

Varchar (30)

Int

int

Int

Student Record System Application Manual

Software required: Wamp Server, MS Access, ODBC driver, and Windows 7 or above operating system

  1. Extract the Zip file as an SRS folder.
  2. Install Wamp server
  3. In the www folder of the Wamp server Copy and paste SRS complete folder
  4. Open the Wamp server and start all the services
  5. The SRS folder has a database file named srms.mdb file.
  6. Open odbc driver by clicking on data sources.
  7. Click Add and select Microsoft Access Driver .mdb and accdb.
  8. Enter the Data source name as srms and select the folder path where the srms.mdb file is i.e. C:wamp/www/SRA/srms.mdb
  9. Click finish and see that the srms.mdb is listed as the new database.
  10. Now open a web browser and type URL localhost/SRS

This shall open the home page

  1. The admin can enter the application through username and password as admin and admin

  1. From the dashboard the admin can navigate to any sections like course, fees, subjects, students, results, etc.

For example Admin wants to add a student

  1. The student form opens wherein the student details can be entered.

In this way results, courses, subjects, etc can be added and updated.

On the student end, the student can enter the application through the Roll number as a password and select the class from the dropdown list.

  1. On entering details if the results are not ready you get

Otherwise

From where the result file can be downloaded.

ER DIAGRAM

Download Student School Record Keeping System Application Database PHP, MySQL, and MSAccess Project Code and Database.