Database Job Interview

I am going to give you a written component of a job interview. The position is back-end engineer for my eBay-based antiques company. For the sake of the interview I want you to walk me through your thought process on designing the architecture for a real-life data management application. I will also ask some skills assessment questions. DO NOT COLLABORATE ON THIS PROJECT. Please create a professional response and return it to me by July 30th.

High level overview of the business

Estate Auctions Inc. sells high-end, yet affordable, antiques on eBay. We auction 300 antiques a week, beginning at 99 cents a piece. Our brand is built on customer service and variety. We offer consignment services to other antique dealers looking to reach a wider audience. Our staff has 10-20 employees and we service 50-100 thousand customers.

Project Proposal:

In this assessment I would like to hear your thoughts on developing the back-end for an data management system. We would like to have our items inventoried, tracked, researched, published to eBay, reported to consignors, and shared with the public. I will give you a set of user-stories for the life cycle of an item/antique. Please ask for any clarification you would like about our specifications.

• A receiver takes 1 picture (on a mobile device) of an Inventory, identified by a date and time in the Christian epoch which is uploaded to the system, and puts the physical item in the receiving area of the warehouse so that it can be weighed.
• An inventory employee weighs Inventory and takes 1 picture (on a mobile device) if also receiving the item and moves the physical item to the photography area of the warehouse.
• A photographer goes to the photography area of the warehouse and takes multiple Images of the Inventory on a memory stick and moves the physical item to a shelf so that it can be described.
• A photoshopper takes a memory stick and creates a PreListing for that Inventory so that it can be described.
• A describer examines the Inventory and photos of the PreListing, writes a Description and Title, assigns an eBay category to the item, reorders the photos or deletes bad photos, and marks the PreListing as ready for eBay so that it can be a Listing on Ebay at the end of the day.
• A warehouse manager carries Inventory that has been researched to shipping area after checking if the Inventory was described so that the physical item can be shipped once sold on Ebay (in approximately 10 days).
• An accounts receivable person sends an Invoice for each Inventory sold that day and then prints a PullingSheet for each Payment received that day and hands the stack of PullingSheets to the warehouse manager.
• A puller pulls all of the Inventory for the packer and places them with the PullingSheet in the packing area.
• The packer boxes the Inventory and sets it on a shelf in the packing area, writing weights and measures of the box on the PullingSheet so that a ShippingLabel can be produced.
• The labeler produces the ShippingLabel marks the Listing on Ebay as shipped and gives the ShippingLabel to the packer so that it can be taped to the box which is put on a mailing cart to be picked up by Fedex/USPS.
• After 2 weeks without complaint the accounts payables guy writes a Cheque to the consignor.
• If there is a return the describer re-lists the item. If it is already paid then there is no Consignor this time.

Skills Assessment:

Please write a detailed response to each question, but no more than 1-2 page(s) per answer.

Architectural Questions:

1. What architecture do you think would make the most sense for this business?
2. How do you propose to handle scaling our in-house and public-facing needs as we grow?

SQL questions:

3. Design an SQL schema for some aspect of this project in 3NF or higher.
4. When and/or why would you choose to denormalize?
5. When/why would you use a view?
6. In your proposed schema show me a query that might:
   1. Return the daily average number of items processed by each employee.
   2. Return average weekly revenue.

Breadth questions:

7. Design a rough no-SQL schema for this project.
8. What are the pros/cons of choosing noSQL vs SQL?
9. In your proposed schema show me a mechanism that might:
   1. Compute the daily average number of items processed by each employee.
   2. Compute average weekly revenue.

Technical awareness:

10. Tell me about the various JOIN statements.
11. What is a functional dependency?