Database and Database Manipulation Language

SQL, DATA WAREHOUSE, AND DATA MARTS-Module 4 Case
Database and Database Manipulation Language
SQL AND DATA WAREHOUSE
Name
Course
Instructor
Date
Part I
Introduction
A database store information in a central location for one department or specific area in an organization, making it easier to share such information. Since the databases have the ability to recall information, users can identify specific information that is required by the users. The use of queries in this case focuses on identifying the check out details for customers who have borrowed books and they are overdue.
Populate data to the tables in your database
Insert into ‘book’ values


Check out table


Retrieve names and phone number of customers who have overdue books as of a certain date.
SELECT “SSN”, “Name”, “Phone”, “Email” FROM “Student contact”


Retrieve the contact details of library customers who live in certain city.
SELECT “NAME”, “PHONE NUMBER”, “CITY” FROM “COMMUNITY LIBRARY”
WHERE “City”= ‘San Diego’
Retrieve names and phone number of customers who have overdue books as of a certain date.
Select “SSN”NAME”, “DATE DUE”, FROM CHECK OUT
WHERE DATE DUE< ‘CURRENT DATE’
6] Describe what you have learned in this assignment
The SQL is a standardized language that allows querying and updating of database tables, and SQL statements can run on various SQL servers (Sack, 2006). Since each query created has a corresponding SQL statement, it is possible to view the statements for every existing query. The assignment focused creating SQL queries, but then one has to fully understand how to navigate the database before they change or modify the queries where a need arises. In all cases using the keyword ‘SELECT’ in an SQL statement identifies the name of fields selected from a table, while ‘FROM’ contains the names of tables with the specified fields. The keyword ‘WHERE’ then specifies the condition when filtering the records returned after using the SELECT command.
Planning ahead is crucial to creating SQL statements that are appropriate for the desired results. The first criterion to be determined is the field, including the data types and values in the fields, and the fields are mostly text or numeric. At other times, there is a need to compare operators from one field to another in cases, where the tables are joined the fields are identified depending on where they match. The assignment further highlighted that determining criteria are necessary in the planning phase of SQL querying.
One of the options of creating a query is to use the design view, where one then needs to specify the filter, input table, field names as well as the properties for using different tables. This option takes more time, and one needs to be specific about how they sort the information to give specific results. Using wizards is more cumbersome as there are fields and attributes that are not contained in the initial query. Creating queries using the SQL view is the best option when one has clearly written and SQL statement, which is then entered directly. The SQL view option also allows one to test the SQL query using the SQL editor before running the query.
This assignment highlighted the importance of creating queries, which then filter information as per the SQL query. The reorganized information is then displayed in the database as required by the user. Even though, the data recorded was manageable, queries are potentially useful when in need of sorting out specific information efficiently. One needs to first normalize the data to ensure better efficiency, meaning that creating the database should be well planned. The Base Query Wizard is applicable when one has no previous knowledge of working with Open Office, but with practice the Design Views, SQL View as such, the Query Wizard is still necessary as it gives guidance on how to create the SQL query.
In data manipulation using OpenOffice one needs to be proficient in using the different options when creating queries. Using the wizard to create a query one can only make changes using the Design Vie, but this option is less user-friendly compared to the wizard. The query wizard provides an overview of the filtered information as well as the selected fields if one decides to display the query. The different database systems all present data as virtual tables containing the fields in a table. This highlights that an SQL statement is equally important to determine the table created based on the SQL query.
Part II
Data warehouse and business intelligence
Database and data warehouse
Organizations typically use technology to organize their database, with separate data warehouses. The databases are use for single applications, especially transactions, but the data warehouse is used for multiple domains (Hubpages, 2012). Database also contains information about people, places (warehouses) and objects in a business. Hence, the data warehouse give more detailed view on how an organization is performing rather than simply focus on specific areas. The data warehouse further helps businesses to track business trends, and not events that affect one department of the business. The database is mostly applied for particular applications, while the data warehouse is a repository site applied at the enterprise level....