Delivery in day(s): 5
Diploma in Computing and System Development
Unit Number and Title
Unit 17 Database Design and Concept
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.
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:
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:
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:
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.
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:
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:
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:
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).
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:
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.
Relationship: Entities may be related to each other with relations. The relationship in ER diagram may exist in following manner:
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:
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:
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:
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).
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
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
The database solution is effective in Tom & Jerry Ltd as it is capable to deliver the following efficiency in work of organization.
Behind the features and benefits of database, there are something which must be noted for the future use and improvement of database solution:
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
Open Product form from left panel. Fill entries for new product and click to save.
Open employee table and click on report function. Select desired columns and criteria in report. Click finish.
The document is designed for developer to assist them in new changes to meet future requirements of workplace.
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.
Development methodology: SSADM
Test date: 21/04/2016
Test count: 15
Test successful: 12
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.
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