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.

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:

  1. Design an SQL schema for some aspect of this project in 3NF or higher.
  2. When and/or why would you choose to denormalize?
  3. When/why would you use a view?
  4. 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:

  1. Design a rough no-SQL schema for this project.
  2. What are the pros/cons of choosing noSQL vs SQL?
  3. 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:

  1. Tell me about the various JOIN statements.
  2. What is a functional dependency?