Unit 17 Database Design and Concept Assignment

Program	Diploma in Computing and System Development
Unit Number and Title	Unit 17 Database Design and Concept
QFC Level	Level 4

Introduction

Databases are useful to store the data in well managed form so that data consistency and integrity can be managed during operations. The unit 17 database design and concept assignment will discuss key issues of database along with major application in real world. The report will also identify the features and benefits of database in an organization. Later report will describe the development methodology for database development in Tom & Jerry Ltd. Report will also list and explain the details of components of entity relationship diagram and normalization forms. Report will present and use the user requirements in development life cycle of database. The report will indicate the effectiveness of database along with some future changes. It will also provide the user manual and developer documents for system.

Task 1

1.1 Key issues and application of databases within organization environment

Database is systematic collection of data to offer efficiency in data storage and retrieval. Normally databases are not accessed directly because there is possibility of erroneous data insertions and deletion. Also the data may exist in redundant manner. Database management systems (DBMS) resolve these issues with enough security and validation in data operations. A database in organization may suffer with following issues:

• Data security: In an organization, database must be accessible only to certain employees those have permissions and rights to operate on data. The database with improper security on access may lead to disclose organizational information to competitors as most of information is stored in database. The centralization of data in database may be an issue in case of theft and lost.
• Data integrity: Number of users may have access on single database so it may be difficult to manage the data consistency and integrity in database (Özsu and Valduriez, 2011). Al though, the data partitioning and fragmentation may help the organization to keep the data integrated. Database standardization is required to keep the data consistent during operations.
• Concurrency: Another possibility of issue is associated with number of parallel operations on same data. It may be difficult to manage priority of operations on data from users. The failure of concurrency control may lead the database crash or inconsistent values in it. The concurrency management
• Replication: Data replication through database is effective to restore the data during lost but it is difficult to update the every copy of data which is distributed into partitions of database. For an organization, data replication at multiple databases and storage may be an issue of update and insertion. Data replication also may be problem for organization to retain the original copy of data incase if they face the differences in data (O'neil, 2014).

However, the above issues can be solved with proper implementation of database in organizations. The capabilities of database have solved the most of difficulties related to data management. Therefore, databases are used in every industry like:

• Banking: Banking is the vast application of database system which is used to store the information of customers as well as of employees to regulate the organization effectively. The daily transactions and related information, customer details and timely insertions to accounts etc are not possible without the use of database in workplace of banking sectors.
• E-commerce:  Business strategy are going online on the behalf of security and management of data with databases management system. They also need to store the large amount of information related to their customers and products which is only seems possible with database (Han.et.al.2011).
• Schools and Colleges: Academics are using database applications to store and retrieve the information on demand as they are moving from traditional system to modern data storage system for security and integrity purpose. Databases connected to internet provide the information with correct logins so that user can access information from anywhere and anytime.

1.2 Features and advantages of database management systems

Database management system helps organizations to achieve high efficiency and effectiveness in work. It offers the facilities to organizations for being productive and responsive with time. Main features with database management system are:

• Stability: Database management system provides robust font end to access database which helps to eliminate the data insertions errors and update problems. Erroneous user activities can be handles properly with implementation of database management system. It also helps to prevent the loss of data during incomplete operations.
• Data structuring: Database management system helps to store the data in atomic format with reduced data redundancy to enhance the storage and retrieval time of data. Data can be organized well with consistency (Coronel and Morris, 2016). The structuring of database helps to manage the data in effective manner for later use and presentation.
• Security: Database management system helps the organization to keep database secure with use of credentials to access. DBMS isolates the access to database from unauthorised users in organization and network of communication among employees.
• Large databases: It is difficult to manage large size databases with manual process. DBMS solves this issue effectively with easy to use steps. It is capable to determine the duplications in data, searching of data and consistent modification of data in database. Large size databases can be managed with power of database management systems.
• Faster-smarter: Database management systems are capable to handle larger data for specific operations. Also they are smart enough to determine the use of data tables and values in other operations before to modification. In this manner, they manage the integrity of data during parallel operations. They are faster to handle databases with thousand of unique records.

The above features of databases management system bring the following advantages in any organization. The advantages itself describe the effectiveness of database management system into workplace.

• Redundancy management: Database may contain many records and it is not possible to validate the field for their uniqueness and other features. DBMS solves this issue with proper management of data redundancy in database and saves the storage space with elimination of repeated information from database (Grefen.et.al.2012). The elimination of data redundancy helps to keep the data consistence during operations.
• Data sharing: DBMS is effective to define the users those can access the database so that unauthorized users can be prevented from data access. Also it offers the facility to log the user activities on database so that roll back can be done effectively to recover original data.
• Consistency: DBMS is powerful solution to manage multiple operations on data by providing priority or locking system so that information can be kept consistent state in every transaction. Consistency means to retrieve always the right values from database and no operation should be performed on older values when concurrent operations are beings on same data.
• Integrity: Database management system ensures that database always contain the integrated data into fields. For instance, date type attribute only can contain date value. Also the data is encrypted for storage and sharing to keep it integrated (Weidema.et.al.2013).

Task 2

2.1 Database developmental methodology for Tom and Jerry

Database development methodology helps to modularize the development work so that each part can separately developed to meet the expectations with database system. For Tom & Jerry Ltd, following design methodology can be used for faster and robust development with requirements.

Structured System Analysis and Design Methodology (SSADM)- The flexible and accessibility of SSADM helps to develop the system according to user requirements. The methodology is modularised in manageable components so that team members and talents can be used properly towards high productivity and performance. SSADM is selected as methodology for Tom & Jerry Ltd because it has visual presentation tools to generate graphical information for users to collect the right requirements within time. Also it has sequential stages those are progressive to achieve entire developed system. The modularization helps to make the database easy to develop in manageable chunks. The stages of SSADM are discussed below in reference of Tom & Jerry.

Stage 0: feasibility study- During feasibility study, the capabilities of Tom & Jerry Ltd will be identified in term of financial and technical support for database system. Also the business constraints and requirements will be identified to determine the problems to those a new database system is required (Rock-Evans, 2014). With feasible study, following information can be addressed:

• The quality and performance within financial and technical assets
• Set of problems in current system
• Set of possible solution to problems.

Stage 1: Investigation of current environment- The current working conditions and environment will be investigated to know the right solution for organization. The skills of employees, logical and physical data flow in workplace and activities to manage the data are considered as main activities of investigation in workplace. In this activity, sample data and stock files will be collected and examined.

Stage 2: Business system options (BSO)- In this stage, the functionality and scope of the database development will be analysed by Tom & Jerry with different number of business system options. According to constraints and business patterns the options will be selected to design and implement. During this stage, following requirements will be identified:

• Make a choice with minimum requirements in workplace
• Number of options available for every requirement of user
• The least requirement to fulfil in system.

Figure 1: SSADM stages for Tom & Jerry Ltd

Stage 3: Requirement specification- Once the requirement are clarified that stage 2, methodology can be used to determine the requirements for the selected BSO. Rational  data analysis  is made along with identification of data model, data processing model and prototyping. All the requirements for development process are determined at this stage via the user consultation and support (Sun, 2013).

Stage 4: Technical system options- BSO can be analysed for the technical solutions and skills to accomplish it in workplace. If the option is technically possible than all the technical approaches are identified to list the technical requirements for the development of system. Technical requirements always have the influence of business constraints and capital to invest in system.

Stage 5: Logical Design- In this stage, development team of Tom & Jerry Ltd needs to design the system with logical flow of control and data. The logical design of proposed system is required for the following reasons:

• It clarifies what data will be used and how it will flow for the proper execution in system. It is represented normally with flow diagrams and relationship diagrams for various entities.
• It is required to document the textual information of user requirements and demands and to show how they are being accomplished in system.

Stage 6: Physical design- The logical design of the system is used to bring the system into existence. The physical system will be developed according to the logical diagrams and data presentation. Tom & Jerry Ltd will get full working system in this stage where the implementation will be dependent on three things- mainly on data storage development, data processing mechanism and performance and the characteristics of system according to users (Weidema.et.al.2013).

2.2 Entity-relationship (ER) modelling and normalisation

The relationship among entities can be represented with ER diagram which is capable to present the basic operational detail clearly. To create the entity relationship model for Tom & Jerry Ltd, it is necessary to determine the entities and their processing in logical design.

Components of ER diagram

Entity: Entity represents the real world object those may be animated or unanimated but are easily identifiable. In relation database design, entities are represents with rectangle. For instance, In Tom & Jerry Ltd entities are employees, date, mobile number and address etc.

Attribute: The property of an entity is called attribute. Attributes describe the entity in real world or database development. For instance, a number has no meaning until it is not describe as amount or mobile number. In Tom & Jerry Ltd, attributes are employee name, mobile number etc (Thalheim, 2013). Attributes are presented with eclipse shape in ER model. Attributes may be of many types like:

• Simple: Attributes those contain atomic entities and not further devisable into smaller entities are called simple attribute. For instance, employee’s mobile number is an atomic value with 10 digits. Simple attribute has single eclipse as identification in entity relationship diagram.
• Composite attribute: Attributes those represent the set of entities and are further divisible are composite in nature. For instance, employee name is an attribute which can be sub-partitioned into first name and last name.
• Derived attributes: Attributes those are derived from other attributes in database are derived attributes. For instance, the average salary is a derived attribute which is calculated based on employee salary attribute.
• Single valued: Attribute with single values are single valued attribute. Social security number is example of single valued attribute (Bagui and Earp, 2011).
• Multi valued: Attributes with more than one values in single cell of database table are multi valued attribute. An employee in Tom & Jerry Ltd may have more than one mobile numbers or addresses.

Key: Key is an attribute which can uniquely identify the records or entity in database. Keys are required to access the record easily and effectively from large set of records.

• Super key: Set of attributes which can identify the record from table. For instance, in a small class of school, student name and father name can be used as key to identify the student exactly. It works if there are no two students with same name of father and same name of themselves.
• Candidate key: A minimum super key is called candidate key. A table may contain more than one candidate keys. For instance, mail id and department phone number may be used as candidate key if they are not shared among others in workplace.
• Primary key: A not null and unique key is database table is primary key which is capable to determine the record effectively. For instance, employee id in Tom & Jerry Ltd is primary key in employee table (Elmasri and Navathe,  2011).

Relationship: Entities may be related to each other with relations. The relationship in ER diagram may exist in following manner:

• One to one relationship (1:1): One entity of a table is only related to one entity of another table. For example, one employee may be associated to only one department in Tom & Jerry Ltd.

• One-to-many (1: M): One entity mat has relation with more than one entities in another table. For instance, a manager may handle more than one department in company.

• Many-to-many (M: N): most of entities in table may be related to most of entities in another table. For instance, many employees may be in many activities in workplace.

Normalization of database- The process to remove redundant data and to solve the issues of update and insertion anomalies is called normalization of database. Normalization helps to keep the information well organized and structured so that update, insertion and deletion can be done effectively on database (Van Gigch, 2013). Three basic forms of normalization are given below:

First Normal form (1NF): According to first normal form, every column of table may contain single value. There should be no set of  information and knowledge  for specific attribute. For instance, the employee table from Tom & Jerry Ltd database is not in first normal form as department attribute contains set of information. The adjacent table is modified table which is in first normal form.

Second normal form (2NF): A table of database is in second normal form if it is already in first normal form and it has a candidate key on which all other attributes are dependent for identification. For instance in product table, every table contain single data but product ID is not unique for products and Brand has no relation with product ID (Teorey.et.al.2011). Therefore, table can be split into more tables to meet criteria of second normal form. 

Third normal form (3NF): A table is in third normal form if it follows the criteria of second normal form and it has no transitive dependency among attributes. A transitive dependency means that an attribute X depends on Y and Y depends on Z for identification. Henceforth, X is dependent of Z as X→Y→Z. For instance, in below table zip is effective to determine street, city and state attribute (Bagui and Earp, 2011).

The employee table in third normal form:

Task 3

3.1 User requirements and database developmental cycle

In order to design the database system for Tom & Jerry, it is necessary to document the requirements of user and management so that system can be designed with desired functionality and features. Tom & Jerry Ltd has provided the following requirements for database development:

• New database system must need to reflect the processing of traditional approach in workplace so that training and development cost associated with employees can be reduced.
• There should be form based interaction to perform most of operation with data.
• User’s erroneous activities should be validated before the actual data insertion and update process.
• Invoice should be generated for every new order. If items are not present in inventory, then backorder list should be generated (Clarke, 2010).
• Tom & Jerry Ltd is expecting to have direct options for printing of reports and other analytical results from database.

Applying database development cycle for Tom & Jerry Ltd

Planning- During planning phase, it is identifies which type of scope the new system can bring for Tom & Jerry Ltd and what will be resources to complete the database system. It is also identified how it will aid functionality in workplace to increase productivity and quality.

System definition- At this stage of development cycle, the business definition of Tom & Jerry is taken in count. The business limitations of organization in term of  managing financial  and technical support are determined. The organization can provide the information about following things to help the development team to vision the system definition:

• The structure of organization for work processing and data flow and the relationship mode among levels in organization.
• The skills set available in workplace to help the development team.
• The workplace conditions in which database is expected to work to meet present and future requirements of Tom & Jerry Ltd (Ruparelia, 2010).

Requirement collection and Analysis- Requirements are collected from user and working environment to determine the actual functioning and processing of data in Tom & Jerry. It includes the data collection, feature listing and processing methods etc. Existing system and activities can be analysed for new system along with any other information which may be helpful for design process.

Database design- Design phase of database may go through three model- conceptual model, logical model and physical model. In conceptual model, database design is overview and not in depth information is provided about implementation and data arrangement. In logical model, database is prepared as logical diagrams and entities those are then transform to original implementation form in physical model. Logical model of data shows the data and operations for actual design. All the queries, forms and functions are created according to requirement of Tom & Jerry Ltd in physical design of database.

Database management system selection- Tom & Jerry can choose a specific database management system to use on database for interface and interaction but it is optional and not applicable in Microsoft access database system as it is itself a powerful database management tool. Other options are: Oracle, MySQL and Microsoft SQL server (Munassar and Govardhan, 2010).

Application design- There may be choice of an organization for the custom application to interact with database. Although, Tom & Jerry staff is skilled enough to handle the database with Access application as it provides the features for graphical interface also. Forms and reports are effective to interact with users.

Prototyping- Visual presentational model of actual design will be helpful for Tom & Jerry Ltd to determine the fulfilment of requirements in development. Prototype is useful for non technical staff to help in development process. With prototype, most of staff from Tom & Jerry can contributes in development of system.

Implementations- In it, the designed database solution is implemented in workplace conditions. The workplace and target machines are determined for implementation process. It may include target application, supporting tools and services along with changes in workplace arrangements.

Data Conversion and Loading- The actual database file for Tom & Jerry Ltd will be loaded into system with adequate data conversions according to system configurations and interfaces (Stark, 2015). It includes the processing of data and methods for right functioning of database system.

Testing- The implemented database is tested with some techniques to identify the bugs, errors in code, data validation and processing. The system is also tested for the data consistency and security in multiple operations. The system testing is required to keep system performance and security in workplace.

Operational maintenance- It may be possible that organization may need some changes in present solution to meet future requirements. For that it is necessary to keep system development related documents and decisions. In operational maintenance, the documents and manuals are prepared to assist Tom & Jerry Ltd to add the functionality in system later with knowledge of existing system (Wang and Li, 2010).

3.2 Design of database

Customer table

Product table

Payment table

Invoice table

Forms

Customer form

Product Form

Invoice Form

Payment form

Common Queries for user accessibility

a) Query to search customer by name or ID

SELECT c_id, c_name, c_mail, c_mob, c_order, c_amount, c_due, c_order_date

FROM customers WHERE c_id=1 OR c_name= "Jill";

b) Query to find out all the records in which due date is 28 days before.

SELECT py_cust, py_prod, py_amount, py_due, py_invoice_no, py_due_date FROM payment WHERE py_due_date>"28";

Reports in Database

Payment table report for due date

Product report

Validation and Verification techniques

Each table is verified for the right data type of field and their length to store the data. Fields are properly assigned with required data types. For instance, fields with date are of date/ time type. Also number fields are properly formatted to store decimal, normal and floating values in database (Debenham, 2012). Error messages are used to prompt the user for invalid data or format of data. Forms and reports are designed with error proof interface to store data and to operate with database effectively. For instance, payment table is given below with proper data types for attributes.

                   Figure 2: Payment table with data types

3.3 Effectiveness of database solution and improvements

The database solution is effective in Tom & Jerry Ltd as it is capable to deliver the following efficiency in work of organization.

• Efficiencies in operations: Tom & Jerry Ltd has got the benefits of easily accessible data. Organization has achieved high performance and productivity due to easy information retrieval and storage. In this manner, database has saved a lot of business time of organization to deal with data and data storage.
• Now data is easily available and accessible in various departments of Tom & Jerry. So employees have saved a lot of effort and communication cost for specific data sharing and use.
• Performance and security has increased due to digitalization of business process. Data can be saved easily and effectively for security purpose (Brown and Harvey, 2011).
• Usability has increased in workplace because employees can work independently and can share information easily in other formats and with other employees within time.

Behind the features and benefits of database, there are something which must be noted for the future use and improvement of database solution:

• In future the scalability of Tom & Jerry Ltd to enrol more employees and process like in enterprise business may be issue with capability of database system.
• With increased sharing of database, the system may not be capable to provide enough security. In such case, organization can use more flexible system which can work under sharing among number of users.
• The integration of third party plug-in and software may not support with Microsoft access.

3.4 User manual and technical documentation

User manual

Introduction- The information of Tom & Jerry Ltd is stored in database which can be accessed only with credentials provided by administrator.

Main screens- The main screen of system is helpful to overview the different activities and functions of Tom & Jerry Ltd as shown below. It helps to manage the information of employees and products easily.

                                             Figure 3: Graphical detail on application

FAQs

1. How to insert new products in database?

Open Product form from left panel. Fill entries for new product and click to save.

1. how tHoHow to generate report on employees? (Vita.et.al.2010)

Open employee table and click on report function. Select desired columns and criteria in report. Click finish.

Technical document

The document is designed for developer to assist them in new changes to meet future requirements of workplace.

Framework

OS: windows & or later

DBMS: Microsoft access 2007 or later

RAM: minimum of 1 GB

CPU: 1.3 GHz (64 bit architecture is recommended)

Space: Min 10 GB.

Relationships among tables

                                                       Figure 4: database relationships

Security- MD5 is used to store the credentials of user. They also can login with windows password system.

Testing results

Development methodology: SSADM

Test date: 21/04/2016

Test count: 15

Test successful: 12

Attachments

Conclusion

The report has been discussed the fundamentals of database system in the form of relationship models and normalisation. Later the requirements of Tom & Jerry Ltd have been listed and a methodology has been proposed for development of system. The report has determine the development process of database for organization and provided the documentation of work for future improvements. Snapshots of work have been attached in report as evidence for work.

References

Books and Journals

Bagui, S. and Earp, R., 2011. Database design using entity-relationship diagrams. CRC Press.
Brown, D.R. and Harvey, D., 2011. An experiential approach to organization development. Upper Saddle River, NJ: Prentice Hall.
Clarke, R., 2010, May. User requirements for cloud computing architecture. In Cluster, Cloud and Grid Computing (CCGrid), 2010 10th IEEE/ACM International Conference on (pp. 625-630). IEEE.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.
Debenham, J., 2012. Knowledge Engineering: Unifying Knowledge Base and Database Design. Springer Science & Business Media.
Grefen, P., Pernici, B. and Sánchez, G. eds., 2012. Database support for workflow management: the WIDE project (Vol. 491). Springer Science & Business Media.
Han, J., Pei, J. and Kamber, M., 2011. Data mining: concepts and techniques. Elsevier.
Munassar, N.M.A. and Govardhan, A., 2010. A comparison between five models of software engineering. IJCSI, 5, pp.95-101.
O'neil, P., 2014. DATABASE: principles programming performance. Morgan Kaufmann.
Özsu, M.T. and Valduriez, P., 2011. Principles of distributed database systems. Springer Science & Business Media.
Rock-Evans, R., 2014. Data Modelling and Process Modelling using the most popular Methods: Covering SSADM, Yourdon, Inforem, Bachman, Information Engineering and'Activity/Object'Diagramming Techniques. Butterworth-Heinemann.
Ruparelia, N.B., 2010. Software development lifecycle models. ACM SIGSOFT Software Engineering Notes, 35(3), pp.8-13.
Stark, J., 2015. Product lifecycle management. In Product Lifecycle Management (pp. 1-29). Springer International Publishing.
Sun, Z., 2013, March. User Involvement in System Development Process. In I Proceedings of the 2nd International Conference on Computer Science and Electronics Engineering (ICCSEE 2013) Paris: Atlantid Press.
Teorey, T.J., Lightstone, S.S., Nadeau, T. and Jagadish, H.V., 2011.Database modeling and design: logical design. Elsevier