Unit 17 Database Design and Concepts Assignment - Tom & Jerry

Program	Diploma in Business
Unit Number and Title	Unit 17 Database Design and Concepts
QFC Level	Level 4

Introduction

Every business and application is using database to manage the information in an appropriate and effective manner. Database is helping modern business to manage complex and trivial data in a secure and efficient format. The database design and concepts  will identify the main application areas as well as key issues in database system. database design and concepts will determine the major features of a good database management system along with advantages and disadvantages. Tom & Jerry Ltd is considered to make things easy to understand.

Task 1

1.1  Key issues and applications of database within organizational environment

Database refers to the collection of information in a structured manner so that it can be store, deleted and updated. Database management system (DBMS) is a software tool used to perform these operations efficiently on database. An organization needs to consider the following key issues those may be occur in implementation of DBMS:

Centralized control: Database should be bring into practices with distribute nature. Tom & Jerry Ltd needs to distribute the database among several centres to ensure data availability and consistency during the erroneous and accidental loss of information. Centralised database are easy to manage and use but the failure of host may break the whole network and accessibility with database (Coronel and Morris, 2016). Also decentralization of database helps to balance load and performance with human resources.

Data redundancy: Al though decentralization of database management system helps to achieve high performance and availability but problems may be occurs in data operations. Database is fragmented at several sides which becomes difficult to update. Thus, anomalies may resist in database to provide incurrent values. Also multiple copies of database fragments at various locations need to be treated well. It is essential to ensure that data values are consistent and reflect the operations on all locations.

Data security: Database management system needs to be capable to handle user activities and outsiders so that data values can be retrieved in integrated and consistent format whereas insertion can be made at all copies with validation to secure the data loss. Proper permissions and user access is required to implement with DBMS.

Today, database and DBMS are used in almost every industry to manage the data in efficient form because data is key unit for organization to proceed in growth. From the list of numerous applications, some common applications are:

Banking: DBMS is used in banking sectors to hold customer and account details securely. Database helps banks to easily manage the information among various terminals with consistent and updated values every time. Strongly design banking DBMS helps the organization to achieve high performance and security as it can be seen in banking sector.

E-commerce: Increasing use of internet has made business online. Databases are used to hold the customer orders, account details, mails and recent transaction and visits on site. E-commerce uses the database to order the information easily to serve customers effectively. In e-commerce database with integrated and effective retrieval is expected for better performance.

Hospitals: Database is used in hospitals to store and retrieve information of patients easily. Database helps organization to manage the business and sales operation and patients detail securely as it may contain sensitive information in database (Elmasri and Navathe, 2011). Electronic form of databases is easy to protect form thefts, errors and destroy.

Online booking: In online booking systems, databases are used to store and update the information by customers. Also databases are used to retrieve customer information whenever needed by management. Database is used to find the previous transactions and booking by customers with easy customization of parameters.

1.2 Critical evaluation of feature, advantage and disadvantage of database management system

Database system can provide a number of benefits to Tom & Jerry like organizations. Some common features those are standard to be included in database system are:

Data structuring: This is the feature of database system to provide well structure to store data in integrated and sharable form so that redundancy and duplication can be minimized to optimize storage usage. Tom & Jerry will get high initiative and effective data structure to store all kind of information used in organization.

Data independent programs: Tom & Jerry needs to develop few application programs to generate reports and forms because there is no dependency between data and programs. Therefore it is easy to customize the frontend of database interactivity with users of Tom & Jerry Ltd.

Query language: A good DBMS always contain the query language supports for users those are non-technical but needs to access data effectively. Simple queries help them to accomplish operations with desired data values (Ambler, 2012).

Faster-smarter: Database system should be capable to perform fast and effectively to with integrated data. Also it should be capable to handle information effectively with fewer errors. All the non-recurring tasks should be performed fast with changing parameters of Tom & Jerry Ltd.

Large databases: Tom & Jerry Ltd will need to handle a large amount of data over the time so DBMS should be capable to handle thousands of records efficiently and effectively. It should provide features to maintain large size databases.

Advantages:

• ACID transactions: DBMS provides atomic, consistent, integrated and durable data. It means that there is a little chance to achieve wrong values during transactions. Data is in atomic form means that individual data is stored in undividable form which is effective in retrieval process.
• Backup and recovery: DBMS provides the facility to store and retrieve the database to most consistent state. It is easy to backup part or entire database easily and can be recovered whenever required by organization.
• Sharing: DBMS provides number of choice to convert database results into another form so that data can be shared easily as accordance to requirements of Tom & Jerry Ltd (Varshney, 2012).

Disadvantages

• Complexity: DBMS and database may be ambiguous with increasing size of complexity and data communication process in organization.
• Cost: initial implementation and maintenance cost may be high for organization in comparison of traditional approaches. Also cost may be affected due to hiring of experts and developers.  

Task 2

2.1 Analysis of database developmental methodology

In order to develop a good database for Tom & Jerry Ltd, number of features must be included in design and development so that a high performance and less redundant data can be achieved with a strong security. For that, following points are expected to include in database development to ensure a good database:

• Completeness: database should be capable to hold all type of required data related to operations in Tom & Jerry Ltd so that end-user can store and retrieve data in a complete manner and need not to deal with incomplete values and results from database.
• Integrity: Database must need to hold the data in accurate and consistent manner so that user always retrieves the right and valid data. However, this is also the characteristic of database management system to provide the integrity in database.
• Flexibility: database design should be simple and flexible to store and retrieve data with minimum efforts. Flexibility also means to ensure the interoperability of data among various cells.
• Responsive: Database should be simple and effective to enhance the response time in retrieval of data and storage (Bakalash.et.al.2013).
• Accessibility: Database design is expected to have simple user interface and understandable form so that user can easily navigate to required data.

SSADM structured systems analysis and design methodology:

To include the above features in database development, SSADM can be used which is a set of analysis procedures and design methods to develop a new computer application programs in United Kingdom. SSADM is different from the traditional approaches in development and uses the stage-based model to lead the development in modules and partitions. It is preferred due to following features:

• Modularization of whole process where each unit is capable to carry a specific task.
• Diagrams based visual presentation and modelling of information is useful in requirement gathering and system design.
• Sequential development stages those are easier for users and developers.

According to above described features, Tom & Jerry Ltd will get the following benefits with SSADM:

• Use of resources: Organization will be capable to gather information from technical and nontechnical staffs regarding the design because the visual presentation will help to achieve involvement of employees to meet their requirements in database design.
• Time saving and performance:  It will help Tom & Jerry Ltd to collect and present the data without ambiguity. Thus, most of time will be saved in redone of work to meet the user specification as they can be cleared with modelling of provisional system. Also the performance will be enhanced due to modularity in development.
• Maintenance: Subparts can be upgraded and maintained easily without making whole database design again (Pappas.et.al.2014).

However, there are few points those may arise in form of barriers to develop with SSADM:

• Exceed analyze may divert toward ambiguities in requirements.
• Larger design may be difficult to understand due to unclear and complex nature.
• Overall cost may exceed in development than the traditional approach used by Tom & Jerry Ltd.

Stage in SSADM

Stage 0: feasibility study

This step is useful to identify investment limits, technical skills and business opportunities of Tom & Jerry Ltd regarding the development of new system. It also indentifies the requirements and possible solutions to ensure the development in a feasible manner. Data flow and entity relational diagrams, catalogues and other documents related to methods and techniques are development to complete feasible study. Feasible report is generated at end of study which contains the answer of following points:

• Scope of development
• Problems to deal with.
• Possible solutions.
• The best one in comparison of current state of Tom &Jerry Ltd.

Stage 1: Investigation of current environment

This stage is useful to examine the current environment under which need of new development is expected. Investigation mainly focuses on the data and process used in current environment to accomplish the operations in Tom & Jerry Ltd (Heiser.et.al.2013). Following steps may be carried out to complete the requirement gathering in current environment:

• Recognition of data and processing methods.
• Documentation of physical and logical data flow models.
• Set of problems in current environment
• Log of user activities and practices.

As described in case, the stock file activities and sample data will be examined in this stage.

Stage 2: Business system options (BSO)

BSO is helpful to propose a new system within the consideration on dependency of Tom & Jerry Ltd on data, methods for input and output and results. Textual information along with DFD and RFD can be used to propose the solution in form of new system. BSO may contain options for approval at user side as:

• Choices from minimum requirement based system
• Options for every new requirement of users.
• Minimum requirements and new choices.
• Additional information and choices also can be used as option for new system but it may lead the ambiguity in users to decide the right one. BSO also consider information regarding:
• Cost and benefits with each option for new system.
• Overall impact associated with implementation of new system.
• Required time to accomplish development of choice.
• Constraints and barriers in progress.
• Scheduling of sub modules parallel to impact of Tom & Jerry Ltd (Bakalash.et.al.2013).

By considering the above facts, recommended solution is proposed to organization.

Stage 3: Requirement specification

After selecting the BSO model, requirement gathering phase for data inputs, processing and output start for selected options. Existing information is modifies to cover the requirements and security and privacy like features are considered in account to specify the requirements. Also input- output structure and system dialogues are prepared using requirements. Rational data analysis can be carried out to ensure the validity of ERD. Addition to it, erroneous activities and practices are prototyped to as additional requirements for new system development. All the entities and attributes are checked and validated with entity-event modelling which is also helpful to map events and trigger as according to requirements of end users in Tom & Jerry Ltd. All the gathered information and requirements are documented with details and diagrams.

Stage 4: Technical system options

Cost, facilities, performance and supports are treated as the constraints to decide the different options for new system design. This is much of similar to BSO and offers number of choices those are prepared on the basis of constraints to decide the new system. However, all the options are technical and may not be understandable at end user of Tom & Jerry Ltd. Experts can help them to decide the best one to meet their requirements (Singer, 2016). This stage is liable to select the design which is technically and feasibly suited in organization.  

Stage 5: Logical design

This stage will define the logical structure of data and processes in new development system so that appropriate actions can be understand with logical designs. In order to prepare the logical design, these steps can be considered:

• Normalization of entities in data representation.
• Detailed flow and execution of procedures.
• Interoperability of data and processes in system.
• Textual information about the fulfilment of user requirements and additional requirements added by developers in design along with details of design.

Stage 6: Physical design

SSADM ends with physical design which provides the instructions for physical implementation of system. Physical design deals with software settings and hardware positions. This stage mainly deals with physical arrangement of resources for database implementation and software based changes in system to ensure the database connectivity among all terminals. Mainly three factors will be considered to implement the design: storage of data, performance and processing characteristics (Pappas.et.al.2014). Clustering, normalization and indexing like strategies are made in physical design.

2.2 Entity-relationship modelling and normalisation

Data modelling: Data modelling is the technique to present the data flow and procedures in a graphical manner. Data modelling shows the relationship among different type of data being used in design. It is normally considered as the first step of database designing which is a progression from conceptual design to logical and then to physical schema.

Entity-relationship model: Entity-relationship model is a used to show entities and their relationship with each other. Individual entities can be identified to determine the relationship among them and the analysis of entities can be used to draw ERD (Hoffer, 2012). There are number of benefits associated with entity-relationship diagrams:

• ER diagrams are straightforward to understand the relational model with easy representation.
• ER diagrams can be transformed to other data representation models easily.
• Graphical representation is easy to understand.

However, model has no industrial notations and mainly used for high level designing works. These are the basic limitations associated with ER diagram. But still ER diagram is effective to handle most of data representation in development.

Components of ER Diagram

• Entity: it is the small unit object in database which is represented within a rectangle. Ex: employee or product.
• Attribute: It is property of an entity. It is represented with an eclipse. Ex: name, age etc.
• Key attribute: it is used to present main entities those are used to identify the record. Ex: primary key with eclipse and underline.
• Primary key: it is unique and not null to identify a record in table. Only one primary key can be in a table. Ex: employee ID
• Foreign key: one or more attribute of table those are similar with candidate key of other table. 

Relationship:

• One-to-one (1:1): each entity of a table can be related to only one entity of other table. Ex: one student can participate in one course only.

• One-to-many (1: N): one entity can relate to many entities of another table.

Ex: one student can participate in many courses.

• Many-to-many (N: N): many entities form a set can be related to many entities in other set. Ex: many students can participate in many courses (Tavares.et.al.2016).

Normalization of database

Normalization is the process to remove redundancy in data storage and to eliminate operational anomalies in insertion, update and delete. Normalization is used to following objectives:

• To remove the undesirable database operations in insertion, deletion and update (operation anomalies).
• To eliminate the chances in restructuring of database as life span of database application increases.
• Simplified relational model and database structure.
• Normalization can be done in several forms. Three basic forms of normalization are:

First normal form (1NF): A database can be said into first normal form if following features are exposed by it:

• Absence of multi valued attributes
•  Atomic attributes and entities
• Two columns are not similar in information.

Table contains the multi values attributes in corder column. Table with 1NF:

Second normal form (2NF): if database meet the following requirements, it is in 2NF.

• Database is in 1NF
• There is a primary key on which all other entities depend fully or partially.
• No functional dependency.

Third normal form (3NF): a table can be said into 3NF if:

Boyce-code normal form (BCNF): A database in 3NF may contain the anomalies in form of multiple candidates in a relation. Also composite candidates may not contain the attribute common to them. In this manner, overlapped candidate keys may be problematic in database. BCNF is introduced to remove such kind of anomalies from database. A database can be said in BCNF if:

• It follows the third normal form.
• For each functional dependency as X related to Y, X needs to be a super key of table (Coronel and Morris, 2016).

For example: Given table is in 3NF but not in BCNF as

Author name	nationality	book	Genre	pages
Shakespeare	English	Hamlet	Literature	150
Winand	Austrian	SQL Performance Explained	Textbook	250
Ullman	American	Database systems	Textbook	500
Schildt	American	Java Programming	Textbook	650

The table contains non-trivial functional dependencies as:

• author → nationality
• book→ genre, pages

The above table will be split into three tables when converted into BCNF:

Author name	nationality
Shakespeare	English
Winand	Austrian
Ullman	American
Schildt	American

Book	Genre	page
Hamlet	Literature	150
SQL Performance Explained	Textbook	250
Database systems	Textbook	500
Java Programming	Textbook	650

Author name	book
Shakespeare	Hamlet
Winand	SQL Performance Explained
Ullman	Database systems
Schildt	Java Programming

As there is a set of keys as {author name, book}

Forth Normalization Form (4NF): However, BCNF also contain some anomalies due to multi valued dependencies in database. A database is in 4NF if it follows the rules:

• Database is in third normalization form or in BCNF.
• No multi valued dependency.

Task 3

3.1  User requirements and database developmental cycle

Tom & Jerry Ltd is needs the database system to manage the customer related task easily as they are f

Database development cycle: Requirements analysis is already done and developer has enough knowledge about the required functions to develop database system (Singer, 2016). Organization will follow the steps shown in figure to make improvements in design and to complete design of database. Paper based designs will be implemented through programmable code and will be tested with the various sample data and load balancing techniques so that high performance and speed can be achieved. User’s feedback will be taken with operability of new system to make enhancements. New system will be tested for performance, reliability and security. Additional features like security, maintenance and interface will be discussed in life cycle of development to deliver a robust system. Prototype will be design to gather the end user views on design and interoperability with new system. Each module will be tested separately and then in combined form to ensure the proper function with other modules so that together they can result into a database system. In brief, following steps are followed to develop a new database system.

• Planning: requirements are collected to know the exact need of new system and problems in current system. Analysis of users and business activities to develop a robust system.
• Designing: selection of procedures and options to design the prototype. Logical coding of new system to understand the working.
• Evaluation: mark the changes in design and make it final. Users can be consulted to meet their skills and business requirements.

Finalize the design.

• Collect skill sets and technologies and estimate the expenses and times in new development of database.
• Design and test the database system with some real values and environment. Optimize the work to get better speed and security like load balancing and performance tuning (Coronel and Morris, 2016).
• Create documentation of work parallel to development so that future changes can be made easily.
• Operate and maintain the services. Clear the issues in installation and implementation process and log them in document for further reference.
• Highlight possible changes those can be made in future with changing requirements.

However, database development process is a cyclic process and demands the continuous changes and feedbacks to ensure the desirable application at end of cycle.

3.1 Design of database

Database consist mainly four tables those are unique to handle data within categories and easy to maintain the state of data during update and deletions.

Customers table with sample data

Product table

Invoice form is given below and contains the organization name and logo with proper address and product details (Kim.et.al.2015). The default format of invoice is A4 size paper

Form contains the checkbox and dropdowns to facilitate the user to enter the information quickly. Also, it prevents user to enter wrong information in fields. Textbox are configured only to accept text values whereas fields for numerical values are checked for their validity to ensure the numbers only with a specific format. In similar manner, data fields are protect from wrong values by providing calendar as popup. Main functions are mapped with buttons so users need not to deal with queries and database programs. Also validity of data is checked at database side in comparison of field type in database so prompts can be given to users to enter right information if it is wrong. Simple user interface helps the users to operate with multiple customers and invoices to take an action on bulk of records.

As the above window shows, invalid fields are marked with a red sign after them. Thus, user can not submit the information unless he completes the fields with required values. Also, the validity of data is check as seen in birth and income field. Therefore, user cannot enter the textual information in numerical fields and needs to provide the information in predesigned format. For instance, date of birth filed is configured to accept the information in dd-mm-yyyy format whereas income field contains the positive numbers only. In same manner, mail accounts can not a simple string without @ sign in between.

3.1  Effectiveness of database solution and methods of improvement for the Tom & Jerry LTD

New database program for Tom & Jerry is effective to deal with customers and invoices. Organization got the following benefits in comparison of traditional file based management:

• Performance: Organization is now capable to manage information in digital form and capable to handle task with more efficiency. Most of analytical results on monthly and weekly transactions are at one click away.
• Time saving: Tom & Jerry Ltd has saved a lot time to feed the information in files. Also automatic alarms on decreasing stock values and increasing dues on payments help the organization to save time on calculation and review of records. Due to the automatic functions, organization is capable to invest business time in other activities.
• Security: digital data is more efficient to recover and backup than traditional paper files. Also there are no chances of wrong values in database through the forms and it is easy to manage access on database for different users (Begrajka.et.al.2014).
• Surety: Now data accuracy and consistency is sure for organization. Also the data availability is sure in any time if there is no failure at main server.

However, the system has fulfilled the demands of organization, but there are few things that may be considered for improvements:

• Implementation of database in enterprise level: If organization needs to maintain the same database connected to number of departments, then there is possibility that data may not be stored or retrieved in updated form due to simultaneous access to database.
• Access to database: login facilities can be provided and time of session can be set to prevent the unauthorised logins and modifications to database.
• Auto backup: Database system can be configured for automatic backup of data after a fix interval so that in failures, most recent data can be restored to database.

Improvements can be made on elimination of data fields those are redundant in database so that performance advantages can be achieved with secure update (Tavares.et.al.2016).

3.2  User manual and technical documentation

User manual contains simplified information to guide the knowledge of users to use the system. It may contain textual information as well as graphics to help the users.

User manual: Some of the key features and interfaces are defined below to facilitate the users to start the use of system. Also some of key points are discussed to inform the features and usability of them.

Login system: users need to provide there password and username to access the database application. If password is not known or account is not registered, please contact administrator.

Current login system only supports the username which is combination of text and numbers but does not contain special symbols. It is normally the mail ID or mobile number associated with your profile stored in database of management. Password is minimum of eight characters length which combination of small and uppercase latter with numbers.

Main window: This is main application window which contains button for common task and provides the overall view of database as in number of customers, due amount, upcoming events and graphs for analysis. Each can be click to view more.

To create a new customer, select “new customer” button and provide the information. Also context menu can be used to select operation on database entries. Invoice and print buttons can be used to generate hardcopies of records of multiple customers. At right hand side, user can view the notifications on processes and operations (Heiser.et.al.2013). Following feature are integrated in main window:

• Create a new customer
• Insert product details
• View summary
• Last 10 transactions
• New invoice etc.

Configuration window: It can be opened from upper menu bar. Go to tools > settings to open the configuration window. Here you can-

• Change the invoice settings like logo, name. Contact details, address or format etc.
• Change the layout of main screen, fonts, and colours.
• Notification settings can be managed here.
• Account login settings with verification to mail.
• Backup setting and format, interval, scheduling etc.

Technical documentation:

It contains the information for developers so that future changes can be made easily. It may contain information of technologies and methods used to achieve the desired functionality in database programs.

Relationships:

• C_Key_id----in_cid
• Pid----in_cid----pyid
• Pyid---in_no

When a user try to login the system, username and password are matched with the account created in administrator database. Passwords are stored in MD5 hash and cannot be retrieved in original form. Login window appears only after the successful connection to management database named as users. Then main window get the connectivity with original database of organization and retrieves the values from different tables to present at main form.

Framework:

• Application program: Microsoft Access 2007 or later
• Front end: visual studio to create forms and database connectivity (Valacich.et.al.2015)

Minimum system requirements:

• Windows  XP
• 512 MB RAM
• 1.2 GHz processor
• 4GB external storage
• LAN connectivity
• Attachments

Referemces

Books and Journals

Aguilera, A., Borjas, L., Rodriguez, R. and Tineo, L., 2013, October. Experiences on fuzzy DBMS: Implementation and use. In Computing Conference (CLEI), 2013 XXXIX Latin American (pp. 1-8). IEEE.
Bakalash, R., Shaked, G. and Caspi, J., Yanicklo Technology Limited Liability Company, 2013. Database management system (DBMS) employee relations datastore and a multi-dimensional database (MDDB) for servicing query statements in a manner transparent to client machine. U.S. Patent 8,452,804.
Bakar, W.A.B.W.A., Abdullah, Z.B., Saman, M.Y.B.M., Jalil, M.M.B.A., Man, M.B. and Herawan, T., 2015, August. Vertical Association Rule Mining: Case study implementation with relational DBMS. In Technology Management and Emerging Technologies (ISTMET), 2015 International Symposium on (pp. 279-284). IEEE.
Baumann, P., Dumitru, A.M. and Merticariu, V., 2013, August. The array database that is not a database: file based array query answering in rasdaman. In International Symposium on Spatial and Temporal Databases(pp. 478-483). Springer Berlin Heidelberg.
Begrajka, D., Sogani, A. and Jain, A., 2014. Performance Enhancement of Database Driven Technique using Cynosure Method in Cloud. International Journal of Computer Applications, 103(13).
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.
Elovici, Y., Waisenberg, R. and Shmueli, E., Ben-Gurion University Of The Negev and Development Authority, 2015. Method and system for database encryption. U.S. Patent Application 14/622,961.
Heiser, G., Le Sueur, E., Danis, A., Budzynowski, A., Salomie, T.L. and Alonso, G., 2013, April. RapiLog: reducing system complexity through verification. In Proceedings of the 8th ACM European Conference on Computer Systems (pp. 323-336). ACM.