Question

We aim to analyze students’ results in exams. The designer advocated a mixed approach. On the one hand, he relies on the study of the analytical requirements requested by the university and by a committee that examines the results, and on the other hand by examining the data source model described by the relational database of exams. Below is a partial description of this database  STUDENT (STD-NO, FName, LName, Birth-Date, Gender, Housing, Tel, ID-FAC#
FACULTY (ID-FAC, Name, Short-Name, Address,
MODULE (ID-CORS, Name, ID-SPEC#, Semester,
SPECIALTY (ID-SPEC, Name, Nb-Years,
RESULTS (STD-NO #, ID-CORS#, Acad-Year, Semester, Mid-Term1-Mark, Mid-Term2-Mark, Final-Exam-Mark, Average-Mark, Final-Grade,

Queries below constitute a non-exhaustive list of the main analytical requirements:
1) Numbers of students in each Course who succeeded in the Mid-Term1, split by academic year and by Faculty.
2) Average mark by Course and by Faculty.
3) Success rate per Course and gender of students.
4) Number of students who succeed in each course per academic year and Faculty.
5) Average score per course and Faculty.
6) Success rate per course and gender of students.
7) Percentage of failure per course.
8) Total number of students per faculty and specialty.
9) Final (end of year) success rate of students per gender

1) Identify Subject of Analysis.
2) Considering the above relational data source model and the given requirements, elaborate a sheet giving for each identified fact its measures, dimensions and their attributes as well as the matching with the data source and the extraction expressions.
3) Draw a multidimensional star schema.

Answer 1

Answer 1.

This requires that all data be stored as values at column positions in rows in tables, and in no other way. You need to decompose the table into three correctly related tables along these lines:

Students
....StudentID (PK)
....FirstName
....LastName

Subjects
....SubjectID (PK)
....Subject
....PassMark

And to model the many-to-many relationship type between them:

StudentExaminations
....StudentID (FK)
....SubjectID (FK)
....Mark

You can then return a result table of those students who have failed to achieve the pass mark in any one subject with:

SELECT *
FROM Students
WHERE EXISTS
     (SELECT *
      FROM StudentExaminations INNER JOIN Subjects
      ON StudentExaminations.SubjectID = Subjects.SubjectID
     WHERE StudentExaminations.StudentID = Students.StudentID
     AND Subjects.PassMark > StudentExaminations.Mark);

If you want to show the subjects in which a student has not achieved the pass mark:

SELECT Students.*, Subject
FROM (Students INNER JOIN StudentExaminations
ON Students.StudentID = StudentExaminations.StudentID)
INNER JOIN Subjects ON Subjects.SubjectID = StudentExaminations.SubjectID
WHERE Subjects.PassMark > StudentExaminations.Mark;

//Query for calculating average of marks

SELECT student_full_name,
        AVG(results) AS average_result
 FROM viewEnrol
 WHERE average_result > ( SELECT (AVG(results))
                          FROM viewEnrol

 GROUP BY student_full_name

Answer 3.

Multidimensional Star schema:-

Dimension Table Dimension Table Branch Dim Branch JD Name Address Country Fact Table Fact Table Dealer Dealer JD Location ID Country ID Dealer NM Dimension Table Revenue Dealer ID Branch ID Date JID Units Sold Revenue Product Product ID Product Name Variant ID Product Product ID Product Name Model ID ariant ID Dimension Table Deale,CNTCT Date Dim Date ID Year Month Quarte Date