Analyze

Objective

In this milestone, we will:

· Analyze the sample dataset and understand the relevant business context (data, terms, rules, etc.). Step 1

· Design a reasonable star schema to model the data. Step 2

· Implement the star schema in SQL Server and create a dimensional data mart structure (structure only). Step 3

Requirements

1. Two files are provided.

a. “sample-data.zip” include several HTML files showing class registration data.

b. “business requirements.docx” provides some more details and explanations and shows some sample queries that are to be answered based on this data.

Reasonably expand the scope of the sample data to consider additional data that might be beneficial (even though they are not in the sample data), like all departments, past semesters, or more faculty information. The project is designed to be exploratory and open. You will have questions and you need to ask questions to find out details.

2. Create a star schema for this data based on your analysis of the requirements and understanding of the domain. They must be reasonable and justifiable. Clearly show major measures, dimensions and their attributes. Use a software program to create the model.

E.g. ERDplus (https://erdplus.com/standalone) or MS Visio.

3. Create a data mart based on the star schema using SQL Server Database Engine. Schema/data mart requirements (may or may not align with your design; but for consistency please meet the following minimum requirements).

· The fact table should include at least three measures: actual enrollment (the first number of the last column), original enrollment, maximum seats.

· Design at least four dimension tables.

· Create all primary keys, relationships (foreign keys), appropriate data type/length, and other constraints.

4. Create a SQL Server database diagram and take a screenshot of the diagram.

Submission

Compile a milestone report with all required documents and screenshots in ONE PDF file. Submit it in D2L. All screenshots must be clear, original, and show the complete screen - no graphic editing or cropping is allowed. Clearly label and explain all parts.

The report must include the following:

1. Basic data analysis: identify facts/measures, dimensions, hierarchies, and potential aggregates.

2. A screenshot (or an image) of the star schema (logical design) designed using a modeling tool like ERDplus.com. Justify the design, and explain how they satisfy the requirements.

3. A screenshot of the SQL Server data mart (database) structure diagram. Make sure all column data types and keys are shown in the diagram (choose the standard view for each table).

Grading

The milestone is assessed based on:

· Demonstrated understanding of the business and data

· Design of the star schema

· Implementation of the schema in a SQL Server data mart

· Clarity, originality, and completeness of the screenshots and reports.

Rubric:

Score	Summary	Rating Description
10	Outstanding work; beyond expectation.	Most reasonable design with sound justification. Considered almost all situations; clearly understand the business case. Correct SQL Server implementation based on the schema design without any flaw. Professional report.
8-9	Good work; meet expectations.	Correctly identify and model the facts and dimensions; may have minor design issues but does not impact too much. Shows adequate business analysis and reasonable understanding of the data. Has minor data mart implementation issues in SQL Server.
6-7	Adequate work; need improvement.	Has some design flaws in star schema. Shows inadequate business analysis and misunderstanding of the data. Has data mart implementation issues in SQL Server; incorrect design on constraints (keys) and columns. Disorganized report.
<6	Lack of effort.	Has major design flaws. Shows weak business analysis. Incorrect implementation and database creation. Disorganized report with unclear, partial screen, or edited screenshots.

Page 2 of 2