FUNDAMENTALS OF

DATABASES
Northwind project

NGUYEN Hoang Ha
Email: nguyen-hoang.ha@usth.edu.vn
 Context
¡ Northwind a fictious company that imports and exports
specialty foods from around the world.
¡ Northwind’s office is in the Seattle, Washington, USA and
London, British Isles.
¡ Mission: develop a RDB for business of Northwind

2
DB Modelling and Implementation Process

High-Level RDB
Ideas RDBMS
Design Schema

3
GETTING IDEAS
 Interview to get Ideas
¡ What kind of objects objects/entities do you want to manage?
¡ List of Suppliers who provide products for Northwinds
¡ List of Customers who buy products
¡ Products: list of product information
¡ Purchase orders: the order that Northwind place with their suppliers.
¡ Orders: the selling orders
¡ Shippers: the company helps Northwind to ship products to customer
according to the order
¡ Invoices: the bill issued once the order completed
¡ Employees: the employees’ information. As the sale persons, the
employees support client make orders, to follow up the order, to select
shipper for each order.
¡ order to by products from Suppliers.

5
 Interview to get Ideas
¡ What is the detailed information of each entities/objects
¡ Products: are the products that Northwind trades in. It has a ID, Name, List
Price, and Category name.
¡ Customers: who buy products from Northwind with following properties:
Name, Email (unique), Phone, Address.
¡ Suppliers– who supply to the company with following information: Name,
Email (unique), Phone, Address.
¡ Orders: the order that customers place with Northwind. Each Order consists
OrderCode which is unique, of Order Date, and some lines which are often
called Order Details with following information: which Product, Quantity, and
Unit Price.
¡ Purchase Orders (a.k.a. PO): refer to purchase order transaction that
Northwind makes with a Supplier. Each PO has Creation Date, an ordinal
number which is unique with each Supplier, Payment Method (cash, check,
or transfer), and a list of Purchase Order Details describing which Product,
Quantity, and Unit Cost.

6
 Bussiness processes
¡ Northwind wants to Insert, update, delete, make a report on
the list of: suppliers, customers, products, POs, orders,
shippers, invoices, employees.
¡ But do not allow to delete products, employees, customers, suppliers,
shipper who/which already invovled in an order/PO/invoice.

¡ When staff of Northwind record an order/PO/invoice

sucessfully, he should be able to print out it.
¡ The manager of Northwind wants to see some reports on
the busuniess to be able to make better decissions.

7
 Some reports for the manager

8
9
 What are the reports do you want
¡ What are the reports needed?

10
11
12
13
14
 Other Reports
¡ Show the product (all columns) whose deviation between List Price and
Standard Cost is the largest.
¡ Show the best seller product (which is one being ordered the most so far)
¡ Compute the average List Price of all products
¡ Compute the average List Price and the standard deviation of each category
¡ Count how many employees in each city
¡ Count how many items in each category
¡ Show list of product categories with their average prices from small to big
¡ Show the list of product names that have not been ordered ever.
¡ Show how many orders each Customer ID has placed.
¡ Identify the employee ID corresponding to the employee having the highest
number of orders

15
 ¡ Show the last name of the employee having the highest number of orders
¡ Compute the subtotal of each Order ID where the subtotal is the sum of values of all the
corresponding order lines. Note: the value of each line was mentioned in question 11 of
Assignment 3, which is (Quantity * [Unit Price])*(1-Discount)
¡ Show the Order ID(s) with a subtotal greater than 1500 in the descending order.
¡ Count how many orders each Employee ID have processed. If an Employee ID has no
corresponding order, his results should be 0. A part of the results will look like the below figure.
¡ Compute the sale of each product. The results should consist of 3 columns: Product ID, Product
Name, and Sale.
¡ Show products who List Price(s) are greater than the average List Price of all products in the
table Products.
¡ Compute the average shipping fee to each city of orders with status “Closed”. The resulting rows
consist of only average values greater than or equal to 100, and is sorted in ascending sequence.
¡ Show products who List Price(s) are greater than the average List Price of all products in the
same category. The results includes 4 columns: Product Name, List Price, Category, and AVG Price
of Category
¡ Compute how many days in average each Employer ID processes orders (from the Order Date
to the Shipped Date). Note: only orders with Shipped Date filled are considered in this query.
¡ Retrieve information of orders with the following columns: Customer’s Company, Employee’s
Last Name, Order Date, and Order Status Name.

16
DERIVING THE ERD FOR
A HIGHT LEVEL DESIGN
Suggested Tools: draw.io/Visio/Word
 1st step: select Entity sets/Properties
¡ Underlines all the nouns from the business descriptions
¡ Entity Set: select the nouns you need to manage as a lists,
things contains the detail information
¡ Properties: simple data belonging to an Entity set

18
 2nd step: Propose the relationships
¡ Focus on the verbs in the business decriptions
¡ Chosing caldinality (1-1, 1-n, n-n) for each relationship

19
 3nd step: refine/revise the ERD
¡ Decide each Entityset is weak or not
¡ If weak, should we add an artificial ID to make it strong?
¡ Test the reports: is this design convenient to build these
reports?

20
 Email Address

Name Order Date

Phone
OrderCode
Quantity Unit Price
List Price
Name
1 N N Includes Order
Customers Make Orders
Details
Category
ID
Name

Address
Email N
Creation
Phone Products
Name Ordinal Date Unit Cost
Number Quantity

N
1 N Includes Purchase
Suppliers Supply Purchase Orders
Order Details
N

21
 A proposed ERD (with out enough
attributes)

22
CONVERT ERD TO DB SCHEMA
 Tables
¡ All entity sets to become tables:
¡ Products
¡ Employees
¡ Suppliers
¡ Customers
¡ Orders
¡ Shippers

¡ N-n relationships to becomes tables, add attributes of theses

relationship to colums of tables
¡ Order details (OrderID, ProductID, Quantity, Price, Discount)
¡ Purchase order details
24
 What are columns of tables?
¡ Attributes of entity sets à columns of table
¡ For 1-n relashipship, use the Primary Key of the 1-side entity
set to become Foreign Key of the n-side entity set
¡ Eg: Orders (OrderID, CustomerID, …) where CustomerID is the
Foreign key

25
 Final database Schema
¡ List all tables with their attributes and contraints (PR, FK,
…)

26
SQL FOR REAL DBMS
 SQL statements
¡ DDL: already ready for Northwind
¡ Define db
¡ Define tables with contraints, index if any
¡ Other objects: views, procedures, triggner…

¡ DML
¡ How to CRUD: insert, update, delete
¡ How to perform queries for require questions

¡ Write all SQL statement in *.sql files

28