Day: September 9, 2022

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