Appendix B: Database Schemas

Suppliers Region Northwind

Database
PK SupplierID PK RegionID
Employees
CompanyName RegionDescription
ContactName
ContactTitle
PK EmployeeID
Diagram
LastName
Address
FirstName
City
Title
Region Territories
TitleOfCourtesy
PostalCode
PK TerritoryID BirthDate
Country
HireDate
Phone CustomerDemographics
TerritoryDescription Address
Fax
FK1 RegionID City
HomePage PK CustomerTypeID
Region
PostalCode
CustomerDesc
Country
HomePhone
EmployeeTerritories Extension
Photo
Products PK,FK1 EmployeeID
Notes
PK,FK2 TerritoryID CustomerCustomerDemo
PK ProductID ReportsTo
PhotoPath PK,FK2 CustomerID
ProductName PK,FK1 CustomerTypeID
FK2 SupplierID
FK1 CategoryID
QuantityPerUnit
UnitPrice Orders
Order Details
UnitsInStock
UnitsOnOrder PK,FK1 OrderID PK OrderID
ReorderLevel PK,FK2 ProductID Customers
Discontinued FK1 CustomerID
UnitPrice FK2 EmployeeID PK CustomerID
Quantity OrderDate
Discount RequiredDate CompanyName
ShippedDate ContactName
FK3 ShipVia ContactTitle
Freight Address
Categories
Shippers ShipName City
PK CategoryID ShipAddress Region
PK ShipperID ShipCity PostalCode
CategoryName ShipRegion Country
Description CompanyName ShipPostalCode Phone
Picture Phone ShipCountry Fax
 Library
Database Diagram

member loan loanhist

PK member_no PK,FK1 isbn PK,FK1 isbn

PK,FK1 copy_no PK,FK1 copy_no
lastname PK out_date
firstname FK3 title_no
middleinitial FK2 member_no FK2 title_no
photograph out_date member_no
due_date due_date
reservation in_date
PK,FK1 isbn fine_assessed
PK,FK2 member_no fine_paid
fine_waived
log_date remarks
adult
remarks
PK,FK1 member_no

street
city
state
zip copy
phone_no item
expr_date PK,FK1 isbn
PK isbn PK copy_no

FK1 title_no FK2 title_no

translation on_loan
cover
loanable

title
juvenile
PK title_no
PK,FK2 member_no
title
FK1 adult_member_no author
birth_date synopsis
 Pubs
stores

PK stor_id
discounts Database
stor_name Diagram
stor_address discounttype pub_info
city FK1 stor_id PK,FK1 pub_id
state lowqty
zip highqty logo
discount pr_info

titles employee
jobs
PK title_id publishers PK emp_id
sales PK job_id
PK pub_id
I1 title I1 fname
PK,FK1 stor_id type I1 minit job_desc
PK ord_num pub_name min_lvl
FK1 pub_id I1 lname
PK,FK2,I1 title_id city max_lvl
price FK1 job_id
state
advance job_lvl
ord_date country
royalty FK2 pub_id
qty ytd_sales hire_date
payterms notes
pubdate

authors

PK au_id
roysched titleauthor
I1 au_lname
FK1,I1 title_id PK,FK1,I1 au_id I1 au_fname
PK,FK2,I2 title_id phone
lorange address
hirange au_ord city
royalty royaltyper state
zip
contract
 Joindb
Database
Diagram

Buyers
Produce
Sales PK buyer_id
PK prod_id
buyer_name
prod_name
FK1 buyer_id
FK2 prod_id
qty