A
Database Management (4331603)
Micro Project Report
On
“Online Chatting App
Management System”
Submitted By
Khatri dev (236470316055)
Khandla Harshil (236470316054)
Narola yagnesh (236470316068)
Under the guidance of
Ms. Nikita A. Patel / Mrs. Dipika K. Patel
Submitted in Partial Fulfillment for 3rd Semester of the Degree of
DIPLOMA IN INFORMATION TECHNOLOGY
TAPI DIPLOMA ENGINEERING COLLEGE, SURAT
Year-2024
TAPI DIPLOMA ENGINEERING COLLEGE, SURAT
DEPARTMENT OF INFORMATION TECHNOLOGY
CERTIFICATE
This is to certify that the course based project entitled “Railway
Reservation System” submitted Khatri dev (236470316055), Narola
Yagnesh (236470316068),Khandla harshli (236470316054) in partial
fulfillment of Database Management (4331603) Micro Project, 3rd
Semester of the award of the degree of Diploma in Information
Technology during academic year-2024.
DATE:
Faculty
Ms. Nikita A. Patel
or
Mrs. Dipika K. Patel
Abstract
The Online Chatting App Management System facilitates users to inquire about chat
rooms available based on their interests, join and leave chat rooms, send and receive
messages, and check the status of their chat history, etc. The aim of this case study is to
design and develop a database maintaining the records of different chat rooms, user
activities, and messages. This project contains an introduction to the Online Chatting
App Management System. It is a computerized system for managing chat rooms and user
communications in real-time. It is mainly used for facilitating group discussions or
personal conversations over the internet. Online messaging has made the process of
communication easier than ever before. In our daily online life, there are numerous
platforms where one can easily join chat rooms and start conversations. This project
contains an entity-relationship model diagram based on the online chatting app system
and an introduction to the relational model. There is also the design of the database of
the online chatting app system based on the relational model. Example of some SQL
queries to retrieve data from the chat app database are also provided.
Index
Chapter No. Table of Content Page No.
Certificate 2
Abstract 3
1 Introduction 5
2 Data Requirements 6
3 Entity Relationship Diagram 7
4 Schema Diagram 8
5 Normalization 9
6 Data Dictionary 10
7 Creating Database using Oracle 12
8 Test SQL Queries 18
9 Conclusion 21
References 21
Online Chatting App
Management System
CHAPTER - 1: INTRODUCTION
Online Chatting App Management System is an organized collection of data.
The data is typically organized to model aspects of reality in a way that supports
processes requiring information. A DBMS makes it possible for end users to create,
read, update, and delete data in a database. The DBMS essentially serves as an
interface between the database and end users or application programs, ensuring that
data is consistently organized and remains easily accessible. The DBMS manages
three important things: the data, the database engine that allows data to be accessed,
locked, and modified, and the database schema, which defines the database’s logical
structure. These three foundational elements help provide concurrency, security, data
integrity, and uniform administration procedures. The DBMS can offer both logical
and physical data independence. That means it can protect users and applications from
needing to know where data is stored or having to be concerned about changes to the
physical structure of data.
The main purpose of maintaining a database for the Online Chatting App
Management System is to reduce the manual errors involved in managing chat rooms,
messages, and user data, making it convenient for the users and administrators to
maintain information about their users and chat rooms. Due to automation, many
loopholes that exist in the manual maintenance of records can be removed. The speed
of obtaining and processing data will be fast. For future expansion, the proposed
system can be web-enabled so that users can engage in conversations from various
devices. Due to this, sometimes a lot of issues arise in the manual management, and
users may face disputes with lost messages or errors in the system. To solve the above
problems, we design a database that includes user details, chat room availability,
message history, and details of participants in each chat room.
This project is about creating a database for the Online Chatting App
Management System. The system facilitates users to inquire about available chat
rooms based on topics of interest, join and leave rooms, send and receive messages,
and check the status of their messages and chat history. The aim of this case study is
to design and develop a database maintaining the records of different chat rooms, their
TAPI DIPLOMA ENGINEERING COLLEGE Page | 5
Online Chatting App
Management System
statuses, and user interactions. The record of a chat room includes its ID, name,
creator, and creation time, whereas the record of chat room status includes the number
of users present and the number of messages sent.
Users can join any chat room where they are allowed. For this, a user has to
provide the desired chat room ID and their user ID to join. Before joining a chat room,
the validity of the user and chat room ID is checked. Once the IDs are validated, the
user is allowed to join the chat room. The user can then send messages, which are
stored with a unique message ID. The chat room participation can be ended at any
time. For this, the user provides their user ID and room ID. The record of the user’s
participation is then removed from the chat room. When a user leaves, other waiting
users may be able to join if room limits are enforced.
TAPI DIPLOMA ENGINEERING COLLEGE Page | 6
Online Chatting App
Management System
CHAPTER - 2: DATA REQUIREMENTS
Entities Attributes
USER Userid
Password
Username
Email
ChatRoom RoomID
RoomName
Created_By
Created_At
Message MessageID
RoomID
SenderID
Content
Sent_At
UserChatRoom UserID
RoomID
Joined_At
TAPI DIPLOMA ENGINEERING COLLEGE Page | 7
Online Chatting App
Management System
Relationship – cardinality
• Messages - Ternary relationship between USER, CHAT ROOM, and MESSAGE
• Joins - Between USER and CHAT ROOM
• Leaves - Between USER and CHAT ROOM
• Sends - Between USER and MESSAGE
• Contains - Between CHAT ROOM and MESSAGE
• A user can send many messages (1..N)
TAPI DIPLOMA ENGINEERING COLLEGE Page | 8
Online Chatting App
Management System
CHAPTER - 3: ENTITY RELATIONSHIP DIAGRAM
TAPI DIPLOMA ENGINEERING COLLEGE Page | 9
Online Chatting App
CHAPTER - 4: SCHEMA DIAGRAM
User
UserID Username Email Password Created_At
ChatRoom
Room_ID RoomName Created_By Created_At
Message
MessageID RoomID Sender_id Content Sent_At
UserChatRoom
UserID RoomID Joined_At
TAPI DIPLOMA ENGINEERING COLLEGE Page | 8
Online Chatting App
CHAPTER - 5: NORMALIZATION
User
UserID Firstname lastname Email Password Created_At
ChatRoom
RoomID UserID RoomName schedule Created_By Created_At
Message
MessageID UserID RoomID SenderID Content Sent_At
UserChatRoom
UserID RoomID Joined_At
TAPI DIPLOMA ENGINEERING COLLEGE Page | 9
Online Chatting App
CHAPTER - 6: DATA DICTIONARY
6.1 Table Name: User
Description: User information
Field Datatype Size Constraints
UserID VARCHAR2 10 Primary Key
Username VARCHAR2 100 NOT NULL
Email VARCHAR2 100 UNIQUE, NOT NULL
Password VARCHAR2 100 NOT NULL
DEFAULT CURRENT_TIMESTAMP
Created_At date 10
6.2 Table Name: ChatRoom
Field Datatype Size Constraints
RoomID VARCHAR2 100 PRIMARY KEY
RoomName VARCHAR2 10 NOT NULL
Created_By VARCHAR2 100 FOREIGN KEY
Created_At date 10 DEFAULT CURRENT_TIMESTAMP
TAPI DIPLOMA ENGINEERING COLLEGE Page | 10
Online Chatting App
6.3 Table Name: Message
Field Datatype Size Constraints
MessageID Number 100 Primary Key
RoomID VARCHAR2 100 Foreign Key
SenderID Varchar2 10 Foreign Key
Content VARCHAR2 100 NOT NULL
Sent_At DATE 10 DEFAULT CURRENT_TIMESTAMP
6.4 Table Name: UserChatRoom
Field Datatype Size Constraints
UserID VARCHAR2 10 Foreign Key
RoomID Varchar2 100 Foreign Key
Joined_At DATE 10 DEFAULT CURRENT_TIMESTAMP
TAPI DIPLOMA ENGINEERING COLLEGE Page | 11
Online Chatting App
CHAPTER - 7: CREATING DATABASE USING ORACLE
• Create Commands:
SQL> CREATE TABLE User (
UserID VARCHAR2(10) PRIMARY KEY,
Username VARCHAR2(100) NOT NULL,
Email VARCHAR2(100) UNIQUE NOT NULL,
Password VARCHAR2(100) NOT NULL,
Created_At DATE
);
CREATE TABLE ChatRoom (
RoomID VARCHAR2(100) PRIMARY KEY,
RoomName VARCHAR2(10) NOT NULL,
Created_By VARCHAR2(100) FOREIGN KEY REFERENCES User(UserID),
Created_At DATE
);
CREATE TABLE Message (
MessageID NUMBER(100) PRIMARY KEY,
RoomID VARCHAR2(100) FOREIGN KEY REFERENCES ChatRoom(RoomID),
SenderID VARCHAR2(10) FOREIGN KEY REFERENCES User(UserID),
Content VARCHAR2(100) NOT NULL,
Sent_At DATE
);
TAPI DIPLOMA ENGINEERING COLLEGE Page | 12
Online Chatting App
CREATE TABLE UserChatRoom (
UserID VARCHAR2(10) PRIMARY KEY FOREIGN KEY REFERENCES
User(UserID),
RoomID VARCHAR2(100) PRIMARY KEY FOREIGN KEY REFERENCES
ChatRoom(RoomID),
Joined_At DATE,
);
SQL> desc User;
Output:
SQL> desc ChatRoom;
Output:
TAPI DIPLOMA ENGINEERING COLLEGE Page | 13
Online Chatting App
SQL> desc Message;
Output:
SQL> desc UserChatRoom;
Output:
TAPI DIPLOMA ENGINEERING COLLEGE Page | 14
Online Chatting App
• Insert Commands: (Note: insert 5 rows in each tables)
SQL> User table
INSERT INTO User (UserID, Username, Email, Password, Created_At)
VALUES ('U001', 'Alice', 'alice@example.com', 'password123', SYSDATE);
INSERT INTO User (UserID, Username, Email, Password, Created_At)
VALUES ('U002', 'Bob', 'bob@example.com', 'password456', SYSDATE);
INSERT INTO User (UserID, Username, Email, Password, Created_At)
VALUES ('U003', 'Charlie', 'charlie@example.com', 'password789', SYSDATE);
INSERT INTO User (UserID, Username, Email, Password, Created_At)
VALUES ('U004', 'David', 'david@example.com', 'password101', SYSDATE);
INSERT INTO User (UserID, Username, Email, Password, Created_At)
VALUES ('U005', 'Eve', 'eve@example.com', 'password102', SYSDATE);
SQL> ChatRoom table
INSERT INTO ChatRoom (RoomID, RoomName, Created_By, Created_At)
VALUES ('R101', 'TechTalk', 'U001', SYSDATE);
INSERT INTO ChatRoom (RoomID, RoomName, Created_By, Created_At)
VALUES ('R102', 'GamingZone', 'U002', SYSDATE);
INSERT INTO ChatRoom (RoomID, RoomName, Created_By, Created_At)
VALUES ('R103', 'MusicRoom', 'U003', SYSDATE);
INSERT INTO ChatRoom (RoomID, RoomName, Created_By, Created_At)
VALUES ('R104', 'MovieZone', 'U004', SYSDATE);
INSERT INTO ChatRoom (RoomID, RoomName, Created_By, Created_At)
VALUES ('R105', 'SportsTalk', 'U005', SYSDATE);
SQL> Message table
INSERT INTO Message (MessageID, RoomID, SenderID, Content, Sent_At)
VALUES (1001, 'R101', 'U001', 'Hello Tech Enthusiasts!', SYSDATE);
INSERT INTO Message (MessageID, RoomID, SenderID, Content, Sent_At)
VALUES (1002, 'R102', 'U002', 'Anyone playing the new game?', SYSDATE);
TAPI DIPLOMA ENGINEERING COLLEGE Page | 15
Online Chatting App
INSERT INTO Message (MessageID, RoomID, SenderID, Content, Sent_At)
VALUES (1003, 'R103', 'U003', 'Any good music suggestions?', SYSDATE);
INSERT INTO Message (MessageID, RoomID, SenderID, Content, Sent_At)
VALUES (1004, 'R104', 'U004', 'Best movie of the year?', SYSDATE);
INSERT INTO Message (MessageID, RoomID, SenderID, Content, Sent_At)
VALUES (1005, 'R105', 'U005', 'Did you see the last match?', SYSDATE);
SQL> UserChatRoom table
INSERT INTO UserChatRoom (UserID, RoomID, Joined_At)
VALUES ('U001', 'R101', SYSDATE);
INSERT INTO UserChatRoom (UserID, RoomID, Joined_At)
VALUES ('U002', 'R102', SYSDATE);
INSERT INTO UserChatRoom (UserID, RoomID, Joined_At)
VALUES ('U003', 'R103', SYSDATE);
INSERT INTO UserChatRoom (UserID, RoomID, Joined_At)
VALUES ('U004', 'R104', SYSDATE);
INSERT INTO UserChatRoom (UserID, RoomID, Joined_At)
VALUES ('U005', 'R105', SYSDATE);
SQL> SELECT * FROM User;
Output:
TAPI DIPLOMA ENGINEERING COLLEGE Page | 16
Online Chatting App
SQL> SELECT * FROM ChatRoom;
Output:
SQL> SELECT * FROM Message;
Output:
SQL> SELECT * FROM UserChatRoom;
Output:
TAPI DIPLOMA ENGINEERING COLLEGE Page | 17
Online Chatting App
CHAPTER - 8: TEST SQL QUERIES
1- Find a user by username:
SQL> > SELECT * FROM User WHERE Username = 'Alice';
Output:
2- Find users created after a specific date:
SQL> SELECT * FROM User WHERE Created_At > TO_DATE('2024-10-23',
'YYYY-MM-DD');
Output:
3- Update a user's email address
SQL> UPDATE User SET Email = 'newalice@example.com' WHERE UserID =
'U001';
Output:
TAPI DIPLOMA ENGINEERING COLLEGE Page | 18
Online Chatting App
4- Find usernames containing a specific substring
SQL> SELECT * FROM User WHERE Username LIKE '%li%';
Output;
5- Update a chat room's name:
SQL> > UPDATE ChatRoom SET RoomName = 'TechieTalk' WHERE RoomID =
'R101';Output:
6- Order chat rooms by creation date:
SQL> > SELECT * FROM ChatRoom ORDER BY Created_At DESC;
Output:
7 -Count users with passwords of a certain length (more than 8 characters)
SQL> > SELECT COUNT(*) FROM User WHERE LENGTH(Password) > 8;
Output;
TAPI DIPLOMA ENGINEERING COLLEGE Page | 19
Online Chatting App
8- Count users created after a specific date::
SQL>> SELECT COUNT(*) FROM User WHERE Created_At > TO_DATE('2024-
10-22', 'YYYY-MM-DD');
Outpur;
9- Find chat rooms where the room name has at least 6 characters:
SQL > > SELECT * FROM ChatRoom WHERE LENGTH(RoomName) >= 6;
Output;
10- Find messages that were sent in a specific chat room and contain
the word 'play':
SQL> > SELECT * FROM Message WHERE RoomID = 'R102' AND Content LIKE
'%play%';
Output;
TAPI DIPLOMA ENGINEERING COLLEGE Page | 20
Online Chatting App
CHAPTER - 9: CONCLUSION
In conclusion, the Student Attendance Management System is a robust and essential tool for
educational institutions. By meticulously organizing student data, class details, and
attendance records, the system facilitates seamless tracking of student participation in
various classes. The database structure ensures data accuracy and enables educators to
monitor and analyze attendance trends efficiently. This system not only streamlines
administrative tasks but also contributes to a more proactive approach in addressing
attendance-related concerns. With features like class schedules, instructor details, and
subject information, the system offers a comprehensive solution for educational institutions
to enhance their operational efficiency. Overall, the Student Attendance Management System
stands as a vital asset in promoting accountability, improving communication, and fostering a
conducive learning environment within educational settings.
References
• https://www.javatpoint.com/dbms-tutorial
• https://www.academia.edu
TAPI DIPLOMA ENGINEERING COLLEGE Page | 21
