程序代写代做代考 database case study Assignment

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