Database Design Concepts Report: Case Study Analysis and Design

This report provides a comprehensive analysis of database design concepts, focusing on the issues and applications of database management systems within organizational environments. It critically evaluates the features and advantages of DBMS, including concurrent use, structured data, data integrity, and atomic transactions. The report then delves into database development methodology, entity-relationship modeling, and normalization, using a case study to illustrate these concepts. It covers the design of tables, sample queries, form design, and an evaluation of the design, including support documentation. The report emphasizes the importance of database design in ensuring efficient data management, security, and data integrity, offering insights into the practical application of these concepts.

Database Design Concepts 1
Database Design Concepts
Student's Name:
Instructor's Name:
Date:

Database Design Concepts 2
Contents
Task 1..........................................................................................................................................................3
1.1- Analyze the key issues and application of databases within organizational environments [1]....3
Issues with database management systems........................................................................................3
Applications for database management systems....................................................................................4
Sharing Information.............................................................................................................................4
Eliminating Duplicates.........................................................................................................................5
Ensure Security....................................................................................................................................5
Backups...............................................................................................................................................5
1.2- Critically evaluate the features and advantages of database management systems. [1][2]........6
Concurrent Use....................................................................................................................................6
Structured & Described Data...............................................................................................................6
Here is a simple example of how data can be structured into tables:.................................................7
Clean Layers.........................................................................................................................................7
Data Integrity.......................................................................................................................................7
Atomic Transactions............................................................................................................................8
Data Persistence..................................................................................................................................8
Data Views...........................................................................................................................................8
Task 2..........................................................................................................................................................9
2.1- Analyze a database developmental methodology for the given case study. [3]...............................9
Database development involves three important stages....................................................................9
2.2-Discuss entity-relationship modelling and normalization for the given case study. [1][2][3][4]......13
Entity Relationship Diagram..............................................................................................................14
Normalization....................................................................................................................................15
Task 3........................................................................................................................................................17
Design of Tables.....................................................................................................................................17
Sample queries to extract data from the system...................................................................................22
Form Design...........................................................................................................................................23
Evaluation of Design..............................................................................................................................28
Support Documentation........................................................................................................................29
References.................................................................................................................................................31

Database Design Concepts 3
Task 1
1.1- Analyze the key issues and application of databases within organizational
environments [1]
Issues with database management systems
Many organizations face serious issues with respect to data management. They find it difficult
to manage huge volumes of data efficiently. To be more precise, it is quite difficult for
organizations to handle multiple tasks at ease. When it comes to data management, serious
hassles are seen with data organization, storage and maintenance. Some companies struggle
because data serves as the backbone of their operations. They spend loads of money on
business operations that take care of mission critical data.
Without the right kind of data management systems, organizations and businesses are not able
to generate upright results. They find it hard to synchronize and manipulate data productively.
For example, businesses are unable to gather data from various systems like project
management, student information, list of library books, payroll and inventory. These are
scenarios with versatile business operations cannot be carried forward.
Without proper data management methods, organizations tend to perform tasks manually. This
means, tasks require more time and effort to be completed. In fact, data cannot be structured
or categorized to suit the needs of an organization or company.
In places where database management is done manually, the following issues are seen:
1) It is difficult to maintain the integrity of information
2) It is difficult to share data efficiently.

Database Design Concepts 4
3) It is difficult to judge the accuracy and validity of information.
4) It is not possible to reuse information effectively.
5) It is difficult to maintain data quality.
6) It is difficult for upper management to track the overall performance of their business.
Indeed, it needs lots of organization and structure to ensure sound business
performance.
In very simple terms, both big and small organizations face issues in connecting five major
components. This includes people, data, procedures, software and hardware. When these
components are not in sync, it can be difficult to focus on better and revenue generating
aspects of a business. That is where database management systems come into the picture.
Applications for database management systems
There are plenty of applications for database management systems in an organization. First of
all, organizations can use database management systems to store huge volumes of information.
It is essential for companies to focus on data. Customers can be attracted only with the right
kind of information. Data is valuable for organizations of all sizes. It has to be handled precisely
and with lots of care. Information present in a company’s database should be flawless.
Sharing Information
Database management systems prove to be useful when you wish to share information. Based
on user privileges, you can share information. Even if your business is located in different
countries, you don’t have to worry about data sharing. Multiple users can access data
simulataneously from different business spots. This is a unique feature that sets relational

Database Design Concepts 5
database management systems apart! You will have quick access to all necessary information.
With right data at the right time, you can offer better customer service. This is a way of
facilitating better work.
Eliminating Duplicates
The talk about database management systems will remain incomplete without the elimination
of duplications. Database management systems should be used in places where data
duplication has to be eliminated. DBMS centralizes information. Centralization removes the
need for duplicate information. This is a common problem faced by many business owners. As a
company adopts to DBMS systems, they don’t have to be concerned about duplicates.
Ensure Security
Another important application for database management system is to ensure security. An
organization is bound to have different levels. Employees at each level will have unique
privileges and requirements. Database management systems can be used to handle these
requirements and privileges. You can decide and assign access to users based on their role. This
will improve confidentiality and security of data. In fact, it is very easy to maintain employee
data too. That is because everything can be digitalized, protected and accessed from anywhere.
Backups
Database management systems can be backed up easily. This is another critical reason to use
DBMS. Organizations have the freedom to store data with “availability” in mind. That way, the
organization will never lack or lose information. Even when hardware devices fail, they will have
a way of regaining important data.

Database Design Concepts 6
On the whole, database management systems are necessary to support all internal operations
of an organization. When data has to be used and shared safely, database management
systems prove to be handy. An efficient database can handle specialized information easily. It is
required to ensure and improve business operations.
1.2- Critically evaluate the features and advantages of database management systems. [1]
[2]
Database management systems offer a unique set of advantages. These benefits help
organizations like Shoengalleric with easy data management and smooth business operation.
Concurrent Use
Concurrent use is an important benefit in using database systems. For organizations like
Shoengalleric, concurrent use improves the economy of the entire system. Database storage
and capture will not be redundant. This means, the system can be operated from a central
location. All control will be handled centrally. This is when data manipulation becomes efficient.
A very good example of concurrent data use would be:
Imagine a travel database of a huge transport agency. Employees are bound to be
located in different branches. They will access the database concurrently for gathering and
booking journeys. Each travel agent should be able to access and work on the database without
any hassles. When they make a booking, cancelation or an update, everyone else should be able
to see the changes.

Database Design Concepts 7
Structured & Described Data
Structured and described data is the fundamental benefit of database management systems.
Every data column in your database will have a complete description and definition.
Additionally, you can control the way data is related to one another. Data in DBMS systems are
divided systematically and are linked carefully. There is a way to store and access data from the
DBMS.
Here is a simple example of how data can be structured into tables:
Id [number] Name [string] DOB [date] City [string]
1 Tom 12/12/2012 New Town
2 Jane 01/01/2013 Grande Road
Clean Layers
Efficient database management systems separate applications and data. The application
doesn’t have to worry about physical data storage. It doesn’t have to be concerned about data
format, storage, encoding or anything that is related to the physical part of a system. Most
companies like Shoengalleric can rely on communication languages like SQL to interact and
process data. Because data and applications are neatly separated, internal reorganization and
improvements become easy. The changes will have no influence on the application software.

Database Design Concepts 8
Data Integrity
DBMS became famous for data integrity. This is a byword for qualities like reliability and quality.
In the broader sense, integrity ensures the protection of data from unauthorized usage. Any
unauthorized changes will not be allowed too! Data in systems is a reflection of real world facts.
Logically, data has to be accurate to a good extend. Database management systems tend to
support tasks and bring only consistent data. All transactions can be controlled to maintain
consistency.
Atomic Transactions
Database operations are known as transactions. By definition, transaction is a collection of
actions that can be carried out in a database to ensure consistency. One transaction translates
data from one particular state to another consistent state. All transactions in a database should
be atomic. That means, you will not be able to break transactions into smaller groups. Likewise,
transactions should not result in an inconsistent database. Here is an example to demonstrate
this feature and its benefits.
Image a bank transfer, where money goes from one account to another. Money has to
be debited from one account and credited into another account. This is an example of a
consistent transaction. The transaction is atomic too. If money was only debited or credited
from a single end, the transaction will result in an inconsistent state. Database management
systems perform transactions in a similar fashion.

Database Design Concepts 9
Data Persistence
Database management systems ensure data persistence too. This is a feature that makes sure
data is not destroyed unless the user has programmed or manually wants to. The overall
lifetime of data in a database management system depends on the user. It doesn’t depend on
the system. All changes that are carried out in the database should be persistent too. When a
transaction makes a change to a database, it should not crash or increase the risks in using the
system.
Data Views
Data views play an important role in database management system. A single database can have
numerous users. Each user is bound to have desires, requirements and access rights. The
individual view of data can differ from one user to another. Data view is treated as a subset of
stored information. Here is an example to demonstrate data views in a system:
A school handles data about teachers and students. Apart from personal information,
there can be administrative details too. And, the administrative details will not be shown to
everyone. Database management systems will give you the freedom to display views based on
the rights and needs of users.
Task 2
2.1- Analyze a database developmental methodology for the given case study. [3]
Database development begins with requirements gathering. To begin with, the needs of
Shoengalleric should be understood. The proposed system, necessary documents and

Database Design Concepts
10
functional needs will be gathered. By the end of this step, Shoengalleric’s requirements will be
detailed and properly established. Data requirements are very important because it pictures
the overall database. The requirements don’t have to describe how data would be processed.
But, it should identify what the data items can be, the kind of attributes involved, relationships
and possible constraints.
Database development involves three important stages. The stages are:
1) Conceptual data modeling
2) Logical database design
3) Physical database design
Conceptual data modeling
Once the requirements of Shoengalleric is clear, data analysis would happen. During this step, a
conceptual data model is developed. The ultimate aim of data analysis is to produce a
descriptive outline of data. This data should meet all user requirements. It should take care of
low level and high level data properties. For example, in Shoengalleric’s database, the
employee details, customer details and gallery details should be identified. Possible attributes
for an employee table would be id, name, address, location and contact number. Conceptual
data model provides a formal and acollective representation of what is shared between
developers and Shoengalleric. This stage of development focuses only on what is needed and
not on how to achieve it.
During conceptual data modeling, the focus is on Shoengalleric’s data. It doesn’t stress on the
eventual use of data in various processes. Or, how data is going to be showcased in

Database Design Concepts
11
Shoengalleric’s galleries. The entire model is concerned on the structure and meaning of data.
Without a proper conceptual model, it will be difficult to move to the next stage of
development.
Logical database design
The next step in developing Shoengalleric’s database would be logical design. Logical design
focuses on the development of a logical schema. It determines the kind of database system
involved. It revolves around object-oriented, relational and network systems. The relational
representation is independent of the database management system. Relational representation
serves as an input to the logical design phase. The output of logical design would be a detailed
relational specification alias logical schema. It has all the tables and constraints requested by
Shoengalleric. The tables and constraints should satisfy the organization’s data description.
During the design stage, the right tables must be created to represent the database. The tables
should control duplication, ensure flexibility and represent all constraints in the best possible
way. The tables will describe how data should be stored and manipulated in Shoengalleric’s
database management system. Many novice database designers believe that relational
databases like SQL can be used to implement the conceptual data model immediately.
However, it is wrong and rather inefficient to translate the conceptual model to SQL tables
directly. You should have a logical schema to ensure integrity, usability, completeness, flexibility
and efficiency in Shoengalleric’s database. Throughout the process, tables will be flexed! Flexing
is the process of capturing ideas simultaneously and bending into something that can serve the
purpose. The output of logical database design serves as an input for the next stage. The output
consists of a global logical data model. This includes the entity relationship diagram, supporting

Database Design Concepts
12
documents, relational schema and data dictionaries. All these sources of information are
required for the physical design process. If Shoengalleric’s logical design is efficient and reliable,
the final database management system would be spotless. It can be used for long periods of
time without any changes. That is because a good logical design can resolve performance
issues, physical considerations and meet user requirements.
Physical database design
The next stage of development is physical database design. This is where the implementation of
secondary storage is understood. During the physical design phase, all file organizations, basic
relations and indexes are identified. This phase is important because it results in efficient data
access, takes care of integrity constraints and manages all security concerns. The logical
database design of Shoengalleric focuses on “what”. On the other hand, the physical database
design of Shoengalleric focuses on the “how” part. In this phase, the designer makes important
decisions on how a database would be implemented. The final outcome would be tailored to a
specific database management system. Feedbacks from Shoengalleric ‘s logical and physical
design is required to improve performance. The physical database design model can affect the
logical data model too!
Steps in designing the physical database of Shoengalleric.
1) The global logical data model should be translated carefully. This model involves
operations, derived data and enterprise constraints.
2) The physical representation of data should be identified. This includes the selection of
offline organization, disk space needs, transaction flows and selection of indexes. This is