RFP Response - Authentic4D: Relational Database Design

A company called Authentic4D has created an RFP, and the company you work for is preparing a proposal in response. As the chief technologist with the company, your job is to prepare much of the content for the proposal.

The text of the RFP is provided below:

RFP for Authentic4D

A4D (Authentic4D) is a leading provider of medical imaging review technology and resolution services often used for casualty insurance claims. When a person has a worker’s compensation case because of a work-related injury or illness, time to an IME is critical. And, instead of waiting months for an IME Authentic4D provides expert medical reviews in 48 hours and video evidence of injury severity in 72 hours and does so at a fraction of the cost for an IME. The 3D views of injuries and illnesses created by A4D empower patients with knowledge enabling them to make better and more informed decisions about their care and their treatment. A4D’s work is not limited solely to plaintiffs and their attorneys but also extends to defendants: insurers and claims managers who must combat medical fraud, allowing insurers to defend against exaggerated and inflated injury claims.

To better understand our work, please review the following before submitting your proposal:

•https://authentic4d.com (review the website)

•https://authentic4d.com/a4d-process/

•https://authentic4d.com/pricing/

•https://authentic4d.com/defense-video-archive/

•https://authentic4d.com/plaintiff-video-archive/

A4D has created incredible technology as part of its core business, but it lacks systems to track orders, clients, work that is done for clients, billing, and payments. We want a relational database designed to handle the back-office administrative work while we further concentrate on our core business.

The following is the minimum requirements for the new database, but additional features and functionality would be appreciated with the proposal:

•Ability to track orders. Our orders intake team receive orders via the online website, mail, and through customized quotes. Whether an order is placed online, via phone with a representative, or via mail, we receive images that need to be kept with the client’s information. We want our intake team to post the images to the new database along with the client’s information, the specific product being ordered, and additional notes taken by the intake team.

•Create billing. Currently, we offer four products, three of which have firm pricing and one additional product that is priced according to the specific needs of the client. We may offer additional products in the future. When an order is created, we need the ability to track which product is being purchased (see online pricing page for further details). Furthermore, any individual client may purchase more than a single product, and we need to track these individually.

Minimum Requirements for the New Database

•Track clients. We need to keep detailed information about our clients, including their imaging records noted above. We would want this system to be capable of tracking client status where notes/details of conversation, date and time, and client representative creating status are all tracked. Any individual client may have any number of status updates. Furthermore, clients may require A4D to testify in court, and we need to track court dates along with details about the court case.

•Track medical practitioners on staff. A4D employees over 100 Board Certified radiologists. We need to track these individuals, their medical specializations, credentials, and licensure, along with the state(s) where licenses are valid.

You will write a professional and detailed report as a Word document that exhibits all the following:

1.Write a well-formed paragraph that demonstrates your knowledge of the company and what it is seeking. This paragraph should demonstrate that you have done more than simply read the RFP but also demonstrates your knowledge of the company’s information on its website. Also, in this paragraph, you need to demonstrate that you know the meaning of the following terms RFP (and how it differs from an RFQ), IME, plaintiffs, and defendants. Finally, this paragraph should not simply repeat the information but should narrate your overall understanding, in your own words, the company and its needs.

2.Formulate a list of 10 questions that you would want to ask in interviews and identify the people with whom you would want to conduct such an interview. 

3.Choose one other method of requirements gathering (observation, questionnaires, focus groups, and brainstorming sessions), that you would conduct. After identifying one of the above four methods write a well-formed paragraph in which you provide the design for the methodology you have chosen: who will be involved, what questions will be asked, what you hope to gain from this particular requirement gathering session.

4.Create a graphical design using Draw.IO of your database design. No data was provided with the RFP. The reason is that the details provided should be sufficient to create a draft proposal for a database design. You will need to design the database to meet the minimum requirements as listed in the RFP. Your design must include the tables, their attributes (columns), the primary key for each, and the relationships between tables (refer to chapter 5 from Harrington). Make sure to export the image from Draw.IO and insert it into your Word document.

5.Implement your database in AWS and ensure that you meet the normalization rules. Create your tables and see if you can create the relationships between the tables. You should have a minimum of four tables in your design—you certainly can add others as you see fit.

6.Add data to each of the tables you created. Add at least two rows of data to each of your tables to verify the design accommodates what you believe the data will eventually be.

7.Create a diagram in SSMS using all the tables in your database. Copy this diagram to your Word document.

8.Query the data from all the tables, either from each individual table one-at-a-time or from all the tables through a multi-table select statement. Take a screen capture of your query and the results and attach this image to your report as well. Make sure you read the above steps carefully.