Relational data model, you’ll then design a simple relational data model to represent some of the ideas from your conceptual model

INSTRUCTIONS TO CANDIDATES

ANSWER ALL QUESTIONS

The final assessment for course 1 is peer-graded. While it won’t incorporate every idea we’ve covered in the course, it will include key elements from each of Modules 1 through 4 in one integrated exercise.

The assessment has four parts:

1. Conceptual business model. You’ll construct a conceptual business model similar to the one we discussed in Module 1.

2. Relational data model. You’ll then design a simple relational data model to represent some of the ideas from your conceptual model (like in Module 2), and describe what types of systems you think the data might come from (Module 1).

3. SQL queries. You’ll write two SQL queries to extract an interesting data set from your data model (Module 3)

4. Sensitive data and data quality issues. Finally, you’ll identify whether your model contains certain types of sensitive data, and assess where your model might be susceptible to data quality issues (Module 4).

Detailed Instructions

Submission file formats. The graphical items in the assignment may be drawn and scanned or constructed using any convenient software, but the submitted file should be in a common file format like PDF (preferred), JPG, Word, PowerPoint or Excel. Regardless of method, the submission must be neat and legible to facilitate peer review. 

Part 1: Conceptual business model. Construct a conceptual business model for an industry or business that you are familiar with or have interest in. Visually it should be similar to the one we illustrated in Module 1, Video 2.

• Your model should represent at least 10 ideas

• It should visually represent one to one, many to one, or many to many relationships among ideas

Part 2: Relational data model. Take a subset of the ideas from the conceptual model you constructed in Part 1 and design a simple relationship model similar to the ones we discussed in Module 2, Video 4

• Your model should have at least 5 tables

• You should include at least 20 attributes, or fields, in your model (20 total across all tables, not per table)

• Your model should be normalized

• Identify the primary key in each table, and state whether it is a natural or surrogate key

• For each relationship between tables, identify any foreign keys needed to define the relationship

• For each table, identify what type of system or systems you think the data might come from, like those we discussed in Module 1, Video 6.

Part 3: SQL queries. Using the data model you constructed in Part 2, come up with two data extracts you think would be interesting, then write SQL queries to provide each one.

• For each query, state what data you are trying to get and why it would be interesting

• Provide the SQL query using the commands and syntax we learned in Module 3

• For maximum credit, at least one of your queries should involve a join across two or more tables.

Part 4: Sensitive data and data quality issues. Consider the data privacy and data quality implications of the data model you constructed in Part 2.

• Identify any fields you think might be PII, CFI, CPNI, or PHI as we defined in Module 4, Video 4

• What data elements in your model will present the most significant data quality challenges? Explain your reasoning

Review criteria

Scoring of your assessment will be based on a combination of Yes/No (90 points) and variable (10 points) criteria, as follows, with a maximum score of 100 points:

Part 1 (30 points): Conceptual business model. Evaluation criteria:

• 10 points: Conceptual business model present (Yes/No)

• 10 points: Model has 10 ideas - 0 points if less than 10 ideas (Yes/No)

• 5 points: Model has lines linking ideas - 0 points if no lines (Yes/No)

• 5 points: Model has one to one, many to one, many to many relationships defined - 0 points if no relationships defined (Yes/No)

Part 2 (35 points): Relational data model. Evaluation criteria:

• 10 points: Relational data model present (Yes/No)

• 5 points: Model has 5 entities - 0 points if less than 5 entities (Yes/No)

• 5 points: Model has 20 total attributes - 0 points if less than 20 total attributes (Yes/No)

• 5 points: Primary key of each table is defined (Yes/No)

• 5 points: Foreign keys for each linkage are defined (Yes/No)

Part 3 (20 points): SQL queries. Evaluation criteria:

• 10 points: SQL Queries present (5 points for each of two queries present) (Yes/No)

• 5 points: At least one query contains a JOIN (Yes/No)

• 5 points: Variable points for quality of answer; full points should be awarded if answers seem reasonable and are in the spirit of the material presented in the course; no points should be awarded if submission is gibberish

Part 4 (15 points): Sensitive data and data quality issues. Evaluation criteria:

• 10 points: Answers to questions present (5 points for each of two questions present) (Yes/No)

• 5 points: Variable points for quality of answer; full points should be awarded if answers seem reasonable and are in the spirit of the material presented in the course; no points should be awarded if submission is gibberish.

Attachments:

Related Questions

. Introgramming & Unix Fall 2018, CRN 44882, Oakland University Homework Assignment 6 - Using Arrays and Functions in C

DescriptionIn this final assignment, the students will demonstrate their ability to apply two ma

. The standard path finding involves finding the (shortest) path from an origin to a destination, typically on a map. This is an

Path finding involves finding a path from A to B. Typically we want the path to have certain properties,such as being the shortest or to avoid going t

. Develop a program to emulate a purchase transaction at a retail store. This program will have two classes, a LineItem class and a Transaction class. The LineItem class will represent an individual

Develop a program to emulate a purchase transaction at a retail store. Thisprogram will have two classes, a LineItem class and a Transaction class. Th

. SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 1 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of

. Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of Sea Ports. Here are the classes and their instance variables we wish to define:

1 Project 2 Introduction - the SeaPort Project series For this set of projects for the course, we wish to simulate some of the aspects of a number of