Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

1. Create a star schema for this data based on your analysis of the requirements and understanding of the domain. They must be reasonable and

1. 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.

2. 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.

___________________________________________________________

Data explanation

1. CRN is course section offering id, and should be unique across semesters (80% sure)

2. Course section: 9xx online, 8xx hybrid, 0xx in-classroom

3. Course number: 1xxx-4xxx (undergraduate, 1 to 4 for freshman, sophomore, junior, and senior), 5xxx and above (graduate)

4. CCSE courses have five prefixes: IT, CS, SWE, CGDD, CSE

5.The last column are the three types of enrollment headcounts: actual enrollment number/initial enrollment number/max available seats

Sample queries and analysis

1. We want to focus on CCSE the college as well as the IT department.

2. 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

3. 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.

4. 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

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image

Step: 3

blur-text-image

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

Sybase Database Administrators Handbook

Authors: Brian Hitchcock

1st Edition

0133574776, 978-0133574777

More Books

Students also viewed these Databases questions