Database Design and Security for Gill Art Gallery Project

This project presents a database design solution for the Gill Art Gallery, addressing the need to maintain data on customers, artists, and paintings. Task 1 identifies potential security threats to the database, such as password cracking, privilege escalation, and SQL injection, and details the types of data that could be compromised in each attack. It also includes a normalization table up to 3NF. Task 2 provides an Entity Relationship diagram and a data dictionary, outlining the structure of the database with tables for customers, purchases, products, bank details, stores and stock warehouse. The design aims to provide a secure and efficient system for managing the gallery's data. Desklib offers a variety of resources and solved assignments for students seeking help with database design and related topics.

DATABASE DESIGN

TABLE OF CONTENTS
TASK 1............................................................................................................................................1
Factors because of which database might be a target for an attack.............................................1
Type of attacks which may occur................................................................................................1
Type of data that might be extracted from the system in each attack..........................................1
Normalization table of The Gill Art Gallery Scenario................................................................2
TASK 2............................................................................................................................................3
Entity Relationship diagram........................................................................................................3
Data dictionary.............................................................................................................................3
REFERENCES................................................................................................................................6

TASK 1
Factors because of which database might be a target for an attack
There are various reason or factors because of which the Gill Art Galary database might be
at attack such as:
 Attacking database would provide attackers about information of rich or upper- class
customers who buy costly painting frequently (Alwan and Younis, 2017). Not only this, this
information is useful for attackers as using this information they can easily get account
details of those customers because of which security of those customers personal and
financial details might be at risk.
 Attackers might target and attack The Gill Art Gallery database for that they can easily show
costliest painting already sold without even making a payment for it.
 Attackers might target and attack The Gill Art Gallery database for altering prices of costliest
painting to cheapest price so that they can buy it for approximately negligible price (Ma, Xu
and Liu, 2020).
 Attackers can also target database for logging in to database as admin and making
alternations to information associated with client, paintings, sales data and past sales record.
 Mostly attackers attack database in order to gain access to sensitive or confidential data or
information which is being stored within database such as credit card details of clients or
customers. To gain access of backup storage media because often in database backup is
unprotected which becomes much easier for attackers to attack as backup media consist of all
important information backup which is stored within database.
Type of attacks which may occur
There are various types of attacks that might occur on database of The Gill Art Gallery.
Some of them are:
 Password cracking: it is one o the most common attack that attackers can do on database in
fact most of the organizations do not change their default password provided by service
providers and it can be easily compromised.
 Privilege escalation: with the help of this attack attackers can provide users with more access
to files than necessary (Uwagbole, Buchanan and Fan, 2017).
 SQL injections: Due to this attack, attackers can get into database and can make false
information and store false information to database.
1

Type of data that might be extracted from the system in each attack
There are different kinds of data that can be compromised by each type of attack identified
above:
 Password cracking: due to this attack not only database login password can be extracted but
all the other details that can be access by admin can also be accessed or extracted.
 Privilege escalation: this attack can provide access to low level employees of high- level data
such as credit card details of customers. Due to this financial information of customers can be
extracted and threat of fraud with customers and their finance can occur (Uwagbole,
Buchanan and Fan, 2017).
 SQL injections: almost all kind of data can be extracted or retried from database. But more
over fake or false information in database can be stored. This can even destroy a database
completely if it do not have proper security measures implemented.
Normalization table of The Gill Art Gallery Scenario
Unnormalized 1NF 2NF 3NF
Customer id
Customer name
Customer telephone
Customer address
Customer postal code
Artist id
Artist name
Painting id
Painting title
Paining price
Purchase id
Purchase date
Sales price
Customer id
Customer name
Customer telephone
Customer address
Customer postal code
Paining title
Sales price
Artist id
Artist name
Painting id
Painting title
Purchase id
Purchase date
Sales price
Customer id
Customer name
Customer telephone
Customer address
Customer postal code
Artist id
Artist name
Painting id
Painting title
Purchase id
Purchase date
Sales price
Customer id
Customer name
Customer telephone
Customer address
Customer postal code
Artist id
Artist name
Painting id
Painting id
Painting title
Paining price
Purchase id
Customer id
Painting id
2

Artist id
Purchase date
Sales price
TASK 2
Entity Relationship diagram
Data dictionary
Customers table
Attribute name Data type Description Requir
ed
Vali
dati
ons
Form
at
PK FK Comments
Cust id SMALLINT Primary key of
table: customer
Id
Y R/N Y R- Regular
N- New
Cust_name VARCHAR Name of
customer
Y
Cust_address VARCHAR Address of
customers
3

Cust_telephone_no INTEGER Telephone
number of
customer
Cust_dob DATE Date of birth of
customer
DD-
MM-
YY
Account_no BIGINT Foreign key of
Bank details
table: Account
number of
customer
Y Bank_details.
account_no
Purchases table
Attribute
name
Data type Descriptio
n
Require
d
Validation
s
Forma
t
P
K
FK Comment
s
Purchase id SMALLIN
T
Primary
key of
table:
purchase Id
Y Y
Product_id SMALLIN
T
Id of a
product
Y Product.product_id
Customer_i
d
SMALLIN
T
Foreign
key of
customer
table:
customer id
Y Customers.customer_i
d
Product_cos DECIMAL Cost of Y 9999.9
4

t product 9
Product table
Attribute name Data type Description Required Validations Format PK FK Comments
Product id SMALLINT Primary key
of table:
product if
Y Y
Product_name VARCHAR Name of
product
Y
Product_type VARCHAR Type of
product
Y
Product_description VARCHAR Description
of product
Product_cost DECIMAL Cost of
product
Y 9999.99
No_of_items SMALLINT Number of
items
present
Y
Bank_details table
Attribute
name
Data type Description Required Validations Format PK FK Comments
Account no BIGINT Primary key
of table:
account
number
Y Y
Bank_name VARCHAR Name of the
bank
Y
5

Sort_code SMALLINT Sorting
number of
account
Y
Stores table
Attribute
name
Data
type
Description Require
d
Validations Format PK FK Comments
Store id SMAL
LINT
Primary key
of table: id
of store
Y Y
Store_locati
on
VARC
HAR
Location of
store
Y
Product_id SMAL
LINT
Foreign key
of product
table
Y Product.pr
oduct_id
Stock_warehouse table
Attribute name Data type Description Requi-
red
Valida-
tions
Format PK FK Comments
Product_id SMALLINT Foreign key
of product
table: id of
product
Y Y Product.product_id
Product_name VARCHAR Name of
product
Y
Product_description VARCHAR Description
6

of product
Product_cost DECIMAL Cost of
product
Y 9999.99
Product_sold SMALLINT Number of
products
sold
Y
Product_available SMALLINT Number of
products
available in
store
Y
Current_status VARCHAR Current
status of
availably of
product in
store
Y A/NA A-
Available
NA-Not
Availalbe
7

REFERENCES
Books and Journals
Alwan, Z.S. and Younis, M.F., 2017. Detection and prevention of SQL injection attack: A
survey. International Journal of Computer Science and Mobile Computing. 6(8). pp.5-
17.
Ma, Y., Xu, Y. and Liu, F., 2020. Multi-Perspective Dynamic Features for Cross-Database Face
Presentation Attack Detection. IEEE Access. 8. pp.26505-26516.
Uwagbole, S.O., Buchanan, W.J. and Fan, L., 2017, May. Applied machine learning predictive
analytics to SQL injection attack detection and prevention. In 2017 IFIP/IEEE
Symposiums on Integrated Network and Service Management (IM) (pp. 1087-1090).
IEEE.
8