Cis 515 Store Database Management Assessment Answers

1.Identify the potential sales and department store transactions that can be stored within the database.

2.Design a database solution and the potential business rules that could be used to house the sales transactions of the department store.

3. Evaluate all relationships of each entity within your database solution using the Crow’s Foot notation. Include all data elements, entities, relationships

Answer:

Introduction

The database technology has attracted attention to the current businesses. It is commonly used in large organizations in the storage and transfer of data such as the employee's details; names, salary, and also the company's product information. The area, however, demands a high level of knowledge and skills in database designing rather than its operations. A more professional database designer is more likely to design a database that is easy to maintain. Most of the organizations have specialization groups of database administrators specialized in designing and maintaining databases.

The purpose of the research paper is to identify various transactions and probably the departmental store and the potential sales transaction that can be stored in the database for easy retrieval when the demand arises or for the update. I will design potential database rules and the diabase solutions that can be used in the departmental store in performing various tasks such as the data retrieval and updates. As usual, a database requires relations between the entities for easy access or transfer of the data, and therefore the database will use the Crow's Foot notation in showing the relationships. The Crows Foot notation is the most common tools used in showing many relationships by use of graphical symbols (Clifford, & Bhandari, (2015). The research will also consider other external sources to identify how the retail stores use the database in maintaining their customers' information. It will also be essential to determine how such retail stores retain their customers and increase sales by use of a well-designed database. Moreover, cloud computing employs the use of a database, and, therefore, the research will also consider how the data vendors design such a database.

The second part of the research paper will calculate the appropriateness of the use of the distributed DBMS and identify the best techniques that should be considered in optimizing its operation in an organization. This may include the use of current technology in designing an easier database in its maintenance. In this database, fives departments will be generated and two examples of how uncommitted data and lost updates may occur. The database will also identify the concurrency control factor vital within the current multiuser environment to ensure any execution of transactions within such an environment is valid. Due to the security threats on the database information, the research will also venture into the internet to determine the recovery and backup solutions and any other security threats to the database that could be applied to the department (Selinger, 2017). 

Information professionals with adequate skills are required to maintain the business continuity of database services in an organization. Identify and assess at least three professional skills that would be required to be successful database administrators.

According to Sumathi, & Esakkirajan (2017), there are various professional skills required for a database administrator such as:

• Knowledge about the database itself.
• Knowledge of database query.
• Knowledge of database design.

Give your opinion on what the critical nature of a database administrator's role is to IT industry organizations. Support your answer with specific examples of functions and responsibilities they perform.

The database administrators help in enhancing data communication. They are tasked with the responsibility of storing and retrieving data from the database. This data is used to run on the same or different computer.

They ensure that all the information can be accessed at any time. The administrators are tasked with the responsibility of running queries on the data stored in the SQL database to extract information that may be hard to find (McClure, & Kruger 2014, May).

The best database model leads to a more effective function of the industry. The database designer determines what should be stored in the database and entities to interrelate. 

Give three security vulnerability and classify each with being technical, managerial, cultural, or procedural. Provide your rationale.

According to Alhazmi, Malaiya, & Ray (2015), databases are vulnerable to security threats such:

Stolen database back up. It is a managerial vulnerability of database from both the external attacker and those within the corporation. The insiders steal the archives including the database backups.

Deployment Botches. It is a procedural vulnerability that is caused as a result of ignorance of the installation procedure.

Database feature abuse. It is a technical vulnerability and is associated with a missing standard feature in the database. 

Suggest specific actions that could be performed to minimize the vulnerabilities. Describe how often these measures should be performed and the order in which each should be performed.

The stolen database backups can be mitigated by encrypting the archives frequently to ensure inaccessibility by both the external and internal attackers within the corporation.

Before the installation, the database should be tested, and the correct procedure of installation be followed at any time the database is installed. Failure to the test and ignorance in installation process exposes the database to security attacks (Bireley, 2012).

Any simple errors such as failing to consider certain feature in the database by designers expose the database to attacks. This can be mitigated by considering all the necessary features during the time of implementation to keep the database away from any attack. 

Assignment

Transaction in the department store and potentials sales stored in database

Due to the current market activity which may project the increase in sales, there is the need to design a comprehensive relational database for several new stores. In the mission to design a useful database, several factors should be weight depending on the nature of the database and its intended functions. After keenly analyzing all the transaction or activities to be performed in every store department, a list of major entities can be developed to help ensure the success of the transaction from one department to another. According to Starkey (2012), the transactions that are performed in each department include. 

Every core entity in the database will include other attributes whose information is relevant to that department and this will make it easier to display the data in a hierarchical nature. The main attributes contained in each entity are as shown below: 

The key entities in the departments are vital in providing the essential information of the transaction that takes place in each department. According to McHugh (2017), a store contains various department at the simplest level each with vital information for the department, and it is staffed by employees who provide the services related to database functions.

Customers – It sells the organizations' products to the customers and also provides inquiries to the customers which is the primary transaction in a database. After identifying and working out on the entities in each department and their attributes, business rules are then implemented concerned with the organizations' sales transaction and storage of the information (McCarthy, & Dayal, 2016, June).

Database – It aims at organizing and managing the information or data, but it also needs to ensure that the designing technology used is easy to maintain and it appropriately fits its objective.

Potential business rules and database solution in departmental store.

Before any rule of business is setup, an organization should make sure the database design to be implemented is effective in the functionality of the intended purpose. It should assist in reporting and visualizing the data by ensuring no correlating relationship designed to allow proper analysis. According to Stonebraker, & Kemnitz (2013), the rules include:

Rule 1: Various codes are programmed to direct specific activities in each department. Due to a large number of stores that sells products, there are constraints that are associated with the transactions, and therefore only the assigned codes in each store are permitted as valid.

Rule 2: The relationship constraints within the database will be vital in ensuring that each of the four vendors has an equal chance of supplying a product and they will only be able to supply at least one product at a time. The rule will still, hold even if there is legacy or discontinued vendors or products.

Entity relationship using the Crow's Foot notation.

After identifying the rules that should be applied in the database implementation, the next step is to design the relationship among the database entities (Lohman et al., 2012). This relationship should also consider the type of the transaction that will be shared by the two or more entities. The relationship between the entities will be the key determinant for the effective functioning of the database such as the data transfer from one entity to another, and, therefore, it requires accuracy and keenness. We can state that the products and services are sold to customers, and the customers buy the products and services. However, this does not show each cardinality's relationship.

For an instant, how many sales transaction are carried out in a single store? By following this procedure, it will, therefore, be easy to identify all the relationships in the six entities. Since in the database we have six entities, they will all relate to one another giving a total of 30 relationships.

Based on the six entities by five relationships, then the relationship has been captured successfully. Next is to determine the cardinality of the relationship among the entities and represent it graphically to define the relationship clearly. According to Stonebraker, Rowe, & Hirohama (2011), each relationship is expressed as;

• One to One (1:1)
• 1 to Many (1: N)
• Many to 1 (N:1)
• Many to Many (N: N).

The relationships among the entities are shown below: 

Use of Big Data tools for sales and inventory forecasting

The database plays a crucial part in data storage which can be used by the researchers in predicting the future depending on the data. The presence of a database in an organization also provides security to the stored information as each transaction is effectively logged and is only accessible by the authorized person. Analyzing the data helps in identifying the most profitable customers and the frequencies at which the customers buy from the department (Gehani, & Jagadish, 2015, September). This will also be an opportunity for identifying the weaker area and therefore applying the best strategy to increase the sales volume. There is also access for the information concerning the customers who have only purchased the products ones, and this will help in identifying the reason behind the customers not revisiting the store so as to solve the problem. Perhaps there may be either compelling competing officer or a bad customer experience.

With the implementation of the database, the customers or sales related issues are represented to the sales and marketing teams. This team in turn makes the decision concerning the current customer stand in the business which is vital for the organization. The extraction of the data for the analysis also requires the database administrators. The growth of an organization means that the database will have to grow in size, therefore, presenting the information in many proportions. Due to the extensive database data, it, therefore, becomes difficult in analyzing a large amount of data using the conventional database management tools and thus the Big Data tools are considered more effective (Stonebraker, 2012). Given the constant updating stream of the information in each table and the live nature of the database, the Big Data tool is more considered due to its ability in manipulating and extrapolating a large amount of data (Widom, & Finkelstein, 2011, May). The Big Data tool such as the Jaspersoft's BI Suit helps in retrieving a large amount of data from any SQL table, and, therefore, the tool will be used to retrieve and collect the data in accordance to the organizations' requirements. The information retrieved using the tool allows the presentation of the data in the form of graphs and PDF which accelerates the analysis and presentation of the analyzed report compared to the conventional reporting methods (Lamb, 2016). 

Two Stored procedures for SQL in predictive and explanatory analysis

The SQL stored procedure is also used in the analysis of the data. However, this procedure requires the database specialist as compared to the other analysis that can be performed by statisticians after being served with the company's data. The comprehensive Big Data suite can also be used in addition to the SQL stored procedure hence completing the SQL statement (Ture, 2017). For an instant, we can write a certain procedure for a promotional activity that has been conducted in an area to retrieve the information related to the customer in that location such as making any purchase or changing the purchase frequency based on a wildcard entry. The SQL codes are as shown below:

SQL code:

Create procedure loccheck @address nvarchar 25)

as

Select *

From dbname.customer.address

Where address like @address + ‘%'

Select *

From dbname.sales.customer

Where customer = @customer

Go

The SQL code will lead to the availability of a LocCheck which is a Stored Procedure. The result can be reported at any time of run by performing the procedure via the below command.

EXEC LocCheck @Address = ‘ ‘

The result is returned in the above code if the desired location to search is entered in the quotes. According to Engelhardt, & Ashley (2013), an alternative that encompasses the transaction and date can also be used in an attempt to identify the trends in the finance sector (e.g. it can be in the form of month end, annual).

The command of the Stored Procedure:

Create procedure SalesDate

 @BeginningDate DateTime.

 @Endingdate datetime

as

Select

 Sales.date

 Sales.products

Where

 Sales.date between @beginningdate and @endingdate

Go

For the information to be called and displayed, the EXEC command should be used in the same way as the previous stored procedure. The first Stored Procedure in the above examples is considered the best as it can relate the planned and ongoing promotional and marketing activities that are planned (Yamakawa, Gokan, & Ohtsuji, 2012). It can be used as a method in the marketing and promotional for obtaining immediate feedback.

Pricing structure and security considerations for cloud- hosted solution for database

Some expenditures accompany the implementation of a database cloud-hosted solution. The table below shows the pricing structure and the estimated cost that is required in the complete implementation of the cloud-hosted database. It also includes an analysis of different cloud computing types together with their security considerations. 

How the cloud-based services fit on a mobile user environment and the technical requirements for data integrity.

To determine the best vendor among the cloud-based service, it is important to consider the price of the security affordability. Therefore, the Amazon EC2 has the highest security affordable price in addition to the (PaaS) and the (IaaS). The other cloud-based services have less affordable security price and also lower prices. The Amazon Web Service (AWS) is more significant as it helps in reducing the cost incurred in the information technology in many ways. With the AWS such it is possible to pay only for the resources consumed by replacing the upfront capital expenses with the low variable cost (Keohane, 2016).

Appropriateness of the distributed DBMD structure and its optimization techniques.

Theoretically, all the requirements for the proposed database will be met. As mentioned earlier, due to the increased number of transactions as the increased customers, the database will require constant connectivity due to the rapid increase in the size of the database. The larger database will have to be split across multiple systems through the distributed database management system (Abiteboul, 2014). Here, all the entities will be combined and represented as one database. The single database configuration will be optimized for Scalability and High Availability. Moreover, it will be optimized for being backed up by the best practices and disaster recovery mechanisms.

Due to some of the risk associated with the database, it is important to set up a mirrored database with failover by utilizing the SQL server built-in technologies (Goldman, & Widom, 2015). This helps in the database backup for the disaster recovery purpose. It also helps in the continued functioning of the other databases if the main database server becomes unavailable or stops functioning as a result of various reasons. According to He, & Naughton (2016), a further Setup of a copy of the Backup Logs along with the central database regular copies reduces the chances of data loss in case of failure of the functioning of a database.

Two examples on how uncommitted data and lost updates may occur.

It is essential to understand that due to the frequencies of the data transmission, it is possible for the loss of the updates or the data not being committed successfully. This case is commonly experienced in the big databases that encompass multiple-user environments. According to Florescu, & Kossmann (2010), a data loss occurs when two or more transactions in the databases choose the same row, and by being unaware of any ongoing transaction and based on the original value, the data gets updated. The two or more different transactions from different originalities get updated as one update and thus overwriting the previous update. Therefore, data loss is said to have occurred.

There is also a scenario where the second transaction of data in the databases can select a row that contains another transaction with the ongoing update. Due to the interruption of the data being updated, the update can be affected, therefore, changing the original transaction. The uncommitted data is consequently said to have occurred (McClure, & Kruger, 2014, May).

To ensure all the transactions are executed correctly, it is vital to alleviate the issues of data loss and uncommented data through the use of SQL statement, row version. It helps in issuing a stamp to each database table as a unique identity that ensures automatic updates and accurate recording of data. Besides, with the need to set the concurrency model as required, the Java Database Connectivity (JDBC) can also be used. For an instant, the Transparent Data Encryption is used a JDBC drive to secure the database by setting the concurrency as its mechanism. This also results in the encrypted database that ensures the authentication mechanisms and the relevant keys are safely stored. Such procedures ensure that without the encrypting key, it cannot be possible to view or access the data even with the physical access of the database (Chan, & Cheung, 2011).

The concurrency control factors that ensures execution of instructions area valid in a multi-user environment.

 CONCUR_SS_SCROLL_LOCKS

The read-write model of the database setting helps in maintaining row integrity by locking each row during each transaction. This ensures that the update of the row can be done once the previous operation in the database has been completed and the row unlocks (Lee, Low, & Wong, 2012, October). The JDBC contains available varying models of concurrency, but this can, however, be updated depending on the requirements of the business. Although there are security threats related to the database, the methods should address the technical concern associated with the successful capture of each transaction and the database. It must be remembered that the database contains an extensive data related to a specific customer such as the pricing and also the employee's data that is vital to the organization and so its protection is required.

In ensuring the access, control, and permissions to the database are not compromised by being accessed by an unauthorized person, various mechanisms can be applied. One of the best and the easiest method is to ensure that the database only contains the relevant information and the access to the data are only controlled by a specific person (Neuman, 2014). Any alteration to the data should be highly under secure and to the only authorized and well qualified and experienced person. The organization should also ensure that it adheres to the government regulatory by allowing data auditing.

Option for availing disaster management function in a retail environment database.

The disaster recovery options include:

• Log shipping.
• Database mirroring
• Failover clustering

Security threats associated with database in management store and the appropriate measures to minimize them

According to Shulman, & Co-founder (2016), the retail industries have some common security threats such as: Spam/spim/spit, Virus, Spyware, Spoofing, Botnet, Keystroke logging (keylogging), Worm, Adware, Trojan horse, Denial-of-service attack (dos attack), and the Blended threat. There are some regulators and the standard bodies forcing for the vital security requirements to avoid penalties imposed by for example the credit card providers. This will help in minimizing the security threats.

References

Abiteboul, S., Quass, D., McHugh, J., Widom, J., & Wiener, J. L. (2014). The Lorel query language for semistructured data. International journal on digital libraries, 1(1), 68-88.

Alhazmi, O. H., Malaiya, Y. K., & Ray, I. (2015). Measuring, analyzing and predicting security vulnerabilities in software systems. Computers & Security, 26(3), 219-228.

Bireley, W. R., Curran, L. R., Lin, F. L., Lobo, A. B., & Wong, J. Q. (2012). U.S. Patent No. 6,115,703. Washington, DC: U.S. Patent and Trademark Office.

Chan, M. Y., & Cheung, S. C. (2011). Testing Database Applications with SQL Semantics. In CODAS (Vol. 99, pp. 363-374).

Clifford, P., & Bhandari, R. (2015). U.S. Patent Application No. 10/488,592.

Engelhardt-Cronk, K., & Ashley, D. (2013). U.S. Patent Application No. 10/034,090.

Florescu, D., & Kossmann, D. (2010). Storing and querying XML data using an RDMBS. IEEE Data Engineering Bulletin, Special Issue on, 1060(22), 3.

Gehani, N. H., & Jagadish, H. V. (2015, September). Ode as an Active Database: Constraints and Triggers. In VLDB (Vol. 91, pp. 327-336).

Goldman, R., & Widom, J. (2015). Dataguides: Enabling query formulation and optimization in semistructured databases. Stanford.

Hanson, E. N. (2015, June). Rule condition testing and action execution in Ariel. In ACM SIGMOD Record (Vol. 21, No. 2, pp. 49-58). ACM.

He, J. S. K. T. G., & Naughton, C. Z. D. D. J. (2016). Relational databases for querying XML documents: Limitations and opportunities. Very Large Data Bases: Proceedings, 25, 302.

Keohane, S. M., Mcbrearty, G. F., Mullen, S. P., Murillo, J., & Shieh, J. M. H. (2016). U.S. Patent No. 6,983,352. Washington, DC: U.S. Patent and Trademark Office.

Lamb, C., Landis, G., Orenstein, J., & Weinreb, D. (2016). The ObjectStore database system. Communications of the ACM, 34(10), 50-63.

Lee, S. Y., Low, W. L., & Wong, P. Y. (2012, October). Learning fingerprints for a database intrusion detection system. In European Symposium on Research in Computer Security (pp. 264-279). Springer, Berlin, Heidelberg.

Lohman, G. M., Lindsay, B., Pirahesh, H., & Schiefer, K. B. (2012). Extensions to Starburst: Objects, types, functions, and rules. Communications of the ACM, 34(10), 94-109.

McCarthy, D., & Dayal, U. (2016, June). The architecture of an active database management system. In ACM Sigmod Record (Vol. 18, No. 2, pp. 215-224). ACM.

McClure, R. A., & Kruger, I. H. (2014, May). SQL DOM: compile time checking of dynamic SQL statements. In Software Engineering, 2005. ICSE 2005. Proceedings. 27th International Conference on (pp. 88-96). IEEE.

McClure, R. A., & Kruger, I. H. (2014, May). SQL DOM: compile time checking of dynamic SQL statements. In Software Engineering, 2005. ICSE 2005. Proceedings. 27th International Conference on (pp. 88-96). IEEE.

McHugh, J., Abiteboul, S., Goldman, R., Quass, D., & Widom, J. (2017). Lore: A database management system for semistructured data. SIGMOD record, 26(3), 54-66.

Neuman, D. (2014). Designing databases as tools for higher-level learning: Insights from instructional systems design. Educational Technology Research and Development, 41(4), 25-46.

Selinger, P. G., Astrahan, M. M., Chamberlin, D. D., Lorie, R. A., & Price, T. G. (2017, May). Access path selection in a relational database management system. In Proceedings of the 1979 ACM SIGMOD international conference on Management of data (pp. 23-34). ACM.

Shulman, A., & Co-founder, C. T. O. (2016). Top ten database security threats. How to Mitigate the Most Significant Database Vulnerabilities.

Starkey, J. A. (2012). U.S. Patent No. 8,224,860. Washington, DC: U.S. Patent and Trademark Office.

Stonebraker, M. (2012). The integration of rule systems and database systems. IEEE Transactions on Knowledge and data Engineering, 4(5), 415-423.

Stonebraker, M., & Kemnitz, G. (2013). The POSTGRES next generation database management system. Communications of the ACM, 34(10), 78-92.

Stonebraker, M., Rowe, L. A., & Hirohama, M. (2011). The implementation of POSTGRES. IEEE transactions on knowledge and data engineering, 2(1), 125-142.

Sumathi, S., & Esakkirajan, S. (2017). Fundamentals of relational database management systems (Vol. 47). Springer.

Ture, M., Krishnaprasad, M., Davis, M., Hsin, C., Bhavsar, M., Koide, H., ... & Bhatkar, S. (2017). U.S. Patent Application No. 11/680,558.

Widom, J., & Finkelstein, S. J. (2011, May). Set-oriented production rules in relational database systems. In ACM SIGMOD Record (Vol. 19, No. 2, pp. 259-270). ACM.

Yamakawa, S., Gokan, H., & Ohtsuji, A. (2012). U.S. Patent No. 6,357,015. Washington, DC: U.S. Patent and Trademark Office.