Star Schema Help

[Ianthelmt]

Hello, I am trying to create a star schema but I am a bit lost. I dont even know where to start and this is very confusing to me. I have attached an Access example of what it should look like, but I have no idea how to translate this data into such a design.


Can anyone help me?


Here are my instructions for the task:

Goal: create a star schema for a data warehouse which is a fictitious college

-Analysts want to:

-detect trends in registrations in courses over time.
-determine if the registrations for individual courses are increasing, decreasing or remaining constant over time.
-Is this information the same or different at each of the college campuses.


............

Reporting:

-Must be possible by campus and course.
-Registration data extracted must be summarized by term.
(Assume registration counts already exist. Ie; An extraction process already exists that will query the system and add up registration numbers.
-This quantity shall be referred to as: RegistrationCount.
-Analysts need to see reports: (RegistrationCount) for both yearly and term data.

..............
Relevant tables(operational database):


COURSE (CourseID, Description, Credits, LabFee, EffectiveDate)
CAMPUS (CampusID, Address, City, State, Zip)
Create the appropriate dimension table(s) and fact table(s) for the data warehouse.

Special tips:

-Underline the primary key column(s).
-Italicize the foreign key column(s) in each table unless you use Microsoft Access.

I will be forever greatful if someone can help me with this!

 

[plog]

Just to make sure--the image you posted is not your attempt to create what the assignment is--correct? It's just a sample of the format that needs to be delivered to the professor?

If so, what you posted is a screenshot of the Relationship screen in Access. To create that for your project you need to create your tables in Access, then click on the Relationships icon in the ribbon, bring in your tables and link them appropriately.

So, do that, then take a screenshot of your attempt and post that here. We'll critique it and help you get it to what it needs to be.

 

[Ianthelmt]

Yes, that is correct.

Okay excellent. That is my step no. 1 then. I appreciate the direction. I will try and work on it tomorrow. It is due next Sunday so I have a good deal of time to tinker.

Now as far as creating tables, I need to brush up on creating the dimension tables(i assume these are term, year, campus?) and fact table (a bit lost on that one)

Any quick tips before I sink my teeth into the design?

I greatly appreciate your feedback!

Most of this class was just theory, not working in Access. This is the first time im really working in Access other than doing hand-coded sql queries on pre-designed databases. I wish the instructor started us with working in the database design side of things from the very beginning!

 

[plog]

If you have Access on your computer, then its a great way to layout a database--even if the database isn't going to ultimately reside in Access. You could also use excel, notepad or even paint, but Access will let you create that image you posted.

Think of your fact table as the main table, its the one that ties all your dimension tables together. My one suggestion is to err on making something a dimension table. If your not sure if something should be in the fact table or dimension table, make a dimension table for it.

 

[Ianthelmt]

Okay here is attempt no. 1.

I hope I am at least on the correct track... Any input on attempt no. 1 would be much appreciated!

 

[Ianthelmt]

Hi pat, sorry I had realized I uploaded the wrong one. the link should be correct now!

 

[plog]

Actually, I think that looks like you are 95% of the way there. The only question I have is what does the RegistrationCount field tell you?

After that, I think the next step is to see if this fulfills the purpose the analysts have for it (can it do those 3 things you listed in your first post?) I think it can, but just make sure that you can see in your mind how you would go about fulfilling requests for data that they would make (i.e. trends, monthly/yearly reports, etc.)

 

[plog]

I just reviewed your first post and see that RegistrationCount will be determined. That kind of confuses me.

If you are to know what each students' names are, then that is storing data at its most granular level and having a field that summarizes it at a higher level is not possible. To put it another way, it seems that RegesitrationCount will always have to be 1, because it denotes a specific student taking a specific class at a specific campus for a specific term.

That confuses me.

 

[Ianthelmt]

As far as I know, the Reporting must be possible by campus and course.

The instructions I was given state that the reg data extracted from the online trans. system will be summ'd by term. I am told to assume that the reg. counts already exist, ie
the process by which info is extracted has already been created (this process will search through the database and sum count the registration numbers. I have been told to refer to this quantity as RegistrationCount.

Also, that the analysts desire to see RegistrationCount of the yearly and term data.


So the RegistrationCount field should tell me how many people have registered for x or y course based on the term and year.

Should that maybe be a field in the Term table? Not sure how to put it all together from this point. I put it all on paper and worked backwards. I am a bit old school and need to draw things with a pen and pad before I write them up on computer. Just helps me sift through my ideas!

Not sure where to really go from here...

 

[plog]

The problem is the student table. Having it means that all your data will be by the student/course/term level which is the lowest level possible thus making the RegistrationCount pointless.

Either the student table shouldn't be there or the RegistrationCount is pointless.

 

[Ianthelmt]

So maybe I should then delete student table and make just the registration tabke, with the addition of student ID field, studentFirstName, StudentLastName fields?

 

[stopher]

So maybe I should then delete student table and make just the registration tabke, with the addition of student ID field, studentFirstName, StudentLastName fields?

As part of the model you have to design a fact table. So what fact(s) are you portraying (the question tells you)?

What dimensions do you need (again these are given in the question)?

You should not be including facts or dimensions that are not required to provide the output requested by the question.

Take a look here to understand facts/dimensions. May be worth googling further to seem some different examples.