B2B Startup DB Design

You are the owner of a startup: a B2B app development company. Your company develops different types of apps (desktop, web, mobile, etc.) for other businesses to support their processes. You need to develop a database to keep track of your apps, developers, and other aspects of your business.

Requirements:

Your database should keep track of all apps you developed and you are currently working on. For each app, you want to capture its name, and its brief description. Since you continuously update each app, each app can have multiple versions. For each version, you want to capture the version number and its release date. (This will help you generate reports about the number of versions of a specific app, and the frequency with which apps are updated.)

You want to keep track of businesses who purchase your apps. For each business, you want to capture the business name, address, the name of the contact person at this business, and his/her phone number. Obviously, a business may purchase many apps, and an app can be purchased by many businesses. When a customer purchases an app, you want to capture the date of purchase.

You want to capture your marketing efforts for each app too. Marketing efforts help you advertise and sell your apps. For each marketing effort, you want to capture the date of the effort, its brief description, and the amount spent. You may have many marketing efforts for a single app, or none at all.

Your customers can purchase support agreements from you after purchasing your apps. Currently, you have three types of support: Level 1, Level 2, and Level 3. For each level of support, you want to capture its type, description, and the price. When a customer purchases a support agreement, you want to keep track of the start date of the agreement, its expiration date, and the date of payment. Not all customers purchase support agreements, and a customer can purchase multiple agreements (especially if a current agreement expires).

Sometimes, you send your apps to a third party to perform quality control. The third party tests all functionalities of the app and returns a report about its bugs. You want to keep track of these quality control reports. For each quality control report, you want to keep track of the date of the report, the name of the third party who created the report, and the contents of the report (stored as unformatted plain text). An app can undergo multiple quality controls over its lifetime, and all apps must undergo at least one quality control.

You want the database to keep track of developers who develop these apps too. For each developer, you want to keep track of the developer's name, phone number, and employee ID. Each developer works on only one app, but an app has at least one and possibly many developers.

It is possible that the developers work in a "paired programming" arrangement. In this case, one developer mentors the other as they work in tandem. Therefore, the database should capture each pair (if there is any). A developer can be paired with only one developer (to mentor him/her), or none at all.

You want to capture each developer's expertise too. For each developer, the database should keep track of the programming languages he/she knows, and his/her expertise in them. Obviously, this list should expand as developers learn new languages.

As a company perk, you invest in your developers and frequently send them to training to hone their coding skills. When you send a developer to training, you want to capture the date of training, the description of training, and the amount paid for training. A developer can be sent to many training sessions, or none at all.

Finally, you want to keep track your developers' self-initiated projects. To foster creativity, you allow each developer to spend 20% of his/her company time on a project of their choosing. The project can be anything ranging from a business app to entertainment whatever the developer wants and is interested in. For each project, you want to keep track of the name of the project, its brief description, and its start date. A developer can start and work on multiple projects, but a project can only have one developer.

Instructions:

1) Part A:

Based on the key of Deliverable 1 (provided in Canvas), convert the ERD into a set of tables. Use solid underline for primary key(s) and dashed underline for foreign key(s):

Example:

Hint: To create dashed underline in Word: select the text, click on the arrow next to the underline icon on the toolbar and select the dashed style.

2) Part B:

For each table, list all fields and their possible data types (as presented below):

Example:

Field, Data Type
VIN, Varchar(13)
Make, Varchar(15)
Year, Date
(etc.)

Notes for Part B:

• Use the "Practical MySQL data types" document (in Module 5) to see a list of available data types.
• If you are unsure about which data type to use, please refer to the "Chapter 5 Physical Design" slides and see the examples.
• While assigning data types, don't overkill overkilling will result in point deductions.
• Feel free to use auto increment for primary keys when necessary!
• Feel free to use "unsigned" data types if you don't need negative integers/decimals.
• Make sure the data types of foreign keys MATCH the data types of the corresponding primary keys.
• Foreign keys CANNOT be assigned auto increment even if the corresponding primary key is!