Question Below：
- Question Below 1 (242.78 KiB) Viewed 18 times
- Question Below 2 (108.06 KiB) Viewed 18 times
Task 1. Conceptual modelling of a data warehouse (3 marks) < An objective of this task is to create a conceptual schema of a sample data warehouse domain described below. Read and analyse the following specification of a data warehouse domain.< A university would like to create a data warehouse to store information about the submissions of student assignments and later on to analyse the contents of a data warehouse. It is expected that the planned data warehouse will contain historical information collected over a long period of time. < A data warehouse supposed to contain information about the students, courses enrolled by students, degrees, specifications of assignments, lecturers who prepared assignments, submitted assignments, and assessments of submitted assignments.< H The students are described by a student number, first name, last name, and email address. A student number and email address separately identify each student. < H A university offers the courses. A course is described by a unique code, unique title, credits, session, and year when offered. A course is offered one time per year in either Autumn or Spring session.< A specification of assignment is described by an assignment number, maximum evaluation, release date, and deadline for submission. < A specification of assignment is prepared by a lecturer. A lecturer is described by a unique staff number, first name, last name, building and office number.< The lecturers belong to the departments. A department is described by a unique name and building number where it is located.< The courses enrolled by the students belong to the degrees. A degree is described by a unique name and total number of credit points required for its completion< The students submit the assignments prepared by the lecturers and related to the courses enrolled by the students. A submission is described by a submission date and time, evaluation done by an assessor, amount of time spent on its implementation declared by a student, and complexity level declared by a lecturer. e A data warehouse must be designed such it should be possible to easily implement of the following classes of applications. < ( (1) It should be possible to find the total number of submitted assignments per year, per session, per month and per day, per student, per course, per lecturer who prepared the assignments, etc. For example, it should be to find the total number of submitted assignment in each month of 2017 in each course.< E (2) It should be possible to find an average evaluation of assignment per student, per course, per year, per session, per month, per department, and per degree, etc. For example, it
should be possible to find an average mark from assignment 1 in a given subject in the last 5 years. (3) It should be possible to find the largest evaluation of assignment per course, per year, per students, per course, etc. For example, it should be possible to find the largest evaluations of each assignment in a given subject in the last 5 sessions.< (4) It should be possible to find the total amount of time spent on implementation of assignments per student, per course, per session, per year, etc. For example, it should be possible to find a total amount of time spent on implementation of each assignment in a given subject over a period of last 5 sessions.< To create a conceptual schema of a sample data warehouse domain follow the steps listed below. ( Step 1 Find a fact entity, find the measures describing a fact entity.< Step 2 Find the dimensions.< Step 3 Find the hierarchies over the dimensions.< Step 4 Find the descriptions (attributes) of all entity types.< Step 5 Draw a conceptual schema.<