[SOLVED] database ISCG6423 Database Design and Development Assignment Requirements

ISCG6423 Database Design and Development Assignment Requirements
Case Study C: Book Broker Services Expected Results
1. Enter, modify or delete clients
List clients (client ID, first name, last name, street address, suburb, city, credit status, country name, and phone number) by first name within last name
Page 1 of 19

ISCG6423 Database Design and Development Assignment Requirements
List countries (country Id and country name) by country name
Page 2 of 19

ISCG6423 Database Design and Development Assignment Requirements
2. Enter, modify or delete vendors
List vendors (vendor ID, vendor name, country name, contact first name, contact last name, email) by vendor name.
List countries (country Id and country name) by country name.
Page 3 of 19

ISCG6423 Database Design and Development Assignment Requirements
List contact details (last name, first name, and email) for a selected vendor
To test this query, you need to hard code an ID of any vendor who has a contact. To get
the results below, I chose the vendor whose ID is 2:
Page 4 of 19

ISCG6423 Database Design and Development Assignment Requirements
3. Enter, modify or delete books
List books (book ID, cost, price, status, date published, book info ID, and vendor name) by book ID
Truncated first 34 out 44 records shown:
Page 5 of 19

ISCG6423 Database Design and Development Assignment Requirements
List book info (book info ID, title, author name, and notes) by title.
Page 6 of 19

ISCG6423 Database Design and Development Assignment Requirements
List authors (author id, last name and first name) by first name within last name
List vendors (vendor ID, vendor name, email) by vendor name
Page 7 of 19

ISCG6423 Database Design and Development Assignment Requirements
4. Enter, modify or delete client orders
List client orders (client order id, date, status and client name) by client order ID
Page 8 of 19

ISCG6423 Database Design and Development Assignment Requirements
List clients (client ID, first name, last name, street address, suburb, city, credit status, country name, phone number) by first name within last name
Page 9 of 19

ISCG6423 Database Design and Development Assignment Requirements
List books (book ID, price, availability, date published, title, and vendor name) by title Truncated first 34 out 44 records shown:
Page 10 of 19

ISCG6423 Database Design and Development Assignment Requirements
5. Add or remove categories to or from books and clients
List clients (client ID, first name, last name, street address, suburb, and city) by first name within last name
Page 11 of 19

ISCG6423 Database Design and Development Assignment Requirements
List books (book ID, price, availability, date published, title, and author name) by book ID within title Truncated first 34 out 44 records shown:
Page 12 of 19

ISCG6423 Database Design and Development Assignment Requirements
List categories (category id and category description) by description.
Page 13 of 19

ISCG6423 Database Design and Development Assignment Requirements
6. Mark a client order as shipped or paid
List client orders (client order id, date, status and client name) by client first name within client last name
List books (book id and price) for a selected client order.
To test this query, you need to hard code an ID of any client order that has books. To get
the results below, I chose the client order whose ID is 1006:
Page 14 of 19

ISCG6423 Database Design and Development Assignment Requirements
7. All vendors and unavailable books
For all vendors with unavailable books, show the vendor id, vendor name, vendor contact name, the book id, title, author name and price sorted by book title within vendor name.
Page 15 of 19

ISCG6423 Database Design and Development Assignment Requirements
8. All vendors and available books
For all vendors with available books, show the vendor id, vendor name, vendor contact name, the book id, title, author name and price sorted by book title within vendor name.
Page 16 of 19

ISCG6423 Database Design and Development Assignment Requirements
9. All shipped orders for all clients
For all clients with shipped client orders, show the client id, last name, first name, street address, suburb, city and country name, client order id, client order date, book id, title, price, and vendor name sorted by first name within fast name.
10. All clients
For all clients, show the client id, last name, first name, street address, suburb, city and country name, client category level, and category description sorted by first name within fast name.
Page 17 of 19

ISCG6423 Database Design and Development Assignment Requirements
11. Books by vendor
For all books show the book id, cost, availability, date published, title, and vendor name sorted by book title.
Truncated first 34 out 44 records shown:
Page 18 of 19

ISCG6423 Database Design and Development Assignment Requirements
12. Books by client
For all books show the book id, price, client order id, client order date, client last name and first name sorted by first name within fast name.
Page 19 of 19