Presetation-Assignment-DBMS .pdf

Presetation-Assignment-DBMS .pdf

1. EDU AI MUSEUM
MANAGEMENT SYSTEM
OVERVIEW
An Academic Project
Presentation
Presented by: Asif Zaman
Date: 29/12/2025

2. Welcome to our presentation on the EDU AI Museum Management System. This
system is designed to enhance the management and operation of a museum
overseen by the Computer Science and Engineering department. It includes
managing various entities like visitors, museum sections, specimens, developers, and
administrative staff.
Introduction

3. Functional Requirements
Introduction: Briefly explain that functional requirements describe what the system should do to meet the
needs of its users and stakeholders.
Visitor Management:
Objective: Ensure comprehensive management of visitor data.
Features:
Record Visitor Details: Store essential information such as Name, National ID (NID), Contact
Information, and Date of Visit.
Retrieve Visitor History: Allow users to look up past visit data using either the visitor’s NID or name,
facilitating easy access to visitor records for customer service and historical analysis.

4. Functional Requirements
Museum Section and Specimen Management:
Objective: Efficiently manage the categorization and details of various museum sections and the specimens
contained within.
Features:
Add/Update/Delete Sections: Provide functionalities to modify museum section details as exhibits
change or expand.
Add/Update/Delete Specimens: Facilitate the management of specimen records including their name,
description, developer, and development date.
Detail Recording for Specimens: Maintain thorough records for each specimen, crucial for educational
purposes and museum management.

5. Functional Requirements
Developer Management:
Objective: Manage information about the individuals or groups who develop the specimens, typically
teachers and students.
Features:
Record Developer Details: Keep a database of all developers including their names, roles (teacher or
student), and contact information.
Track Developer-Specimen Relationships: Identify and manage the links between developers and
the specimens they have created, which is essential for attribution and historical records.

6. Functional Requirements
Admin Management:
Objective: Oversee administrative functions related to museum management.
Features:
Professor Rotation: Manage the scheduling and rotation of governing professors from the CSE
department, maintaining fresh leadership and perspectives.
Assignment of Roles: Assign professors and other staff to various management roles within the
museum.

7. Functional Requirements
Report Generation:
Objective: Provide robust reporting tools to support decision-making and museum operations.
Features:
Visitor Reports: Generate data on visitor numbers for specific dates, assisting in crowd
management and event planning.
Specimen Reports: Produce detailed reports about specimens in various sections, useful for
educational purposes and inventory management.
Developer Reports: Output lists of developers associated with specific specimens, useful for
accreditation and performance evaluations.
Conclusion: Sum up by emphasizing how these requirements are designed to ensure the system is user-
friendly, comprehensive, and meets the specific needs of managing a museum effectively.

8. Entities and Attributes
Visitor
VisitorID (PK): Primary
identifier.
Name, NID, Contact,
VisitDate: Personal and
visit details.
Section:
SectionID (PK): Unique
identifier for museum
sections.
SectionName,
Description: Details of
each section.
Specimen:
SpecimenID (PK):
Unique identifier for
each specimen.
Name, Description,
DevelopmentDate:
Details of specimens.
SectionID (FK): Links
specimen to its
respective section.

9. Entities and Attributes
Developer:
DeveloperID (PK):
Unique identifier for
each developer.
Name, Role, Contact:
Details of the
developer's role and
contact information.
Professor:
ProfessorID (PK):
Unique identifier for
each governing
professor.
Name, Contact,
RotationStartDate,
RotationEndDate:
Details of professorial
tenure and contact
information.

10. E-R Diagram
SECTION
SectionID
SectionName
Description
SPECIMEN
SpecimenID
Name
Description
DevelopmentDate
SPECIMENDEVELOPER DEVELOPER
DeveloperID
Name
Role
Contact
SECTIONSPECIMEN
VISITOR
VisitorID
Name
NID
Contact
VisitDate
PROFESSOR
ProfessorID
Name
Contact
RotationStartDate
RotationEndDate

11. Schema Diagram
VISITOR
VisitorID
Name
NID
Contact
VisitDate
SECTION
SectionID
SectionName
Description
SPECIMEN
SpecimenID
Name
Description
DevelopmentDate
SectionID
PROFESSOR
ProfessorID
Name
Contact
RotationStartDate
RotationEndDate
SPECIMENDEVELOPER
SpecimenID
DeveloperID
DEVELOPER
DeveloperID
Name
Role
Contact

12. Sample Database
CREATE DATABASE EDU_AI_Museum;
USE EDU_AI_Museum;
CREATE TABLE Section (
SectionID INT
AUTO_INCREMENT PRIMARY
KEY,
SectionName
VARCHAR(100),
Description TEXT
);
CREATE TABLE Visitor (
VisitorID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100),
NID VARCHAR(50) UNIQUE,
Contact VARCHAR(15),
VisitDate DATE
);
CREATE TABLE Specimen (
SpecimenID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100),
Description TEXT,
DevelopmentDate DATE,SectionID INT,
FOREIGN KEY (SectionID) REFERENCES Section(SectionID)
);

13. Sample Database
CREATE TABLE Developer (
DeveloperID INT AUTO_INCREMENT
PRIMARY KEY,
Name VARCHAR(100),
Role ENUM('Teacher', 'Student'),
Contact VARCHAR(15)
);
CREATE TABLE Professor (
ProfessorID INT AUTO_INCREMENT PRIMARY KEY,
Name VARCHAR(100),
Contact VARCHAR(15),
RotationStartDate DATE,RotationEndDate DATE
);
CREATE TABLE SpecimenDeveloper (
SpecimenID INT,
DeveloperID INT,
FOREIGN KEY (SpecimenID) REFERENCES Specimen(SpecimenID),
FOREIGN KEY (DeveloperID) REFERENCES Developer(DeveloperID),
PRIMARY KEY (SpecimenID, DeveloperID)
);

14. Sample Database
Developer Table
Section Table

15. Sample Database
Specimen Developer Table
Professor Table

16. Sample Database
Specimen Table
Visitor Table

17. Sample queries
Query 1: Retrieve All Visitors
Query 2: Retrieve Specimens in the "Robotics" Section
SELECT * FROM Visitor;
SELECT S.Name AS SpecimenName, S.Description AS
SpecimenDescription
FROM Specimen S
JOIN Section Sec ON S.SectionID = Sec.SectionID
WHERE Sec.SectionName = 'Robotics';

18. Sample queries
Query 3: Find Developers of "AI Robot Prototype"
SELECT D.Name AS DeveloperName, D.Role AS DeveloperRole
FROM Developer D
JOIN SpecimenDeveloper SD ON D.DeveloperID = SD.DeveloperID
JOIN Specimen S ON SD.SpecimenID = S.SpecimenID
WHERE S.Name = 'AI Robot Prototype';
SELECT COUNT(*) AS VisitorCount
FROM Visitor
WHERE VisitDate = '2024-11-16';
Query 4: Count Visitors on '2024-11-16'

19. Sample queries
Query 5: List All Professors Currently Governing the Museum
SELECT Name, Contact, RotationStartDate, RotationEndDate
FROM Professor
WHERE CURRENT_DATE BETWEEN RotationStartDate AND
RotationEndDate;

20. Sample Outputs
Query 1: Retrieve All Visitors
Query 2: Retrieve Specimens in the "Robotics" Section

21. Sample Outputs
Query 3: Find Developers of "AI Robot Prototype"
Query 4: Count Visitors on '2024-11-16'

22. Sample Outputs
Query 5: List All Professors Currently Governing the Museum

23. ASIF ZAMAN
01670415337
[email protected]