4931_Grace College of Engineering,Thoothukudi

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA

SCIENCE

B.Tech- Artificial Intelligence And Data Science

Anna University Regulation: 2021

AD3381- Database Design And Management Laboratory

II Year/III Semester

Lab Manual

Prepared By,

Mrs. S. Porkodi, AP/AI&DS

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

AD3381 DATABASE DESIGN AND MANAGEMENT LABORATORY L T P C 0 0 3 1.5

COURSE OBJECTIVES:

• To understand the database development life cycle

• To learn database design using conceptual modeling, Normalization

• To implement database using Data definition, Querying using SQL manipulation and SQL programming
• To implement database applications using IDE/RAD tools

• To learn querying Object-relational databases

SUGGESTIVE EXPERIMENTS

1. Database Development Life cycle:

Problem definition and Requirement analysis
Scope and Constraints
2. Database design using Conceptual modeling (ER-EER) – top-down approach
Mapping conceptual to relational database and validate using Normalization

3. Implement the database using SQL Data definition with constraints, Views

4. Query the database using SQL Manipulation

5. Querying/Managing the database using SQL Programming - Stored Procedures/Functions - Constraints

and security using Triggers

6. Database design using Normalization – bottom-up approach

7. Develop database applications using IDE/RAD tools (Eg., NetBeans,VisualStudio)

8. Database design using EER-to-ODB mapping / UML class diagrams

9. Object features of SQL-UDTs and sub-types, Tables using UDTs, Inheritance, Method definition

10. Querying the Object-relational database using Objet Query language

COURSE OUTCOMES

After the completion of this course, students will be able to:

CO1:Understand the database development life cycle

CO2:Design relational database using conceptual-to-relational mapping, Normalization

CO3:Apply SQL for creation, manipulation and retrieval of data

CO4:Develop a database applications for real-time problems

CO5:Design and query object-relational databases TOTAL : 45 PERIODS

HARDWARE: • Standalone Desktops

SOFTWARE: • PostgreSQL

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

EX Database Development Life cycle:

NO:1a

Aim:
To implement an Database Development Life cycle using PgAdmin-PostgreSQL.
Procedure:
Creating a database in pgAdmin involves several steps, starting from problem definition and requirement analysis to
defining the scope and constraints. Here's how you can go through these stages using pgAdmin:
Step 1: Problem Definition and Requirement Analysis
Identify the Problem: Clearly define the problem you are trying to solve with the database. For example, managing
customer data for a retail business.
Gather Requirements: Collect all necessary information regarding what data needs to be stored, how it will be used,
and by whom. This can involve meetings with stakeholders, understanding business processes, and documenting
requirements.
Step 2: Scope and Constraints
Define Scope: Determine the boundaries of the database project. What functionalities will be included and what will
be excluded? For example, the database might include customer information, order details, and product inventory
but not financial transactions.
Identify Constraints: Recognize any limitations or constraints such as budget, time, technology stack, data security,
regulatory requirements, and performance expectations.
Step 3: Create the Database using pgAdmin
Step-by-Step Guide:
Install pgAdmin and PostgreSQL:
Ensure pgAdmin and PostgreSQL are installed on your system. You can download them from the official website.
Open pgAdmin:
Launch pgAdmin and connect to your PostgreSQL server.
Create a New Database:
In the pgAdmin interface, right-click on the 'Databases' node in the Browser tree, then select 'Create' -> 'Database'.
Define the Database:
In the 'Create Database' dialog, provide a name for your database, for example, RetailDB.
Click 'Save' to create the database.
Create Schemas and Tables:
Right-click on the newly created database and choose 'Create' -> 'Schema' to define the schema.
Right-click on the 'Schemas' node and select 'Create' -> 'Table' to start defining your tables based on your
requirements analysis.
For each table, define the columns, data types, constraints (e.g., primary keys, foreign keys), and other attributes.
Add Constraints and Indexes:
While creating tables, you can add constraints like NOT NULL, UNIQUE, PRIMARY KEY, and FOREIGN KEY
to ensure data integrity.
Right-click on a table and choose 'Properties' -> 'Constraints' to add constraints.
Add indexes to columns that will be frequently queried to improve performance.
Insert Initial Data:
Use the 'Query Tool' in pgAdmin to write SQL INSERT statements to populate your tables with initial data.
For example:
sql code
INSERT INTO customers (customer_id, name, email) VALUES (1, 'porkodi', porkodi@gracecoe.org');
Define Relationships:
Ensure tables are properly linked through foreign keys to maintain referential integrity.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

For example, link the orders table to the customers table using a foreign key.
Set Up User Roles and Permissions:
Define roles and permissions to control access to the database.
Right-click on the 'Login/Group Roles' node and choose 'Create' -> 'Login/Group Role' to create users and assign
appropriate permissions.
Example: Creating a Table with Constraints
Syntax:
CREATE TABLE table_name (
column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
...
);
sql code
CREATE TABLE customers (
customer_id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);

CREATE TABLE orders (

order_id SERIAL PRIMARY KEY,
order_date DATE NOT NULL,
customer_id INT NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers (customer_id)
);
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY, -- Primary Key Constraint
FirstName VARCHAR(50) NOT NULL, -- Not Null Constraint
LastName VARCHAR(50) NOT NULL, -- Not Null Constraint
Email VARCHAR(100) UNIQUE, -- Unique Constraint
DepartmentID INT, -- Foreign Key Column
Salary DECIMAL(10, 2) DEFAULT 50000, -- Default Constraint
DateOfBirth DATE CHECK (DateOfBirth < '2005-01-01'), -- Check Constraint
CONSTRAINT FK_Department FOREIGN KEY (DepartmentID) -- Foreign Key Constraint
REFERENCES Departments(DepartmentID)
);
OUTPUT:

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Procedure 2:
In the database development lifecycle, the initial stages involve defining the problem, analyzing requirements, and
identifying the scope and constraints. Below is a detailed procedure for each of these stages, including example SQL
code snippets and expected results.

Step 1: Problem Definition and Requirement Analysis

Problem Definition:
Clearly define the problem your database aims to solve. For example, let’s assume we are developing a database for
a library management system.

Requirement Analysis:

Gather Requirements: Conduct interviews, surveys, and workshops with stakeholders (librarians, library members,
administrators).
Document Requirements: List out all the required functionalities like managing books, members, loans, and returns.
Identify Entities and Attributes:
Entities: Books, Members, Loans
Attributes:
Books: BookID, Title, Author, ISBN, PublicationYear
Members: MemberID, Name, Email, MembershipDate
Loans: LoanID, BookID, MemberID, LoanDate, ReturnDate
Step 2: Scope and Constraints
Scope:
Inclusions: The system will include functionalities to manage book details, member details, loan transactions, and
report generation.
Exclusions: The system will not handle digital media, financial transactions, or integration with external systems in
this phase.
Constraints:
Technical Constraints: Limited to using PostgreSQL and pgAdmin.
Resource Constraints: Limited budget and time for initial development (6 months).
Operational Constraints: Must comply with data protection regulations (GDPR).
Creating the Database Using pgAdmin
Install pgAdmin and PostgreSQL:
Ensure pgAdmin and PostgreSQL are installed on your system.
Open pgAdmin:
Launch pgAdmin and connect to your PostgreSQL server.
Create a New Database:
In pgAdmin, right-click on the 'Databases' node in the Browser tree and select 'Create' -> 'Database'. Name it
LibraryDB.
Define Tables:

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Right-click on the 'Schemas' node and select 'Create' -> 'Table' to start defining your tables based on the requirement
analysis.

Validating Design Using Normalization Normalization Steps:

First Normal Form (1NF): Ensure all tables have atomic values and primary keys.

Example: The Books table has atomic attributes like Title, Author.
Second Normal Form (2NF): Ensure all non-key attributes are fully functional dependent on the primary key.

Example: The Loans table ensures LoanDate and ReturnDate are dependent on LoanID.
Third Normal Form (3NF): Ensure no transitive dependencies.

Example: The Members table ensures all attributes are dependent only on MemberID.

RESULT:
Thus the above program was implemented and executed successfully.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

EX NO:2 Database design using Conceptual modeling (ER-EER) – top-down approach

Aim:
To implement an Database design using Conceptual modeling (ER-EER) – top-down approach. Mapping conceptual
to relational database and validate using Normalization
Procedure:

Database Design Using Conceptual Modeling (ER-EER) – Top-Down Approach

The top-down approach in database design starts with a high-level conceptual model, typically an Entity-
Relationship (ER) or Enhanced Entity-Relationship (EER) model, and maps it to a relational database schema. This
process includes the following steps:

Conceptual Modeling:Create an ER or EER diagram that captures the entities, relationships, and attributes of the
system.
Mapping Conceptual Model to Relational Model: Convert the ER/EER diagram into a relational schema.
Normalization: Validate and refine the relational schema using normalization techniques to ensure it meets desired
properties.
Step 1: Conceptual Modeling with ER/EER Diagrams
Identify Entities: Determine the main objects (entities) in the system. For example, in a retail system, entities might
include Customer, Order, and Product.
Identify Relationships: Determine how entities are related to each other. For example, a Customer places an Order.
Define Attributes: Identify the attributes for each entity. For example, a Customer might have attributes like
CustomerID, Name, and Email.
Enhanced ER (EER) Modeling: Incorporate additional concepts like specialization, generalization, and
aggregation if necessary.
Example ER Diagram:

Entities: Customer, Order, Product

Relationships: places, contains
Attributes:
Customer: CustomerID (PK), Name, Email
Order: OrderID (PK), OrderDate
Product: ProductID (PK), ProductName, Price
Relationships:
Customer places Order
Order contains Product

Step 2: Mapping Conceptual Model to Relational Database

Convert Entities to Tables: Each entity in the ER diagram becomes a table.

Convert Relationships to Foreign Keys: Relationships are mapped using foreign keys.
Map Attributes to Columns: Each attribute becomes a column in the corresponding table.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Procedure
Identify Entities: Determine the main objects (entities) in the system.
Identify Relationships: Determine how entities are related to each other.
Define Attributes: Identify the attributes for each entity.
Enhanced ER (EER) Modeling: Incorporate additional concepts like specialization, generalization, and aggregation
if necessary.
Example: Library Management System

Entities: Books, Members, Loans

Relationships: Members borrow Books
Attributes:
Books: BookID, Title, Author, ISBN, PublicationYear
Members: MemberID, Name, Email, MembershipDate
Loans: LoanID, BookID, MemberID, LoanDate, ReturnDate
ER Diagram
Books: BookID (PK), Title, Author, ISBN, PublicationYear
Members: MemberID (PK), Name, Email, MembershipDate
Loans: LoanID (PK), BookID (FK), MemberID (FK), LoanDate, ReturnDate
Relationships:

A Member can borrow multiple Books.

A Book can be borrowed by multiple Members.
Step 2: Mapping Conceptual Model to Relational Database
Procedure
Convert Entities to Tables: Each entity becomes a table.
Convert Relationships to Foreign Keys: Relationships are mapped using foreign keys.
Map Attributes to Columns: Each attribute becomes a column in the corresponding table.

SQL QUERY:

-- Create Books Table

CREATE TABLE Books (
BookID SERIAL PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
Author VARCHAR(255) NOT NULL,
ISBN VARCHAR(13) UNIQUE NOT NULL,
PublicationYear INT
);

-- Create Members Table

CREATE TABLE Members (
MemberID SERIAL PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Email VARCHAR(255) UNIQUE NOT NULL,
MembershipDate DATE NOT NULL
);

-- Create Loans Table

CREATE TABLE Loans (
LoanID SERIAL PRIMARY KEY,
BookID INT NOT NULL,
MemberID INT NOT NULL,
LoanDate DATE NOT NULL,
ReturnDate DATE,
FOREIGN KEY (BookID) REFERENCES Books(BookID),
FOREIGN KEY (MemberID) REFERENCES Members(MemberID)

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

);
Step 3: Validate Using Normalization
Procedure
First Normal Form (1NF): Ensure all tables have atomic values and primary keys.
Second Normal Form (2NF): Ensure all non-key attributes are fully functional dependent on the primary key.
Third Normal Form (3NF): Ensure no transitive dependencies (non-key attribute dependent on another non-key
attribute).
Example Normalization Process:

1NF: Ensure atomic attributes and primary keys.

CREATE TABLE Members (
MemberID SERIAL PRIMARY KEY,
Name VARCHAR(255),
Email VARCHAR(255)
);
2NF: Ensure non-key attributes depend on the primary key.
CREATE TABLE Loans (
LoanID SERIAL PRIMARY KEY,
BookID INT,
MemberID INT,
LoanDate DATE,
ReturnDate DATE,
FOREIGN KEY (BookID) REFERENCES Books(BookID),
FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);
3NF: Remove transitive dependencies.
CREATE TABLE Books (
BookID SERIAL PRIMARY KEY,
Title VARCHAR(255),
Author VARCHAR(255),
ISBN VARCHAR(13),
PublicationYear INT
);
OUTPUT:

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

RESULT:
Thus the above program was implemented and executed successfully.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

EX NO:3 Implement the database using SQL Data definition with constraints, Views

Aim:
To Implement the database using SQL Data definition with constraints, Views
Procedure:
Step 1: Define the Database Schema
Launch pgAdmin: Open pgAdmin and connect to your PostgreSQL server.
Create a New Database:
Right-click on the 'Databases' node in the Browser tree.
Select 'Create' -> 'Database'.
Name the database LibraryDB.
Step 2: Create Tables with Constraints
Open Query Tool:

Right-click on the LibraryDB database.

Select 'Query Tool'.
Create Tables:

Enter the following SQL commands to create tables with constraints.

SQL Query :
-- Create Books Table
CREATE TABLE Books (
BookID SERIAL PRIMARY KEY,
Title VARCHAR(255) NOT NULL,
Author VARCHAR(255) NOT NULL,
ISBN VARCHAR(13) UNIQUE NOT NULL,
PublicationYear INT CHECK (PublicationYear > 0)
);

-- Create Members Table

CREATE TABLE Members (
MemberID SERIAL PRIMARY KEY,
Name VARCHAR(255) NOT NULL,
Email VARCHAR(255) UNIQUE NOT NULL,
MembershipDate DATE NOT NULL
);

-- Create Loans Table

CREATE TABLE Loans (
LoanID SERIAL PRIMARY KEY,
BookID INT NOT NULL,
MemberID INT NOT NULL,
LoanDate DATE NOT NULL,
ReturnDate DATE,
FOREIGN KEY (BookID) REFERENCES Books(BookID),
FOREIGN KEY (MemberID) REFERENCES Members(MemberID)
);
3.Execute SQL Commands:
Click the 'Execute/Refresh' button to run the SQL commands.
Step 3: Create Views
Open Query Tool:

Continue in the Query Tool or open it again if closed.

Create Views:

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Enter the following SQL commands to create views.

SQL Query :

-- Create Books View

CREATE VIEW BookDetails AS
SELECT
BookID,
Title,
Author,
ISBN,
PublicationYear
FROM Books;

-- Create Members View

CREATE VIEW MemberDetails AS
SELECT
MemberID,
Name,
Email,
MembershipDate
FROM Members;

-- Create Loans View

CREATE VIEW LoanDetails AS
SELECT
Loans.LoanID,
Books.Title AS BookTitle,
Members.Name AS MemberName,
Loans.LoanDate,
Loans.ReturnDate
FROM Loans
JOIN Books ON Loans.BookID = Books.BookID
JOIN Members ON Loans.MemberID = Members.MemberID;
3.Execute SQL Commands:
Click the 'Execute/Refresh' button to run the SQL commands.
Step 4: Verify Database Schema
View Tables and Views:
In the Browser tree, expand the LibraryDB database.
Expand the 'Schemas' node, then 'public'.
Verify that the 'Tables' and 'Views' nodes contain the appropriate entries.
OUTPUT:

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

The following constraints are commonly used in SQL:

NOT NULL - Ensures that a column cannot have a NULL value

UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
CREATE INDEX - Used to create and retrieve data from the database very quickly

RESULT:
Thus the above program was implemented and executed successfully.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

EX NO:4 Implement A* algorithms

Aim:
To implement an basic search starategies like Implement A* search algorithm.
Procedure:

To manipulate and query the database created in the previous steps, follow these procedures and SQL commands:

Step 1: Inserting Data

Procedure

Open pgAdmin and connect to your LibraryDB database.

Open Query Tool to execute SQL commands.
SQL Query:
-- Insert data into Books table
INSERT INTO Books (Title, Author, ISBN, PublicationYear) VALUES
('The Great Gatsby', 'F. Scott Fitzgerald', '9780743273565', 1925),
('To Kill a Mockingbird', 'Harper Lee', '9780061120084', 1960),
('1984', 'George Orwell', '9780451524935', 1949);

-- Insert data into Members table

INSERT INTO Members (Name, Email, MembershipDate) VALUES
('John Doe', 'john.doe@example.com', '2023-01-15'),
('Jane Smith', 'jane.smith@example.com', '2023-03-22'),
('Alice Johnson', 'alice.johnson@example.com', '2023-02-10');

-- Insert data into Loans table

INSERT INTO Loans (BookID, MemberID, LoanDate, ReturnDate) VALUES
(1, 1, '2023-06-01', '2023-06-15'),
(2, 2, '2023-06-05', '2023-06-20'),
(3, 3, '2023-06-10', '2023-06-25');

Step 2: Querying Data

Procedure

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Open Query Tool in pgAdmin.

Execute SQL Commands to retrieve and manipulate data.

Select All Books,Members,Loans

SQL Code Examples

SELECT * FROM Books;

SELECT * FROM Members;
SELECT * FROM Loans;

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

4.Join Tables to Get Detailed Loan Information

SELECT
Loans.LoanID,
Books.Title AS BookTitle,
Members.Name AS MemberName,
Loans.LoanDate,
Loans.ReturnDate

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

FROM Loans
JOIN Books ON Loans.BookID = Books.BookID
JOIN Members ON Loans.MemberID = Members.MemberID;

5.Find Overdue Loans (Assuming Current Date is '2023-06-30')

SELECT
Loans.LoanID,
Books.Title AS BookTitle,
Members.Name AS MemberName,
Loans.LoanDate,
Loans.ReturnDate
FROM Loans
JOIN Books ON Loans.BookID = Books.BookID
JOIN Members ON Loans.MemberID = Members.MemberID
WHERE Loans.ReturnDate < '2023-06-30';

6. Count Number of Books by Each Author

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

SELECT
Author,
COUNT(*) AS NumberOfBooks
FROM Books
GROUP BY Author;

7. Update a Member's Email

UPDATE Members
SET Email = 'john.newemail@example.com'
WHERE MemberID = 1;

8. Delete a Loan Record

DELETE FROM Loans
WHERE LoanID = 3;

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

RESULT:
Thus the above program was implemented and executed successfully.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

EX NO:5 Querying/Managing the database using SQL Programming

- Stored Procedures/Functions
- Constraints and security using Triggers

Aim:
To implement Querying/Managing the database using SQL Programming
- Stored Procedures/Functions
- Constraints and security using Triggers
Procedure:
Stored Procedures/Functions
Procedure to Create a Stored Procedure
Open pgAdmin and connect to your LibraryDB database.
Open Query Tool to execute SQL commands.
Create the Stored Procedure: Enter the SQL commands to create a stored procedure.

Example: Stored Procedure to Add a New Book

-- Drop the procedure if it already exists(Here )

DROP PROCEDURE IF EXISTS AddNewBook;

-- Create the procedure

CREATE PROCEDURE AddNewBook(
IN p_Title VARCHAR(255),
IN p_Author VARCHAR(255),
IN p_ISBN VARCHAR(13),
IN p_PublicationYear INT
)
LANGUAGE plpgsql
AS $$
BEGIN
INSERT INTO Books (Title, Author, ISBN, PublicationYear)
VALUES (p_Title, p_Author, p_ISBN, p_PublicationYear);
END;
$$;
OUTPUT:

Alternative Choice to create a Stored procedure in Pgadmin

The syntax error occurs because PostgreSQL does not use PROCEDURE for stored procedures; instead, it uses
FUNCTION. Let's correct the code using FUNCTION.

Corrected Procedure and Example SQL Queries

Stored Functions
Procedure to Create a Stored Function
Open pgAdmin and connect to your LibraryDB database.
Open Query Tool to execute SQL commands.
Create the Stored Function: Enter the SQL commands to create a stored function.
Example: Stored Function to Add a New Book

-- Drop the function if it already exists

DROP FUNCTION IF EXISTS AddNewBook;

-- Create the function

CREATE FUNCTION AddNewBook(
p_Title VARCHAR(255),

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

p_Author VARCHAR(255),
p_ISBN VARCHAR(13),
p_PublicationYear INT
) RETURNS VOID AS $$
BEGIN
INSERT INTO Books (Title, Author, ISBN, PublicationYear)
VALUES (p_Title, p_Author, p_ISBN, p_PublicationYear);
END;
$$ LANGUAGE plpgsql;
Call the Stored Function

SELECT AddNewBook('The Catcher in the Rye', 'J.D. Salinger', '9780316769488', 1951);

Constraints and Security Using Triggers

Procedure to Create a Trigger
Open pgAdmin and connect to your LibraryDB database.
Open Query Tool to execute SQL commands.
Create the Trigger Function: Define a function that the trigger will call.
Create the Trigger: Define the trigger that will call the function.
Example: Trigger to Enforce a Constraint on Loans

Let's create a trigger that ensures a book cannot be loaned if it is already on loan.

-- Drop the function if it already exists

DROP FUNCTION IF EXISTS CheckBookLoanStatus;

-- Create the trigger function

CREATE FUNCTION CheckBookLoanStatus() RETURNS TRIGGER AS $$
BEGIN
-- Check if the book is already on loan and not returned
IF EXISTS (SELECT 1 FROM Loans WHERE BookID = NEW.BookID AND ReturnDate IS NULL) THEN

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

RAISE EXCEPTION 'Book is already on loan and not returned yet';

END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Trigger

-- Drop the trigger if it already exists

DROP TRIGGER IF EXISTS BookLoanCheck ON Loans;

-- Create the trigger

CREATE TRIGGER BookLoanCheck
BEFORE INSERT ON Loans
FOR EACH ROW
EXECUTE FUNCTION CheckBookLoanStatus();

Example Queries
Insert a Loan (Should Fail if the Book is Already on Loan)

INSERT INTO Loans (BookID, MemberID, LoanDate) VALUES (1, 1, '2023-07-01');

If the book with BookID = 1 is already on loan and not returned, this will raise an exception.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

RESULT:
Thus the above program was implemented and executed successfully.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

EX NO:6 Database design using Normalization – bottom-up approach

Aim:

The aim is to design a normalized relational database in PostgreSQL using pgAdmin, following the bottom-up
approach to normalization. The process involves starting with unstructured data and progressively refining it into
normalized tables (1NF, 2NF, 3NF, etc.) to reduce redundancy and improve data integrity.
Procedure:
1. Start pgAdmin and Connect to PostgreSQL Server
Open pgAdmin and connect to your PostgreSQL server.
Create a new database:
Right-click on the Databases node and select Create > Database.
Name the database (e.g., OrderManagement) and click Save.
2. Create and Populate the Unnormalized Table
Step: Create an Unnormalized Table

Open a Query Tool:

Right-click on the newly created database (OrderManagement) and select Query Tool.
1.Create the Orders table with unstructured data:
CREATE TABLE OrdersUnnormalized (
OrderID INT,
CustomerName VARCHAR(255),
CustomerAddress VARCHAR(255),
ProductName VARCHAR(255),
Quantity INT,
Price DECIMAL(10, 2)
);

Insert sample data:

INSERT INTO OrdersUnnormalized (OrderID, CustomerName, CustomerAddress, ProductName, Quantity,

Price)
VALUES
(1, 'John Doe', '123 Elm St.', 'Widget A', 2, 20.00),
(1, 'John Doe', '123 Elm St.', 'Widget B', 1, 15.00),
(2, 'Jane Smith', '456 Oak St.', 'Widget A', 1, 20.00),
(2, 'Jane Smith', '456 Oak St.', 'Widget C', 3, 30.00);

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Normalize to First Normal Form (1NF):

Step: Ensure Atomicity and Eliminate Repeating Groups

Create the normalized Orders table

CREATE TABLE Orders1NF (

OrderID INT,
CustomerName VARCHAR(255),
CustomerAddress VARCHAR(255),
ProductName VARCHAR(255),
Quantity INT,
Price DECIMAL(10, 2)
);

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Copy data from the unnormalized table:

INSERT INTO Orders1NF (OrderID, CustomerName, CustomerAddress, ProductName, Quantity, Price)
SELECT OrderID, CustomerName, CustomerAddress, ProductName, Quantity, Price FROM OrdersUnnormalized;

Normalize to Second Normal Form (2NF):

Step: Remove Partial Dependencies

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Create the Customers table:

CREATE TABLE Customers (
CustomerID SERIAL PRIMARY KEY,
CustomerName VARCHAR(255),
CustomerAddress VARCHAR(255)
);
Create the Orders table with foreign keys:
CREATE TABLE Orders2NF (
OrderID INT,
CustomerID INT,
ProductName VARCHAR(255),
Quantity INT,
Price DECIMAL(10, 2),
PRIMARY KEY (OrderID, ProductName),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Populate the Customers table:

INSERT INTO Customers (CustomerName, CustomerAddress)
SELECT DISTINCT CustomerName, CustomerAddress FROM Orders1NF;
Populate the Orders2NF table:
INSERT INTO Orders2NF (OrderID, Customer_ID, ProductName, Quantity, Price)
SELECT o.OrderID, c.Customer_ID, o.ProductName, o.Quantity, o.Price
FROM Orders1NF o
JOIN Customers c ON o.CustomerName = c.Name AND o.CustomerAddress = c.email;

Normalize to Third Normal Form (3NF):

Step: Remove Transitive Dependencies

Create the Products table

CREATE TABLE Products (

ProductName VARCHAR(255) PRIMARY KEY,
Price DECIMAL(10, 2)

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

);

Update the Orders table to reference the Products table

CREATE TABLE Orders3NF (

OrderID INT,
Customer_ID INT,
ProductName VARCHAR(255),
Quantity INT,
PRIMARY KEY (OrderID, ProductName),
FOREIGN KEY (Customer_ID) REFERENCES Customers(Customer_ID),
FOREIGN KEY (ProductName) REFERENCES Products(ProductName)
);

Populate the Products table:

INSERT INTO Products (ProductName, Price)
SELECT DISTINCT ProductName, Price FROM Orders2NF;

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Populate the Orders3NF table:

INSERT INTO Orders3NF (OrderID, CustomerID, ProductName, Quantity)
SELECT OrderID, CustomerID, ProductName, Quantity FROM Orders2NF;

Verifying the Normalized Database

Step: Write Queries to Verify the Normalized Structure

Retrieve customer orders:

SELECT c.CustomerName, c.CustomerAddress, o.OrderID, p.ProductName, o.Quantity, p.Price
FROM Orders3NF o

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

JOIN Customers c ON o.CustomerID = c.CustomerID

JOIN Products p ON o.ProductName = p.ProductName;

Check for Data Integrity by ensuring there is no redundancy or anomalies in the output.

RESULT:
Thus the above sql was implemented and executed successfully.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

EX NO:6 Database design using Normalization – bottom-up approach

Aim
The aim is to develop a VB.NET console desktop application that interacts with a SQL Server database, allowing
users to perform CRUD operations on a set of data (e.g., products, customers). The application will demonstrate
database connectivity, data manipulation, and user interaction in a console environment.

Procedure
1. Set Up the Development Environment
Install Visual Studio:

Ensure Visual Studio is installed with the .NET desktop development workload.
Install SQL Server:

Install SQL Server Express or SQL Server Developer Edition if it’s not already installed.
Optionally, install SQL Server Management Studio (SSMS) to manage your database.
2. Create the Database
Create a new database in SQL Server:
CREATE DATABASE ProductManagement;
USE ProductManagement;
CREATE TABLE Products (
ProductID SERIAL PRIMARY KEY,
ProductName VARCHAR(255) NOT NULL,
Price NUMERIC(10, 2) NOT NULL,
Quantity INT NOT NULL

);

CREATE TABLE Customers (

CustomerID SERIAL PRIMARY KEY,
CustomerName VARCHAR(255) NOT NULL,
Email VARCHAR(255) NOT NULL
);

-- Create Orders table

CREATE TABLE Orders (
OrderID SERIAL PRIMARY KEY,
CustomerID INT REFERENCES Customers(CustomerID),
OrderDate DATE NOT NULL,
TotalAmount NUMERIC(10, 2) NOT NULL
);

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

-- Create OrderDetails table

CREATE TABLE OrderDetails (
OrderDetailID SERIAL PRIMARY KEY,
OrderID INT REFERENCES Orders(OrderID),
ProductID INT REFERENCES Products(ProductID),
Quantity INT NOT NULL,
Price NUMERIC(10, 2) NOT NULL
);

Insert sample data:

Insert sample data into the Products table:
INSERT INTO Products (ProductName, Price, Quantity) VALUES
('Widget A', 20.00, 100),
('Widget B', 15.00, 150),
('Widget C', 30.00, 200);

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

3. Implement the Database Connection

1. Set Up the Connection String:

o In the code-behind file (e.g., Form1.vb), set up the connection string to connect to your
PostgreSQL database:
Dim connectionString As String =
"Host=localhost;Username=your_username;Password=your_password;Database=ProductManagement"

2. Create a Function to Establish the Database Connection:

Private Function GetConnection() As NpgsqlConnection
Return New NpgsqlConnection(connectionString)
End Function

4. Implement CRUD Operations

1. Add a Product:
Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
Dim productName As String = txtProductName.Text
Dim price As Decimal = Convert.ToDecimal(txtPrice.Text)
Dim quantity As Integer = Convert.ToInt32(txtQuantity.Text)

Using conn As NpgsqlConnection = GetConnection()

conn.Open()
Dim query As String = "INSERT INTO Products (ProductName, Price, Quantity) VALUES
(@ProductName, @Price, @Quantity)"
Using cmd As New NpgsqlCommand(query, conn)
cmd.Parameters.AddWithValue("@ProductName", productName)
cmd.Parameters.AddWithValue("@Price", price)
cmd.Parameters.AddWithValue("@Quantity", quantity)
cmd.ExecuteNonQuery()
End Using
End Using

MessageBox.Show("Product added successfully.")

LoadProducts()
End Sub

2. Update a Product:
Private Sub btnUpdate_Click(sender As Object, e As EventArgs) Handles btnUpdate.Click
Dim productId As Integer = Convert.ToInt32(txtProductID.Text)
Dim productName As String = txtProductName.Text
Dim price As Decimal = Convert.ToDecimal(txtPrice.Text)

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Dim quantity As Integer = Convert.ToInt32(txtQuantity.Text)

Using conn As NpgsqlConnection = GetConnection()

conn.Open()
Dim query As String = "UPDATE Products SET ProductName = @ProductName, Price = @Price,
Quantity = @Quantity WHERE ProductID = @ProductID"
Using cmd As New NpgsqlCommand(query, conn)
cmd.Parameters.AddWithValue("@ProductID", productId)
cmd.Parameters.AddWithValue("@ProductName", productName)
cmd.Parameters.AddWithValue("@Price", price)
cmd.Parameters.AddWithValue("@Quantity", quantity)
cmd.ExecuteNonQuery()
End Using
End Using

MessageBox.Show("Product updated successfully.")

LoadProducts()
End Sub

3. Delete a Product:
Private Sub btnDelete_Click(sender As Object, e As EventArgs) Handles btnDelete.Click
Dim productId As Integer = Convert.ToInt32(txtProductID.Text)

Using conn As NpgsqlConnection = GetConnection()

conn.Open()
Dim query As String = "DELETE FROM Products WHERE ProductID = @ProductID"
Using cmd As New NpgsqlCommand(query, conn)
cmd.Parameters.AddWithValue("@ProductID", productId)
cmd.ExecuteNonQuery()
End Using
End Using

MessageBox.Show("Product deleted successfully.")

LoadProducts()
End Sub

4. List All Products:

Private Sub btnListProducts_Click(sender As Object, e As EventArgs) Handles btnListProducts.Click
LoadProducts()
End Sub

Private Sub LoadProducts()

Using conn As NpgsqlConnection = GetConnection()
conn.Open()
Dim query As String = "SELECT ProductID, ProductName, Price, Quantity FROM Products"
Dim dt As New DataTable()
Using da As New NpgsqlDataAdapter(query, conn)
da.Fill(dt)
End Using
dgvProducts.DataSource = dt
End Using
End Sub

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

5. Run the Application

1. Build the Solution:

o Go to Build > Build Solution to compile your project.
2. Run the Application:
o Press F5 to start the application.
o Use the interface to add, update, delete, and list products in the PostgreSQL database.

RESULT:
Thus the above SQL-normalization was implemented and executed successfully.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

EX NO:8 . Database design using EER-to-ODB mapping / UML class diagrams

Aim:
To Design a database using EER or UML class diagrams and map it to an Object-Relational Database (ODB) using
pgAdmin’s ER tools and third-party diagramming tools.

Procedure and Steps:

1. Designing the Conceptual Model (EER/UML Diagrams):

Before using pgAdmin for implementation, you should first create your conceptual model.

1. Identify Entities and Relationships:

o Start by identifying the entities, attributes, relationships, and constraints based on your system’s
requirements.
o Example entities for a Library Management System: Book, Member, Loan.
2. Use a Third-Party Tool to Create EER/UML Diagrams:
o Tools like dbdiagram.io, Lucidchart, Draw.io, and pgModeler allow you to design detailed
EER/UML class diagrams.
3. Map Entities and Relationships:
o In EER diagrams:
 Entities map to tables.
 Attributes map to columns.
 Relationships map to foreign keys.
o In UML class diagrams:
 Classes map to tables.
 Attributes map to table columns.
 Associations and generalizations map to foreign keys and inheritance structures.
4. Export the SQL Script (Optional):
o Once your design is complete, you can export the ER/UML diagram as an SQL script, ready for
import into pgAdmin.

1. Requirement Analysis:

In a Student Management System, the entities, attributes, and relationships might include:

 Entities (Classes): Student, Course, Enrollment, Instructor.

 Attributes:
o Student: StudentID, Name, Email, DateOfBirth.
o Course: CourseID, CourseName, Credits.
o Instructor: InstructorID, Name, Department.
o Enrollment: EnrollmentID, Grade.
 Relationships:
o A Student enrolls in many Courses (many-to-many relationship).
o An Instructor teaches many Courses (one-to-many relationship).

2. Conceptual Model Design (EER or UML Diagram):

Before implementing the schema in pgAdmin, design the EER/UML diagram:

EER Diagram:

 Entities:

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

o Student, Course, Instructor, Enrollment.

 Relationships:
o Enrollment (between Student and Course): A many-to-many relationship that breaks down into
two one-to-many relationships using a join table (Enrollment).
o Instructor-Course Relationship: A one-to-many relationship where an instructor can teach
multiple courses.

UML Class Diagram:

 Classes and Associations:

o Student, Course, Instructor, Enrollment.
o Associations represent relationships, like a student "enrolling in" a course and an instructor
"teaching" a course.

UML Diagram Example:

 Class: Student (attributes: StudentID, Name, Email, DateOfBirth)

 Class: Course (attributes: CourseID, CourseName, Credits)
 Class: Instructor (attributes: InstructorID, Name, Department)
 Class: Enrollment (attributes: EnrollmentID, Grade, with references to StudentID and CourseID)

3. Implementation in pgAdmin (ERD Tool):

Here’s how to map this conceptual model into a physical schema using pgAdmin:

1. Access pgAdmin and Create the Database:

 Open pgAdmin and connect to your PostgreSQL instance.

 Create a new database for your Student Management System.

2. Create Tables Using SQL or the ERD Tool:

If your pgAdmin version supports the ERD Tool, follow these steps:

1. Add Entities (Tables):

o In the ERD Tool, create tables for Student, Course, Instructor, and Enrollment.
2. Define Relationships:
o Set up relationships using foreign keys:
 StudentID in Enrollment references Student.
 CourseID in Enrollment references Course.
 InstructorID in Course references Instructor.
3. Generate the SQL Script:
o The ERD Tool can generate the corresponding SQL script to create these tables and relationships.

2. Implementing the Model in pgAdmin (ERD Tools):

pgAdmin does offer basic ERD functionalities through its ERD Tool (available in some versions, primarily
pgAdmin 4). Here’s how to use it:

1. Open pgAdmin and Connect to Your Database:

o Launch pgAdmin and connect to your PostgreSQL instance.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

o Create a new database if necessary.

2. Access the ERD Tool:
o Go to Tools > ERD Tool in pgAdmin (if available).
o The ERD Tool allows you to visually create tables, define relationships, and manage your schema.
3. Create Entities (Tables):
o In the ERD Tool, add entities (tables) by specifying their names and defining their attributes
(columns).
o Define primary keys for each table.
4. Define Relationships:
o Use the ERD Tool to establish relationships between entities.
o You can create one-to-many, many-to-many, or one-to-one relationships by setting foreign key
constraints.
5. Auto-Generate SQL Schema:
o After designing your ERD, you can generate the SQL code that represents your schema.
o This SQL code can be directly executed in the Query Tool within pgAdmin to create the tables.

3. Manual Schema Design in pgAdmin (Without ERD Tool):

If your version of pgAdmin does not support the ERD tool, you can still design and implement your schema
manually using SQL scripts:

1. Use the Query Tool to Create Tables and Relationships:

o Write SQL statements to define your tables and relationships
CREATE TABLE Book (
BookID SERIAL PRIMARY KEY,
Title VARCHAR(255),
ISBN VARCHAR(13),
Author VARCHAR(255)
);

CREATE TABLE Member (

MemberID SERIAL PRIMARY KEY,
Name VARCHAR(255)
);

CREATE TABLE Loan (

LoanID SERIAL PRIMARY KEY,
BookID INT REFERENCES Book(BookID),
MemberID INT REFERENCES Member(MemberID),
DateIssued DATE,
DueDate DATE
);
1. Add Constraints and Indexes:
o Ensure that appropriate constraints (like UNIQUE, NOT NULL, CHECK) and indexes are applied
for optimized queries and data integrity.
2. Visualize Relationships in the Object Browser:
o After creating your schema, you can explore the relationships and structure using the Object
Browser in pgAdmin.

4. Testing and Refinement:

1. Populate Tables with Sample Data:

o Insert sample data using SQL INSERT statements to validate the integrity of your relationships.
2. Run Queries to Test Relationships:
o Use SQL queries to test joins and relationships between tables:

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

SELECT Member.Name, Book.Title, Loan.DateIssued

FROM Loan
JOIN Member ON Loan.MemberID = Member.MemberID
JOIN Book ON Loan.BookID = Book.BookID;

RESULT:
Thus the above sql was implemented and executed successfully.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

EX NO:9 Object features of SQL-UDTs and sub-types, Tables using UDTs, Inheritance, Method definition

Aim:

To implement Object-Oriented features like User-Defined Types (UDTs), sub-types, inheritance, and methods in
PostgreSQL using pgAdmin, you’ll need to take advantage of PostgreSQL's support for Object-Relational concepts.

2. Creating and Using User-Defined Types (UDTs):

Step 1: Define a UDT in PostgreSQL:

You can define a UDT using the CREATE TYPE statement:

CREATE TYPE address AS (

street VARCHAR(100),

city VARCHAR(50),

state CHAR(2),

zipcode CHAR(5)

);

SELECT * FROM pg_type WHERE typname = 'address';

In this example, the address UDT is composed of multiple attributes.

Step 2: Use UDTs in Tables:

Once you have defined a UDT, you can use it as a column type in your tables:

CREATE TABLE student (

student_id SERIAL PRIMARY KEY,

name VARCHAR(100),

contact_address address

);

In this table, the contact_address column uses the custom address type.
3. Creating Sub-Types and Type Inheritance:
You can define sub-types that inherit from a base UDT. However, PostgreSQL supports type inheritance more
effectively at the table level than at the type level.
Table Inheritance Example:
CREATE TABLE person (
person_id SERIAL PRIMARY KEY,

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

name VARCHAR(100),
date_of_birth DATE
);

CREATE TABLE student (

student_id SERIAL PRIMARY KEY,
major VARCHAR(100)
) INHERITS (person);

CREATE TABLE instructor (

instructor_id SERIAL PRIMARY KEY,
department VARCHAR(100)
) INHERITS (person);
In this example:
 student and instructor tables inherit all columns from the person table.
 Both sub-tables can have their own additional columns.
Benefits of Table Inheritance:
 Inherited columns are shared among all child tables.
 Queries can be run on the parent table to access data from all child tables:
SELECT * FROM person;
4. Defining Methods (Functions) for UDTs:
PostgreSQL allows defining methods (functions) that can operate on UDTs or tables.
Creating a Function:
-- 3. Create a Method (Function) to Format Address
CREATE OR REPLACE FUNCTION get_full_address(address address) RETURNS TEXT AS $$
BEGIN
RETURN CONCAT(address.street, ', ', address.city, ', ', address.state, ' ', address.zipcode);
END;
$$ LANGUAGE plpgsql;
This function takes an address UDT and returns a formatted full address.

-- 4. Insert Data and Test

INSERT INTO student (name, contact_address)
VALUES ('Alice Smith', ROW('123 Main St', 'Somewhere', 'CA', '90210')::address);
SELECT name, get_full_address(contact_address) FROM student;
Using the Function in Queries:
SELECT name, get_full_address(contact_address)
FROM student;
5. Using pgAdmin to Implement UDTs, Inheritance, and Methods:
1. Define UDTs and Tables:
o Open the Query Tool in pgAdmin.
o Execute SQL scripts to define UDTs, tables, and relationships.
2. Manage and Visualize the Schema:
o Use the Object Browser to see the UDTs under the Types section.
o View table inheritance relationships under the Tables section.
3. Create and Run Functions:
o Define custom functions using the Query Tool.
o Run queries that utilize these functions to interact with your UDTs and tables.
4. Testing and Refinement:
o Insert data into inherited tables and test if parent-child relationships are correctly maintained.
o Query parent tables to see how inheritance works in practice.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

RESULT:
Thus the above SQL was implemented and executed successfully.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

EX NO:10 Querying the Object-relational database using Objet Query language

Aim:
To Design a database using Querying the Object-relational database using Objet Query language.

Objective:

1. Understanding Object-Relational Concepts in PostgreSQL:

PostgreSQL supports the following object-oriented features:

 User-Defined Types (UDTs): Composite types that act as classes.

 Table Inheritance: Parent-child relationships where child tables inherit columns and constraints from
parent tables.
 Functions as Methods: Custom functions that operate on types or tables.
Procedure:

Setting Up an Object-Relational Schema:

Let’s use a simple example of a Student Management System that involves person, student, and instructor entities.

Step 1: Create the Base Type and Tables:

Define a composite type and use table inheritance:

SQL Query:

-- Step 1: Create a User-Defined Type (Composite Type)

CREATE TYPE address AS (
street VARCHAR(100),
city VARCHAR(50),
state CHAR(2),
zipcode CHAR(5)
);

-- Step 2: Create a Base Table (Parent Table)

CREATE TABLE person (
person_id SERIAL PRIMARY KEY,
name VARCHAR(100),
date_of_birth DATE,
address address
);

-- Step 3: Create Child Tables (Inheriting from Person)

CREATE TABLE student (
student_id SERIAL PRIMARY KEY,
major VARCHAR(100)
) INHERITS (person);

CREATE TABLE instructor (

instructor_id SERIAL PRIMARY KEY,
department VARCHAR(100)
) INHERITS (person);

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Step 2: Insert Data into the Inherited Tables:

Add some sample data:

INSERT INTO student (name, date_of_birth, address, major)

VALUES

('Alice Smith', '2000-01-15', ROW('123 Main St', 'Somewhere', 'CA', '90210')::address, 'Computer Science'),

('Bob Johnson', '1999-05-23', ROW('456 Elm St', 'Anywhere', 'NY', '10001')::address, 'Mathematics');

INSERT INTO instructor (name, date_of_birth, address, department)

VALUES

('Dr. Jane Doe', '1975-11-01', ROW('789 Maple Ave', 'Elsewhere', 'TX', '75001')::address, 'Physics'),

('Dr. John Roe', '1980-02-14', ROW('321 Oak St', 'Nowhere', 'FL', '33101')::address, 'Chemistry');

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

3. Querying the Object-Relational Database:

You can now use SQL queries to treat your tables and types in an object-relational manner.

Query 1: Query All Persons (Including Students and Instructors):

Since student and instructor tables inherit from person, you can query the person table to retrieve all persons:
SELECT person_id, name, date_of_birth, (address).street, (address).city, (address).state, (address).zipcode
FROM person;

Here, (address).field allows you to access fields within the composite type address.

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Query 2: Query Specific Types (Students Only):

If you only want to retrieve students:

SELECT student_id, name, major, (address).street, (address).city, (address).state, (address).zipcode

FROM student;

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

Query 3: Use Functions as Methods:

Let’s define a function that behaves like a method in an object-oriented environment:

CREATE OR REPLACE FUNCTION get_person_info(p person) RETURNS TEXT AS $$
BEGIN
RETURN CONCAT('Name: ', p.name, ', DOB: ', p.date_of_birth, ', Address: ',
(p.address).street, ', ', (p.address).city, ', ', (p.address).state, ' ', (p.address).zipcode);
END;
$$ LANGUAGE plpgsql;

You can use this function to retrieve formatted information about any person:

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

SELECT get_person_info(person) FROM person;

Query 4: Query Across Inherited Tables:

You can run more complex queries that involve both student and instructor tables:
SELECT name, major, department
FROM student
JOIN instructor ON student.person_id = instructor.person_id;

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

4. Advanced Queries with Table Inheritance:

PostgreSQL’s inheritance mechanism allows querying both parent and child tables, offering flexibility:

 Query a Parent Table and Include All Inherited Rows:

SELECT * FROM ONLY person; -- Fetches rows from 'person' only, excluding child tables.

Query Including Child Tables:

SELECT * FROM person; -- Fetches rows from 'person' and all inherited tables (student, instructor).

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

5. Working with Object-Oriented Features in pgAdmin:

1. Use the Query Tool in pgAdmin to run the above SQL queries.
2. Visualize the Schema using the ERD Tool (if available) to understand the relationships between tables.
3. Manage UDTs and Functions under the Types and Functions sections in the Object Browser.

When querying multiple tables with potentially overlapping column names, use table aliases to distinguish them:

SELECT s.name AS student_name, s.major, i.name AS instructor_name, i.department

FROM student s

JOIN instructor i ON s.person_id = i.person_id;

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

3. Handling Column Ambiguity:

When columns have the same name in multiple tables, always qualify them with the table name or alias to avoid
ambiguity:

SELECT s.name, s.major, i.department

FROM student s

JOIN instructor i ON s.person_id = i.person_id;

AD3381_DDM Lab
4931_Grace College of Engineering,Thoothukudi

RESULT:
Thus the above sql was implemented and executed successfully.

AD3381_DDM Lab