Database Design and Application Report - Computing HND - 2017

This report provides a comprehensive overview of Database Management Systems (DBMS), focusing on their application within the context of Shoengalleric Art Gallery. The report begins with an introduction to DBMS, highlighting its key issues and various applications across industries such as telecom, banking, education, and online shopping. It then delves into the features and advantages of DBMS, emphasizing its role in data management, security, and cost-effectiveness. Task 2 explores database design techniques, including the database developmental methodology, SDLC, and the waterfall model, as well as entity-relationship modeling and normalization. Task 3 focuses on the database development cycle, the creation of a fully functional database, and methods to improve the effectiveness of the database solution. The report also discusses supporting user and technical documentation. The content includes detailed discussions on data distribution, replication, transaction models, query processing, caching, fault tolerance, and location-based services, alongside key issues faced by businesses using DBMS. The report concludes with a discussion of the benefits and challenges of implementing a database system, with specific examples relevant to Shoengalleric Art Gallery, aiming to improve its operations and online presence.

DATABASE
MANAGEMENT
SYSTEMS

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
TASK 1 DATABASES AND DATA MANAGEMENT SYSTEMS.............................................1
1.1 Key issues and Application of databases..........................................................................1
1.2 Features and Advantages of DBMS.................................................................................4
TASK 2 DATABASE DESIGN TECHNIQUES............................................................................5
2.1 Database Developmental Methodology...........................................................................5
2.2 Entity- Relationship Modelling and Normalization.........................................................8
TASK 3 DATABASE DESIGN....................................................................................................10
Overview..............................................................................................................................10
3.1 Database Development Cycle.........................................................................................10
3.2 Fully Functional Database..............................................................................................17
3.3 Effectiveness of the Database Solution and improvement methods..............................32
3.4 Supporting user and Technical Documentation.............................................................33
CONCLUSION..............................................................................................................................35
REFERENCES..............................................................................................................................32

ILLUSTRATION INDEX
Illustration 1: waterfall model..........................................................................................................6
Illustration 2: Database life-cycle....................................................................................................7

INTRODUCTION
A database management system also called as DBMS is known as the first manufactured
database followed by its management. DBMS is an internal system software. There is a
systematic way by which users can create, edit, update or delete the data. DBMS provides them
this way. This system can be divided into four parts i.e. the Users, DBMS, Database and the
Database application. The users can be of many type like administrators, developers and the end
users. DBMS application can be personal, regulatory, operational and internal as well (Chen and
Hu, 2011). Database applications are being used almost everywhere, some very common
examples are computerised system in libraries, reservations at airport, ATMs etc. Database is
the overall contribution of the logical data. Some functions are that it supplies data
independence, gives recovery services and various beneficial services. It provides minimum
duplicates of data and it is very easy to recover the data in DBMS but is quite costly to use and it
deals with a lot of complexity. Shoengallric Art Gallery is an art gallery, established in 2010 in
China. This report explains how database management helps the art gallery to recover fast.
TASK 1 DATABASES AND DATA MANAGEMENT SYSTEMS
1.1 Key issues and Application of databases
There are a various number of database applications worldwide being used by the people.
Some of the applications are being discussed below: Telecom: All the calls made, the overall network usage, details of the customer, these all
are being tracked by a database (Chen and Hu, 2011). It is very difficult to maintain the
large amount of data without the database system. In database system, data keeps
updating every millisecond. Industry: No matter whether it is a warehouse, retailer centre, distributor centre or any
manufacturing unit, databases are required to keep the record for every entry of in as well
as of out. A very common example of this can be online shopping sites, when the product
is being out for the delivery, the buyer can track where it is and when it will be delivered.
Although it gives an approx., but most of the times, it gives the appropriate schedule. Banking System: Daily debit and credit transactions, storing the customer's information,
Chalan details, new account openers etc. all the record of these activities are maintained
by the database management system.
1

 Education Sector: Every schools and colleges use the database management systems for
storing the student's details, teacher's details, course details, exams details etc. All the
details of students, exams, teachers, course etc. constitutes lot much amount of data
which is impossible for any person to keep in mind for a long time (Chen and Hu, 2011).
So, a database management system makes a difference here by storing this much large
amount of data.
 Online shopping: All the online websites as Flipkart, Amazon, Jabong etc. keeps a
record of the card details one saves while billing online. If the information like address
etc. are stored once, the next time it automatically shows the previous details. This is
called the database management system.
The managers at Shoengalleric Art Gallery can also use some of these applications for the
betterment of the business. They can use database system for the online demand of their art
pieces (Voulodimos and et.al, 2010). They can open an online portal where they can send their
antiques online. Their customers will be able to track the product from time to time and this can
go good for their business as people from other states will also be able to buy that online and can
time to time track the product and also can cancel it if want to. Also, the Art Gallery can keep
record of their employees, staff, customers etc. Usage of the database management system can
help Shoengalleric Art Gallery to grow faster.
Key issues of Databases
There are various issues also while dealing with databases. Some key issues and
challenges are discussed as under: Data distribution issues: This deals with availability of data is much higher than the cost
of remote access. It is lower than the availability of data. It provides more proper access
to data as well as higher security. Replication Issues: The more we increase the number of duplicates, the more increase in
costs for updates and signalling takes place. There are mobile hosts which can move
anywhere and anytime. Transactional Models: The ACID i.e. atomic, consistent, isolated, durable properties are
being satisfied by all the transactions (Chen and Hu, 2011). Concurrent transactions that
are performed should be serialised. Mobile transactions are a part of distributed
transactions in which some transactions are performed in fixed hosts while others in
2

mobile computers. When the mobile computers are disconnected, ACID properties are
hard to impose on them. Query processing: When mobility is being considered, Query processing gets affected. It
is very common as nowadays, various latest apps have been developed as we can easily
search e.g. “gas stations near me”, theatres near me etc. on any search engine. Even on
Google also and it replies back and that too within a few milliseconds. Caching: These techniques for query processing dramatically reduces communication
costs. It is quite difficult to apply these techniques in mobile context. Fault Tolerance: Even with some internal faults, the capability of performing function
efficiently, is called Fault tolerance. Faults can be either transient or permanent. There
are some features by which mobile computing environment can be characterised by and
these are limited availability of resources, high flexibility, low bandwidth and repetition
in disconnection.
 Location based services: To find out the location of some mobile users is not as easy. It
is quite challenging. There are some techniques such as GPS etc. but there are some other
methods also which include Cell of Origin, Time of arrival and Angle of arrival and
enhanced and observed time difference. GPS is also a very developed technology. GPS
stands for Global Positioning System (Chen and Hu, 2011). GPS can also be accessed in
mobile phones as if someone feels troubled in some weird place and does not know the
location, or he/she has to travel somewhere else. GPS can be operated in those phones in
which GPS receiver chip has been integrated. There are some issues also with it e.g. it
somehow interferes the user’s privacy as if someone don't want to let other knows their
location, but for that they can turn off their GPS also.
As obvious, the database management system has some issues, so Shoengalleric Art Gallery
also have to bear some, if they are using database management system. It can affect them in a
way that if they do not enable their location services on the internet, people won't be able to get
the exact location when they search for it (Voulodimos and et.al, 2010). Also, if they will not
register themselves on the internet, google or any other website will be unable to deliver the
results about it which becomes as a barrier for their popularity in the market. The staff there
should know how to deal with Fault tolerance because many times some faults have been done
by any staff member or any member of the gallery and that fault can be transient or it can be
3

permanent also. So, they should know the techniques to deal with it so that it does not affect the
name of the Art Gallery.
1.2 Features and Advantages of DBMS
Some well-known features of DBMS are that it manages the data effectively, it is user
friendly and it provides full security of the shared as well as the stored data. As the
communication and sharing of information in the Shoengalleric Art Gallery is poor, database
management system can help them to come out of it as it provides full security of the shared data
as well as the data in their storage (Chen and Hu, 2011). This will improve conditions in
Shoengalleric Art gallery as the employees there can communicate well about various issues and
they will be sure of the security of the data as well. DBMS also provides information that is free
of errors. In computers, usually users generate backup for their data on a regularly basis but it
can be very hectic and time consuming if the data is large. So, DBMS has created recovery sub
systems by which data backup and restore can automatically happen when required. If someday
this problem happens at Shoengalleric Art Gallery, the gallery will face no sort of loss because
DBMS can easily restore the data (Färber and Dees, 2012).
Another important fact that DBMS provides is the restriction of the unauthorized access.
It becomes a very important factor for all the organisations as no one can steal the information by
this means and this refers to high security. The developing cost as well as the maintenance is low
in DBMS. Although the initial cost of setting of database management system is high, but the
cost of development and maintenance is much lower than the initialization. The manager of
Shoengalleric Art Gallery should set up the database system for their gallery so that they will not
go into further losses and by means of DBMS, they can recover well. Only the initial cost is
quite high, after that the development, fixing and maintaining is not as high as the initial so it
will not be that tough for the gallery. It is just one-time fixation (Voulodimos and et.al, 2010).
The gallery will automatically recover soon and will become a boom in the market. Some other
characteristics of DBMS are:1. Real World Entity: To design the architecture, real world entities are being used by the
user. Taking an example, in a school database, students are entities and their age act as
attributes.
4

2. Relation based Tables: The database system permits the entities and relations to form
tables. It becomes very easy for the user to understand the architecture just by looking at
the tables.3. Separation of data and application: Data is quite different from its database system.
This is an entity which is always active whereas data is always passive (Färber and Dees,
2012). The data about data is called as Metadata. It is also stored by DBMS because it
eases its process.4. Less redundancy: When any of the attributes is having redundancy, it splits its relation
i.e. it starts following normalisation. Normalisation is a method in which data redundancy
can be reduced.
5. Stability: Every relation remains stable at a stage called stability. If it is unstable, there
are some techniques which can detect it.
TASK 2 DATABASE DESIGN TECHNIQUES
2.1 Database Developmental Methodology
As in a very large firm, a huge team has coordinated groups that are focusing on their
work simultaneously on database design, the comparative database lifespan is called the
relational database system. Partitioning of all the development processes into different parts such
as phases, steps whose only forte is the development. It can be termed as Adrienne Watt. The
process of database development consists of many processes which are discussed as below:
 SDLC - Waterfall
5

Waterfall model is termed as the easiest model in the database design techniques and is
mostly used in many software processes. As in fig, the waterfall model is partitioned into a few
components (Voulodimos and et.al, 2010). The outcome of one component is the input of the
other component and similarly the process goes on till component maintenance.1. Requirement analysis - Development of all the necessities of the system takes place.2. System Design – All the necessary specifications are kept in mind so that the system
design can be prepared easily.3. Implementation – The inputs from the system design are evolved into small parts called
units and that units become cohesive in the next phase (Färber and Dees, 2012). It refers
to unit testing.4. Testing – From the implementation phase, all the evolved units are taken in a system
after their testing. Faults and failures are also being tested later on.5. Deployment – After the functioning process, the product is being placed for the
customers in the market.
6
Illustration 1: waterfall model
(Source: Färber and Dees, 2012)

6. Maintenance – If the client has some issues, patches for the product start releasing and
modified versions are also being produced. To provide the new and modified changes in
the market, maintenance is being done.
Database Life Cycle
Database Life Cycle can also be said as DBLC. It is a combination of technical and
business approach which helps in the betterment of development of the database, delivery and
the management (Voulodimos and et.al, 2010). Database Life cycle includes database making
and the resource allocation, which helps in managing the database schema and data. It also does
backup and recovery tasks. The life-cycle tasks have been performed by the database
administrators and the application developers. One can create tables, appoints primary and
foreign keys and creates views and indexes and other database entities (Färber and Dees, 2012).
It regularly takes and tests backups, safeguarding the data. Its scale up is very good as it
increases the size of database as required. To perform the maintenance tasks, it can be stopped as
well as restarted if the user wants. The user can disable the database whenever it wants to as well
as deleting databases is also easy because it is good to free up the facilities when not required.
7
Illustration 2: Database life-cycle
(Source: Lee and Lin, 2015)

 Requirements gathering: For Requirements Gathering, establishing requirement are must.
It includes consultation and the agreement along with the users. The data administrator
has a key role in this process. Understanding of requirements with the users is necessary.
 Analysis: Data Analysis starts with the data requirements and produces a conceptual data
model. The main forte of data analysis is to acquire an exact formatted description of the
data that users are comfortable with. Some examples of its properties are like in school
database system, course number, course title, roll number of students etc.
2.2 Entity- Relationship Modelling and Normalization
The graphical representation of entities and their relationships with each other is called
Entity-Relationship. Real world objects, that can be easily identified are called Entities. Taking
example of a school database, students, classes, teachers etc. are considered as the entities of the
database. Similar types of entities are collected in an entity set. By means of some properties,
entities are represented as attributes. The attributes can be simple, composite, derived, single
value attributes and multi value attributes. In an entity set, there are some keys. Super key
constitutes an attribute set that identifies an entity in an entity set. Candidate key is also a super
key that is minimal. There is an entity model relationship which is usually the systematic
analysis of defining and describing the importance to process in the business area. Business
processes are not defined under this. It only represents a graphical schema of business entity
relationship. Conceptual data model is the ER model that is on highest level but it consists the
least granular detail which has established the overall scope of everything what is to be included
in the entity relation model. Logical data model is another model which does not requires an ER
model that is conceptual. It is specially when it only has the production of distinct information
system. Physical data model is another model that can be developed from a logical ER model.
Each physical ER model must have satisfied the required details. The ER model is basically used
to design the new modifications to the relational database. The candidate keys can be more than
one in an entity set. Primary key is a type of Candidate key which uniquely identifies the set.
Normalization can be described as under :
Normalization can be termed as a method in which something can be returned back to its normal
state. It decomposes the relations into small relations. Use of normalization eliminates the
useless data. It also ensures data dependencies. Ensuring data dependencies satisfies all integrity
8

constraints. Elimination of useless data prevents insert, delete and update analomy. Various
forms of normalization are described as under :
(Source : Chen and Hu, 2011)
1. First Normal Form (1NF) – According to this, the two simultaneous rows does not
contain any repetition of data. That means every column should have a unique value. No
value in the table should be repeated. The table should be organised into rows and each
row should have a particular and unique primary key. This form has an advantage that it
helps in increasing the data redundancy.
2. Second Normal Form (2NF) – In this form, on any primary key there should not be any
partial dependency. If even any one column relies on concatenated key, then the table
fails to be in second normal form.
9
Illustration 3: Normalization