Database Design Concepts for Shoengalleric Art Gallery

This report delves into database design concepts, focusing on the application within Shoengalleric Art Gallery. It begins by identifying key issues in database management, such as data redundancy, security concerns, data integrity, and validity, and contrasts database systems with traditional file processing systems. The report then explores the features and advantages of database management systems (DBMS), including data availability, minimized redundancy, and data accuracy. It further details the database development methodology, encompassing conceptual, logical, and physical database design phases, and includes a discussion on requirement specifications, database design, and implementation. The report also covers entity-relationship (ER) modeling and normalization techniques, providing a comprehensive view of database design principles. Finally, it touches upon the database development lifecycle, functional database design, and the effectiveness of database solutions, culminating in a discussion on the technical documentation of the database.

Database Design Concepts

Table of Contents
INTRODUCTION ..........................................................................................................................1
TASK 1............................................................................................................................................1
1.1 Key issues and application of databases in organizations................................................1
1.2 Feature and advantage of database management system ................................................2
TASK 2............................................................................................................................................3
2.1 Data base development methodology .............................................................................3
2.2 Entity relation ship modeling and normalization techniques of database ......................5
M 1...................................................................................................................................................7
D 1....................................................................................................................................................8
TASK 3............................................................................................................................................9
3.1 Database development life cycle .....................................................................................9
3.2 Functional database design ...........................................................................................10
3.3 Effectiveness of database solution ................................................................................14
3.4 Technical documentation of database ..........................................................................14
CONCLUSION..............................................................................................................................15
REFERENCES .............................................................................................................................17

INTRODUCTION
Database management system is an application used to connect with user. It is also used
to interact with various other applications in data evaluation.. Database is an important and
intrinsic part for any organisation system. It is tool use by companies to store their data of
employees. information of their work and various other kind of data. Database are use to store
and extract data. DBMS is an application which is provides organisations a platform on which all
the information is managed. It allows user to access data, update data and searching of data. It
gives many links to user like programming, analysis of systems, human computer interaction and
interfacing with systems.
Present report describes about various issues and problems in database application in
context with organisations environment. Evaluation of feature and database of DBMS system in
companies has been done. Further more, development methodology of database in enterprises
have been evaluated. Entity relation ship model for the given organisation has been illustrated.
More over, database development cycle and, concept of normalisation has been explained.
Effectivenesses of DBMS and improvements required are also illustrated. Database for t
organisation has been designed. Effectiveness an further improvements has been suggested.
TASK 1
1.1 Key issues and application of databases in organisations
There are various issues discovered in database of Shoengalleric Art Gallery. Database
have many issue which can effect the proper functioning of the organisation (Abadi and et.al.,
2013). Issue like redundancy of data, interdependency, security issue, data integrity and data
validity. Following are some issues of company's database : Data Redundancy: It is the most vital issue which is found in database of the
organisation. Data redundancy is the condition in which same data is reflected in two
different places (Coronel and Morris, 2016). This means two distinct places are showing
same information which is causes repetition problem. Redundancy occurs when two or
more tables in the database holds same value. For example in art gallery shop
redundancy can occur when two customers have same name. Security Issue : Security of data is termed data stored in database should be kept safe
from unauthorised people. as Security issue occurs when organisations stores the
information of customers, financial data, HR records etc. Large amount of information
1

contained in database can be steal by hacker for illegal use. The theft of information can
causes loss of reputation of firm. Is also causes problems to customers. Data integrity : It is defined as consistency and accuracy of data. It is indicated by
alternation of data within two tables of database. In designing phase of database it is
needed that data is stored ion right table and no repetition/ missing of data is there. Data validity : It is defined as validation of data in the single database. It is a process
used to ensure that all the field within the database are showing some valid value.
Consistent value is required in the table and table should not return null value (Di and
Kerns, 2015). For example in customer database of shop amount field of table can not
contain null value.
Database system are replacement of traditional systems
In traditional system data was used to be stores and arranged in file of computers.
Processing of data was done using traditional system for file processing. These system causes the
problem of data redundancy and data inconsistency. Integration of data was also a issue. Where
as in the database system these issue of data redundancy and inconsistency are solved. Database
system provides user friendly interface and improved data integrity. It also provide security of
data. Form this it is clear that database system are replacement of traditional systems.
Application database in organisations
Databases in organisation plays an important role in storing data of company. An
organisation contains three level of functional activities. The data of all these activities within the
organisation is stored in the database. Corporate database holds data related to company,
operations and various plans. It provides effective storage of data. It allows users to put queries
and access of data. It provides a platform in which is user friendly and provides easy interaction
to users. There are various roles of database administrator which are taking back ups, recovery of
data, creation of new database, ensuring security of database and upgrading database.
1.2 Feature and advantage of database management system
There are various features of database management system that can help Shoengalleric
Art Gallery shop in managing the data. DBMS applications are used widely by all business
entities to mange the data effectively. Some benefits of database management system in the
organisation are as follows:
2

 Data availability : it is one of most useful feature of DBMS which helps in reflecting
same data of firm available to many employees at the same time. It supports in enabling
multiple access to authorized users (Dimitrieski and et.al., 2015). That data is made
available to all the users remotely. For example gallery shop can made its data available
every time to all its branches. Its employees can access data at any time using internet. Minimized redundancy : Data and information stored in which is stored in database
management system is compact and it appears only at one place. This feature help in
reducing redundancy of data. For any business for it required that data should repeat
again and again. DBMS help in reducing the cost of maintaining significant information
of business in storage devices.
 Accuracy and consistency of data : DBMS provides accuracy of data by using data
integrity feature (Embley and Thalheim, 2014.). It gives user a relevant piece of data for
use. Using database organisations can assist the integrity of their significant records of
business. It provides a platform on which changes are to be made only at one place. It
also prevent faults,bugs, and loss of data.
Features of DBMS: Consistency : Implementation of database any organisation benefits them to sue file
formats that are consistent and standard. This feature make the data manipulation and
update easy. It became easy for business venture to easily update data because there are
some rules in database which are applied to al type of data (Coronel and Morris, 2016).
Customization of business records become easier due to this feature. Data structuring : Whole data in the database is stored in the form of hierarchical
structures. Table, rows field and tuple are used to feed data. It become easier for
administrator to update and recover the data. Query languages : DBMS uses query languages to store and retrieve data. Query
language are use for data collection, searching , data sorting, modification and
manipulation . Customized database : DBMS provide various options that can be used to make
customized elements according to requirement of user.
 Metadata : Data base management system provides a library which is called metadata.
This data dictionary gives information about how data is organized within the system.
3

ACID properties:
These properties of database systems supports in safe sharing of data. ACID stands for
atomicity, consistency, isolation and durability.
Atomicity: It helps in ensuring that all the operations in transaction are reflecting in database.
Transaction which is atomic are either complete or not even started.
Consistency: This property helps in ensuring that al the data in the database is valid in the end of
transaction. In case of any error in the system change are rolled back .
Isolation: It ensure that one action in the system should be carried out only once.
Durability: This property ensures that transactions are durable upon successful completion.
Data concurrency: Meaning of data con currency is that several users can access data at one
time. It is the ability of database to permit users to perform multiple transactions.
Data portability: It is the ability of database to move, copy and transfer data from one database
to another. It explains the extent to which a data can be ported.
TASK 2
2.1 Data base development methodology
According to case study Shoengalleric is an art gallery which is situated in China.
Company has many branches sin the country due to which it is difficult for the gallery to mange
its data using paperwork methodology (Embley and Thalheim, 2014). Sharing of information is
poor due to use of old method in storing data. The organisation wants to build its database for
effective management of data. Data base will help gallery in reducing errors in storing of data.
Following are the designing methodology that should be used in making of database for the art
gallery. The methodology is divided into three parts :
Conceptual database design
In this designing method conceptual data model is made for the organisation. This model
is totally depends on implementing details like target DBMS,system programs, languages for
programming and issue of performance (Harrington, 2016).
Logical database design
In this database design construction information does not depends on particular database
management system. This type of designing method uses conceptual model to make logical
model. This model gets affected by data models like relational database model. This logical
model works as a source of data for physical model (Hernandez, 2013). It generates output in
4

form of global logical model which contains ER diagram and relational diagrams. Data
dictionary is also the part of this model. All this information is used by physical designer to make
efficient design of physical model. This would help database system developer of the
Shoengalleric art gallery in designing a database which is does not depends on particular
database management system.
Physical database structure
In this method database is implemented on secondary storage devices. This phase
explains about base relation, organisation of files and various indexes used for accomplishing
data access. This type of database design will allows database designer of Shoengalleric art
gallery to determine how to implement it.
There are various components used in database deigning methods which are as follows : Requirement specification : In requirement analysis phase two aspect are considered.
First is planning in which system strategy is considered. It also concerns about planning
of whole DDLC. Second one is system definition in which scope and limitation of the
present database is defined (Kiani and Zivari, 2014). Database designing : In this two model are given consideration to create database. First
is logical model in which complete design of database is made on paper. Second is
physical model in which logical model is use to make decision of this phase. Implementation phase : In the implementation phase conversion of data and testing of
system is done.
2.2 Entity relation ship modelling and normalization techniques of database
There are two techniques which can be used by Shoengalleric art gallery to design it
database. Techniques are as follows:
ER modelling : Entity relationship modelling is used in designing of database. This model uses
object of real work to represent relation between entities. (Shu and Cheong, 2014) Entity is
defined as an object which is different from on another. Example of entity in case can be
employee of art gallery organisation. Entity can contain values. ER model is an technique use to
understand and manage data which is independent form the real database. ER modelling have
following components :
5

 Entity : It is a thing which has independent identity. This is the base on which data is
collected. For example employee of firm is an entity. Instance entity : It is a particular subset of entity type such as particular employee of
employee entity type. Regular entity : This entity contain its own characteristic such as particular employee
will have it unique attributes (Vissers and et.al., 2016). Weak entity : This entity depends on another entity for it attributes. This type of entity
does not contain any key characteristic. For example leader and employee relationship in
which employee depends on leader for everything. Here employee is weak entity. Attributes : Attributes describes properties and detail of entity. These attributes ion er
modelling are described using oval shape (Embley and Thalheim, 2014). For example
employee attribute will be name , phone no. , address etc. Key attribute : It is an attribute which remains unique for each entity in the model. Such
as employee_id, employee_name, employee_address etc. Any key attribute which
contains two or more characteristic the it is called composite key. Simple attribute : It is a single attribute which have no further scope to be divided. For
example id of employee emp_id (Dimitrieski and et.al., 2015.).
 Composite attribute : It is an attribute which can be divide further. Such as emp_name ,
emp_first name, emp_last name.
6

(Source: Data base design concept. 2017.)
Relationship
Line which connects entities together are called relationships. For example
workers works for art gallery. Here “work for” is relationship between worker entity and firm .
Notation for relationship in ER modelling is diamond shape. Weak entities are connected by
weak relationship notation which is diamond within diamond. Relationship degrees are unary
binary and ternary (Hernandez, 2013). Cardinality of relationship is 1: 1, 1:N, M:1, M:M.
Normalisation in database
Normalisation is process in which anomalies in the database are removed for making
consistent database. Anomalies like repeated data, data duplicacy and wrong manipulation of
data are removed to ensure consistency (Vissers and et.al., 2016). In this process three step are
followed which are update anomalies, delete anomalies and insert anomalies.
7
Illustration 1: ER diagram for art gallery management system

Normalisation has 3 normal forms which are as follows : First normal form : 1NF is defined in the table definition itself. This describes that all
attribute within the relation should have atomic field. Value in atomic field are
indivisible.
 Second normal form : 2NF contains prime and non prime attribute. Prime attribute is
part of prime key and non prime is not the part of prime key (Shu and Cheong, 2014).
For example non prime has functional dependency on prime key. If A-> B contains then there
cannot be any subset of C of A in which C-> B is also true. Third normal form: A relation should in 2NF to be in 3NF. In this normal form non
prime attributes depends transitively on prime key.
 Boy codded normal form : BCNF is an extension of 3NF. It describes that no trivial
functional dependency A-> B A should be a super key.
 Summarization in table of all normal forms
Normal forms 1NF 2NF 3NF BCNF
Properties Table that
qualifies relation
with 1 NF
All non key
attribute are
dependent on
primary keys
A relation is in 3
NF if it is in 2
NF.
Relation is in
BCNF if attirbuet
is a candidate key
.
M 1
According to the case study it has been found that Shoengalleric art gallery organisation
was facing issue in marinating its data as paperwork in the firm was increasing due to use of
outdated procedures used in the office. This issue can be solved by using database design
techniques. Organisation is facing data repetition problem in old method of data storing. These
database will support company to achieve minimized redundancy of data. Database administrator
of the firm will be able to made data available to all the workers of the organisation. The data
availability feature of the database system will help company to give access of data to all the
authorized workers.
Data consistency is also an issue in the storage of data. Data consistency feature will help
organisation in providing accurate data to its users. Employees of the company can easily search
8

data in database, update and manipulate data. This would be easy for workers of art gallery by
using query languages. Data dictionary will help users to understand the structuring and
operations of database. Making customised database would also be easy for organisation.
Database development methodology will support database designer to design it make
efficient database. Appropriate requirement analysis will support designer to make effective
database which can solve current issues of company. Planning phase of requirement specification
can help in finding appropriate strategy. Using logical model and physical model approaches will
make it easy for designer. Testing is required of database to identify errors and bugs in the
proposed system. Entity relationship modelling can help in making entities in making entities in
organisation's database. Designer can use employee and customer entities to define relationships.
All the attribute of employee entity can support in making effective database of employees. This
database would contain information about each employee in each store of the gallery. Attribute
of customers would get saved in customers database. This database would contain all the data
about each customer of art gallery.
Database system can also solve the issue of record keeping of product. Manager can
easily maintain the data about products. Data bas administrator of the organisation can update,
delete and manipulate the data about products. DBA can insert the data about product if it is
available in the store. Similarly deletion would be done on unavailability of item. DBA can use
normalisation process to remove anomalies in the database. Removing errors would be easy for
company by using normalisation method which was not available in older methods.
D 1
Proposed database system has been made by database designer which has solved various
issues of company. Issue of data redundancy has been solved by using database to store data.
Shoengalleric art gallery manager is now able to make data available for its employees and user.
Authorized user can access the data remotely anytime. It has helped organisation to make data
consistency in database. Security feature of system has been supporting company to secure their
employee and customer data. Thus data security has been enhanced in the organisation. ER
diagrams has supported in making appropriate entity for each data which is to be store in the
application. Normalisation technique has been used in database to remove errors and bugs form
the system.
9

Database development life cycle has supported in require collection analysis, data
conversion and testing of data. DDLC is also helping maintenance and prototyping of data.
However, there are some problems in the current database system as some value in the table field
are repeating. On adding age data of employee in the field the system is returning null value.
Customers information is redundant in the database. Same information about one customer
reflects in two field which is causing data redundancy issue. There is some security issue also
which is allowing unauthorized user to access data of customer. In the newly designed database
there are some errors which is causing problems in deleting the obsolete data form the database.
Data validity problem is also there in the database due to which some field are returning null
values.
TASK 3
3.1 Database development life cycle
Shoengalleric art gallery's database designer can use data base development life cycle for
designing its database. DDLC is a collection of steps that should be followed in implementation
of database (Ruddigkeit, Blum and Reymond, 2013). This model uses waterfall model as basic
which consists of three assumptions:
 Development of database can be separated into specification and creation phase.
 Three schema structure can be used for differentiating all the functions associated.
 Constraints can be represented by enforcing data semantics in the database at once.
Database development life cycle is process to determine requirement and analysation of these
requirements. These requirement specification helps in designing and implementation of
database system. Steps of DDLC a re as follows:
Requirement collection
In this step designer of the database collect all the requirements form the user/ customer
of the database. It is done for understanding the system to be made (Allee and Peinhardt, 2014).
This phase of DDLC obtains the functional requirements and document of these specifications is
made. Proper document of requirements is the result of this step which is provide by users. This
document is used to confirm the specifications from customers. It should be simple, precise and
easily understandable for the designer. Document provides summary of the overall prerequisites
of the system to be made. Requirements should explain data item rather than the processes.
Constraints and attributes which should be there in database are describe in this phase.
10