Database Report: Analysis of ER Model and SQL Queries for Database

This report presents a comprehensive analysis of a database assignment. It begins with an Entity-Relationship (ER) diagram, which visually represents the entities, relationships, and attributes within a database system. The ER diagram includes key components such as entities, relationships (with names), attributes, primary keys, cardinality (one/many), and participation symbols (optional/mandatory). Following the ER model, the report provides a series of SQL queries designed to retrieve and manipulate data from the database. These queries demonstrate a range of operations, including data selection, filtering, aggregation, and joining of tables. The queries cover a variety of use cases, such as retrieving customer information, identifying specific complaints, calculating totals, and summarizing data based on different criteria. The report showcases the practical application of database design principles and SQL querying techniques. This report is a valuable resource for students studying database systems, providing a clear understanding of ER modeling and SQL query writing.

Running head: INTRODUCTION TO DATABASE
INTRODUCTION TO DATABASE
Name of the Student
Name of the University
Author Note

1INTRODUCTION TO DATABASE
ER Model
Figure 1: ER Model

2INTRODUCTION TO DATABASE
Source: created by author
Relational Model

3INTRODUCTION TO DATABASE
Figure 1: ER Model
Source: created by author
Queries
1.
select DISTINCT c.CustomerID, CONCAT(c.firstname, ' ',
c.lastname) as name from customers c inner JOIN complaint
cm on cm.CustomerID=c.CustomerID order by c.Firstname asc;
2.
Select customerid, CONCAT(firstname, ' ', lastname) as
name, streetaddress, town, state, postcode, worknumber,
homenumber from customers order by customerid asc;
3.
SELECT max(Date) as latestComplainDate FROM complaint;
4.
Select ComplaintNumber from Complaint where status
=’open’;
5.
Select complaintnumber, customerid, complaintdate,
complainttype from complaint order by complainttype asc;
6.
Select p.productid, p.productname from products p inner
join productcomplain pc on pc.productid=p.productid order
by p.productname asc;
7.
Select sum(p.productunitcost*pc.numberofitems) from
products p inner join productcomplain pc on
pc.productid=p.productid);
8.

4INTRODUCTION TO DATABASE
select c.customerid, count(cm.complaintnumber) from
complaint cm, customer C where c.CustomerID=cm.customerid
GROUP BY c.customerid having count(cm.complaintnumber)>4;
9.
Select e.departmentid, count(ec.complaintnumber) as
totalemployee from employee e inner join employeecomplaint
ec on ec.employeeid=e.employeeid group by e.departmentid;
10.
Select c.customerid, CONCAT(c.firstname, ' ', c.lastname)
as name, max(co.date), co.typeofcontact from customer c
inner join complaint cm on cm.customerid=c.customerid
inner join contact co on
co.complaintnumber=cm.complaintnumber and cm.status=’open’
group by c.customerid, c.firsstname, c.lastname;