Database Design, Queries, and ER Diagram Assignment Solution

This assignment solution addresses key concepts in database management systems (DBMS). It differentiates between a database and a DBMS, explaining their roles and functionalities. The solution includes an ER diagram for a sales management system, created using Microsoft Access, demonstrating database design principles. It also presents sample SQL queries for retrieving and manipulating data within the database, such as listing territories and creating a customer master list. Furthermore, the assignment covers the design of relational database tables, including the identification of primary and foreign keys, and relationships between tables in an employee-branch-empPhoto scenario. The document showcases practical application of database concepts and provides a comprehensive understanding of database design and query implementation.

Part 1: Database vs DBMS
Database can be defined as a collection of a data. It may include a word document or even a
digital file. However, Database Management System (DBMS) is a software which manages
database and also allows users to create, update and delete data/databases. The data is created
in the form of tables. Each table has various rows of data and respective attributes to define
the columns (Campbell, 2014).
A sales management system has been created for an organization – HOLT. ER diagram of the
proposed database is shown below:
Microsoft access has been chosen to make the pictorial representation because it is easy to
use and install. It can be used for both frontend and backend operations (Guan, 2004).
Data is shown as below:

Sample queries:
1. List of territories:
SELECT t.tNo AS Territory, t.tName AS [Territory Name], s.sNo AS [Sales Person No],
([s].[firstName]+' '+[s].[lastName]) AS [Sales Person], s.Address AS Address, c.cNo AS
[Customer No], ([c].[firstName]+' '+[c].[lastName]) AS [Customer Name], (c.addFirstLine +
', ' + c.city + ', ' + c.state + ', ' + c.zip) AS [Customer Address]
FROM customer AS c, territory AS t, salesRep AS s
WHERE (((t.tNo)=s.territory) And ((s.sNo)=c.saleRep));
2. Customer master list
SELECT c.cNo, (c.addFirstLine + ', ' + c.city + ', ' + c.state + ', ' + c.zip) AS
MainAddress, (c.sFirstLine + ', ' + c.cCity + ', ' + c.cState + ', ' + c.cZip) AS
ShipToAddress, s.sNo, (s.firstName + ' ' + s.lastName) AS SalePerson, (s.address + ', ' +
s.City + ', ' + s.State + ', ' + s.zip) AS Address, t.tNo, t.tName
FROM customer AS c, salesRep AS s, territory AS t
WHERE c.saleRep = s.sNo and s.territory = t.tNo;

Part 2: ER diagram for the employee-branch-empPhoto scenario:
Data in the database is shown as below:

Foreign and primary key: Primary key is denoted by a “key” symbol besides the key name.
Branch table does not have any primary key.
Following are relationships exist among the tables:
 Between Employee and Emp_Phot – One to many
 Between Employee and Branch – One to many
 Between Branch and Emp_Phot – One to many
References
Campbell, M. (2014). The Microsoft Access handbook (2nd ed., pp. 12-14). Berkeley:
Osborne McGraw-Hill.
Guan, K. (2004). Microsoft Access (2nd ed., pp. 11-14). Sandgate, Qld.: Knowledge Books
and Software.