Unit17 Database Design Concepts
BTEC HND in Computing and Systems Development
Student Name Wesenu Irko
Student No 14716
Table of contents
This assignment is based on the concept of database design, which is helpful for storing the data in a secured manner and avoiding the possibility of data redundancy. In this assignment, it is required for developing an understanding of the concepts and issues regarding the database management system as well as its design. Moreover, there is a requirement of practical skills for translating that previous understanding into the creation of all the complex databases and into the design as well. The objective of making this assignment is to understand the advantages, issues and applications of the DBMS and databases. In this assignment, there are 3 tasks which are required to be done, each of the tasks have various objectives, moreover, for completing this assignment, a research based on case study has been done. In addition to it, for giving an effective reflection and for getting the best outcome, various supportive materials such as websites, books and journals have been involved. This assignment is also based on the analysis of methodology related to the development of the database, for that, there is a need of database developer, who can analyze it and evaluate the entity relationshi
1.1 Analyzing the key issues and application of databases within the environment of organization:
A database can be referred to the structured system or management or organization of files in accordance with a specific data model. Relational database is the collection of data items, which are organized as a set of tables, the relational data model can be the base of relational databases. Furthermore, database management system or DBMS can be considered as an important aspect of data processing and data storing. DBMS involves various data models which are evolving into various software packages of DBMS (Siau and Rossi, 2011, p.250). There are various commercial DBMS available in the market such as ORACLE, SQL, INGRES and Sybase.
Replacement of the static files: There is a clear separation between the physical and logical representation of the data
Relational databases have another advantage of representing the domain universe
This DBMS system is easier as compared to other DBMS for adding, removing or deleting, updating and associating the data
With the help of relational database management system, it is an easy task for searching the specific data or for sorting them
Database management system can be termed as various open source along with the commercial implementations of the concept based on database
The model based on relational database management system, which can help to resolve the common errors such as duplication of data, redundancy of the unneeded data and non-optimized data relation.
Database is being used from the prospective of the model application such as mobile application and web application
Mobile application: Native mobile applications have been using the DBMS system for considering the limitations of mobile computing such as disk resources and battery. For example, SQLite, it is a kind of mobile friendly database
Web application: In web application, with the use of the architecture based on 3-tier, these applications are used as the platform of social media, education sector, banking services and many others as well. These areas of organization are utilizing the resources of database to manage their data.
There are various other examples, where the relational database management is used such as booking of air ticket, involve while functioning the login procedure of user. Moreover, it is used in the chat application as well while recording the conversations.
Key issues of databases faced by the environment of the organization are given below:
Centralized control: In DBMS, entire data of the organization are joined into a single database. This database falls under the control of a centralized management system which is known as Database Management System or DBMS, wherein, the storage device is not attached to a common CPU or central processing unit (Sein et al. 2011, p.50). This can be stored in a multiple number of computers, which are located in a same physical location or this can be dispersed over the network of the interconnected systems.
Backup and recovery: DBMS, moreover, provides platform or option of recovery and backup of data and information, if there is any failure condition occurs within the system such as power failure, disk crash and many others, which further help for recovering the database from the inconsistent state.
Data Redundancy: Redundancy of data can be referred to the duplication of data. The database is created in the system; the similar data can be required for storing or recording in many other files (Romney and Steinbart, 2012, p.12). Therefore, in that case, repetition of the information about the entity may not be avoided.
Concurrency problems: There may be a possibility of data inconsistency if more than two users are allowed for processing the database. Moreover, in the same case, when more than two users are trying to update the same data element, which is shared among them, then this causes data inconsistency (Besnard et al.2012, 216).
Sharing data: This system allows the user, who can share the data as many numbers of the application programs under the control of DBMS. This can be meant as accessing the data by involving multiple users at the same time.
Application of database management system within the organizational environment
DBMS has been widely used in various areas of business organization, such as e-commerce, Human resource and many other as well; it is due to the reason that this system has various advantages. Various applications of this management system are given below;
E-commerce: Today, the market of online business has been growing since several years. Therefore, this application is in demand at the e-commerce level because databases can be used there for storing products along with the details of customer and transaction (Vitaet al. 2014, p.D406). Furthermore, the database is also used there for keeping the track of the online purchase and products via debit card or credit card for generating the monthly statement
Hospital: Here, this application is required for storing and maintaining the details of patient healthcare, details of the doctor, details regarding the salary and appointment of the employees. This database management system can help the hospital by providing good services to their patients.
Banking: This organization uses the DBMS for loans, accounts, other transactions and customer inquiry. The database can be well designed; moreover, it can control all the branch operation regarding the customer transaction (La Rosa et al. 2011, p.7030).
Human resource management: The department of human resource uses this application for storing and managing the information about the employee, their salaries, taxes, benefits. Moreover, this application can also be used for generating the salary cheques.
Education: The education department needs to use a centralized system of DBMS for their entire use or purposes. Colleges, universities and school use this application for the course registration, admission details, result, including the information of faculty and administration department. The system of DBMS can further be used for managing the information regarding the student and staffs in all the areas related to school or college life such as progress report, fees or finance along with the payroll system, scheduling (Gray and Malins, 2016, p.190). In addition to the application DBMS in the education sector, other uses of it are for keeping the track or progress report of each student of every class, tracking the record of the assignment. Moreover, this application is useful for communicating with the whole-school via chat or email.
Unit17 Database Design Concepts
1.2 Critically evaluating the features and advantages of database management system:
Features of database management system:
DBMS is based on the interface of user friendly for managing the databases.
This system have the security feature as well; it is due to the reason that the DBMS enable the user for creating the database users with having different privileges while managing the databases.
Moreover, the DBMS system has external four features which are commonly known as ACID; A=Atomicity: Any failure condition regarding the hardware or failure during the transaction process, then the process will not be completed. Therefore, the transaction process will go back to the starting level of this process
C=consistency: The database allows input of only valid data within the database
I=Isolation, this can provide the platform, where multi simultaneous transactions can take place without making disturbance to each other
D=delete, after the successful completion of transaction, entire changes which are made to the system are considered as permanent.
There are several advantages of DBMS:
It provides control to the data redundancy, that is, duplication of the data
This system helps in sharing the data including the storage of data via the DBMS system, that enables the user for sharing the data either within the users of the database management system or via importing the data to the other applications, such as software applications or other system of DB
DBMS have the control over data consistency, that is, it is an allowance that only valid data can be written to the database.
This application have enough data storage
There is a wide use of project which are of either small size or of high level of enterprise
This application even offer those researches and database management which are based on the memory such as SAP Hana, OES (Oracle Enterprise Solution), Elastic search anfd any others as well at the same time.
The advantages of DBMS are given below:
DBMS can provide an abstract view of the data which can hide the important details
DBMS is considered as the good base or platform or mechanism of security and protection if there is any failure situation occurs regarding the system (Röglinger et al. 2012, p.330)
For managing the database management system, users are not required to write the entire procedures
DBMS is more efficient as compared to other data storing software for using in an ease way
This system has also a mechanism for recovering the crash
Furthermore, the DBMS can also take care of the concurrent access via using various forms of the locking system.
After analyzing the above benefits of database management system, the organization can install the application of this system for storing data of large volume; moreover, the data can be arranged in a systematic and secure manner as well (Moniruzzaman and Hossain, 2013, p.1030). Along with the above advantages, there are various advantages as well, serviceability, reliability, manageability and recoverability
LO2: Understanding the techniques of database design
2.1 Analyzing the database developmental methodology for the given case study:
According to the given case scenario, the company Schoengalleric Art, which is an exhibition performing company, has been lacking in their communication system and sharing of information as well, this is required for the company. Therefore, the director of the company has an idea of converting their offline way of managing the data into online way of storing and managing the data by installing the DBMS (Anderson and Shattuck, 2012, p.20). Due to this reason, they are required to analyze the methodology for the database development and SSADM methodology can be used for the given case scenario.
For designing the database, there are various properties, which are essential for having good design along with the implementation of that database. Those properties are usability; this can help the administrator and technician of the company, completeness, efficiency, flexibility, integrity.
Structured Systems Analysis and Design Methodology: SSADM is useful for the given company because it can help in analyzing and designing the methodology. Moreover, if the Schoengalleric Art can choose this methodology, then the mentioned company does not require any implementation, testing, review and maintenance regarding this methodology or approach. The company can insert the details of the company structure such as director, manager, administrator, technician, preparator, gallery assistant and booking assistant. With the help of this approach, the company can make this hierarchy in exact details and can divide this structure into phases and sub-phases (Creswell and Poth, 2017, p.13). This can allow the tools of project management, which can be used within the methodology.
This SSADM carry specific characteristics, which can help the technician of the chosen company for ensuring the benefits of this methodology for installing the DBMS within their organization (Refer to appendix 1). The specific characters of SSADM are given below:
With the help of SSADM, the company named Schoengalleric Art can perform the activities in a sequential manner
This methodology can easily be understood by the developers such as technician and administrator of the company as well as by the client of the same company
As the above methodology deals with the diagrammatic representation along with various techniques of modeling, therefore, the company can make their art gallery or collection of art in a more attractive manner
SSADM also deals with specific requirement followed by design of system stage, therefore, technician of the Schoengalleric Art company can oversee all the requirements regarding the technical issue in regards to installation of exhibition
2.2 Discussing the entity relationship modeling and the normalization for the give case study
In accordance with the given scenario, entity relationship can be of three types, which need to be followed within the technical department of Scheolongalleric Art Company, such as one to one, one to many and moreover, many too many. In this case, one to one: manager to administrator, one to many: administrator to various clients of the company and many to many: technician assistants to various clients.
Entity relationship: This is a type of data modeling process or technique where graphical representation of the entire entities along with the relationship between the entities is drawn. This model can help the chosen company for maintaining the relationship with their client (Shigley, 2011, p.23). While implementing the ER diagram, the Scheolongalleric Art can understand the importance of the ER diagram, which has its own way of creating such as identifying the entities, here entities are manager, director, preparator, gallery assistant, administrator, booking assistant, technician and the client. Determining all the important interactions such as interaction between the technician and the assistant technician or booking assistant and client, entity relationship is required.
- Various components of E-R diagram, which needs to be understood first before having the entity relationship, entity: employee, manager, department and products such as Exhibition or art gallery are considered as entity. Attributes such as name of the employee, id of the departmental staffs including their age and many others can be used by the company. Key attributes such as important characteristics of the entity (Gray and Malins, 2016, p.78). The primary key is considered as the candidate key, which is used to identify the entity uniquely, there is various candidate key, but there is only one primary key. A Foreign key is used for an attribute or set of attributes of one table, which needs to be matched with the candidate key.
- One to one
Here, in this case, scenario, client can have only one access, that is, they can contact the booking assistant department only
- One to many
According to the case scenario, booking assistant can interact with many customers.
- Many to many
In accordance with the case scenario, technician assistant can contact various departments such as head of the technical department, gallery assistant, administrator and many other as well.
- Normalizations for this company can be of 1NF, 2NF and 3NF (Shigley, 2011, p.22).
- Implementation of 1NF can help the company in respect of below aspects;
- The company can have unique attributes, such as there is no any attributes of multi-valued
Value of each attribute is atomic
This can help the company for reducing the duplicate entries of booking of art gallery
- Implementing the 2NF can help the company for decreasing the redundancy, increasing the efficiency along with the reduction of anomalies, which is also known as Normal Forms. As this company is medium sized company, therefore, normal form is required to be implemented within the environment.
LO3: Designing, creating and documenting the databases
3.1 Applying the database developmental cycle for developing a database solution for the given study:
According to the given scenario, the best-suited database developmental cycle to develop the solution for the database of Scheolongalleric Art Company is SDLC (System Development Life Cycle). This company is facing issues while communicating with their staffs and with their client, therefore, the company needs to apply SDLC model within their system. This model could be applied to the development of computer system such as the creation of a database for solving the problem of this company. This model can be used in this case for identifying the tasks, which are needed for both the input and output for each of the activity (Galliers and Leidner, 2014, p.89). Here, the activities can be described as managing the details of the staffs, booking details of the customer, salary details of the employee working in this company. This model can be used for meeting the objective of the activity, which is given below:
Establishing the requirements: This involves the requirements made by the consultation, stakeholder, technician, administrator, preparator of the company about the system, where they can input their queries and details. The data administrator of this company needs to play a vital role in this stage because they have to overview the ethical and legal issues within this organization which can impact on the requirements of the data.
Analysis: This phase gets started by considering the requirements statement and gets finished via producing the specification of the system such as DBMS application is required to be implemented within the system of this art gallery company. Here, for this company, under analysis, the conceptual data model can provide the shared and formal representation of the communication between the developer and clients.
Logical Design: In this case, there are various aspects of the database design and the implementation of the conceptual model, which depends on the DBMS which this company can use, that is, SDLC waterfall model (Refer to Appendix 2).
Implementation: This is the construction of the computer system in accordance with the given specification of the case scenario, such as security enforcement, storage schema and many others. In the DBMS, inputs or data such as details of the staffs are entered into the tables; issues such as communication gap among the employees and the process of the users are required to be addressed.
3.2 Designing a fully functional database including tables, queries, forms, validation, reports and verification technique along with various interactions in the forms such as check box, drop box and many others as well.3.2. But would be better if you could offer more information about your solution itself. That is, saying few things about it’s technology (like PHPMyAdmin, Mysql, Server), it’s users and user’s policy, back up strategy and a general maintenance table
Here it will be discussed about the staff details and booking details of a company. In order to do that, a table is created in the database Two structured elements,i.e. Fields and records are there in a table. The elements are used by the companies to specify the details. Column headings are represented as fields and rows represent records that are stored in the database application.
The screenshot above shows how a gallery table was created.
Queries which the data administrator of the company needs to use for retrieving the selected data from the table, moreover, they have to insert query, which must be based on SQL or Structured Query Language. Administrator and the technician search for queries to delete, modify or add new data in a new table, which can be deleted any point of time if the employee leaves or terminated. Creating new tables require query such as “CREATE Table” where you can add new entities and their attributes in VARCHAR (variable-character), INT(integer) and the size of string followed by the name. now the entities can be rearranged or moved depending upon the requirement. DELETE is initiated in such scenarios where one can simple remove the record from the table. This can be understood by the below figure.
The screenshot above shows how a gallery table was created.
Forms: The employee or the customers are not allowed to insert data and access database. Therefore, the form is the easiest and safest way of entering the data. It requires a bit of code based on the VB (Visual Basic Programming language) for error checking for preventing from those data which are formatted improperly (Vaishnavi and Kuechler, 2015, p.10). That improper format in this situation can be employee of one department such as assistant technician is entered into the Department of administrator
The table visits expressing the relation between the tables: gallery and clients is designed as follows:
Validation reports: Validation report is based on the query which selects all the data of the company including the employee, customer and booking details that the report requires from underlying the tables.
The SELECT statement is used to SELECT DATA FROM ONE or MORE tables:
Ex: SELECT column_name(s) FROM table_name
We can also select ALL columns from a table:
Ex: SELECT * FROM table_name
To update a data in a table:
SET column1=value, column2=value2,…
With the help of check box, the company can give the platform to the users for filling their choice of the art gallery for booking (Carl, 2012, p.90). It is used when there is more than one option for the users, such as buying floral art gallery or scenic art gallery.
A query to select a given customer among many customers is shown below:
Sample Table: emp_info
First Last ID Age City State
Mary Jones 99982 45 Payson Arizona
Eric Edwards 88232 25 Payson Arizona
Mary-Ann Edwards 88233 32 San Diego California
Ginger Howell 98002 32 Phoenix Arizona
Sebastian Smith 92001 47 Cottonwood Arizona
SELECT * from empinfo where first = ‘Eric’;
This will only show us the person who’s first name starts with ERIC in the Table
SELECT last, city, age from empinfo
where age > 30;
Similarly this will only show us those customers who is aged more than 30 in the table
That is, we have created a foreign key constraint for the table visits
Dropbox in this company can be used by the data administrator, because if the customer or when the company need to select details of only one employee among all the employee of one department. Moreover, when the customer needs to choose one gallery art among various art galleries then the administrator need to use the Dropbox, needs to be evaluated, shown in the below figure
An example of entering values to the table visits is given by the following forms:
Firstly, we need to insert gallery values:
And the correspondent row of the gallery table is:
And a concrete record for relating a specific client with clientID=1 with the Bijning North Gallery is shown below:And the correspondent record of the table visits is shown below:
3.3 Evaluating the effectiveness of the database solution and suggesting the methods of improvement for the chosen study
There are various analytical models or methods which the chosen company for evaluating the effectiveness of the database solution such as simulation modelling, queuing model, benchmarking and cost models. Queuing model if applied to this company for having an effective evaluation, then the dynamics of the database system can be studied. For example, when the administrator needs to insert the algorithm for transaction control, allocation and management of data in the distributed system (Majchrzak et al. 2014, p.18), this model is required. Whereas, if the model named as the simulation is applied, then this model might help the administrator for obtaining the better estimation (Robertson and Simonsen, 2012, p.18). It can help the chosen company. It is due to the reason that the details of this company can be summarised in an effective manner not only in isolation model.
In accordance with (Liu and Issa, 2012, p.378), the costs model can use by this company because this model is based on the study of the time of query processing which is an effective tool while designing the database and physical storage. Moreover, this model more emphasises on the performance of the database system along with the physical structure. Therefore, this model can consider as the real and highly effective model.
(Chomal and Saini, 2013, p.2252), have suggested various steps followed by the company for improving the performance of the database system of this company:
To make sure that the hosts which are serving the process of the database has availability of enough resources such as CPU, memory and disk space. Which means the computer system of this company is having faster CPU, the host is out of the memory or not? And disk space is more than enough.
Once, the platform of the database is healthy then, it is required to identify that which of the applications are accessing the database
Understanding the load and the individual responses time for each of the performance and service
There must be the determination of the highest number of the connections that the database of this company can tackle.
Keeping the information be tactful, there must not be any error occur regarding the network metrics. Therefore, the company can maintain the details the booking or order, made by the customer of the chosen company.
3.4 Providing the support user and the technical documentation of the database which is to be implemented from the perspective of given scenario3.4 Usually a user documentation for a database solution, is more oriented to the delivered solution. I do understand that you have already done this in
Support user of the database, which the chosen company can implement, where the customer can give feedback about the services provided by the company. Moreover, this use support can give an option for rating the company on the products and services of this company. In the user support manual, the customer, furthermore, can contact the customer support executive, if any error occurs during the booking process or delivery of their products such as art craft and many others (Kaur and Bhardwaj, 2012, p.740). There is various way of providing support to their use such as via email. Chat or telephone. This helps the customer for solving their issue in an effective and efficient manner. For resolving the issue of the customer, this company has to recruit a customer support executive. Moreover, the assistant technician can also help the customer for solving their queries or issues.
Technical documentation of the database includes defining the basis for the database design of the application, providing expected volumes of the data such as details of a large number of employees working in this company. In addition to it, there is the functional and nonfunctional usage of the tables and ensuring the transactions over database which meets or can exceed the performance requirements. Moreover, the technical documentation includes:
2.1 Information about the system: It specifies the configuration of the DBMS, configuration of hardware, utilities of database software and any supportive software if used in this company’s system
2.1.1 Configuration of the DBMS
Vendor Hardware Version Comments
Conrep Microsoft SQL Server 10g of Oracle Database Need to be updated within 12 months
2.1.2 Utilities of Database Software
Vendor Product Version Comments
SAP CODASYL DBMS I64 Can create, link and compile the database application
After analyzing the above case study, it has been concluded that the chosen company is required to get modified in respect of their communication system, computer system and booking procedure. Moreover, this company has been required to install the database management system within their computer system; therefore, they can communicate with each other in an efficient manner. This can improve the business success if the Shoengalleric company of China.
- Reference list:
Anderson, T. and Shattuck, J., 2012. Design-based research: A decade of progress in education research?. Educational researcher, 41(1), pp.16-25.
- Azhar, S., Khalfan, M. and Maqsood, T., 2015. Building information modelling (BIM): now and beyond. Construction Economics and Building, 12(4), pp.15-28.
- Besnard, J., Ruda, G.F., Setola, V., Abecassis, K., Rodriguiz, R.M., Huang, X.P., Norval, S., Sassano, M.F., Shin, A.I., Webster, L.A. and Simeons, F.R., 2012. Automated design of ligands to polypharmacological profiles. Nature, 492(7428), pp.215-220.
- Carl, M., 2012. The CRITT TPR-DB 1.0: A database for empirical human translation process research. In AMTA 2012 Workshop on Post-Editing Technology and Practice (WPTP-2012), 13(5), pp.125-165
- Chomal, V.S. and Saini, D.J.R., Software Template to Improve Quality of Database Design on basis of Error Identification from Software Project Documentation. International Journal of Engineering and Management Research, 4, pp.2250-0758.
- Creswell, J.W. and Poth, C.N., 2017. Qualitative inquiry and research design: Choosing among five approaches. London: Sage publications.
- Galliers, R.D. and Leidner, D.E. eds., 2014. Strategic information management: challenges and strategies in managing information systems. Abingdon: Routledge.
- Gray, C. and Malins, J., 2016. Visualizing research: A guide to the research process in art and design. Abingdon: Routledge.
- Kaur, A. and Bhardwaj, M., 2012. Hybrid encryption for cloud database security. Journal of Engineering Science Technology, 2, pp.737-741.
- La Rosa, M., Reijers, H.A., Van Der Aalst, W.M., Dijkman, R.M., Mendling, J., Dumas, M. and GarcíA-BañUelos, L., 2011. APROMORE: An advanced process model repository. Expert Systems with Applications, 38(6), pp.7029-7040.
- Liu, R. and Issa, R.R.A., 2012. Automatically updating maintenance information from a BIM database. In Computing in Civil Engineering (2012), 16(5), pp.373-380.
- Majchrzak, A., Beath, C.M., Lim, R. and Chin, W., 2014. Managing client dialogues during information systems design to facilitate client learning.
- Moniruzzaman, A.B.M. and Hossain, S.A., 2013. Nosql database: New era of databases for big data analytics-classification, characteristics and comparison. arXiv preprint arXiv:1307.0191.
- Robertson, T. and Simonsen, J., 2012. Participatory Design. Routledge international handbook of participatory design, 13(2), pp.1-15
- Röglinger, M., Pöppelbuß, J. and Becker, J., 2012. Maturity models in business process management. Business Process Management Journal, 18(2), pp.328-346.
- Romney, M.B. and Steinbart, P.J., 2012. Accounting information systems. Boston: Pearson.
- Sein, M.K., Henfridsson, O., Purao, S., Rossi, M. and Lindgren, R., 2011. Action design research. MIS quarterly, 14(1), pp.37-56.
- Shigley, J.E., 2011. Shigley’s mechanical engineering design. Tata McGraw-Hill Education.
- Siau, K. and Rossi, M., 2011. Evaluation techniques for systems analysis and design modelling methods–a review and comparative analysis. Information Systems Journal, 21(3), pp.249-268.
- Vaishnavi, V.K. and Kuechler, W., 2015. Design science research methods and patterns: innovating information and communication technology. London: Crc Press.
- Vita, R., Overton, J.A., Greenbaum, J.A., Ponomarenko, J., Clark, J.D., Cantrell, J.R., Wheeler, D.K., Gabbard, J.L., Hix, D., Sette, A. and Peters, B., 2014. The immune epitope database (IEDB) 3.0. Nucleic acids research, 43(D1), pp.D405-D412.