University Database Concepts: Modeling and SQL Assignment, Semester 3

This assignment solution, prepared by a student, addresses key database concepts including data warehousing, business intelligence, and SQL. Section A focuses on applied database theory, specifically warehousing and business intelligence, discussing their processes and benefits. Section B provides SQL queries, demonstrating practical application of SQL. Section C presents data modeling through Entity Relationship Diagrams (ERDs) for different scenarios, such as a Specialist Clinic, Flight Management, Building Project Company, and rental management. The solution adheres to USQ data modeling and normalization methodology and includes relevant references. This assignment provides a comprehensive understanding of database design, implementation, and application.

Running head: DATABASE CONCEPTS - MODELLING AND SQL
DATABASE CONCEPTS - MODELLING AND SQL
Name of the Student
Name of the University
Author Note

1DATABASE CONCEPTS - MODELLING AND SQL
Table of Contents
Section A: APPLIED DATABASE THEORY...............................................................................2
Warehousing and Business Intelligence......................................................................................2
Process and benefits.....................................................................................................................2
Section B: SQL................................................................................................................................3
Section C: DATA MODELLING....................................................................................................8
Question 1....................................................................................................................................8
Question 2....................................................................................................................................9
Question 3..................................................................................................................................10
Question 4..................................................................................................................................11
Referencing....................................................................................................................................12

2DATABASE CONCEPTS - MODELLING AND SQL
Section A: APPLIED DATABASE THEORY
Warehousing and Business Intelligence
Data Warehousing and Business intelligence are the part of BI (Business Intelligence)
Architecture. It describes the standards policies for data organization by the computer based
technologies to improve the business functionalities. The important and critical decisions are
taken with the help of data visualization, reports and then analysis to direct the business towards
organizational goals. Data warehousing is the component of this architecture, which is used in
performing the data cleaning, organization, filtering, storing and extracting in a centralized
manner (Thamir & Poulis, 2015). The important factors that contributes in the modern business
intelligence are data collection, integration, storage, analysis, distribution, and decisions on the
basis of the insights.
Process and benefits
Data warehousing is a method of data management in a centralized manner and to store
data from different sources into one storage. The main problem that warehousing solves is all the
data which always needed to be analyzed presents in a central storage system. It mainly has three
process that runs analysis of data which is ETL (Extract Transform and Load). ETL simplifies
the larger and complex data very effectively and efficiently (Anand, 2014). The warehousing has
become a very common in business organizations today. Some of the benefits of the Data
Warehouse for a business are discussed below:
i. Improved Business Intelligence: The analysis report from the data warehousing helps a
lot in improving the information or the access to it. Without having, complete
information a decision cannot be made in any business organization. These decisions are

3DATABASE CONCEPTS - MODELLING AND SQL
mainly based on the data driven facts and supported by the queries and information
related to the company.
ii. Improved performance: Data warehouses are designed to achieve a faster retrieval of
the data using any query. The process of storing data is also fast in the database and it has
a larger capacity. It is designed different than the normal conventional databases to
achieve data cleansing, filtering, and analysis. Usually the Data warehouse follows the
Star or Snowflake schema for the implementation (Warnars & Randriatoamanana, 2016).
iii. Easily Accessible: In data warehouses, the users and the companies can have easy access
and on the data for storage and retrieval both. The runtime for ETL in a data warehouse
is small than the other conventional databases as it follows the specific type of schemas
which has less chains of relations between the entities. The interface implementations
can also be done with the data warehouses.
iv. Quality and Consistency: The data is stored in a data warehouse comes from multiple
different sources which cannot be stored directly into the storages. The data is first
cleaned, filtered, converted and processed, then only it is stored in the system (Ilyas &
Chu, 2015). This consistency and standardization of the data allows the users to use the
same data for analysis, query and other operations.
v. High value return: The data warehouse helps in generating an improved profits from the
cost and time saving operations. The data ware housing has become a standard
methodology for increasing business intelligence which justifies the successful impact of
data warehousing for any business.
Section B: SQL
1. select lastname||', '||firstname as fullname, Region, referred from customers

4DATABASE CONCEPTS - MODELLING AND SQL
where region like '%E%' and referred is not null;
2. Select distinct lname, fname from author where lname like '%O%' order by lname asc;
3. select isbn, title, retail, (retail-cost) as profit, category from books
where discount is null and ((retail-cost)/retail)*100>35;

5DATABASE CONCEPTS - MODELLING AND SQL
4. select c.customer#, c.lastname, c.firstname, o.order#, o.orderdate from customers c inner
join orders o on o.customer#=c.customer# and o.shipdate is null order by o.orderdate asc;
5. select isbn, title, category from books where (category like '%ER%' or category like
'%RE%' or title like '%PIZZA%') and discount is not null order by category asc;
6. select isbn, title, cost, retail from books where extract(year from
to_date(pubdate,'dd/mon/yyyy'))=03 order by retail desc;
7. select isbn, quantity, paideach, (quantity*paideach) as "Total Order Price" from
orderitems where quantity>1 order by (quantity*paideach) desc;

6DATABASE CONCEPTS - MODELLING AND SQL
8. select p.name, b.title, b.retail, ((b.retail)-nvl(discount, 0)) as "Discounted Price" from
books b inner join publisher p on p.pubid=b.pubid order by p.name asc, ((b.retail)-
nvl(discount, 0)) desc;
9. select isbn, title, retail,category, pubdate from books where (category like
'%COMPUTER%' or category like '%HELP%') and retail>20 and pubdate >'30-JUN-06';

7DATABASE CONCEPTS - MODELLING AND SQL
10. select order#, orderdate, shipdate from orders where shipcost is null and shipdate is not
null;

8DATABASE CONCEPTS - MODELLING AND SQL
Section C: DATA MODELLING
Question 1
Figure 1: Entity Relationship Diagram of Specialist Clinic
Source: Created by author

9DATABASE CONCEPTS - MODELLING AND SQL
Question 2
Figure 2: Entity Relationship Diagram of Flight Management
Source: Created by author

10DATABASE CONCEPTS - MODELLING AND SQL
Question 3
Figure 3: Entity Relationship Diagram of Building Project Company
Source: Created by author

11DATABASE CONCEPTS - MODELLING AND SQL
Question 4
Figure 4: Entity Relationship Diagram of rental management
Source: Created by author