Скачать или смотреть AD3381-DATABASE DESIGN AND MANAGEMENT LAB EX 1 :Database Development Life cycle -PostgreSQL(pgAdmin)

📌 AD3381 - Database Design and Management Lab EX 1 | Database Development Life Cycle in PostgreSQL (pgAdmin)
👨‍🎓 Designed for B.E CSE (AIML) & B.Tech AIDS students following Anna University R-2021 syllabus.

✅ Topics Covered:
✔️ Introduction to Database Development Life Cycle
✔️ Setting up PostgreSQL & pgAdmin
✔️ Creating and Managing Databases
✔️ Schema Design & Normalization
✔️ Practical Demonstration in pgAdmin

🚀 Perfect for:
🎯 Engineering students looking for a hands-on PostgreSQL lab guide!

📢 🔔 Don't forget to LIKE, SHARE & SUBSCRIBE to Cyber Codex for more lab tutorials!

📖 Suitable for:
🎓 B.E CSE (Artificial Intelligence & Machine Learning)
🎓 B.Tech AIDS (Artificial Intelligence & Data Science)
🎯 Anna University R-2021 students, slow learners, and exam preparation

📢 Subscribe to Cyber Codex for More Educational Videos!
🔔 Don’t forget to LIKE, SHARE & SUBSCRIBE for the latest updates.

📥 Download PostgreSQL: Official Site
📥 Download pgAdmin: Official Site

How to download and install :   • B.Tech III Sem-DDM Lab |Install PostgreSQL...  

💬 Got questions? Drop a comment below!

PROCEDURE:
Database Development Life Cycle (DDLC) in PostgreSQL (pgAdmin) with Example
The Database Development Life Cycle (DDLC) consists of several phases that guide the development of a database system. The main phases include:

Requirement Analysis
Conceptual Design
Logical Design
Physical Design
Implementation
Testing & Maintenance
Let's go through each phase with an example using pgAdmin (PostgreSQL GUI Tool).

Step-by-Step Implementation in pgAdmin (PostgreSQL)
1. Requirement Analysis
Identify the business requirements and data needs.
Example: A university wants to store student information, courses, and enrollments.
Requirements:

Store students with ID, name, and email.
Store courses with course ID and name.
Store enrollments to track which students take which courses.
2. Conceptual Design (ER Model)
Develop an Entity-Relationship Diagram (ERD).
Entities & Relationships:

Student(Student_ID, Name, Email)
Course(Course_ID, Course_Name)
Enrollment(Enrollment_ID, Student_ID, Course_ID, Enroll_Date)
3. Logical Design (Schema Definition)
Convert the conceptual design into a relational model using SQL.
Creating Database in pgAdmin
Open pgAdmin.
Right-click on Databases → Click Create → Database.
Name the database UniversityDB → Click Save.
Creating Tables in SQL Query Tool

-- Create Student Table
CREATE TABLE Student (
Student_ID SERIAL PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE NOT NULL
);

-- Create Course Table
CREATE TABLE Course (
Course_ID SERIAL PRIMARY KEY,
Course_Name VARCHAR(100) NOT NULL
);

-- Create Enrollment Table (Many-to-Many Relationship)
CREATE TABLE Enrollment (
Enrollment_ID SERIAL PRIMARY KEY,
Student_ID INT REFERENCES Student(Student_ID) ON DELETE CASCADE,
Course_ID INT REFERENCES Course(Course_ID) ON DELETE CASCADE,
Enroll_Date DATE DEFAULT CURRENT_DATE
);
4. Physical Design (Indexing, Performance Tuning, Storage Considerations)
Indexes improve query performance.

CREATE INDEX idx_student_email ON Student(Email);
CREATE INDEX idx_enrollment_student ON Enrollment(Student_ID);
Normalization: The design avoids redundancy and follows 3rd Normal Form (3NF).
5. Implementation (Inserting Data & Running Queries)
Insert Sample Data

-- Insert Students
INSERT INTO Student (Name, Email) VALUES
('Alice', '[email protected]'),
('Bob', '[email protected]');

-- Insert Courses
INSERT INTO Course (Course_Name) VALUES
('Database Management Systems'),
('Computer Networks');

-- Insert Enrollments
INSERT INTO Enrollment (Student_ID, Course_ID) VALUES
(1, 1), -- Alice enrolled in DBMS
(2, 1), -- Bob enrolled in DBMS
(1, 2); -- Alice enrolled in Computer Networks
Query Data

-- Retrieve all students with courses they enrolled in
SELECT s.Name, c.Course_Name, e.Enroll_Date
FROM Student s
JOIN Enrollment e ON s.Student_ID = e.Student_ID
JOIN Course c ON e.Course_ID = c.Course_ID;
6. Testing & Maintenance
Testing: Run test queries and ensure data integrity.
Backup:

-- Backup the database
pg_dump -U postgres -d UniversityDB -f /path/to/backup.sql
Security: Assign user roles.

CREATE USER db_admin WITH PASSWORD 'password';
GRANT ALL PRIVILEGES ON DATABASE UniversityDB TO db_admin;

📌 Watch More:
▶️ GE3151 - Python Programming Lab

   • GE3171 -Problem Solving and Python Program...