Tag: DBMS

Posted on

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?

Posted on

Blood Bank Management System DBMS Mini Project

The main purpose of the PHP & MySQL-based blood bank management web application system DBMS mini project is to provide the blood bank with an easier way to store and retrieve data and keep a record of the availability of blood in the blood bank.

After inserting the data into the database, staff need not register the same person again. They can simply search for recorded data and retrieve them for future blood donation or receiving purposes of that person.

In the nutshell, it can be summarized that the future scope of the project circles around maintaining information regarding:

  • The person can fix their donation schedule using an online reservation for the donation of blood.
  • The person can search for the availability of required blood in the local blood bank in the case of an emergency.
  • The blood bank stores the details of the blood donated by a person, like RBC, WBC, platelet count, etc.

The above-mentioned points are the enhancements that can be done to increase the applicability and usage of this project.

E-R diagram of the blood management system

E-R diagram of blood management system

MODULE DESCRIPTION

the list of modules incorporated with the “blood management system” is:

Login module:

This module is for employees of blood banks to log in so that only they can access the database.

Person module:

This module is used to store all the personal details of the donor or receiver.

Donor module:

This module is used to record the date and time along with the quantity of blood donated by a person. Personal details are accessed using a personal ID.

Receiver module:

In this module date and time, the quantity of blood received along with hospital details is recorded.

Stock module:

Here we can check the availability of each blood group blood.

RESULT AND ANALYSIS

Home Page of blood bank management system
The login page shows the login page, if the user wants to log in, they must enter a valid/registered username and password and click on the login button.

The home page shows information about the blood bank management system.

Add person page shows the Add person page, here details of the donor/receiver are registered.

Add person page shows once the entry of personal details is done.

The search person page shows the result of the search we have done. If we enter the person’s id it will return all the personal records present in the database.

The donation page shows the New donation page, here person id and units of blood donated are recorded, and submit button is clicked. On successful completion of entering it displays “Your donation is successful”.

Receive page shows the New Receive page, here person id, units of blood donated, and hospital details are recorded, and submit button is clicked. On successful completion of entering it displays “Your receiving is successful”.

Check the Stock page shows the stock details i.e units of blood present in the blood bank of each blood group.

The donation History page shows the Donation history page, the user has to give the time interval from when he wants to see the donation history details. Upon clicking submit he shows the donation history. If there is a donation that took place at that time interval it displays “No record found in the specified time interval”.

Receive History page shows the Receiving history page, the user has to give the time interval from when he wants to see the receive history details. Upon clicking submit he shows the received history. If there is receiving of blood that took place at that time interval it displays “No record found in the specified time interval”.

Add user page shows the add user page, where it asks for the super admin password. Next, you have to enter the new username, and password and confirm the password. Click on create user button. If the super admin password is wrong or the password and confirms password don’t match. It will pop an invalid password. On successfully creating a user it will display “New user created successfully”.

Download the complete Blood Bank Management System DBMS PHP & MySQL Project source code, project report, and execution steps here.

Posted on

Student Database Management System Bootstrap Project

PREFACE

1) The Student Database Management System project DBMS is basically a model to show how various real-life entities in a college are interlinked.

2) This Student Database Management System project is an extract from the model which is used in various institutions.

3) This Student DBMS project tries to map a relationship between various departments in an institution, their teachers, various courses by the teacher, and the students.

4) This Student DBMS project keeps a database about various departments in the institution, teachers involved, various courses taught by teachers, students enrolled in the institution, and keeps a report on the grades scored by students in various courses.

PROJECT ANALYSIS

TECHNOLOGY USED

The Student DBMS project DBMS uses technologies like MySQL, WAMP, and PHP for storing and accessing the database also HTML, CSS/and BOOTSTRAP to maintain the GUI of the project.

MYSQL/WAMP: They were used to store the data of the Student DBMS project.
PHP: It was used as a backend language for storing and accessing the database.
HTML: Front-end language used for developing the architecture of the page.
CSS&BOOTSTRAP: Used for designing the page.

ENTITY MAPPED

1) STUDENTS: This stores the various information about students.
2) DEPARTMENT: This stores the various information about Department.
3) COURSES: This store’s various information about courses.
4) INSTRUCTOR: This stores the various information about the instructor.
5) GRADES: This stores the various information about Grades scored by students.

CONCLUSION

The Student project DBMS mapped the various real-life entities of the institution like departments, teachers, and students and gave us a model through which we can efficiently store, manipulate and retrieve our data.

Through this Student Database Management System Bootstrap Project, we got a rough idea about how the data flows between the departments and how it is stored.

Posted on

Vehicle Rental Service Management System Project

Problem Description:

Vehicle Rental Service Management System is a project that provides unique benefits to the customers to rent a vehicle as their heart desires and gives an opportunity for crowdsourcing to provide a vehicle for rent. Whether renting a vehicle for a vacation or business enterprise, Vehicle Rental Service has a wide range of sports, luxury, and hybrid vehicles to meet every vehicle rental need. Moto of the company is to provide hassle-free check-in and check-out time to satisfy the needs of the customer and make the ride delightful.

Vehicle Rental Service has a unique benefit of crowdsourcing the vehicles to rent; connecting people who want to rent a vehicle and people who provide a vehicle for rent. Vehicle Rental Service thrives on customer satisfaction and building a long-term relationship with the customer and guarantees the best price in the market by reducing the intermediaries. Relational database besides storing database also helps track customer reservations, date reserved, types of vehicles available, and the location of the vehicle.

Database Tables:

The database consists of 9 tables that are closely related to each other and contain crucial information for the business of the Vehicle Rental Service, all the tables provide easy to manage inventory and transaction details for Vehicle Rental Service. The integrated database enables the execution of the dynamic query and provides optimization.

Customer Table

Customers are central to the business and our business transactions. This table contains all important information about the customer such as customer name, contact details, and address information. The customer id consists of six alphanumeric codes. The first two digits will be the first two letters of the customer’s last name, the next two digits will be the month of the date of birth and the last two digits will be the first two digits of the license number.

vehicle Details

The vehicle details table beacons on the information and type of the vehicle. Each vehicle is given an identification number as Vehicle_ID which will be the primary key for the table. A key is a 4 number that is auto-generated. vehicle details provide a wide range of options for the customer to pick from.

Booking Details Table

As the name indicates, this table consists of booking details of the service and comprises all the important booking information such as vehicle identification number, the rate associated with the vehicle_id, and pick up time and return time mapped to a customer identification number.

Payments

One of the critical table involving financials, the payment table consist of a unique primary identification number as a transaction id and identifies each transaction. The attribute card type is used to determine which type of card the customer uses to make a payment.

Rates

Gives the tariffs for various options to rent a vehicle, Rates are aligned with business strategy to provide the best price for the customers and for the clients who provide their vehicle on rent, the rates are provided on the daily basis and prices are strategized to provide a competitive edge to the customers to rent a vehicle and at the same time help vehicle owners. 

Availability

This table gives available options for the customers to choose from while booking a vehicle. The availability table reflects the product line and product depth of Vehicle Rental Service, in general, the availability table gives information about the details of the vehicle and the time slot and duration as to how long a particular vehicle is available.

Feedback

A long-term relationship with the customer leads to the success of the firm, a feedback table is essential for the growth of the business and to provide impeccable service quality. The feedback table consists of a primary key which is a foreign key referencing from the booking table. This table helps capture all overall ratings of the service and facility to provide additional suggestions and comments for the customers.

vehicle Owners

The fundamental concept of the Vehicle Rental Service business model is to facilitate vehicle Owners to rent their vehicles and provide an opportunity to the customers who want to rent a vehicle. Vehicle Rental Service bridges the needs wants, and desires of the customers.

Location

The location table is an essential table to track the city and state details for all the transactions and payment details. 

Posted on

A DBMS Project on Fingerprint-based Attendance System

In this Fingerprint-based Attendance System Database management System project, we have tried to use the knowledge that we gained in this course to make a Fingerprint-based Attendance System. Although we had aimed to implement it using hardware due to hardware constraints, we have tried to implement it online. In this DBMS system, the Faculty for a particular course switches ON the system, and the system marks attendance for the students based on their fingerprints.

Features:

This system apart from marking attendance also maintains the student’s records and the faculty members. We have tried to incorporate the following features:

Administrator: For the modification of crucial information we have created an administrator account. The Administrator only has the privilege to add/remove courses and edit profiles of students and faculty members.

Login Page: On this page, the students and faculty members can log in to their accounts and view their information.

Register Page: For students and faculty members who don’t have their accounts can create their accounts by clicking on REGISTER on the login page. The register page asks for data and creates a profile.

Timetable: This page displays the schedule of all the courses.

Attendance: On this page, a list of the currently running courses and their respective faculty is displayed. There is a slot for the faculty fingerprint. Once the faculty fingerprint matches with any one of the faculty members whose name is being displayed on the page the system automatically turns ON the database to be modified. Now the students are required to input their fingerprints and their attendance is marked.

Tools used:

  • Database Management System: MySql
  • Server for hosting the project: Apache Tomcat with port number 8080.
  • Programming scripts: Java Server Pages, Javascript, HTML, CSS.

Relation to the course:

This whole project like many other projects has two ends. The Front end consists of the GUI which has been made using our prior knowledge and the Back end consists of database interaction which we have learned in this course. We have tried to incorporate the following things that we have learned in this course.

ER Model: The basic thing that we did before starting with the project was to make an ER Model and check the feasibility of the project. This helped us in enlisting the various aspects and classify them into entities and relations.

MySql Queries: In this project we have tried to incorporate the different queries such as Insert, Create, Update, and Alter that we learned in this course.

Triggers: To let the student know about his short attendance we have tried to incorporate the concept of triggers.

System Time: We have tried to fetch the system’s current time and have used it to compare it with the time of the lectures stored in the database.

Conclusion:

While working on this project we brushed upon the knowledge we gained in this course and the hardships and problems faced in implementing such a real-time project helped us learn a lot.

Posted on

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.

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

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