Database Design Concept

DATABASE DESIGN CONCEPT 1
Database Design Concept
Student’s Name:
Instructor’s Name:
Date:

DATABASE DESIGN CONCEPT 2
Table of contents
Task 1
1.1 Analyze the key issues and application of databases within organizational
environments..........................................................................(4)
1.2 Critically evaluates the features and advantages of database management
systems..................................................................................(5)
1.1.2 Avoid Data Redundancy................................................... (5)
1.1.3 Improve Data Security...................................................... (5)
1.1.4 Better Data Approach........................................................ (6)
1.1.5 Effectiveness and Efficiency................................................. (6)
1.1.6 Program or Data independence.............................................. (6)
1.1.7 Structuring....................................................................... (7)
1.1.8 Recovery and Validation...................................................... (7)
1.1.9 Monitoring and Tuning........................................................ (7)
Task 2
2.1 Analyze a database developmental methodology for the given case study Tom &Jerry
LTD..............................................................................................(9)
2.11. Step 1 – Developing a Schema – Introduction........................... (9)
2.12 Conceptual Model................................................ ............. (9)
2.13 Logical Design................................................................ (12)
2.14 Physical Design Phase...................................................... (13)
2.2 Tools for Database Design............................................................. (14)
2.2.1Entity Relationship Data Model............................................. (14)
2.3 Normalization............................................................................ (18)
Task 3
3.1 –for the case study given above consider the user requirements and apply the database
developmental cycle to develop database solution for the Tom & Jerry LTD......... (20)
3.2 – design a fully functional database which includes tables, forms, queries, reports,
various validation and verification techniques and various interaction in forms such as
drop list , check box etc. by using a suitable database applications for the given case
study. Your database design must contain minimum of four tables linked to your case
study.............................................................................................. (21)
3.2.1Tables in Tom & Jerry sales ordering and stock control system .......... (21)
3.2.2 UML Diagram of Tables.................................................. (24)
3.2.3 Validation and Verification.................................................... (24)

DATABASE DESIGN CONCEPT 3
3.3 Evaluate the effectiveness of the database solution and suggest methods of
improvement for the Tom & Jerry LTD...................................................... (25)
References................................................................................................ (26)

DATABASE DESIGN CONCEPT 4
Task 1
1.1- Analyze the key issues and application of databases within organizational
environments
[1] Database is an integral part of the analysis and design phase of framing effective software
applications. Unfortunately, this is very challenging and critical to performance. By definition,
databases are huge collection of information in an organized and a structured manner. These
systems are designed to handle large bodies of critical data. Data in these systems cannot exist in
mere isolation. They play a crucial role in the operations performed by certain enterprises. The
quality and efficiency of a database depends on its design. This is why engineers are expected to
spend several hours framing a neat design for their databases. The database design has to meet
and eradicate enterprise issues that can else result in a broader set of problems.
Organizations without proper database management systems are bound to face issues like data
redundancy, security hassles and difficulties in retrieving data and maintenance problems. These
are critical issues that should be avoided if the organization wishes to carry out normal
functionalities in the long run. This is when database systems come into picture. In accordance
with the case study – Tom & Jerry LTD, the need for an efficient database system is quite clear.
Without the help of a database management system, the organization would find it difficult to
monitor customer order or manage stock control. This in return would force the organization to
run at a loss. Activities like stock control and sale ordering should be seamlessly integrated. Data
has to be shared across different departments. This cannot be handled manually. As the
organization begins to grow and engage in more activities, processes are likely to become
tougher. With the help of database management systems Tom and Jerry Ltd can take care of such
problems.

DATABASE DESIGN CONCEPT 5
1.2- Critically evaluates the features and advantages of database management systems.
Here are few reasons why Tom & Jerry Ltd should shift to a database management system.
1.1.2 Avoid Data Redundancy
When it comes to real time systems, numerous records are associated to data process. Data items
should not be duplicated. Data redundancy has to be avoided to ensure consistency and effective
use of storage space. It is difficult to eradicate redundancy completely, because of technical and
presentation reasons. If everything is handled manually, eradication of data duplication becomes
very tough. Only a good database can avoid all logical forms of data redundancy. Likewise, a
good database can avoid all forms of record variation. If the organization wants to handle data
flawlessly, it is of paramount importance that the system does not have several versions of the
same data. The best way to avoid data inconsistency is by removing unnecessary data duplicates;
this in turn implies the fact that all data should be stored in a common location that can be shared
between systems. This pool of data is nothing but the DBMS. Designed databases will make sure
data consistency is maintained at all levels within the organization.
1.1.3 Improve Data Security
A major reason behind the need for good database would be upgraded data security. When data
gets stored substantively, the process of imposing security strategies becomes easier. The DBMS
would make sure that only the right resources can get hold of data from the system. There are so
many security tools like passwords and user codes to ensure secure databases. In fact, operations
like deletion, addition and modification can be restricted to certain users too. Organizations like
Tom & Jerry can pull together user interfaces with various constraints on how data like invoices,
stock information and sales processing is done.

DATABASE DESIGN CONCEPT 6
1.1.4 Better Data Approach
Database systems can be used by a wide range of users. For example, customers, stock
managers, reports and owners in Tom & Jerry can use data differently in the company.
Requirements are bound to differ from one user to another. A well-designed database can offer
diverse technical functionalities. It can provide many types of interfaces like application program
interface, query languages and a comprehensive GUI with menus and forms. These interfaces
help users interact and receive data from the system. The complexity in retrieving information
becomes easier with good database designs.
1.1.5 Effectiveness and Efficiency [5]
The importance of database cannot be over-estimated. Databases are strongly linked to the
efficiency and effectiveness of the organization concerned. Great databases can reduce the total
amount of time required to develop and fully-functional company. Manual maintenance models
would undergo a series of changes (though database designing is also an iterative process).
However, as mission critical projects get developed and complex systems are established, the
database would become easier to maintain. It would be easier to transform entities to tables,
attributes to columns, domains to constraints and data types, and relationships to primary or
foreign keys. These transformations become simple only with the right design. This is why it is
wise for Tom & Jerry to computerize their activities.
1.1.6 Program or Data independence [7]
Manual maintenance is a nightmare many organizations face. Luckily, issues like unproductive
maintenance can be eliminated with the help of program or data independence in the database.
Maintenance begins with the ability to add new data items to the system. However, there are