Delivery in day(s): 5
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.
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:
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:
Al though, SSADM may be less effective if following things happen:
Stages in SSADM
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.
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).
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:
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:
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:
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:
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.
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:
Figure 1: Logical data model for Tom & Jerry Ltd
Components of ER diagram
Smallest and atomic unit of data.
Employee, product, number etc
Property of entity
Name of employee, product name, price
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
Full name, address, mobile numbers
It is the association between entities and their occurrences.
Enrolment, responsibility etc
Figure 2: Components of ER diagram
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:
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.
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:
After applying second NF, table will split into two tables:
After applying 3NF, two tables will be generated without the data lose and better performance.
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:
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:
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:
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:
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.
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.
Customers table with sample data
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;
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- firstname.lastname@example.org. 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
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.
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:
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.
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
Answer:Go to main screen, select find invoice and enter invoice number. Then click print.
Answer: Click on find by name or mailin main screen and type the parameter as complete or partial string.
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
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.
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.
Books and Journals
Ambler, S., 2012. Agile database techniques: Effective strategies for the agile software developer. John Wiley & Sons.
Beck, T., Demirgüç-Kunt, A. And Levine, R., 2010. Financial institutions and markets across countries and over time: The updated financial development and structure database. The World Bank Economic Review, 24(1), pp.77-92.
Clifford, P., Robinson, M. And Rogers, T., FACE RECORDING and MEASUREMENTS LTD, 2016. DATABASE MANAGEMENT SYSTEM. U.S. Patent 20,160,078,104.
Coronel, C. And Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.
Embley, D.W. and Thalheim, B., 2014. Handbook of Conceptual Modeling. Springer.
Feuerlicht, G., 2010, April. Database Trends and Directions: Current Challenges and Opportunities. In DATESO (pp. 163-174).
Gupta, P., Mata-Toledo, R.A. and Monger, M.D., 2011. Database Development Life Cycle. Journal of Information Systems & Operations Management, 5(1), pp.8-17.
Lakhe, B., 2016. Using SSADM for Relational Design. In Practical Hadoop Migration (pp. 53-88). Apress.
Lee, D., Cha, S.K. and Lee, A.H., 2012. A performance anomaly detection and analysis framework for DBMS development. IEEE Transactions on Knowledge and Data Engineering, 24(8), pp.1345-1360.
Lemmon, E.W., Huber, M.L. and mclinden, M.O., 2010. NIST Standard Reference Database 23. NIST Reference Fluid Thermodynamic and Transport Properties—REFPROP, version, 9, p.55.
Nadkarni, U., Harrington Technologies Llc, 2013. Skills database management system and method. U.S. Patent 8,554,754.
PA?CU, P., 2010. The Stages of Implementation of the SSADM system in the Government Institutions. Journal of Applied Computer Science & Mathematics, (8), p.4.