Question

A college records the module registration information of their students in a database named College.mdb,which contains three tables,STUDENT,MODULE andMODULE_REG. A student may enroll in the same module as many times as they like. However, they can only enroll each module at most once in each semester. The design of the three tables are shown below:

Table STUDENT

Field Name	Data Type	Note
SID	Short Text	primary key
FirstName	Short Text
LastName	Short Text
Contact	Short Text
Gender	Short Text	“M” or “F”

Table MODULE_REG

Field Name	Data Type	Note
StudentID	Short Text
ModuleCode	Short Text
Year	Number
Semester	Number
Score	Number

Table MODULE

Field Name	Data Type	Note
ModuleCode	Short Text	primary key
ModuleName	Short Text

Download the database file College.mdbfrom Moodle. Rename it as College_Lxx_syyyyyy.mdb(where Lxxis your class andsyyyyyyis your student number, e.g. College_L01_s170001.mdb/College_L01_s170001.accdb) and complete the following tasks.

(a) Create a primary key of the table MODULE_REG.                                                                (1 mark)

(b)   Establish the relationships between the tables STUDENT,MODULE andMODULE_REG.      (1 mark)

(c)    Create queries to perform the following tasks:

1. Display the student IDs, studentnames (including both first name and last name), as well as the code and name of modules that each student had enrolled in the secondsemester of 2019. Arrange the records in ascendingorder of the student IDs. Save the query as Q1.
   Note: You mustnotshow the semester and the year in the output.                            

(ii) Display the total number of students in each gender. Save the query as Q2.              

3. Produce a list of module codes, module names, and the total number of enrollment records of each module. Arrange the records in ascendingorder of the total number of enrollment records of each module. Save the query as Q3.

Produce a list of module codes, module names, student names (including both first name and last name), and student IDs of enrolled students for all modules with module codes beginning with “COM” being registered in the firstsemester of 2019. Arrange the records in ascendingorder of module codes. Save the query as Q4.
Note: You mustnotshow the semester and the year in the output.

Answer 1

Create table Student with the given fields and SID as Primary key

Create Table Module with the given fields and Modulecode as Primary key.

Create Module_Reg with the given fields.

a) Creating Primary key

In the Module_Reg table the primary key is a composite key of StudentID and ModuleCode.

To do this ctrl select two filelds -> right click and select primary key.

Student Table:

Module Table:

Module _Reg Table:

----------------------------------------

b) Relationships:

In the Design tab -> click Relationships

Empty window with Tables at the right corner will be displayed.

Drag and drop all the tables in the window.

Drag the Primary key of Student(SID) to Module_Reg(StudentID)

In the dialog box select enforce referential integrity.

Likewise create the relation between Module and Module_Reg with ModuleCode

-----------------------------------------------------------

c) Query

Q1) design the query using design wizard (will be easier)

Click Create-> QueryDesign

A window with list of tables at the right corner will be opened.

Drag and drop the tables we need to process the query.

To process this query we need all 3 tables.

The query has to display the Student IDs, student names, module codes and names that has enrolled in the second semester of 2019.

The query is designed like this, enter the values as given in the figure. Save the Query as Q1.

The query

SELECT Student.SID, Student.FirstName, Student.LastName, Module_Reg.ModuleCode, Module.ModuleName
FROM [Module] INNER JOIN (Student INNER JOIN Module_Reg ON Student.SID = Module_Reg.StudentID) ON Module.ModuleCode = Module_Reg.ModuleCode
WHERE (((Module_Reg.Year)=2019) AND ((Module_Reg.Semester)=2))
ORDER BY Student.SID;

Output

-------------------------------

Q2)

Create the query as Q1.

In this query we have to aggragate function Count.

To include that in design view We have a icon called totals

click that totals we will be able to use all aggregate functions.

The values to be entered are:

Query:

SELECT Count(Student.Gender) AS CountOfGender
FROM Student
GROUP BY Student.Gender;

---------------------------------------------------------------

Q3)

Create Query in Design view. Save the query as Q3

Query:

SELECT Module.ModuleCode, Module.ModuleName, Count(Module_Reg.ModuleCode) AS CountOfModuleCode
FROM Student INNER JOIN ([Module] INNER JOIN Module_Reg ON Module.ModuleCode = Module_Reg.ModuleCode) ON Student.SID = Module_Reg.StudentID
GROUP BY Module.ModuleCode, Module.ModuleName
ORDER BY Module.ModuleCode;

Result:

---------------------------------------

Q4)

Unselect and the display option for Semester and Year.

Save the query as Q4

Query:

SELECT Module_Reg.ModuleCode, Module.ModuleName, Student.FirstName, Student.LastName, Student.SID
FROM Student INNER JOIN ([Module] INNER JOIN Module_Reg ON Module.ModuleCode = Module_Reg.ModuleCode) ON Student.SID = Module_Reg.StudentID
WHERE (((Module_Reg.ModuleCode) Like 'COM*') AND ((Module_Reg.Semester)=1) AND ((Module_Reg.Year)=2019))
ORDER BY Module_Reg.ModuleCode;

Result: