Assignment on Database Design Concepts - Tom & Jerry Ltd

Introduction

Database design is the process of developing the detail data model for database for organizations. The database design model needed logical and physical design choices as well the physical storage parameters to develop the effective design according to requirements of client. Report will analyze the key issues and application of database within organizational environment as well critically evaluates the features of the database that could be beneficial for organization. Moreover, Assignment on Database Design Concepts Tom & Jerry Ltd  will discuss about the advantages and disadvantages of the database management system for gaining the better understanding about the effectiveness of DBMS in the business and Tom & Jerry LTD. Planning.

Task 2

2.1 Analysis of database developmental methodology

Database development methodology is a set of processes used to develop the database with desired features and data types. From the recommendations, a good database development methodology must possess following features:

• Completeness: It should be capable to design a complete database system with complete set of stages and procedures.
• Integrity: There should be proper integrity of data and data processes in methodology so that desired functionality can be achieved in resultant database.
• Flexible: Methodology should be capable to add flexibility in rearrangement of modules and restructure of processes to accomplish the objective.
• Responsiveness: Each stage of methodology should be responsive to deliver fast and optimized results for the next stage of development (Hoffer, 2012).
• Accessibility: Each module should be easy to understand and maintain along with the reusability of procedures.

SSADM- Structured Systems Analysis and Design Methodology: SSADM is widely adapted design methodology for database system. It was initially designed and used with governmental bodies of United Kingdom to develop database driven application but in today’s context it is also popular in public sectors. SSADM holds all the features of a good database development methodology. SSADM is recommended to use for design of database for Tom & Jerry by considering following features:

• Modularization: SSADM is structured into manageable stages where each stage id dedicated to accomplish the specific task. This modularization helps Tom & Jerry to divide the whole work as capability of resource in organization. Thus, parallelism can be achieved in work progress. Modularization is also helpful for Tom & Jerry Ltd to maintain the development as change making and problem identification is easy in modules.
• Visualization: Graphical information and modelling are helpful for employees to understand the progress and requirements of operations in development. It helps to integrate employee’s support into developer’s efforts. Graphs, trees and models of intermediate stages help to determine the effectiveness of requirements meeting in proposed system (Ambler, 2012).
•  Faster-smarter: The stages of SSADM are clear in functionality. Therefore whole development process gets the benefits of clear target at each stage to proceed and there is a little requirement of redone process on modules to meet objectives.

Al though, SSADM may be less effective if following things happen:

• Redundant analysis: It may divert the concentration from main target objectives.
• Larger designs: Combinations of modules and functionalities may result into larger, unmanaged and ambiguous design.
• Proceed to next stages by leaving requirements may increase cost on design.

Stages in SSADM

Stage 0: feasibility study

This stage is at ground level to find the financial, technical and business constraints of Tom & Jerry Ltd for a new system. It is identified at this stage whether new system is technically possible or financially affordable. Also is checked whether design of system is compatible with current practices and skill set of employees or how they will accept new system in working environment. All the information is managed in a document with textual as well as graphical information so that analytical results can be produced for given system.

Stage 1: Investigation of current environment

Developers use the information from current working environment to understand the design and objectives with purposed system. For that they can interview the employees, organize the questionnaire and analyse the existing documents and practices until they fully develop the understanding about the current environment (PA?CU, 2010).

Stage 2: Business system options (BSO)

This stage is dedicated to design the list of all possibilities of new system as a replace or modification to current system. Users of Tom & Jerry are given the list of all possibilities of options to select the appropriate one to meet the requirements according to them. It may contain information on:

• Computerization and interoperability gap between system and users.
• Distribution of resources: centric or diverse.
• Cost and benefits with proposed system.

Stage 3: Requirement specification

It is the documentation of requirements on select business option which specifies what will be carried out with new system. This is most typical stage because missing of required information may lead into ambiguity, faults and irregular design. Logical diagrams like data flow diagram and logical structures are developed to organize the information collected form users in Tom & Jerry Ltd. Organizational requirements are collected in following manners:

• Data and requirement catalogues.
• Processing document includes the events and functionality matrices.
• Diagrams containing entities and effects (Lakhe, 2016).

Stage 4: Technical system options

Once the system and requirements are clear to developer, they start to question Tom & Jerry Ltd to select the technical model for new system. As they did in BSO, they prepare the list on possible implementation approaches and bring it before the organization to select the most appropriate technical specification to implement. It may contain options based on:

• Hardware and software specification
• Cost and time limits with available resources
• Physical factors of implementation location
• Network capabilities
• Interface to users.

Stage 5: Logical design

Now developer start to design the logical structure of new system on the basis of knowledge gained in previous stages. Logical design may include technical data and process, required features and functionality and any other information from constraints. The logical design is prepared as guideline for physical design of database system for Tom & Jerry Ltd (Hoffer, 2012). Organization gets the following things in logical design stage:

• Updated data catalogues and logical structures.
• Logical process model to interoperate the data and processes.

Stage 6: Physical design

            At this stage, all logical structure is transformed into architectural structures. Logical designs are transformed into tables and relations in databases. All the requirements are mapped in implementation in term of technical codes and descriptions. A complete physical design represents the demand of organization for new system which is optimized and as according to requirements of organization.

2.2 Entity-relationship (ER) modelling and normalisation

Entity relationship model is graphical method to represent the entity types and relationship among number of entities in database and table. In order to create an effective entity relationship model, it is necessary to observe the business requirements deeply. The ER model only represent the data entities and their inter relations but does not represents the schema for business operations and data processing. ER model may contain three abstraction layers:

• Conceptual data model: Conceptual model contains the least set of information about the functionality of system and it may contain one or more logical models to represent the information. Normally conceptual model is use to define the inter relationship between various models (Thalheim, 2013). In other words, a database cannot be designed fully based on information of conceptual data model as it contains only the main semantic and relationships only.
• Logical data model: It is independent of conceptual data model and prepared as according to implementation scenario. For Tom & Jerry Ltd, logical data model will include detailed entity class and attributes, relationships among entities and how the data flow work in model. Logical data model becomes the basis for physical data model.
• Physical data model: Physical data model contains the detailed and practical representation of data entities because it is the the basis of practical implementation of database system. It contains all the relationships, data entities, attributes, keys and their processing. All this is required to create the database in system.

Figure 1: Logical data model for Tom & Jerry Ltd

Components of ER diagram

Component	Description	Symbol	Example
Entity	Smallest and atomic unit of data.	Rectangle	Employee, product, number etc
Attribute	Property of entity	Eclipse	Name of employee, product name, price
Key attribute	Special attribute those can be used to fetch whole record	Underline text within eclipse	Employee ID, product serial
Multi valued attribute	Attribute containing two or more entities	Double eclipse	Full name, address, mobile numbers
Relationship	It is the association between entities and their occurrences.	Diamond shape	Enrolment, responsibility etc

Figure 2: Components of ER diagram

Relationship types

Relationship among two or more entities may be in following manner. Arrow and link between entities is useful to understand the relationship type (Embley and Thalheim, 2014). There are three type of relationships are possible in database:

• One to one relationship (1:1): When an entity in a table related to another entity in another table of database. In this type of relationship only a single entity can relate to another single entity in other scope.
• One-to-many relationship (1: N): When an entity is has relation to more than one entities in other table, the relationship is defined as one-to-many relationship.
•  Many-to-many relationship (N: N): In this type of relationship most of entities in a table has relationship with most of entities in another table in database.

Figure 3: Types of relationships in database

For example, a faculty member may be assigned to multiple classes in college. This is one-to-many relationship but a department may have only a chairperson which denotes one-to-one relationship. Students can enrol for different classes; this is example of many-to-many relationship as shown in above figure.

• Normalization: Normalization is the process of dealing with redundant data entries in a database and anomalies in data operations. A database may have multiple columns those contain redundant data or may not be connected well to ensure the application of insertion, update and deletion (Coronel and Morris, 2016). There are mainly three types of basic normalization process:
• First normal form (1NF): A relation is in 1NF if attribute from domain contains the atomic values and every attribute contains single value from that domain. Domain is used to refer to all the values that a data field may contain.

Above customer table is not in 1NF because attribute “Telephone number” contains multiple values in relation to other attribute in record. Simplified 1NF table is:

• Second normal form (2NF): a relation is in second normal form if the relation is already in 1NF and does not include dependency of non-prime attributes on candidate keys in database. Primary key is a unique and not null attribute in table which is capable to identify the record uniquely in table. Candidate key is a combination of two or more attributes those can be used together to identify a record from table. The below given table is in 1NF but not in 2NF.

  
After applying second NF, table will split into two tables:

• Third normal form (3NF): 2NF still contains the data redundancy and integrity issues. 3NF is answer to such problems as it ensures that: relation should be in second normal from and all the attributes are only dependent of candidate keys (Thalheim, 2013). It is helpful to optimize cost and data storage space. A table in 2NF is shown below.

After applying 3NF, two tables will be generated without the data lose and better performance.

Task 3

3.1 User requirements and database developmental cycle

In order to create a new database system, it is necessary to understand the user requirements with and demands associated with new system. According to requirements specified in case study, it is clear that users have following requirements with new system:

• Management of customer and products in a digitized form rather than to traditional paper based processing.
• Order list as well as back order list management.
• Unique invoice generation for a transaction with customer.
• Simple user interface with login id and password facility to access database.
• Different tables for customers, payment, invoice and products along with a form user interface to perform common task on these tables like create a new invoice, fetch all record, search specific, reorder details etc (Gupta.et.al.2011).
• Validation of data at entry level to eliminate erroneous entries in database.
• Print functionality for invoice and summery of transactions.

Database development cycle: Below given database development cycle is similar to SSADM in stages. As the general aware is developed with SSADM, the below given stages can be followed to deliver a database system with required functionality. Each stage is unique to work and helps to modularize the development among the capability of team.

Database planning: Development team at Tom & Jerry Ltd needs to identify the following requirements in planning phase to move on:

1. Need of new system and scope of decision in business.
2. Associated functionality and resources.
3. List of task being expected with new system.

System definition: This stage answer that which type of system is expected with capability in finance and business limitations of Tom & Jerry Ltd. Organization must need to identify the typical specification in new system. Organizational users and management needs to speak on:

• Connection between departments and employee groups in term of information and data sharing.
• Expectations with new system regarding the current and future context (Lee.et.al.2012).
• Skills and experience of users on new system.
• Ideas on new system with industrial and enterprise environment in which it will be applicable.

Figure 4: database development life cycle

Requirements Collection and Analysis: This stage is related to work on specification provided by users in organization so that developer now needs to collect the requirements related to proposed design and development work. Tom & Jerry Ltd can organize the events and meeting to know from the users about the new system. This stage may include:

• Data identification required to accomplish the design of new system.
• Data and process as part of system. How they will be used and flow within database system.
• Analysis of user activities and current environment of workplace.
• Additional requirements for development.

Database design: Once the analysis and requirement phase ends, developer can move on database design which can be categorised in three parts:

Conceptual design: This design model is away from the physical implementation and constraints on database. It mainly uses the user requirements as the input for model and develops the model with big ideas only. It does not provide any information and detail about how database is sub parted to work. It will define main objectives and parts of database as tables and their relationship.

Logical design: This design model is used to specify the logical data entities and relationship in database in form of rational database schema (Coronel and Morris, 2016). For Tom & Jerry, it will define what the data attributes are and how they are linked to each other to accomplish the task. This is the logical representation of data and process of new system design.

Physical design: This is the actual design model for Tom & Jerry Ltd which is fully basis of logical design. This design model will include types of data, physical schema to implement, indexes, security, integrity constraints and how data is physical dependent on other data values.

Database Management System Selection

This stage is useful to identify the database management system like access, mysql, SQL server and oracle. For tom & Jerry Ltd, access database system is selected due to skill set of employees and requirement of organization.

• Application Design: Developer needs to consider the user requirements to design the form interface for users. In this phase, user interface will be created to facilitate direct options to perform common task like view of pending orders, create new invoice and enter product details etc.
• Prototyping: Prototype will be created as database to test the user requirements and to understand the upcoming features and design. It is useful to collect user views and opinions on new system
• Implementation: This is programming and system development phase to meet expectation in realization of models and design. Here actual design and implementation work goes. Developer will design the codes and queries, interface designs etc.
•  Data Conversion and Loading: This stage is required when we migrate from one database structure to other. However, Tom & Jerry is creating a new database from paper based data, it is not required to follow.
• Testing: Developers will use the sample data to be feed in system by users so that erroneous activities and operations can be tested for improvements (Ambler, 2012).
• Operational Maintenance: If any of the test results are not in favour of expectations with new system, update the design and monitor the activities of new system to make it robust and trustworthy.

3.2 Design of database

Tables
Customers table with sample data

Forms

Customer form

Payment form

Product form

Invoice form

Queries
To find a customer by name or ID.
SELECT customers.c_id, customers.c_name, customers.c_mail, customers.c_addr, customers.c_order, customers.c_order_date, customers.c_amount, customers.c_due, customers.c_mob FROM customers HAVING (((customers.c_name)="alex") OR ((customers.c_id)="04")) ORDER BY customers.c_id;

To find all records where payment due date is 28 days
SELECT payment.ID, payment.py_prod, payment.[py_due date], payment.py_date, payment.py_invoice_no FROM payment WHERE (((payment.[py_due date])=datediff(28,"py_due date","py_date","monday","sunday"))) ORDER BY payment.py_prod;

Reports
Customer reports

Due date report

Validation and Verification techniques: Validation and verification techniques are used to remove the entry of data against the required values and format so erroneous results can be eliminated and data values can be keep in consistent form. For instance, customer table is given below. Here, order date is of type date/ time. Thus, it does not include textual information and user is bound to enter only a valid date format. In the same manner, customer mail ID is of type text which is configured to take input in the form- xxxxxxx@xxx.xxx. Each time user inserts the data, it is validated with the required format and user is prompted if the value is not compatible with field.

Figure 5: Table attributes and types

Main form

Figure 6: Main window for database interactivity

Main and other forms contain the dropdowns, check box and list to ensure the time and error saving in operation. Dropdown and list is provided to select product name, preferred supplier. Check boxes are used to select multiple records and to operate on them. In this manner, most of fields are proofed against the erroneous data values (Elmasri and Navathe, 2011). Addition to it, text boxes are validated against the defined formats. For example, customer name cannot contain the numerical values but address field can.

3.3 Effectiveness of database solution and improvements

New database system is prepared on the requirements of Tom & Jerry Ltd and tested well according to demands and user skills to know the improvements. Thus, system is adaptive and effective for Tom & Jerry Ltd as organization has determined a lot of time and cost saving on management of information. Tom & Jerry Ltd has been disclose the following benefits with new database system:

• Easy management: Organization has got the benefits of easy management of information as digital information can be transmit easily and securely. Also the update work is easy so organization has reduced a lot time to deal with paper based documents.
• Accessibility: Most of data is always available due to direct accessibility with sources. Thus, employees can access the data with assigned permissions and can back up the data easily. Also electronic form of data is easy to retrieve the records with custom search and options in forms. Most of common queries are mapped to forms so users need not to worry with exact commands to access the data.
• Performance:  New database system helps the organization to optimize the use of disk space by normalizing the relations and eliminating redundant data fields. Easy user interface with high productive returns is at core to enhance the performance (Smith.et.al.2011). Now users are capable to click a button to generate all the details of a specific customer or product.
• Security: Most of input fields are configured well to specify the data in given format only. Thus, security has enhanced in term of data violation. Also user and user groups are defined to have access on specific tables in database. Thus data is secure from access of unauthorised users. Multiple copies and auto backup help to achieve security. Addition to it, log system helps to identify the user activities and time.
• Usability: Report based weekly and monthly sales helps organization to analyze the information for business purpose (Feuerlicht, 2010). Tom & Jerry is getting advantages of reports and pivot tables to understand the data more easily. It helps organization to determine the new decisions for business opportunities.

However, there are a few things those are under consideration for future changes. At present, system is capable to handle all the user activities and transactions, but changing working environment and conditions may encourage in making of following improvements in current system.

• Scalability of organization: If organization scale for small domain to enterprise, it is possible to have some changes in current database as it is not optimized and secure to handle data in enterprise system.
• Permissions: In order to ensure the security from outer and insiders, it is necessary to implement permission on database for user accounts and groups so that only right users can make changes in consistent state of database (Beck.et.al.2010).
• Scheduling: In order to handle and add functionality in database, system may be integrated to third party applications. Auto backup and optimization, block of users trying more than three times with wrong credentials etc.

3.4 User manual and technical documentation

User manual

This manual is designed for database system implemented in Tom & Jerry Ltd. This manual will help users to start with a new interface and features in system.

Chapter 1: Introduction
Database system is designed to eliminate the errors and troubles in data values and data management in organization. This system is capable to handle most of user activities with common form interface.

Chapter 2: Main screens
In order to access database, user needs to provide the user ID and password assigned to him by administration. If you have no user account or not able to login, and then please contact administrator. Login screen is given below:

Figure 7: Login screen

Login screen is not the part of database but used as front layer to communicate with database via the application. User name is commonly a mail address and password is minimum eight character long alphanumeric series (Timmer.et.al.2015).
Front Screen: It is the first screen of database system to show all the important information at one place. It contains the graphs and charts to demonstrate the business conditions.

Figure 8: Front face of database program

Chapter 3: Common faqs

1. How to print invoice

Answer: Go to main screen, select find invoice and enter invoice number. Then click print.

1. How to find specific customer name and mail address.

Answer: Click on find by name or mail in main screen and type the parameter as complete or partial string.

Technical Specifications

Technical documents are designed to help developer to maintain and upgrade the system more functionality. It contains graphical as well as textual information used in development and implementation of database system in Tom & Jerry Ltd. Developers are suggested to append their details if any of the changes is made in current system and design. Screenshots can be added to support and justify the implementation of new changes as improvement in system..

Chapter 1: Framework details

Windows XP or later
Microsoft Access 2007 or later
512 MB RAM
1.3 GHZ processor (64 bit is recommended)
8 GB storage
Internet connection

Chapter 2: database configuration
Database contains mainly for tables: product, customers, payment and invoice. Each table contains the unique relationship with other as shown in diagram.

Figure 9: Relationship among attributes

Customer table: c_id primary key, c_mob unique and not null
Invoice: in_no and in_cid are not null and unique
Payment: pyid is primary and py cust---c_name (1:1)
Products: pid---incid----pyid (1: N)

Chapter 3: Security
Default security of database is password which can be obtained from administrator of organization. Also user accounts and groups are configured to have access on database and tables. The dual nature security is implemented to ensure the proper and authorized access to database system. Do not disclose the security information to others (Lemmon.et.al.2010).

Chapter 4: Development detail
Development methodology used: SSADM
Last tested on: 16/08/2016
Test result: optimized and successful
Security points: 3/5 etc.

Conclusion

From the above study, it is been considered that use of database is very essential for organization to record and information and use for future planning. Report has provided the information about the issues that an organization faced using the database and analyzed the areas of application for database such as banking, hospital and education. Moreover report has critically evaluated the features and advantages of database management system to Tom & Jerry LTD.