Instruction: This is a group assignment.
Total Marks: 100
Task: ICT701 Relational Database Systems Assignment
Case Study: Tuff Toys – Ordering and Inventory System
Background
Tuff Toys is a toy wholesale company which was formed in the 1980s by two brothers and a friend. Robert had considerable experience as a buyer for a competing toy wholesaler in the early 1980s and now spends a considerable amount of time overseas, visiting toy fairs and identifying prospective stock for Tuff Toys. His brother Tom is an experienced accountant who manages the Tuff Toys Head Office and Warehouse. Their friend, Mark Jones, worked as a salesman at the same toy wholesaler as Robert and now heads the sales team for Tuff Toys. All three have equal shares in the business.
In the early months of the year, Mark and his team of salesmen take boxes of sample toys around several South East Queensland toy retailers. They visit both small toy shops and the head offices of the large toyshop chains to display toy samples and take orders on toys to be delivered in later months of the year. Tuff Toys business is seasonal with fewer sales in the early months of the year, and sales increased during the year to peak in October-November in preparation for Christmas trade.
Invoicing
The public entrance to the warehouse is through the Head Office. As well as taking orders for future delivery, Tuff Toys has direct sales when toy shop owners visit the warehouse, select their goods, and receive their typed invoice on leaving. Any toys picked up at the warehouse are designated as ‘pick up. An example of the invoice can be found in Appendix B. The invoice is basically very similar to the order. However, what may be ordered may not be in stock and therefore will not be in the invoice.
ORDER This ICT701 Relational Database Systems Assignment NOW And Get Instant Discount
Payments
When Tuff Toys accept a new customer, in addition to storing their name and address, they set a credit limit for the customer. Customers’ payments for the invoiced items can be paid after the goods are received, up to a maximum of three months. However, customers who have gone over their credit limit can no longer order any toys until they make a payment.
Inventory
The total number of stock-on-hand is stored in the items record, which is updated each time an item is received or dispatched. The warehouse clerk adds the quantity of stock received to the current total stock-on-hand. The clerk subtracts the quantity of stock dispatched from the stock-on-hand and checks to see if the new stock level is below the minimum stock level allowed.
Assignment Requirements and Deliverables
Part A
Entity Relationship Diagram in Crows Foot Notation
• Relational Schema
o Including Primary and Foreign Keys
o Including all attributes
Part B
Your Part B MUST work on a MySQL database and be able to be demonstrated
SQL code required:
• CREATE TABLE statements for all tables in your ERD including integrity constraints,
• INSERT INTO statements for populating the database:
o Incorporate the exact 6 Salesrep and 5 Customer details, exactly as given in the sample dataset (create your own email addresses and phone numbers for these 5 customers)
o Incorporate the exact 10 toys, exactly as given in the sample dataset o Incorporate the delivery details, exactly as given in the sample dataset
Appendix A: ICT701 Relational Database Systems Assignment
ORDER This ICT701 Relational Database Systems Assignment NOW And Get Instant Discount
Get ICT704 NoSQL Database Assignment written by experts.