Project Work Answers- Microsoft Server Management :CIS103

Running head: DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN
DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND
ERWIN
N …

Preview text

Running head: DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN
DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND
ERWIN
Name of the Student:
Name of the University:
Authors Note:
1 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
Table of Contents
Introduction …………………………………………………………………………………………………………………….. 3
Part 1: Table creation using Erwin forward engineering and MS SQL Server …………………………. 3
Data Model ………………………………………………………………………………………………………………….. 3
Logical View ……………………………………………………………………………………………………………. 3
Physical View …………………………………………………………………………………………………………… 4
Part 2: SQL Statements and Queries ………………………………………………………………………………….. 5
A. List of Customers …………………………………………………………………………………………………… 5
B. List the Customer by City ……………………………………………………………………………………….. 6
C. List Customer and Sales Representative name …………………………………………………………… 7
D. Employees Not Completed CourseID =100 ……………………………………………………………… 7
E. How many sales reps Does PSC have- ……………………………………………………………………… 8
F. Employees By larger Sales Commision Rate …………………………………………………………….. 9
G. Manager(s) of the sales reps ……………………………………………………………………………………. 9
H. Employees managed by Sales manager …………………………………………………………………… 10
I. Who is the manager of the manager of the sales reps- ………………………………………………. 11
J. Employee names of those that report directly to the manager of the sales manager
Statement of academic integrity ……………………………………………………………………………………. 11
K. Most Costly Product …………………………………………………………………………………………….. 12
L. Employees and Course completed ………………………………………………………………………….. 12
2 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
Conclusion ……………………………………………………………………………………………………………………. 13
Bibliography …………………………………………………………………………………………………………………. 14
Appendices …………………………………………………………………………………………………………………… 16
3 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
Introduction
This study is based on database management and data modelling techniques. The
Microsoft Server management studio has been used for creating database. The Erwin data
modeling tool is being used in this study to create the data model for the following database
where two types of view has been created including the4 logical view and Physical view.
Initially the data model has been created and by using Forward engineering features in the Erwin
data modeler, the SQL script has been generated.
Part 1: Table creation using Erwin forward engineering and MS SQL Server
Data Model
Logical View
Figure 1: Logical view of database
(Source: Created by Author)
4 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
The above figure represents the logical view of the database schema and this data model
has been created by using Erwin data modeler. Through this logical view cardinality,
relationships between these entities have been represented.
Physical View
Figure 2: Physical View of database
(Source: Created by Author)
The above figure represents a Physical view of the database that is different than the
logical view. This logical view includes cardinality, relationship similar to the logical view.
However, the advantage of physical view is it helps to define all the elements that can be used
for creating actual database based on the requirements.
5 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
Part 2: SQL Statements and Queries
In this section, this paper provides documentation of different queries using the database.
A. List of Customers
Query:
Select CustomerID, CustomerName, CustomerState
from CUSTOMER
Order by CustomerName ASC;
The above query gives the data represents alist of customers along with their ID, Names,
City and States. In this case ortder by command has been used to sort the customer ’slist in
alphabetical order.
6 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
B. List the Customer by City
Query:
Select CustomerID, CustomerName, CustomerCity
from CUSTOMER
Group by CustomerCity, CustomerID, CustomerName
Order by CustomerName ASC;
The above query gives data related to the customers by sorting the list by the customers
city in alphabetical order.
7 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
C. List Customer and Sales Representative name
Query:
Select a.CustomerID, a.CustomerName, Concat (a.CustomerStreet, ‘,’,a.CustomerZip) as
CustomerAddress, concat (b.EmployeeFirstName, ”,b.EmployeeLastName) as
SalesRep_name
from CUSTOMER a, EMPLOYEE b
Where b.EmployeeID =a.SalesRepID
Order by a.CustomerName;
The data provides customers ’details and their sales representative names by alphabetical
order.
D. Employees Not Completed CourseID = 100
Query:
select concat (a.EmployeeFirstName, ”,a.EmployeeLastName) as
EmployeeName, b.CourseID
from EMPLOYEE a, EMPLOYEE_COURSE b
8 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
where CourseID Not in (select CourseID from EMPLOYEE_COURSE where
CourseID =100)
And a.EmployeeID =b.EmployeeID;
E. How many sales reps Does PSC have-
Query:
Select Count (EmployeeID) from SALES_REPRESENT
Where CommissionRate is Null;
9 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
F. Employees By larger Sales Commision Rate
Query:
select a.EmployeeID, concat (b.EmployeeFirstName, ”,b.EmployeeLastName) as
EmployeeName, a.CommissionRate
from SALES_REPRESENT a, EMPLOYEE b
Where a.EmployeeID =b.EmployeeID
Order by a.CommissionRate DESC;
G. Manager(s) of the sales reps
Query:
select b.ManagerID, concat (b.EmployeeFirstName, ”,b.EmployeeLastName) as
Manager, a.CommissionRate
from SALES_REPRESENT a, EMPLOYEE b
Where a.EmployeeID =b.EmployeeID
Order by a.CommissionRate DESC;
10 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
H. Employees managed by Sales manager
Query:
Select EmployeeID, EmployeeFirstName, EmployeeLastName,
EmployeeJobTitle from EMPLOYEE
Where EmployeeID not in (Select EmployeeID from EMPLOYEE where
EmployeeJobTitle =’SalesManager’);
11 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
I. Who is the manager of the manager of the sales reps-
Query:
Select EmployeeID, EmployeeFirstName, EmployeeLastName,
EmployeeJobTitle from EMPLOYEE
Where EmployeeID not in (Select EmployeeID from EMPLOYEE where
EmployeeJobTitle =’SalesRep’);
J. Employee names of those that report directly to the manager of the sales
manager Statement of academic integrity
Query:
Select EmployeeID, EmployeeFirstName, EmployeeLastName, EmployeeJobTitle
From EMPLOYEE
Where EmployeeID in (Select ManagerID From EMPLOYEE Where EmployeeJobTitle
=’SalesManager’);
12 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
K. Most Costly Product
Query:
Select p.ProductID, p.ProductDescription, p.UnitPrice, s.SupplierName, a.ProductCost,
a.PurchaseQuantity,(p.UnitPrice *a.PurchaseQuantity) as Most_costly_Product
From PRODUCT p, SUPPLIER s, PRODUCT_SUPPLIER a
Where p.ProductID =a.ProductID
And s.SupplierID =a.SupplierID
Order by Most_costly_Product DESC;
L. Employees and Course completed
Query:
Select CONCAT (a.EmployeeFirstName, ”,a.EmployeeLastName) as Employee_Name,
b.CourseID, c.CourseDescription, b.CompletionDate
From EMPLOYEE a, EMPLOYEE_COURSE b, COURSE c
Where a.EmployeeID =b.EmployeeID
And b.CourseID =c.CourseID
Order By Employee_Name ASC;
13 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
Conclusion
This study has generated different queries where some of the queries included aggregate
functions taking advantage of the relational database. In this study Microsoft server management
studio and Erwin data model has been used. Most importantly this paper utilizes the forward
engineering techniques of the data modeling tool for creating the initial schema of the database.
The study has found that both the physical and logical data modelling techniques are very much
effective in implement arelational database.
14 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
Bibliography
Chiranjeevi, H.S., Shenoy, M.K. and Sundar, D.S., 2018. Integrating on-premises data with
customer relationship management application on cloud: A hybrid IT infrastructure support
service. Cogent Engineering ,5(1), p.1462755.
Danielsson, J. and Danielsson, O., 2021. My media favorites on any device: A study about the
development and evaluation of a partly cloud-based and partly on-premise solution based on
Microsoft Platform.
Ili ć,M., Kopanja, L., Zlatkovi ć,D., Trajkovi ć,M. and Ćurguz, D., 2021, June. Microsoft SQL
Server and Oracle: Comparative performance analysis. Book of proceedings of the 7th
International conference Knowledge management and informatics, Vrnja čka Banja.
Kim, B.S. and Kim, T.G., 2019. Cooperation of simulation and data model for performance
analysis of complex systems. International Journal of Simulation Modelling ,18 (4), pp.608-619.
Kraleva, R., Kralev, V. and Sinyagina, N., 2018. Design and Analysis of aRelational Database
for Behavioral Experiments Data Processing. International Journal of Online Engineering ,14 (2).
Liang, Y., Li, B., Li, X., Deng, X., Lu, J. and Shi, Z., 2022, April. Research on HPLC
Experimental Platform Component Scheme. In Journal of Physics: Conference Series (Vol. 2258,
No. 1, p. 012068). IOP Publishing.
Moral-Benito, E., Allison, P. and Williams, R., 2019. Dynamic panel data modelling using
maximum likelihood: an alternative to Arellano-Bond. Applied Economics ,51 (20), pp.2221-
2232.
Piispanen, J., 2022. Microsoft Azure as an Integration Platform.
15 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
Sahatqija, K., Ajdari, J., Zenuni, X., Raufi, B. and Ismaili, F., 2018, May. Comparison between
relational and NOSQL databases. In 2018 41st international convention on information and
communication technology, electronics and microelectronics (MIPRO) (pp. 0216-0221). IEEE.
Setyawati, E., Wijoyo, H. and Soeharmoko, N., 2020. Relational Database Management System
(RDBMS).
Tavast, A., Langemets, M., Kallas, J. and Koppel, K., 2018, July. Unified data modelling for
presenting lexical data: The case of ekilex. In Ed. J. ÄŒibej, V. Gorjanc, I. Kosem & Simon Krek,
Proceedings of the XVIII EURALEX International Congress: EURALEX: Lexicography in
Global Contexts, Ljubljana (pp. 749-761).
16 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
Appendices
Detach database
17 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
18 DATABASE DEVELOPMENT AND DATA MODELING USING SQL SERVER AND ERWIN DATA MODELER
Customer table

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more