You are the Vice President of Information Technology at a small, growing business. You have been tasked with developing a plan for maintaining databases for storage of business data and use in busines

Database

By:

DBM502

May 25, 2020

A database management system that is most commonly called DBMS is a software package that is designed to store, change, and delete data in a database. A DBMS can have an extensive collection of databases. A Database is, however, defined as a collection of data that is arranged in a simple and understandable structure that can be very easily be retrieved and changed. All the data stored in the database is stored in the form of tables. All the data is arranged in an order that makes such data very easy to read and extract information. The database can be summarized as a structured collection of data.

There are many benefits of using databases in a business to store and manage data. The most significant need of every business is security, and database systems are perfect for granting protection. Unlike file systems that can be accessed and readable to anyone, the database systems are more secured and almost always password protected (Bulacan State University, 2019). An organization's data is tightly secured inside a database. The administrator often gives access to permits to the authorized employees only, and only a few can modify or delete the data.

Database systems are also used in businesses for easy management of data. If a user needs to retrieve the data, add information, change the data, he can do it. Any kind of information and data retrieval is straightforward in database management systems.

Data elements can be understood as a small unit that represents a unique value of the data. The tuples and attributes are mainly considered to be a part of data elements. For example, let us find the relation of the customer within a business database. In such scenario, data elements would be the customer name, a specific customer identity number, customer's contact detail whether it would be a phone number or email, customer's address, his credit history, the year since he became the customer of the company, the salesperson who interact with this customer, etc. These are some of the examples of data elements within a customer relationship, which is a very integral part of any business database.

In a similar manner, there are other data elements that are also part of a business database in addition to the customer table. A small business can have tables carrying information about employees, all the offices of the company, all the orders, the products, paycheck details, a different table to other firms with whom they have some kind of contract, etc.

System Development Life Cycle (SDLC) is a process that is used to plan, design, develop, and test an information system. SDLC is a collection of multiple steps, the end result of which would be a fully functional information system. SDLC is a beneficial procedure when designing and developing a Relational Database Management System (RDMS). All the steps of SDLC are also applying correctly to the system development lifecycle. The best part about designing a database system with the process of SDLC is the quality of SDLC that it covers both components of hardware and software. This makes the overall development much easy and remarkably cohesive.

The System Development Life Cycle has seven significant steps that will be used when developing a database system (Swersky, 2018). The first step is planning, which includes making a plan on how to design it, what will be the budget be, and what data would be stored in the system. The second step is gathering requirements. It is an essential part of development as it is necessary to design a system that fulfills all the needs of the business for which it has been developed.

The next step is to design the system, and then comes the actual development step of the system. After the system is developed, it is then tested to eradicate any kind of mistakes or bugs. After it has been tested and came out with no problems, it is then deployed in the system. The last step is maintenance, which is an ongoing phase as support happens whenever some problem occurs. Hence, developing a fully functional database is very important for a business.

WEEK 2

The database is a very efficient method to store data. It helps to collect data and organize them in a way that makes it easy to extract data and manage it. There are multiple ways to organize data in a database. The database can be hierarchical where each data is stored in a tree or hierarchy, or it can even be the object-oriented database. In the object-oriented database, information is organized around objects rather than actions. But the best method to store and organize data in a database is in the form of a relational database.

Relational database means to store data in a table. In a Relational Database Management System (RDMS), a table is also known as a relation. A relation or a table is always formed of a single entity. A table consists of rows and columns. An attribute represents a characteristic of data, while each row or tuple signifies a unique record of the data. The cell where each row or column collides is known as a field. A field represents a single value of data.

Using a Relational Database Management System is very beneficial for a database administrator as RDMS is very simple in its design. The data could be easily stored and easily discovered through it. Another significant benefit of using RDMS is that it provides the advantage of using Structured Query Language (SQL) in the database, which makes fetching data so comfortable from the database. It simplifies the process of database extraction. SQL is the easiest and efficient language used for running queries on a database.

The database is proved to be very efficient in managing and maintaining data. Database systems remove this issue of space consumption and stored everything in a table where a table denotes a whole school, and one row of the table represents one record of the student. Another essential purpose of Database Systems is definitely the need for digitalizing the storage of data. Now all the data is stored on digital devices rather than manual hardcopy files. The digital version of data makes it extremely easy to manage and maintain the data and make any change whenever needed. A lot of organizations also keep their data on their servers, which makes remote access very easy and manageable.

For any business and organization, data plays a very integral role. It is an essential commodity for any business to manage and maintain data. Therefore, it is in extreme benefits for companies to invest in very efficient database systems. The most commonly used type of database management system is RDMS. It is trendy all around the world, especially for businesses. RDMS helps store numerous amounts of data. Most of the time, companies do not get rid of old or outdated data just for the sole reason that this data could someday be potentially very profitable to them. So, in order to manage such a massive cluster of data, it is essential that the businesses use RDMS, which is stored on their vast servers.

Businesses are also always in need of protection. As mentioned earlier, the data is a massive commodity for any organization, so the safety of said data is a big concern for the company. RDMS provides numerous security and protection features, which make it easy for businesses to protect their data as well as organizing it.

When it comes to designing and developing a database, it is essential to conceptualize what the database will contain and how every entity will interact with each other. This is where the conceptual design phase begins. It is explained as the process sod conceptualizing the design interactions, the strategies, and the elements of the database. The entity-relationship model plays a huge role in this phase.

The entity-relationship diagram or ER diagram is a graphical representation of tables in a database and their relationship between them. ER diagram uses symbols to represent three different types of information (Kriegel, Taylor, Trukhnov, Gillenson, & Ponniah, August 2008). The rectangle is commonly used to represent entities. The diamonds are used to describe relationships, and ovals are used to represent attributes. E-R diagrams are a big help in creating a blueprint on which the entire database will be generated.

WEEK 3

Database management and administration are critical to maintaining a large cluster of data. It is essential for data to be collected and organized in an orderly manner. If the data is kept in an unorderly way, it will create a lot of hindrance for the company which owns this data. This is why it is vital that the database is appropriately administrated.

Database administration makes it possible for the database management system to operate without any problems and to its maximum operability. Database administrators are the people responsible for managing all the databases of a system. They manage and run them smoothly. They are the ones who should be contacted for removing and eradicating any problem arise in the database. Hiring a database administrator proves to be extremely valuable for the company as that person maintains their database very efficiently, which makes it easier for the company to use the database. It helps in increasing the productivity of the database.

Structured Query Language (SQL) is the specific programming language designed to manage, create, and upgrade the Relational Database Management System, also known as RDMS (Beaulieu, 2005). It is because of SQL that a user can interact easily with the database and can fetch and retrieve data from the tables. SQL consists of two main types of languages, DML and DDL. The first one is known as Data Definition Language (DDL), which is a language used to create the tables and modify the structure of the database. SQL statements such as CREATE, ALTER, and DROP are part of DDL.

DDL is the language of creating and modify database schemes. Whereas, the second type of SQL, which is known as Data Modification Language (DML) is used to add, fetch, and modify the data stored inside the database. It is also used to insert and delete the data in the database. SQL statements such as SELECT, INSERT, UPDATE, and DELETE are some of the commands of the DML.

SQL has some constructs that constitute the entire language. These elements are the things that make the language. The first construct is a Query, which is run on the database to perform any action. SQL statements that the user enters into the system are called its Statements. When Queries are combined with Statements, it constitutes a Clause. ORDER BY and GROUP BY are some of the examples of the SQL clause. SQL expressions, another important construct, is a collection of multiple SQL functions, whereas a predicate is a logical condition applied to the database. All these constructs are applicable to DDL and DML as well. These constructs are valuable in developing DDL and DML, as it is much that SQL has these.

The assessment of design is extremely important for a database. Every team member should check the design of the database according to their own functionality. For instance, a security manager would want to assess the security measure taken while developing the design of the database. Similarly, someone who will fetch and enter a lot of data would want to check the functionality and accessibility of these specific functions.

It is also precious to test and assess the design of the database, which is necessary for the integrity of the database. The best method to check the database is through the use of running SQL statements on the database. Trying every SQL statement on the database is extremely valuable to the testing of the design of the database. It gives you a clear idea about how well the database is written and how efficient it is being operated.

The development team needs to ensure that the ACID property is satisfied with the database. The ACID property consists of Atomicity, Consistency, Isolation, and Durability. Only when this property is satisfied, the transaction would be correctly carried out. In order to check the operation, SQL statements that needed to run are BEIGN TRANSACTION and END TRANSACTION# (Software Testing Help, 2020).

In order to check the permissions and limitations that each database has, the SQL statements should be applied to the data that is off-limit. Only in this way, would it be necessary to see whether the DBMS is able to break permission or not. If it can access the forbidden data, it means that the database has severely threatening security issues. Proper testing is crucial for the appropriate management of the database.

Week 4

Data administration is the process through which a large collection of data is collected, managed, and organized. It deals with the business lexicon rather than designing and upgrading the database. The person who performs data administration is known as Data Administrator or DA. He is the one who is responsible for analyzing and gathering data from various sources.

Database administrators are the people responsible for managing all the databases of a system. They manage and operate them smoothly. They are the ones who should be contacted for removing and eradicating any problem arise in the database. Hiring a database administrator proves to be extremely valuable for the company as that person maintains their database very efficiently, which makes it easier for the company to use the database. It helps in increasing the productivity of the database.

The database administrator is only responsible for handling the security and management of customer data. Along with these components, the responsibility of encryption and decryption also falls on the Database administrator. To ensure the proper firewall measure on the application through which the user interacts with the database is a part of Database administration. (Boyer, 2018)

The major difference between a DA and a DBA is that database administrators have more concern for the working and management of database while data administrator is responsible for the gathering and collection of data stored inside the database. DBA is concerned more with the day to day smooth operation of the database management system while a DA worried about the data trends and the data flow within the company.

A data administrator specifies the standards for data used across the database. On the contrary, a database administrator will only be responsible for the installation and configuration of DBMS. While a DA makes policies and set standards of data usage and data storage, a DBA will only be responsible for maintaining those policies and ensuring that the data integrity is preserved.

Data governance is the technique of administrating the methods through which the data is stored, organized, and secured within an organization. Data governance (DG) is very important in an organization as it ensures there is no corruption occurring between the data stored in the enterprise cooperation. It checks the validity, integrity, and security of the data. It stops the misuse of data. An efficient data governance team is responsible for developing and designing the policies and standards of governing the collection of data in an organization. They are also responsible for enforcing and implementing the same standards and policies in order to confirm that the data are stored efficiently.

An efficient data governance system or program is extremely important in the optimum performance and productivity of the company. For efficient data governance, it is important to create a strategy for the DG program with the guidance and assistance of all the stakeholders and executives of the company so that they know what they should expect from the data governance program. The next is to choose a model for data governance, and the best one is to have the officials guiding the process while the data governance team itself is divided into two parts the working team and the developing team (Ott, 2015). A great team is an instant indication of a great data governance policy.

The testing and checking method of a database is quite similar to what a normal software application has with respect to some extensions and additional processes. The same procedure of testing like system, unit, and user test is also run on the database management system. The unit test ensures that there are no bugs within the system. It is used to test even the littlest parts of the database. System testing, however, should be done as a whole when the complete system is checked. The user test indicates how the user will be able to interact with the system. Although all the types of testing are important, the user test is given significance because it establishes how good customer experience will be after using this RDBMS.

When it comes to testing the coding standards of the database, it is important to test all the components of the database. Static analysis and dynamic analysis are two pillars of standard for testing the code of the database. The static analysis includes how well it functions, while dynamic includes how well the metadata is being operated. Testing is a very integral part of the management of a good system (Brewer, 2017).

Week 5

Database performance monitoring is a crucial part of optimum database performance. To find and fix problems just as they are starting to be visible is the perfect solution than to wait and run a quality check every couple of months. There are a lot of database monitoring tools that are helpful in finding database problems in real-time. The best, in my opinion, would be SolarWinds Database Performance Analyzer for SQL Server (Keary, 2020). This software is best in the market for analyzing databases that run on SQL.

Database backup is critical in ensuring that any error would not affect losing data stored in your database. It is done to protect data from any severe loss. There are multiple tools available that provide the best backup techniques for the SQL database. But the best out of all of these are using the help of Microsoft SQL server to create a backup of your database. Microsoft SQL Server does provide the built-in feature of backing up the database. The database administrator only has to select a database that he wants to backup and right-click on it. There will be an option that will allow him to back up the selected database. It will also give you the option of choosing the type of backup you want and also select the destination of the backup file (Microsoft Docs, 2019).

Data quality monitoring is vital to ensure that each type of data stored in the database is correct and up to standards. Data monitoring ensures that each instance of the database contains valid data, and the integrity and validity of data are maintained at all times. Out of all the data quality assurance monitoring tools, the best two are IBM's InfoSphere QualityStage and Cloudingo. The software Cloudingo is a very prominent data cleaning system (Greengard, 2019). It helps in deleting duplicate and corrupt data.

There are multiple ways through which a database administrator can keep the database secured as well as the information stored inside it also safe from interruption and misuse. Encryption is the best method to secure data and information. Encryption means to hide the original data and replace it with some unreadable data that gives no clue about the original data. Data is always encrypted using a specific password, and the access to manipulate the data is only given to some of the few authenticated people. Another important method of data protection is to monitor database security continuously and check if there are no vulnerabilities in database security.

Standards are essential for businesses. Standards provide a compass for business to operate their workings and succeed in the economy. Standards are considered as an integral tool of communication, leadership as well as operations. They are the rules and regulations that guide the business about what should be developed and how it should be made. Standards are like a good playbook for business through which they can learn everything about running their business and succeeding at it.

There are multiple people working together in managing and operating a data warehouse. One such person is a business sponsor who is responsible for maintaining the business side of the affairs. There is also a similar post of system sponsor who will be responsible for managing all the systems currently operable to run the data warehouse and their activities. There is also a documentation specialist who will be the one to document every information regarding the system of the data warehouse so the staff and users alike would be kept updated to the new changes in the system. Data administrators and data managers have a pivotal role in the management of the data warehouse. They are the people who manage the whole large collection of data and ensure there is no data corruption and discrepancies.

The first step in designing an effective data warehouse is to have a business plan. It is necessary to ensure the requirements of the warehouse area and what the purpose of this data warehouse will be. Afterward, the physical storage configuration will come. It involves all setting up servers and systems. Then choose the data warehouse model through which the data will be stored. It helps to decide the schema of the data collection. Next comes the transfer process from the previous storage method to the data warehouse. Then the front end is designed to interact with the system (Smallcombe, 2019).

Beaulieu, A. (2005). Learning SQL. Concord: O'Reilly.

Boyer, J. (2018, September 12). THE AWS SHARED RESPONSIBILITY MODEL: 3 AREAS OF IMPROVEMENT TO MAKE TODAY, PART 1. Retrieved from Hackerone: https://www.hackerone.com/blog/AWS-Shared-Responsibility-Model-3-Areas-Improvement-Make-Today-Part-1-Keep-Your-Private-Keys

Brewer, W. (2017, June 20). Database Code Analysis. Retrieved from Redgate Hub: https://www.red-gate.com/simple-talk/sql/database-devops-sql/database-code-analysis/

Bulacan State University. (2019). Uses, Advantages & Disadvantages of Database, Guides, Projects, Research for Database Management Systems (DBMS). docsity.

Greengard, S. (2019, June 20). 10 Top Data Quality Tools. Retrieved from Datamation: https://www.datamation.com/big-data/10-top-data-quality-tools.html

Keary, T. (2020, May 5). 14 Best Database Monitoring Tools. Retrieved from Comparitech: https://www.comparitech.com/net-admin/best-database-monitoring-tools/

Kriegel, A., Taylor, A., Trukhnov, B., Gillenson, M., & Ponniah, P. (August 2008). Introduction to database management. India: Wiley.

Microsoft Docs. (2019, September 12). Create a Full Database Backup. Retrieved from Microsoft. docs: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/create-a-full-database-backup-sql-server?view=sql-server-ver15

Ott, M. (2015, December 1). A Ten-Step Plan for an Effective Data Governance Structure. Retrieved from The Data Administration Newsletter: https://tdan.com/a-ten-step-plan-for-an-effective-data-governance-structure/19183

Smallcombe, M. (2019, August 2). The Ultimate Guide to Data Warehouse Design. Retrieved from xplenty: https://www.xplenty.com/blog/the-ultimate-guide-to-data-warehouse-design/

Software Testing Help. (2020, April 16). Database Testing Complete Guide (Why, What, And How To Test Data). Retrieved from Software Testing Help: https://www.softwaretestinghelp.com/database-testing-process/

Swersky, D. (2018, May 31). The SDLC: 7 phases, popular models, benefits & more. Retrieved from Raygun: https://raygun.com/blog/software-development-life-cycle/#systems