Database Design and Implementation for Art Gallery Management System

This assignment details a database design and implementation project for an art gallery management system. It begins with a problem domain analysis, outlining the requirements for managing artists, customers, and artworks. The project progresses through conceptual database design, including an Entity-Relationship (EER) diagram, followed by logical database design. The implementation phase involves creating tables using Oracle DBMS, creating indexes to optimize query performance, populating the database with sample data, and formulating SQL queries to retrieve and manipulate data. The SQL queries demonstrate functionality such as finding customer details based on location, retrieving artist and artwork information, counting orders, displaying order details with inner joins, retrieving customer order details with left outer joins, and retrieving artist information based on location. The document concludes with a bibliography of relevant sources.

Running head: DATABASE DESIGN FOR ART GALLERY MANAGEMENT
DATABASE DESIGN FOR ART GALLERY MANAGEMENT
Name of the Student
Name of the University
Author Note

1DATABASE DESIGN FOR ART GALLERY MANAGEMENT
Table of Contents
Task 1.1 2
Task 1.2 Conceptual Database Design 4
EER Diagram 4
Task 2.1 Logical Database Design 6
Task 2.2 Table Creation using Oracle DBMS 6
Task 2.3 Index Creation 8
Task 2.4 Data Population 10
Task 2.5 SQL Query 13
Bibliography 16

2DATABASE DESIGN FOR ART GALLERY MANAGEMENT
Task 1.1
Art Gallery Management: Art Gallery is a place where different types of painting,
photography and visual arts exhibition. Different types of art are buys and sell here. The
owner of the art gallery wanted to develop a digital system for managing all the functionality
of the art gallery where customer can view, buy and sells there arts without going to the art
gallery they can view and buy from their home. The art gallery management has three
different users, and they are Admin, Artist and Customer.
Admin can handle all the details of the customer and artist of the art gallery, and
admin can also change and modify the details of the users. The art gallery charge price for the
arts based on different parameter and type of the arts.
Artist is very important for the Art Gallery because the artist is one who sells art to
the customer through the Art Gallery and art gallery pay for that. The artist sets the price of
the painting and photographs, but sometimes the art gallery sets the final gross price of visual
arts depends on the quality and presence of the art. The Art gallery maintains relevant
information about the artists with their product details.
Customers of the art gallery can buy different types of product from the art gallery.
The art gallery maintains all the necessary details of the customer for future references like
name, address, and contact details along with the product details. The system performs the
following functionality:
 Admin can add, remove and modify any details of the customer and artist of the art
gallery. Admin of the art gallery can also add and remove the painting and other
visual arts details from the system.
 Only the Admin of the art gallery is responsible for managing all the necessary details
of the system.

3DATABASE DESIGN FOR ART GALLERY MANAGEMENT
 Artist can only sell their paintings, and other visual arts for the exhibition and art
gallery pay the respective amount to the artists.
 Customer can buy and sell their visual arts from the art gallery. Customer can also
modify their details.
 All the details of visual arts are maintained separately by the Art Gallery, and the
Administrator of the Art Gallery maintains this detail.
 Every user has to login first to perform the above task. Only the Admin can create a
new user account with appropriate details.
 Every user of the system can view the details of the arts, but only the administrator
can modify and remove the details of the product.
 Artists can sell their visual arts directly to the art gallery without searching for the
customer.
 The admin of the system maintains all the orders details with a unique id to track
every single order details whenever required.

4DATABASE DESIGN FOR ART GALLERY MANAGEMENT
Task 1.2 Conceptual Database Design
EER Diagram
Art Gallery Management has the following table and attributes:
Artists Entity: The attribute of artists entity are Artist_ID, Username, Name, Location,
Contact_Number, Email_id
Admin Entity: Attribute of Admin entity are Admin_ID, Username, Admin_Name,
Admin_Location, Contact Number, and EmailID

5DATABASE DESIGN FOR ART GALLERY MANAGEMENT
Customer Entity: Id, Username, Name, Address, Contact_no and Email_Id this are the
attributes of the Customer entity
Visual Art Entity: In this entity, all the relevant information of a painting or any other visual
art are stored, and the attributes of this entity are Art_ID, Art_title, Art_year and Artist_ID.
Order Entity: Attributes of order entity are Order_ID, Customer_ID, Art_ID and amount
The above entity has in one to one and one to many relations between all the tables of the Art
Gallery Management.

6DATABASE DESIGN FOR ART GALLERY MANAGEMENT
Task 2.1 Logical Database Design
Task 2.2 Table Creation using Oracle DBMS
1. DDL for Art Gallery Table: CREATE TABLE P2544059ARTGALLERY (ID INT,
NAME VARCHAR2(255), LOCATION VARCHAR2(255), CONTACT_NO
INT,CONSTRAINT “ART_GALLERY_PK” PRIMARY KEY(“ID”) ENABLE )
2. DDL for LOGIN Table: CREATE TABLE "P2544059LOGIN"
("USERNAME" VARCHAR2(255), "LOGIN_ID" VARCHAR2(255),
"PASSWORD" VARCHAR2(255),PRIMARY KEY ("USERNAME") ENABLE)

7DATABASE DESIGN FOR ART GALLERY MANAGEMENT
3. DDL for ADMIN Table: CREATE TABLE "P2544059ADMIN" ("ADMIN_ID"
NUMBER(*,0), "USERNAME" VARCHAR2(255),"ADMIN_NAME"
VARCHAR2(255), "ADDRESS" VARCHAR2(255),"EMAIL_ID"
VARCHAR2(255), "CONTACT_NO" NUMBER(*,0),
PRIMARY KEY ("ADMIN_ID") ENABLE,
CONSTRAINT "FK_C_ADMIN" FOREIGN KEY ("USERNAME")
REFERENCES "P2544059LOGIN" ("USERNAME") ENABLE)
4. DDL for Customer Table: CREATE TABLE "P2544059CUSTOMER"
("CUSTOMER_ID" NUMBER(*,0),"USERNAME" VARCHAR2(255),
"CUSTOMER_NAME" VARCHAR2(255), "CUSTOMER_ADDRESS"
VARCHAR2(255), "EMAIL_ID" VARCHAR2(255),"CONTACT_NO"
NUMBER(*,0), PRIMARY KEY ("CUSTOMER_ID") ENABLE,
CONSTRAINT "FK_C_CUSTOMER" FOREIGN KEY ("USERNAME")
REFERENCES "P2544059LOGIN" ("USERNAME") ENABLE)
5. DDL for Artist Table: CREATE TABLE "P2544059ARTIST" ("ARTIST_ID"
NUMBER(*,0), "USERNAME" VARCHAR2(255), "NAME" VARCHAR2(255),
"ADDRESS" VARCHAR2(255), "EMAIL_ID" VARCHAR2(255),
"CONTACT_NO" NUMBER(*,0), PRIMARY KEY ("ARTIST_ID") ENABLE,
CONSTRAINT "FK_C_ARTIST" FOREIGN KEY ("USERNAME")
REFERENCES "P2544059LOGIN" ("USERNAME") ENABLE)

8DATABASE DESIGN FOR ART GALLERY MANAGEMENT
6. DDL for Art Table: CREATE TABLE "P2544059ART"("ART_ID"
VARCHAR2(4000), "ART_TITLE" VARCHAR2(4000),"YEAR"
VARCHAR2(4000), "ART_DESC" VARCHAR2(4000), "ARTISTID" NUMBER,
CONSTRAINT "ART_PK" PRIMARY KEY ("ART_ID") ENABLE)
7. DDL for Order Table: CREATE TABLE "P2544059ORDER" ("ORDER_ID"
VARCHAR2(4000), "CUSTOMER_ID" NUMBER, "ART_ID" VARCHAR2(4000),
"AMOUNT" NUMBER, CONSTRAINT "P2544059ORDER_PK" PRIMARY KEY
("ORDER_ID") ENABLE)
Task 2.3 Index Creation
Indexing is a very powerful tool which helps to retrieve the data from the database
very quickly. It also helps to increase the speed of SQL query execution.
Indexes are implemented on Customer entity, Artist entity, art entity and order entity
of Art Gallery Management for fast query execution.
1. create index CUSTOMER_index on
P2544059CUSTOMER(CUSTOMER_name);
The above query creates an index on Customer_Name attribute of P2544059Customer
because customer name attribute is used in almost every query to find the name of the
customer
2. Create index Customer_index2 on P2544059Customer(Customer_Address);
Art Gallery Management store and manage all the necessary details of the customer
and address is the very important information of the customer. More than one

9DATABASE DESIGN FOR ART GALLERY MANAGEMENT
customer lives in the same location and address of the customer may be accessed
every time, so it helps to increase the query execution speed.
3. Create index artist_index on P2544059Artist(Name)
The above query creates an index on Artist_Name, and it helps to increase the query
execution. More than one artist has the same name, and this attribute is accessed more
than other attributes of the table.
4. Create index art_Index on P2544059Art(Art_Title);
The above query creates an index on Art_Title attribute of Art table. Title of the art is
very useful, and every visual art has a title. This attribute accessed more frequently
than other attributes of the table.

10DATABASE DESIGN FOR ART GALLERY MANAGEMENT
Task 2.4 Data Population
1. P2544059ArtGallery
2. P2544059Login
3. P2544059Admin

11DATABASE DESIGN FOR ART GALLERY MANAGEMENT
4. P2544059Customer
5. P2544059Artist
6. P2544059Art

12DATABASE DESIGN FOR ART GALLERY MANAGEMENT
7. P2544059Order
Task 2.5 SQL Query
1. Find the customer name and address of the customer who lives in London and
Birmingham city.
SQL Query: Select customer_name,Customer_Address from P2544059Customer
where Customer_Address ='London' or Customer_Address='Birmingham'
Output:
2. Find the details of the Artist and their art details from the database.
SQL Query: Select a.artist_id,a.name,a.address,b.art_id,b.art_title,b.year,b.art_desc
from P2544059Artist a,P2544059Art b where a.Artist_ID=b.ArtistId;

13DATABASE DESIGN FOR ART GALLERY MANAGEMENT
Output:
3. Count number of orders of the customer who buys Art from the Art Gallery
SQL Query: select customer_id,count(order_id) from p2544059order group by
customer_id;
Output:
4. Display all the order details with visual art detail using Inner Join.
SQL Query: select o.order_id, o.customer_id, a.art_title, a.year,a.art_desc from
p2544059order o inner join p2544059art a on o.art_id=a.art_id;

14DATABASE DESIGN FOR ART GALLERY MANAGEMENT
Output:
5. Retrive Order details of each customer
SQL Query: SELECT p2544059Customer.Customer_id,
p2544059Customer.customer_name,p2544059order.order_id,
p2544059order.Art_id,p2544059order.amount FROM p2544059Customer
LEFT OUTER JOIN p2544059order ON p2544059Customer.customer_id =
p2544059order.customer_id;
Output:

15DATABASE DESIGN FOR ART GALLERY MANAGEMENT
6. Retrive all information of the artist who belongs to Birmingham city
SQL Query: select * from p2544059Artist where address='Birmingham';
Output:

16DATABASE DESIGN FOR ART GALLERY MANAGEMENT
Bibliography
Alnawaj'ha, F.Y., 2016. The Performance Of Inner Join Types in SQL. International Journal
of Advanced Studies in Computers, Science and Engineering, 5(11), p.164.
Barbas, P.M., Duffy, P.J., Pan, L. and Tilson, D.M., International Business Machines Corp,
2019. Outer join optimizations in database management systems. U.S. Patent Application
10/198,472.
Cole, R.L., Chen, Y., McKenna, W.J., Perfilov, S., Sinha, A. and Szedenits, E., Paraccel
LLC, 2015. Optimizing database queries using subquery composition. U.S. Patent 9,183,254.
Konik, R.P., Mittelstadt, R.A., Muras, B.R. and Theuer, M.W., International Business
Machines Corp, 2016. Managing an index of a table of a database. U.S. Patent 9,460,138.
Ruifeng, G.U.A.N. and Yang, Q., Sybase Inc, 2017. Method and system for data definition
language (DDL) replication. U.S. Patent 9,582,558.