Question
IT 4713 Individual Project Milestone #1: Class Registration Data Mart Fall 2016 - Due date: See D2L Objective In this first milestone, we will: Analyze
IT 4713 Individual Project Milestone #1: Class Registration Data Mart
Fall 2016 - Due date: See D2L
Objective
In this first milestone, we will:
Analyze the sample dataset and understand the relevant business (terms, rules, data, etc.)
Design a reasonable star schema to model the data
Implement the star schema in SQL Server and create a dimensional data mart structure
Requirementsa
1.Analyze the sample business requirement and class registration data provided. Reasonably expand the scope of the data to consider additional data that might be beneficial, like all departments, past semesters, or faculty information. The project is designed as 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 any software program to do the modeling.
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.
Please design at least four-dimension tables.
Create all primary keys, relationships (foreign keys), appropriate data type/length, and other constraints.
Create a SQL Server database diagram and take a screenshot of the diagram.
Submission
Compile all required documents and screenshots in ONE PDF file and 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.
A screenshot (or an image) of the star schema (logical design) you designed using a modeling tool like ERDplus.com.
A screenshot of the SQL Server data mart (database) diagram. Make sure all column data types and keys are shown in the diagram (choose the standard view).
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.
Rubric:
Score | Summary | Rating Description |
10 | Outstanding work; beyond expectation.
| Considering almost all situations; clearly understand the business. Most reasonable design; correct SQL Server implementation without any flaw. |
8-9 | Good work; meet expectations
| Shows adequate business analysis and reasonable understanding of the data. Correctly identify and model the facts and dimensions; may have minor design issues but does not impact too much. Has minor data mart implementation issues in SQL Server. |
6-7 | Adequate work; need improvement.
| Shows inadequate business analysis and misunderstanding of the data. Has some design flaws in star schema. Has data mart implementation issues in SQL Server; incorrect design on constraints (keys) and columns |
<6 | Lack of effort.
| Shows weak business analysis. Has major design flaws. Incorrect implementation and database creation. Unclear, partial screen, or edited screenshots |
Project Business Scenario
The following only provides some preliminary and partial description of the scenario. Its enough to get your started to understand the type of data and the analysis we want to see from data. But you need to exercise your analysis and design skills to understand more. This is critical in any data modeling and design.
Be like an analyst and inquire more!
Data explanation
CRN is course section offering id, and should be unique across semesters (80% sure)
Course section: 9xx online, 8xx hybrid, 0xx in-classroom
Course number: 1xxx-4xxx (undergraduate, 1 to 4 for freshman, sophomore, junior, and senior), 5xxx and above (graduate)
CCSE courses have five prefixes: IT, CS, SWE, CGDD, CSE
The last column are the three types of enrollment headcounts: actual enrollment number/initial enrollment number/max available seats
Sample queries and analysis
We want to focus on CCSE the college as well as the IT department.
Class registration analysis: providing a view of registration head counts from different perspectives.
Total registration head counts as a whole, and by department and class level (graduate or undergraduate).
Registration history (all semesters in the database) of key courses.
Data in current semester compared to those in the same semester of last year.
Online course registration trend (by computing subjects like IT, CS, Security, etc.).
Other metrics like withdraw rate, average class size, class room utilization etc.
[Challenge] Prediction of class enrollment and number/type of sections offering
Degree program analysis
Growth as a whole or in certain aspects like MSIT
Comparison of departments in areas like course number, class size, faculty work load, trend, etc.
Faculty performance and workload. Some key analysis include:Individual faculty members most recent academic year.
Total number of courses, and by semester
Total number of sections, and by semester
Total registration head counts, and by semester and course
Withdraw rate for each course section
Faculty as a whole group
Registration head count total by employment status (full time or part time), rank (professor, associate prof, lecturer, etc.), and by department
Number of course sections total by employment status, and by department
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started