Distributed Database Environment
University of Phoenix
Distributed Database Environment
The database tables used to create the distributed database environment includes the Clients table, the Advertisement table, the Owner table, the Sales Staff table, the Lease table, the Properties table, the Business Owner table, and the Private Owner table. The Clients table contains critical information about the client like their names, gender, and address. The Advertisement table includes the number of advertisement, the cost, and the date the advertisement was made. The Owner table contains information like the owner ID, type of the Owner: whether private or business owner and their information.
The Sales Staff table name, age, and address of the Owner. The Lease table has the payment information like the payment method, the deposit expected, the lease duration, and the start date. There is also the Properties table which stores the type of property, the number of rooms, the rent price, and the address. The Business Owner table carries information about the contract name and the type of business while the Private Owner table will store the owner information and their address.
SQL queries to Develop Reports from the Data Query
SQL queries are used to retrieve data from the database tables. The SQL query that will be used will be the SELECT query. For example,
SELECT * FROM Clients;
This will be used to display all the data stored in the Clients table, i.e., the first name, the last name, the address, and gender of the clients.
Another SQL Query that will be used can be the query to retrieve data using a constant value. For example, the query below will output the last name, address, and the phone number of the Owner from two tables: the Owner table and the Property table based on a constant value: the
property number. This query can be used to search for information based on a few known values (property number is SG14).
SELECT LastName, Address, Phone FROM Owner o, Property p
WHERE o.propertyNo = p.propertyNo AND p.propertyNo = ‘SG14’
Finally, there will be a query used to select either a column or a row. For instance, this query can be used to select the column from the table Owner, where the owner type is business.
SELECT * FROM Property p WHERE Owner = ‘Business Owner.’
Typical Operation of a Data Warehouse and Data Marts
The typical operation of a data warehouse and a data mart. A data warehouse is used to hold multiple areas of data that is collected from multiple access points. This makes them challenging to modify compared to data marts. The data warehouse also tends to have very detailed information. On the contrary, the data mart is dependent on the information from a specific department, and the information it operates on is, therefore, a summary contrary to the data warehouse, which is very detailed. The data mart, therefore, creates access and operates on frequently needed information in order to create a collective view for the information improving the response time for the end user.
The implementation of the data warehouse and the data mart should be uncomplicated and straightforward to allow the users to access the information quickly. However, the implementation also needs to consider the security in order to protect the data warehouse and the data marts from security breaches. Another consideration would be to set up the application programming
interfaces and maintaining the database structures. Systems should be fine-tuned and be optimized for enhanced performance. Additionally, the data warehouse and the data mart should be located in different locations. Constraints come in managing the user access for the ongoing users’ accounts. The data marts and data warehouses are usually poorly planned for and budgeted for by most organizations. The data marts should be less complex to reduce the loading time, which is usually higher due to the complexity of the data marts.
The Benefits of Data Mining Applications to the Dreamhouse Data Model
Data mining involves analyzing large amounts of data in order to establish the correlations, insights, and patterns in the data. Data mining could benefit DreamHome through helping DreamHome to analyze the markets before investing in the business. The firm would be able to forecast the future for possible profitability and increase its revenue. Data mining can also be used to convert the data the organization about the markets into useful information. The firm will be able to make accurate inferences and make the right decisions. Smart market decisions can, therefore, be reached.
Data mining can also help DreamHome to analyze customer behavior and gain insights. This will be important for DreamHome in achieving success and data-driven business. Using data warehouses and data mining can be instrumental in understanding how competitors work. This will enable the firm to gain a competitive advantage through understanding the strengths and weaknesses of the competitors. DreamHome will also have the ability to keep updated data at all times. This will help the firm to detect fraud at all times and be in a position to get financial assistance from banks and other financial institutions to help the business grow and expand.
SQL Statements for the Required Queries
The SQL Statements for the required SELECT queries include the statement to display the Clients whose last names are Smith:
SELECT * FROM Clients WHERE LastName = ‘Smith’ The output is:
The SELECT statement that can be used to display specific information about a client is:
SELECT Customer No, FirstName FROM Clients WHERE City=’California’;
The output for such a statement will be:
Finally, the SELECT statement that will be used to output the results from a certain or where the name has a specific word. For example, in order to output results with the term “brain,” the following SQL statement can be used:
SELECT First_Name, Nickname FROM Clients WHERE LastName LIKE ‘%brain%’;
The expected output will be:
Determine the Data Types
In order to choose the right data types for beginners when creating a database, it is advisable to use the default allocated by the database wizard. However, this does not always work optimally for any functions within the firm (Cuzzocrea, 2014). Therefore, to determine the right data types, several factors need to be considered. This is because the data types chosen to impact the size of storage required, the cost to the firm, and the query performance. The length of the data is one of the factors to consider. When the firm intended to expand, then the firm should keep that in mind and allocate some space for future growth.
Additionally, the type of data that will be stored should be considered (Cuzzocrea, 2014). For instance, when the organization wants to store data, then the value should reflect that rather than using INTEGER which causes common databases errors. Basically, the smallest data type should be used to save the organization costs in terms of storage space to be bought, although the possibility for future expansion should not be ignored. This will also reduce the space requirements and improve the performance of the database.
The most challenging process while designing and building data is finding ways of collecting and analyzing phase. The process of collecting and analyzing information is the most difficult since it requires questions to be asked (El Moukhi El Azami & Mouloudi, 2015). Once the wrong questions have been asked, then the wrong answers will be given, and the whole solution will be implemented in the wrong way. The process might also involve gathering information from leaders who may not be available or might be too busy. This makes it difficult for the data warehouse to analyze and give the right reports. Organizations can overcome these hindrances through careful examination of the overlooked information. All the customer relationship management software and accounting packages should also be carefully be considered. The organization should arrange for extensive interaction and ensuring that the information is understood well.
Cuzzocrea, A. (2014, November). Privacy and security of big data: current challenges and future research perspectives. In Proceedings of the First International Workshop on Privacy and Security of Big Data (pp. 45-47). ACM.
El Moukhi, N., El Azami, I., & Mouloudi, A. (2015, June). Data warehouse state of the art and future challenges. In 2015 International Conference on Cloud Technologies and Applications (CloudTech) (pp. 1-6). IEEE.
Click following link to download this document
DAT 380 Week 3 Hardware Environment.docx