Developing a Database Solution for Tom & Jerry LTD - A Case Study

This report analyzes database design concepts and their application within organizational environments, focusing on a case study of Tom & Jerry LTD. It evaluates the features and advantages of database management systems (DBMS) such as reducing data redundancy, improving data security, and enhancing data accessibility. The report also analyzes a database developmental methodology, including conceptual, logical, and physical design phases, and discusses tools like the Entity Relationship Data Model and normalization techniques. Furthermore, it details the development of a fully functional database solution for Tom & Jerry LTD, including table design, UML diagrams, validation, and verification methods. Finally, the report evaluates the effectiveness of the database solution and suggests methods for improvement, emphasizing the importance of a well-designed database for efficient sales ordering and stock control.

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

DATABASE DESIGN CONCEPT 7
several different types of maintenance. Any of these changes would inform the database that 1) a
change was made 2) a new view should be displayed 3) an error should be displayed if
something goes wrong. Common maintenance in the system includes 1) change of data format 2)
change to the file organization system 3) change in storage methods. Tom & Jerry do not have to
worry about these issues if a clean database is designed.
1.1.7 Structuring
Database is a collection of huge volumes of data. Data becomes meaningless without the right
structure. This is what happens with manual methods of data maintenance. Nevertheless, this is
why database management systems offer data structuring facilities. The database makes sure
large volumes of data get structured efficiently. And, good designs can satisfy the needs of Tom
& Jerry and ensure maintenance is easy for engineers.
1.1.8 Recovery and Validation
Sharing of data is a solution to ensure consistency. However, this can make systems vulnerable
to mistakes. Validations are a crucial part of good databases. Proper validations are inserted at
design level to avoid erroneous data. The system would make sure data is stored and managed in
a proper way. Modern database systems provide recovery procedures to avoid erroneous data.
However, these features can be used only if the database is designed properly. On the other hand,
organizations without databases should rely on manual methods for validation and recovery. In
most cases, manual efforts prove to be time consuming and tedious. As an organization shifts to
computerized systems, they can handle validation and recovery automatically.
1.1.9 Monitoring and Tuning

DATABASE DESIGN CONCEPT 8
Databases can differ from one another in several ways. Applications can come with conflicting
requirements. For example, a customer at Tom & Jerry may wish to retrieve product information
using productName; whereas, stock managers may want to access data through productID. On
the other hand, audits may want small block sizes of data and owners might be interested in
larger blocks of information. The system should facilitate such requirements seamlessly. It must
satisfy all users, without compromising on performance. This task becomes easier when
databases are introduced into the organization at an earlier stage. Organizations will be able to
take care of many things simultaneously.

DATABASE DESIGN CONCEPT 9
Task 2
2.1 Analyze a database developmental methodology for the given case study Tom & Jerry
LTD
2.11. Step 1 – Developing a Schema – Introduction
The heart of any database design would be the database schema. To begin with, perfect database
schemas should be written for Tom & Jerry. The schema represents the entire logical view of the
sales ordering and stock control system. It gives a description of how data can be organized and
associated with other objects in the database. During the development of a relational database,
constraints can be formulated and applied on the data.
Schemas are broken into three different categories: conceptual scheme, logical schema and
physical schema. These schemas are designed in the beginning, when there is no database at all.
Tom & Jerry’s computerized database system should begin with right schema definition. As the
database becomes fully operational, it will be very difficult to alter the schema. And, the entire
database would be built upon the schema. If Tom & Jerry’s computerized system is built with a
good schema, it can last for decades with minimal maintenance. Moreover, the overall schema
judges the performance and reliability to the database management system.
2.12 Conceptual Model
To begin with, a high level model has to be identified. This model educates the database designer
with an important conceptual framework. The model comprises of data requirements and
functionalities. It identifies the flow of data between entities. The database design has to fulfill
all these requirements efficiently. During the initial phase of the design, engineers must
characterize the needs of the data. They should look in terms of the database user. They must

DATABASE DESIGN CONCEPT 10
interact with users and domain experts to understand what they need out of the system. The
outcome of this interaction would be the specification of user needs. Users should give details
about the type of transactions or operations that can be performed on the data. This is known as
the specification of functional requirements. Once user requirements are carefully identified
and documented, the designer has to frame a data model. Necessary concepts should be applied
while choosing the data model. This is when requirements are translated to a conceptual schema.
This schema supports the entire database. This is also known as the conceptual design phase.
The conceptual design phase develops a schema that gives an overall view of the system or
enterprise. The schema has to be reviewed thoroughly to make sure all necessary data
requirements are satisfied. Meanwhile, schemas should not conflict between one another. All
redundant features should be removed. The ultimate focus at this phase is on how data is related
to one another. It doesn’t stress only on the physical storage methods.
In Tom & Jerry sales ordering and stock control system, there are three different types of users.
The customers, stock control employees and audits play an important role in the organization.
The database design must allow customers to order for goods. They should be provided with
invoices and details of whether the order was placed successfully and executed or not. Stock
control employees should be aware of all stock details. They must be aware of the availability of
products and about reordering. As items get dispatched from the warehouse, relevant records
should be updated. The Auditor generates reports of various sales orders and stock
performances periodically. The auditor needs access to various parts of the database system.
With respect to the relational model, the conceptual design phase focuses on the attributes (these
attributes should be captured by the database). Here all attributes in the Tom & Jerry sales

DATABASE DESIGN CONCEPT 11
ordering and stock control system should be grouped together to form tables. According to users,
the current system works as follows and the database must tackle all these operations:
1. Ordering stock – Stocks should be ordered from the supplier. Tom & Jerry would
produce a product list that needs to be distributed by the supplier. This list holds details
of the products. Currently, everything is managed manually, where stock employees look
around and see the availability of products and re-order.
2. Purchasing stock – once the stocks are delivered to the organization, the list is verified
and updated. Books are managed to monitor stocks in the warehouse. All price discounts
and stock-out details are held manually.
3. Customer orders – Books are maintained at Tom & Jerry to keep track of customer
orders. These books are updated when customers make orders. Details of stock are
checked and customers are updated. This is a lengthy process. If stocks are not available,
customers are sent away and told to come another day. Tom & Jerry has lost many
customers due to inconvenience reasons.
4. Payments – Customers pay differently! Employers are expected to keep track of what the
customers want and payments should be handled. Some customers request and are given
discounts. These processes are done manually too. End of transaction, an invoice is
provided to the customer. This invoice has details of the order, product and total cost.
Customer details and organization details are displayed in the invoice.
5. Reporting – Every week, Tom & Jerry keeps track of the sales ordering done. Total sales
done is tracked in these reports. The report stores details of the company’s contact info,
address and weekly invoice total.

DATABASE DESIGN CONCEPT 12
Detail of “how” data is connected is a database design problem. There are two different ways to
handle this problem. One, databases can be designed using an entity-relationship model. Two, a
collection of algorithms can be used to take the necessary attributes and generate relevant tables.
A completely developed schema would describe the functional requirements of the enterprise.
The process of implementing a database from the abstract data requirements as such as what was
given by Tom & Jerry happens in two design phases – logical design and physical design.
2.13 Logical Design
[4][6] During the logical design phase, high level conceptual schema is mapped to the
implementation data model. This forms a system specific database schema. The outcome would
be a logical data model that the end user wishes to see. The logical database design depends on
the conceptual data model. This is why the conceptual data model has to be refined and mapped
carefully to the logical data model. The logical data model revolves around the target data model.
In the case of Tom & Jerry sales ordering and stock control systems, all attributes like customer,
supplier, products and order should be mapped to a system specific database schema. The other
hand, the conceptual data model doesn’t depend on the underlying data structure. However, the
logical design expects the underlying data structure to be perfect. Throughout the design of the
logical model, data should be tested and validated against requirements of the organization (Tom
& Jerry). Techniques like, normalization should be used to validate the logical data model of
Tom & Jerry sales ordering and stock control system. With the help of normalization, relations
(alias tables) are derived properly from the data model that was discussed during conceptual
design. This strategy, eradicates data redundancy and any update anomalies that can be caused.
The logical data model makes sure all kinds of user transactions are supported. Important
transactions in the system are as follows: