Category: DBMS Assignments

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

Restaurant Database Management System Project using C# & MS Access

Title: Restaurant Database Management System.

Topic Research and Executive Summary:

The Restaurant database Management System is an Enterprise Database project which has a few advantages where restaurants can improve customer relationships; Restaurant owners can know when the customers arrive most of the time which can enhance productivity. By using a Restaurant database Management System owners can have effective use of Human Resources. Due to this database system, we can manage time we can reduce human efficiency and errors, and get accurate information.

Statement of Work

Project Description:

This database system is developed in such a way that restaurant can get accurate details of both offline and online ordering through mobile or web application so customer gets a notification if the order is ready for pickup and customer can track the order if he/she orders online for home delivery.

Due to this database management system, we can reduce the complexity between restaurant management and customers.

Project Challenge:

  1. Collecting the information from the Restaurant owner visa by reading articles or interviewing the store manager of the company.
  2. Learning C# to create an application to connect with Microsoft access. C# is a programming language that is simple, and modern. The language and its implementations provide strong support to software engineering principles. C# is familiar to C and C++
  1. Microsoft Access is a database management system from Microsoft that combines the relational Microsoft jet database engine with a Graphical user interface and software tools.
  1. Must learn to design a logical relational database                                                              
  1. Create ER diagram

Project Description and Milestones:

Stage1: Analysis: The main objective of the database is to manage the details of the staff of the restaurant, the customer who visits the restaurant to order and take away behavior that the customers who order online, and manage the delivery system where the restaurant can assign delivery boys to deliver the food the customer.

Stage2: logical design: The Database consists of 5 data tables which are

Restaurants in different locations.

The store manager who takes care of the restaurant manages the staff and the daily supplies like food items

The staff are of different types such as cashier, waiter, receptionist, cleaner

Customers who order online, who visit the store and have dine-in, and others who take away

Delivery guy who takes the orders and delivers the food to the customer

Stage3: physical design:

The Restaurant is located in different locations so it has a restaurant id and restaurant address.

The manager data table has the manager id, manager name, manager age and management experience, and manager address.

The Staff has staff id, staff age, staff assigned work, staff address, and staff experience.

Data table for a delivery system where it has details of delivery boy, location of the delivery, order details

The customer data table has customer order details and customer names.

The customer ordered online then the data table has the customer’s address, customer name, order details, and delivery boy id.

Stage 4: Implementation: Create the application interface using C# programming language, and create a database using Microsoft access.

Stage5: Modification and Maintenance:

Modification and maintenance of the database are done in the Microsoft database.

Methodology and implementation plan:

This database management system has 4 phases, design, coding, testing, and maintenance. By use of RAD methodology, these stages are implemented.

Step 1: Need to create a design a database with five data tables.

Step 2: By using C# programming we need to create a user application interface, Using Microsoft access we need to create a Database and interlink the application interface and database.

Step 3: Need to test the database system and check for the error the clear the errors.

Step 4: Need to check the database on a regular basis because things to be needed to be upgraded.

Expected results: The Deliverables

By creating a working application for the restaurant so they can access it easily. The Restaurant owners can view the details of the store manager, the number of staff working on a daily basis, and the financial details.

The Store manager can use the database system to view the no of people working in the store and can view the accurate details of the orders

Customers can easily order the food through the online menu

Supporting Facilities:

Database: Designed in Microsoft ACCESS

Application program language: C# programming

Other application: Microsoft Visual Studio (Programming IDE)

Posted on

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.

Posted on

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

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

DBMS Assignment for MS Students

Create a Students table which has the following columns: student id, student name, email address, gender, status, GPA, age. The student id can be an integer between 100 and 999. The name is limited to 10 characters. The email address is limited to 25 characters. Gender is a single letter, either m or f. Status is two characters from the set {Fr, So, Ju, Se}. GPA is a real number with one digit to the right of the decimal place between 0 and 4 inclusive. Age is an integer between 15 and 30, inclusive. The number of clubs is an integer greater than or equal to zero. The GPA and age should have default values of zero. No null values are allowed for the any of the columns. The student number is the primary key. Email is an alternate key. There are no foreign keys.

Create a Clubs table which has the following columns, club id, club name, meeting room, number of club members, and contact email. The club id is a string of six characters; all club ids begin with the letter C followed by digits. The club name is limited to 10 characters. The meeting room is a three digit integer. The number of club members is an integer. The email address is limited to 25 characters. There are no default values. No null values are allowed except for the email and number of members columns. The club id is the primary key. The email column is a foreign key reference to the email column in Students.

The club members tables has the following columns: the club id, the student id, and the date joined. The club id has the same type as the club id in the Clubs table. The student id column has the same type as the student id in the Students table. The date joined column is a date. No null values are allowed in any column. The primary key is all three columns. The club id column is a foreign key reference to the club id column in Clubs table. The student id column is a foreign key reference to the student id column in the Students table.

Write the insert statement to populate the students table. The data will be provided by the instructor.

Write the insert statement to populate the clubs table. The data will be provided by the instructor.

Write the insert statement to populate the clubs table. The data will be provided by the instructor.

Write the update statements needed to set the number of members column to the correct value for each club. Use a select query to count the number of members the club has from the club members table and assign this value to the number of members column. There should be an update statement for each club.

Write a select query that produces a list of student names.

Write a select query that produces a list of student names and their GPAs.

Write a query which lists the name of the student to contact for each club along with the club name

Write a select query that produces a list of student names and their GPAs for students having a GPA below 2.0.

Write a select query that lists the names of clubs which have members who are freshman. Use natural joins on the student, club members However, the Clubs table has email and cid columns; you only want to join Clubs using the cid column. The club names should be listed only once. Order the club names alphbetically.

Create a view which uses the query in 11. Write a select query to display the view.

Create a view which shows only clubs with members. Write a select query to display the view.

Write a select query that returns the average number of members in the clubs.

Write a select query which lists a student’s name only if that student is in the Debate club. Use the hint from problem 11.

Write a select query which shows the highest GPA in each the the student statuses (‘Fr’, ‘So’, ‘Ju’, ‘Se’).

Modify the query above to show a class’s highest GPA only if it is greater the 3.5.

Write a query which lists the student names who are members of none of the clubs.

Use the query from 19. in the where part of a delete statement to delete students from the student table who are not members of any club.