Database Systems: SQL Scripting and Relational Database Queries

This assignment provides solutions to two database-related tasks. The first part involves creating a single SQL Server script to answer specific queries against the General Hardware Corp. relational database, including selecting data from the CUSTOMEREMPLOYEE, PRODUCT, SALESPERSON, and SALES relations using joins and filtering conditions. The second part focuses on the Northwind database, requiring an analysis of its schema and the provision of three examples demonstrating the use of different referential integrity rules (No Action, Set Default/Null, and Cascade) when deleting or updating records, along with business justifications for each choice. The document demonstrates the application of SQL scripting for database querying and the understanding of database design principles.

6. Consider the General Hardware Corp. relational database and the data stored in it, as shown in Figure
5.14. create a single SQL Server script (a batch) combining answers to the sections b, d, e, f. Your script
should start with the correct USE command followed by a series of queries. Beware that you should take
care to not include more info in the output than was specified in those four sections, e.g. there should be
no additional. In production environment, such “extras” may disclose confidential information or extend
the output beyond the portable device’s screen.
b. Select rows from the CUSTOMER EMPLOYEE relation in which Customer Number = 2198. Project
Employee Number and Employee Name over that result.
d. Select rows from the PRODUCT relation in which Product Number = 21765. Project Unit Price over
that result.
e. Join the SALESPERSON and CUSTOMER relations using the Salesperson Number attribute of each
as the join fields. Select rows from that result in which Salesperson Name = Baker. Project Customer
Name over that result.
f. Join the PRODUCT relation and the SALES relation using the Product Number attribute of each as the
join fields. Select rows in which Product Name = Pliers. Project Salesperson Number and Quantity over
that result.
Solution:
Script:
use GeneralHardware_00252034;
SELECT EMPNUM, EMPNAME FROM CUSTOMEREMPLOYEE WHERE CUSTNUM=2198;
SELECT UNITPRICE FROM PRODUCT WHERE PRODNUM=21765;
SELECT CUSTNAME FROM SALESPERSON INNER JOIN CUSTOMER ON SALESPERSON.SPNUM =
CUSTOMER.SPNUM WHERE SPNAME='Baker';
Select SPNUM,QUANTITY from PRODUCT INNER JOIN SALES on PRODUCT.PRODNUM = SALES.PRODNUM
where PRODNAME = 'Pliers';
Output:

Task 2
Often, database analysts and designers deal with existing databases and have to make their best guesses
about the intent of the original database creators and plan how to adapt them to reflect the business needs
of the existing owners. Northwind diagram should be created on the NHCC server, as directed in the
beginning of this Unit. Analyze it and, if necessary, for understanding, right-click on table names and
Select Top 1000 Rows. Give an example where it would be the best choice when deleting/updating a
record belonging to one of the Northwind tables. Total three examples:
(a) No Action;
(b) Set Default OR Set Null;
(c) Cascade.
Present each example in the format: • the table name on “one” side or relation; • the foreign key
involved; • business justification of using this particular rule.
As is often the case, there may be different possible answers. I will read your justifications attentively.
For example, Restrict Rule justification may be needed if we want to keep records not just for an ongoing
operation but also for a possible audit of the past transactions.

Solution:
Northwind Diagram:
Categories
CategoryID int
CategoryName nvarchar(15)
Description ntext
Picture image
Column Name Data Type Allow Nulls
Customers
CustomerID nchar(5)
CompanyName nvarchar(40)
ContactName nvarchar(30)
ContactTitle nvarchar(30)
Address nvarchar(60)
City nvarchar(15)
Region nvarchar(15)
PostalCode nvarchar(10)
Country nvarchar(15)
Phone nvarchar(24)
Fax nvarchar(24)
Column Name Data Type Allow Nulls
Employees
EmployeeID int
LastName nvarchar(20)
FirstName nvarchar(10)
Title nvarchar(30)
TitleOfCourtesy nvarchar(25)
BirthDate datetime
HireDate datetime
Address nvarchar(60)
City nvarchar(15)
Region nvarchar(15)
PostalCode nvarchar(10)
Country nvarchar(15)
HomePhone nvarchar(24)
Extension nvarchar(4)
Photo image
Notes ntext
ReportsTo int
PhotoPath nvarchar(255)
Column Name Data Type Allow Nulls
Order Details
OrderID int
ProductID int
UnitPrice money
Quantity smallint
Discount real
Column Name Data Type Allow Nulls
Orders
OrderID int
CustomerID nchar(5)
EmployeeID int
OrderDate datetime
RequiredDate datetime
ShippedDate datetime
ShipVia int
Freight money
ShipName nvarchar(40)
ShipAddress nvarchar(60)
ShipCity nvarchar(15)
ShipRegion nvarchar(15)
ShipPostalCode nvarchar(10)
ShipCountry nvarchar(15)
Column Name Data Type Allow Nulls
Products
ProductID int
ProductName nvarchar(40)
SupplierID int
CategoryID int
QuantityPerUnit nvarchar(20)
UnitPrice money
UnitsInStock smallint
UnitsOnOrder smallint
ReorderLevel smallint
Discontinued bit
Column Name Data Type Allow Nulls
Shippers
ShipperID int
CompanyName nvarchar(40)
Phone nvarchar(24)
Column Name Data Type Allow Nulls
Suppliers
SupplierID int
CompanyName nvarchar(40)
ContactName nvarchar(30)
ContactTitle nvarchar(30)
Address nvarchar(60)
City nvarchar(15)
Region nvarchar(15)
PostalCode nvarchar(10)
Country nvarchar(15)
Phone nvarchar(24)
Fax nvarchar(24)
HomePage ntext
Column Name Data Type Allow Nulls
Examples:
(a) No Action
Table Name: Category
Foreign Key Involved: CategoryID
Business Justification:
When a particular category is to be deleted but it has product which belongs to the category, the
category should not be deleted and hence no action is performed.
(b) Set Default OR Set Null;
Table Name: Shipper
Foreign Key Involved: ShipVia in Order Table
Business Justification:

In the stream of the business many shippers come and some leave in between. It is not important to
maintain these details in order too. Therefore, it can be updated to NULL. So, when a shipper is
deleted the corresponding ShipVia attribute is set to NULL.
(c) Cascade:
Table Name: Order
Foreign Key Involved: OrderID
Business Justification:
When particular Order is deleted all the OrderDetails of the particular OrderID is deleted. This is
done because sometimes a customer may cancel the order in such case all the order details of them
can be removed from the table and need not be maintained in the database.