Database Design Concept

This document discusses the key issues and advantages of database management systems in organizational environments. It explores the importance of avoiding data redundancy, improving data security, and other features. The document also includes a case study on Tom & Jerry LTD.

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:

DATABASE DESIGN CONCEPT 13
1. Customers should be allowed to place an order with Tom & Jerry system.
2. Customers should receive invoices for their orders.
3. Stocks should be up-to-date or re-ordering has to be done.
4. Details of all Orders should be maintained for reporting needs.
5. Suppler details should be managed for each product.
6. Payment details should be managed for each transaction.
A basic schema definition for Tom & Jerry would be as follows:
1) Products (product name, product id, product description, product quantity, reorder
quantity)
2) Supplier (supplier name, supplier id, supplier contact number, products supplied)
3) Customer (customer name, customer id, customer id, order id, product id)
4) Order (order id, customer id, product id, date)
That is because the logical data model acts as a vital piece of information for the very next phase.
It is necessary for avoiding tradeoffs in the database design. Moreover, the logical database
design plays an important role in the operational maintenance phase. A good design allows
future modifications and data can be stored efficiently and accurately in the database.
2.14 Physical Design Phase
During the physical design phase, the physical qualities of the data are specified. This includes
details about the system’s internal storage methods, file organization structures and backup
strategies. The physical design helps in deciding how the actual database has to be implemented.
Details from the logical structure are used to frame the physical design. Though the physical

DATABASE DESIGN CONCEPT 14
design is not directly linked to the DBMS, it has to be developed in-line with a data model like
hierarchic, relational or network. This is why the physical database should begin with the target
system. For a good business database application, the physical design should be tailored to
satisfy specific DBMS systems. Feedback from the physical design phase can change the logical
design model to improve performance. Key aims of physical database design are as follows:
1) Identify storage structures
2) Identify data access methods
3) Design secure systems
4) Create relational tables with constraints. Information from the logical data model has to
be used during the construction of tables.
Once the logical and physical design phase is completed, the database design gets into full form.
Conceptual and logical design for a database is separated from the physical design phase. This is
because physical design is of different subject. It deals with what and not the how part. The
physical design is implemented at a different point of time. The what part of a database should be
understood before how is figured out. Once the logical design comes to an end, the physical
design for Tom & Jerry’s computerized system can be framed.
2.2 Tools for Database Design
2.21Entity Relationship Data Model
The entity-relationship (ER) data model is a very useful tool for designing databases. All
database designs begin with an ER diagram. The ER data model uses a collection of data objects.
These data objects are known as entities. Relationships between these data objects are neatly

DATABASE DESIGN CONCEPT 15
mapped. The entity can be any kind of object with attributes that distinguish the object from
others. For example, student details and vehicle models can be considered as entities.
Attributes describe the entities in a database. For example, customer_name, address and
customer_contact_number are attributes of a customer object. Similarly, product_name and
product_quantity are attributes of a product object. In a database, every entity has to be identified
using a unique key. This is known as the primary key. Details about the primary key have to be
documented at an earlier stage of the design. Relationships in a database are the associations
between various entities. For example, a student relationship can associate the student with her
course. A collection of entities with the same relationships and type are known as a relationship
set or entity set, respectively.
An entity-relationship (ER) diagram can be used to display the overall logical structure of the
database in a graphical form. These diagrams can be drawn using unified modeling language
(UML). Apart from relationships and entities, the ER diagram represents important constraints
that the database should conform to. An important constraint would be mapping cardinalities.
Mapping cardinalities expresses the total number of entities to which other entities can be
associated with. For example, students can be associated with several courses. This is a one-to-
many relationship. A good database design can have one-to-one, one-to-many and many-to-
many cardinalities. And, the design must not have any redundant information. Database
designers must make sure that their design doesn’t include redundant relationships.

DATABASE DESIGN CONCEPT 16
There are two different views for the Tom & Jerry sales ordering and stock control system. It
comprises of a stock view and sales view. The relationships between these views would be as
follows:
1) Tom & Jerry has sales and warehouse staff
2) Sales manager takes care of sales staff
3) Sales staff receive orders from customers
4) Orders have order id, customer id and customer name
5) Order details has products details

DATABASE DESIGN CONCEPT 17
6) Products have product id, product name and price
7) Products can be categorized into categories
8) Categories have category id and category name
9) Each category is associated to a supplier
10) Supplier has supplier id, supplier name and product id
Details of entities and relations in the system would be as follows:
Staff (Staff ID, FirstName, LastName, DateOfBirth, Address, role ID, phone No, username,
password)
Role (Role ID, Role Name, Description)
Customer (Customer Id, name, address, phone)
Order (Order id, bill no, staff id, customer id, order date, dispatch date, payment id, deleted,
paid)
OrderDetails (Order id, bill no, product id, price, order quantity, discount, total, order date,
dispatch date, order detailed, bill date)
Payment (payment id, payment type, credit, debit, balance)
Product (product id, product name, product description, supplier id, category id, quantity, unit
price, size, discount, units in stock, units to order, reorder level, product available, current order)
Category (category id, category name, description)
Suppler (supplier id, company name, contact details, payment methods, discount type)

DATABASE DESIGN CONCEPT 18
2.3 Normalization
Normalization is another important method for designing good databases. The ultimate goal of
any database design is to generate a collection of relation schemas that can store data without
unnecessary redundancy. Moreover, the design should let us store and retrieve data easily. The
design schemas must make sure they are in a proper normal form. To make sure the relationship
schema is a desirable normal form, you should have more information about the real-world. A
common technique called functional dependencies is used to tackle this. Without normalization,
database designs can be filled with many issues. Here are few undesirable properties a poor
design can introduce into a database:
1) Inability to showcase critical information
2) Data redundancy
[4] Normalization is identified as a formal technique during all stages of database designing. It
can be used as a validation technique or a bottom-up standalone method. Regardless of the
approach followed, normalization is required to create well-designed and meaningful relations
that satisfy all data requirements of the system. The user requirements specification acts as the
source for normalization. A set of well-designed relations is formed by merging ER models (top
down approach) and normalization (bottom-up approach). Finally, normalization can be used as
a validation technique that ensures on the structure of the relations. The ability to normalize
based on the bottom-up technique can be limited by factors like the amount of data revealed in
the specification document. Luckily, this limitation does not affect the validation procedure.
Regardless of how complex or big the database is, normalization proves to be useful.

DATABASE DESIGN CONCEPT 19
Details from the E-R model can be converted to normalized tables. Three types of normalization
are to be followed:
1) 1st Normal Form – this is the primitive form of normalization where every individual field
in the table can hold a single piece of data. Field must not have any repeating groups.
They should not have any multi-valued attributed.
2) 2nd Normal Form – every field should be dependent on the primary key fully. Fields that
are partly dependent on the primary key should be removed to another table. This
overcomes data duplication. All non-key attributes should depend on the primary key
entirely.
3) 3rd Normal Form – The table must not have any transitive determinants. Non-key fields
must not depend on other non-key fields. Such fields should be moved to a separate table.
This reduces redundancy by a further level.
4) BCNF – this is a stricter version of the 3NF. Only super keys can be used to determine
attributes.

DATABASE DESIGN CONCEPT 20
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
After normalization, Tom & Jerry sales ordering and stock control system would be as follows:
1. Staff ( staff id, name, dob, address, username, password, role id)
1.1 Primary Key – staff id
1.2 Foreign Key – Role Id references Role (Role id)
2. Role ( role id, role name, description)
2.1 Primary Key – role id
2.2 Foreign Key – None
3. Customer ( customer id, bill reg number, name, dob, address, staff id)
3.1 Primary Key – customer id
3.2 Alternate key – bill reg number
3.3 Foreign Key – staff Id references staff (staff id)
4. OrderDetails ( order id, unit price, size, quantity, discount, total, DDate, Bill Data, Bill
No, Order Id, Product Id)
4.1 Primary Key – Order id
4.2 Foreign Key – Bill Id references Payment (Bill id)
4.3 Foreign Key – Order id references Order (Order id)
4.4 Foreign Key – Product id references Product (product id)

DATABASE DESIGN CONCEPT 21
5. Payment (billNo, paytype, cr amount, cr date, debit amount, debit date, balance)
5.1 Primary Key – billNo
5.2 Foreign Key – None
6. Product ( product id, name, product description, quantity per price, unit price, weight,
size, discount, stock in hand, stock to order, reorder level, supplier id, category id)
6.1 Primary Key – product id
6.2 Foreign Key – Supplier Id references Supplier (Supplier id)
6.3 Foreign Key – Category id references Category (category id)
7. Category ( category id, name, description)
7.1 Primary Key – category id
7.2 Foreign Key – None
8. Supplier ( supplier id, contact number, payment method, discount type)
8.1 Primary Key – supplier id
8.2 Foreign Key – None
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.
3.21 Tables in Tom & Jerry sales ordering and stock control system

DATABASE DESIGN CONCEPT 22
Entity Attributes Description Data Type Note

DATABASE DESIGN CONCEPT 23
Staff Staff id Uniquely identifies the
staff
5 varchar Primary
key
Name Name of staff 15 varchar
Address Address of staff 15 varchar
Contact number Contact number of staff 10 digit
Role id Job title 5 varchar
Username Login detail 15 varchar
Password Login detail 15 varchar
Role Role id Uniquely identify staff role 5 varchar Primary
key
Role Name Job title 15 varchar
Role description Description of role 30 varchar
Customer Customer id Unique customer id 5 varchar Primary
key
Bill reg no Customer registration
number in bill
15 varchar Candidate
Key
Name Name of customer 15 varchar
Address Contact details 30 varchar
Phone Contact details 10 digit
Staff id Staff who registered the
customer
5 varchar
Order Order id Uniquely identify order 5 varchar Primary
key
Staff id Uniquely identifies the
staff
5 varchar Foreign
Key
Customer id Unique customer id 5 varchar
Order date Date of order 10 digit
Order Details Order id Unique order id 5 varchar Primary
key
Bill no Unique bill number 5 varchar Foreign
Key
ODetail id Unique order detail id 5 varchar Foreign
Key
Product id Unique product id 5 varchar Foreign
Key
Size Size of product 2 digit
Order quantity Total quantity ordered 3 digit
Discount Total discount in order 3 digit
Total Total worth of order 10 digit
Order date Date of order 10 digit
Dispatch date Date of order dispatch 10 digit
Payment Bill No Unique bill id 5 varchar Primary
key
Pay type Payment type 5 varchar
Credit amount Credited amount 10 digit

DATABASE DESIGN CONCEPT 24
Credit date Credited date 10 digit
Debit amount Debited amount 10 digit
Debit date Debited date 10 digit
Balance Total balance 10 digit This is a
derived
field
Product Product id Unique product id 5 varchar Primary
key
Product name Name of product 15 varchar
Product description Description of product 15 varchar
Category id Category to which the
product belongs
5 varchar Foreign
Key
Supplier id Unique supplier id 5 varchar Foreign
Key
Quantity per unit Product quantity per order 10 digit
Unit price Product unit selling price 10 digit
Discount Discount offered by
supplier
10 digit
Unit in stock Total units in stock 10 digit
Unit in order Units to toder 10 digit
Reorder level Level at which product has
to be re-ordered
10 digit
Category Category id Unique id to find category 5 varchar Primary
key
Category name Category name 15 varchar
Description Description for category 15 varchar
Supplier Supplier id Unique id to identify
supplier
5 varchar Primary
key
Supplier name Supplier name 15 varchar
Contact address Contact details 15 varchar
Payment method Payment methods 5 varchar
Discount type Type of discounts provided 5 varchar
3.2.2 UML
Diagram of
Tables

DATABASE DESIGN CONCEPT 25
3.2.3 Validation and Verification
The prime objective of normalization is to reduce the total number of attributes in each entity
that satisfies the needs of Tom & Jerry’s sales ordering and stock control system. The process of
normalization happens in several steps. This database system is broken till the third normal form.
The relations are derived from the conceptual data model and validated using the normalization
technique. Verification can be done against user transactions too. This make sure the logical data
model is accurate. The primary keys, foreign keys from the ER diagram are useful during
validation. This process was done manually before. Not, Tom & Jerry can automate it
completely.
3.3 Evaluate the effectiveness of the database solution and suggest methods of improvement
for the Tom & Jerry LTD
On the whole, computerized Tom & Jerry sales ordering and stock control is an efficient system.
It is produced to meet all the data requirements of the company. Data requirements, functional

DATABASE DESIGN CONCEPT 26
needs and transaction requirements are closely monitored and described. Tables and models in
this document can be converted into a functional system. After the right platform is chosen and
hardware components are picked, Tom & Jerry will have a working system. When it comes to
improvements, the organization can send emails of invoices and print reports automatically.
References
Connolly, T.M., & Begg, C.E. 2005. Database Systems: A Practical Approach to Design,
Implementation and Management [pdf]. England: Pearson Education Limited.

DATABASE DESIGN CONCEPT 27
Available at http://www.palinfonet.com/download/software2/database%20systems.pdf
[Accessed 9 February 2017].
Koirala, S. 2014. 11 important database designing rules which I follow. Available at
https://www.codeproject.com/Articles/359654/important-database-designing-rules-which-I-fo
[Accessed 9 February 2017]
Howe, D. 2001. Data Analysis for Database Design. Butterworth-Heinemann Ltd, 3rd edition
ISBN 0750650869
Mullins, C. 2013. The importance of database design. Available at
https://www.toadworld.com/platforms/ibmdb2/b/weblog/archive/2013/02/14/the-importance-of-
database-design [Accessed 9 February 2017].
Sebastian. 2005. Database design guide. Available at
http://www.smart-it-consulting.com/database/progress-database-design-guide/ [Accessed 9
February 2017]
Silberschatz, A., Korth, H.F. & Sudarshan, S. 2011. Database system concepts [pdf]. New
York: McGraw-Hill. Available at

DATABASE DESIGN CONCEPT 28
https://kakeboksen.td.org.uit.no/Database%20System%20Concepts%206th%20edition.pdf
[Accessed 9 February 2017]