Assignment
ISCG6423
Database Design and Development
Case Study C:
Assignment Information
This information may contain ambiguities and/or errors, and may be subject to change – please seek advice from the lecturer if necessary.
Numbers of Records
Client: 1,000 – will increase often
Each Client has an average of 4 orders on the system at any one time
Each client order, on average, consists of 3 books
Each client has, on average, 2 preferred categories
Book Info: 4,000 – will increase often
On average, there are three individual books of the same title
Author: 1000 – will increase but not often
On average each author will have 4 book titles
Numbers of Records
Vendor: 100 – may increase
Each vendor offers, on average, 120 books
Each individual book will belong to one category on average
60% of vendors have contact details
Category: 40 – very unlikely to change
Each Category is preferred by, on average, 50 clients
On average there are 300 individual books of each category
Country: 50 – may increase
Each country has 20 clients on average
Each country has 2 vendors on average
Important Processes
The two most important processes are:
All shipped orders for all clients
Run on average 4 times a day
All vendors and available books
Run on average 3 times a day
Please note that all report processes should take precedence over the data maintenance processes
Notes on Logical Design
The following entities are reference data:
CATEGORY
COUNTRY
AUTHOR
The following entities are associative entities:
BOOK CATEGORY
CLIENT CATEGORY
Look-up Data
CLIENT ORDER
Status
P – Paid
C – Current
S – Shipped
CLIENT
Credit Status
Good
Bad
CLIENT CATEGORY
CC_Level
High
Low
BOOK
Available
Y – Yes
N – No
VENDOR
CATEGORY
COUNTRY
BOOK
BOOK
INFO
CLIENT
CLIENT
ORDER
AUTHOR
Book Brokers Conceptual ERD
CONTACT
BOOK
CATEGORY
VENDOR
CATEGORY
COUNTRY
BOOK
BOOK
INFO
CLIENT
CLIENT
ORDER
CLIENT
CATEGORY
AUTHOR
Book Brokers Logical ERD
CONTACT
Book Brokers Application Relations
COUNTRY (CountryID, CountryName)
CATEGORY (CategoryID, Description)
CLIENT (ClientID, LastName, FirstName, StreetAddress, Suburb, City,
CountryID*, CreditStatus, PhoneNumber)
CLIENTORDER (ClientOrderID, OrderDate, ClientID*, Status)
AUTHOR (AuthorID, LastName, FirstName)
VENDOR (VendorID, VendorName, CountryID*, Email)
BOOKINFO (BookInfoID, Title, AuthorID*, Notes)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
BOOKCATEGORY (BookID*, CategoryID*)
CLIENTCATEGORY (ClientID*, CategoryID*, CC_Level)
CONTACT (VendorID*, LastName, FirstName, Email)
COUNTRY
CLIENT
CLIENT
ORDER
1) Enter, modify or delete Clients
CLIENT
CATEGORY
COUNTRY (CountryID, CountryName)
CLIENT (ClientID, LastName, FirstName, StreetAddress, Suburb, City,
CountryID*, CreditStatus, PhoneNumber)
CLIENTORDER (ClientOrderID, OrderDate, ClientID*, Status)
CLIENTCATEGORY (ClientID*, CategoryID*, CC_Level)
VENDOR
BOOK
2) Enter, modify or delete Vendors
COUNTRY
CONTACT
COUNTRY (CountryID, CountryName)
VENDOR (VendorID, VendorName, CountryID*, Email)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
CONTACT (VendorID*, LastName, FirstName, Email)
BOOK
CATEGORY
VENDOR
BOOK
BOOK
INFO
3) Enter, modify or delete Books
AUTHOR
AUTHOR (AuthorID, LastName, FirstName)
VENDOR (VendorID, VendorName, CountryID*, Email)
BOOKINFO (BookInfoID, Title, AuthorID*, Notes)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
BOOKCATEGORY (BookID*, CategoryID*)
VENDOR
BOOK
BOOK
INFO
CLIENT
CLIENT
ORDER
4) Enter, modify or delete Clients’ Book Orders
COUNTRY
COUNTRY (CountryID, CountryName)
CLIENT (ClientID, LastName, FirstName, StreetAddress, Suburb, City,
CountryID*, CreditStatus, PhoneNumber)
CLIENTORDER (ClientOrderID, OrderDate, ClientID*, Status)
VENDOR (VendorID, VendorName, CountryID*, Email)
BOOKINFO (BookInfoID, Title, AuthorID*, Notes)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
BOOK
CATEGORY
CATEGORY
BOOK
BOOK
INFO
CLIENT
5) Add or remove Categories to or from Books and Clients
CLIENT
CATEGORY
AUTHOR
CATEGORY (CategoryID, Description)
CLIENT (ClientID, LastName, FirstName, StreetAddress, Suburb, City,
CountryID*, CreditStatus, PhoneNumber)
AUTHOR (AuthorID, LastName, FirstName)
BOOKINFO (BookInfoID, Title, AuthorID*, Notes)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
CLIENTCATEGORY (ClientID*, CategoryID*, CC_Level)
BOOKCATEGORY (BookID*, CategoryID*)
BOOK
CLIENT
CLIENT
ORDER
6) Mark a Client Order as shipped or paid
CLIENT (ClientID, LastName, FirstName, StreetAddress, Suburb, City,
CountryID*, CreditStatus, PhoneNumber)
CLIENTORDER (ClientOrderID, OrderDate, ClientID*, Status)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
VENDOR
BOOK
BOOK
INFO
7) All Vendors and unavailable Books
AUTHOR
CONTACT
AUTHOR (AuthorID, LastName, FirstName)
VENDOR (VendorID, VendorName, CountryID*, Email)
BOOKINFO (BookInfoID, Title, AuthorID*, Notes)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
CONTACT (VendorID*, LastName, FirstName, Email)
VENDOR
BOOK
BOOK
INFO
8) All Vendors and available Books
AUTHOR
CONTACT
AUTHOR (AuthorID, LastName, FirstName)
VENDOR (VendorID, VendorName, CountryID*, Email)
BOOKINFO (BookInfoID, Title, AuthorID*, Notes)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
CONTACT (VendorID*, LastName, FirstName, Email)
VENDOR
COUNTRY
BOOK
BOOK
INFO
CLIENT
CLIENT
ORDER
9) All shipped Orders for all Clients
COUNTRY (CountryID, CountryName)
CLIENT (ClientID, LastName, FirstName, StreetAddress, Suburb, City,
CountryID*, CreditStatus, PhoneNumber)
CLIENTORDER (ClientOrderID, OrderDate, ClientID*, Status)
VENDOR (VendorID, VendorName, CountryID*, Email)
BOOKINFO (BookInfoID, Title, AuthorID*, Notes)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
CATEGORY
COUNTRY
CLIENT
CLIENT
CATEGORY
10) All clients
COUNTRY (CountryID, CountryName)
CATEGORY (CategoryID, Description)
CLIENT (ClientID, LastName, FirstName, StreetAddress, Suburb, City,
CountryID*, CreditStatus, PhoneNumber)
CLIENTCATEGORY (ClientID*, CategoryID*, CC_Level)
VENDOR
BOOK
BOOK
INFO
11) Books by vendor
VENDOR (VendorID, VendorName, CountryID*, Email)
BOOKINFO (BookInfoID, Title, AuthorID*, Notes)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
BOOK
CLIENT
CLIENT
ORDER
12) Books by client
CLIENT (ClientID, LastName, FirstName, StreetAddress, Suburb, City,
CountryID*, CreditStatus, PhoneNumber)
CLIENTORDER (ClientOrderID, OrderDate, ClientID*, Status)
BOOK (BookID, Cost, Price, Available, DatePublished,
BookInfoID*, VendorID*, ClientOrderID*)
Referential Integrity Constraints
A client with categories can be deleted
A client with client orders cannot be deleted
A country with clients cannot be deleted
A country with vendors cannot be deleted
An author with book info cannot be deleted
A book info with books cannot be deleted
A client order with books can be deleted – the books will have their client order ID set to null
A vendor with books cannot be deleted
If a vendor is deleted its contact details are deleted
If a book is deleted, its categories are also deleted
A category with books cannot be deleted
A category with clients cannot be deleted
Please note that operational business rules can override these constraints