Normalisation
The following relation is defined to store information about assessment results:
ASSESSMENT_RESULT(STUDENT_ID, STUDENT_FULL_NAME, MODULE_CODE, MODULE_TITLE, ASSESSMENT, PERCENTAGE, MARK, ATTEMPT, ASSESSMENT_DATE) Suggest the attributes that should become part of the primary key.
List all functional dependencies.
Provide all reasons why this relation is not in the first normal form.
Provide all reasons why this relation is not in the second normal form.
Normalise this relation so that is in Boyce–Codd normal form.
Solution
Ans: 1
ASSESSMENT_RESULT(STUDENT_ID, STUDENT_FULL_NAME, MODULE_CODE, MODULE_TITLE, ASSESSMENT, PERCENTAGE, MARK, ATTEMPT, ASSESSMENT_DATE)Ans: 2
1- Functional Dependency - STUDENT_FULL_NAME is functionally dependent on STUDENT_ID so this assessment information show functional dependency.
STUDENT_ID -> STUDENT_FULL_NAME
Like this other functional dependency:
MODULE_CODE -> MODULE_TITLE
ASSESSMENT -> ATTEMPT
ASSESSMENT -> MARK
2 – Fully Functional Dependency - Here STUDENT_ID, MODULE_CODE and ASSESSMENT easily identify MARK, PERCENTAGE like:
{STUDENT_ID, MODULE_CODE, ASSESSMENT} -> ( MARK)
{STUDENT_ID, MODULE_CODE, ASSESSMENT} ->
( PERCENTAGE)
But this relation not satisfied FFD:
{STUDENT_ID, MODULE_CODE, ATTEMPT} -> ( MARK)Ans: 3
Reasons, why this relation is not in the first normal form:
This assessment not in first normal form because the [ASSESSMENT] contain multi values.
For Example: {Java, Python, DBMS}
EX:
[Assessement]-à{Java, Python, DBMS}
It is not atomic value so this relation not satisfied first normal formAns: 4
This assessment not in second normal form because the attribute of this ASSESSMENT_RESULT is partial dependent to each other like:
{STUDENT_ID, STUDENT_FULL_NAME, MODULE_CODE, MODULE_TITLE,
ASSESSMENT, ASSESSMENT_DATE}
In this relation STUDENT_FULL_NAME partial dependent on STUDENT_ID, MODULE_TITLE partial dependent on MODULE_CODE and ASSESSMENT_DATE partial dependent on ASSESSMENT.Ans: 5
Boyce–Codd normal form:
The given assessment_result relation satisfied the all conditions of of third normal form and for every functional dependency X->Y where Y is the super key of X. We can easily understand this with the help by the following example:
ASSESSMENT_RESULT(STUDENT_ID, STUDENT_FULL_NAME, MODULE_CODE, MODULE_TITLE, ASSESSMENT, PERCENTAGE, MARK, ATTEMPT, ASSESSMENT_DATE)ER modelling
Data definition(DDL)
Translate the ER diagram you created in Part 2into tables using SQL CREATE TABLE definitions, which should include primary and foreign keys(if any).
Describethelogicbehindthechoiceofthekeysandtheirroleinmanagingthedatastoredinthedatabase.Use examples to illustrate the latter point.
CREATE TABLE Student(
Id INTEGER,
fname VARCHAR(30),
sname VARCHAR(30),
mobile INTEGER,
DOB INTEGER,
address VARCHAR(30),
module_code VARCHAR(30),
Primary key (Id)
);
CREATE TABLE Lecturer(
Id INTEGER,
fname VARCHAR(30),
sname VARCHAR(30),
d_e_start VARCHAR(30),
a_salary INTEGER,
staff_number INTEGER,
address VARCHAR(30),
Primary key (Id),
FOREIGN KEY(ID) REFERENCES Student(ID)
);
CREATE TABLE Module(
module_code VARCHAR(30),
module_title VARCHAR(30),
credit VARCHAR(30),
Primary key (Module_code)
);
CREATE TABLE lecturer_module(
Id integer(10)
module_code VARCHAR(30),
module_title VARCHAR(30),
assessement_date VARCHAR(30),
Primary key (module_code)
);
CREATE TABLE assessment(
id INTEGER(30),
module_code VARCHAR(30),
assessement_date VARCHAR(30),
Primary key (id)
);
CREATE TABLE assessment_result(
id INTEGER(30),
student_id VARCHAR(30),
assessement_id VARCHAR(30),
marks VARCHAR(30),
percentage VARCHAR(30),
attempt VARCHAR(30),
Primary key (id)
);
------Inserting test record:------
To test here we use some table to check the DML commands which is use to inserted value in to the table.
-------Insert data into student table:
Insert into Student values(150,"LUIS","PASTURE","LUIS PASTURE",9999999915,"12/02/2019",
"WASHINGTON",”A30”),(175,"John","Michel","John Michel",9927722178,"1/05/2017",
"Russia",”C30”);
select *from student;
Output:
150|LUIS|PASTURE|LUIS PASTURE|9999999915|12/02/2019|WASHINGTON|A30
175|John|Michel|John Michel|9927722178|1/05/2017|Russia|C30
-----------Insert data into lecturer table:
Insert into lecturer values (150,"xyz","ABC","01/05/2018",80000,5,"WASHINGTON"),(175,"ASD","ZXC","02/05/2019",75000,5,"Russia");
select *from lecturer;
Output:
150|xyz|ABC|01/05/2018|80000|5|WASHINGTON
175|ASD|ZXC|02/05/2019|75000|5|Russia
-----------Insert data into Module table:
Insert into module values
("C10","DBMS,A+),
("C20","PYTHON,A++),
("C30","JAVA,B);
select *from module;
Output:
C10|DBMS|A+
C20|PYTHON|A++
C30|JAVA|BLike this we inserted the value into the another three tables assessement, assessment_result and lecturer_module.
Data manipulation(DML)
Using the database defined in Part 3, write SQL queries to perform the following actions:
Find all student pairs born in the same place. Note: Do not double count the students.
Find all student-lecturer pairs who live in the same place.
Find a total number of students per module in the academic year 2017/2018.
Find the average mark for each module in the academic year 2017/2018.
Find all students who failed a module in the academic year 2017/2018.
Find the best performing student(s) in the academic year 2017/2018 based on the number of credits and the marks achieved.
Find the highest paid lecturer(s).
Find lecturer(s) who are not teaching any modules in the academic year 2017/2018.
Ans: 1
Select DISTINCT s1.fname,s2.fname
From student s1,student s2 where s1.address=s2.address;Ans: 2
Select s.fname , l.fname
From student s , lecturer l where s.address = l.address;Ans: 3
Select m. module_code, COUNT(ID) “Total number of student”
From module m,Student s
Where m.module_code=s.module_code AND year IN(2017,2018)
Group by m.module_code;Ans: 4
Select mark AVG(marks) as “Average marks” from assessment_result ar, assement a
Where a.id = ar.assement_id AND extract (year from a.assement_date ) in (2017,2018)
Group by a.module_code;Ans: 5
Select ar.student_id from assessment_result ar, assement a
Where a.id = ar.assement_id AND extract (year from a.assement_date ) in (2017,2018)
AND ar.marks<30 ;Note-marks<30 fail
Ans: 6
Select student_id from assessment_result ar, module m, assessement a where ar.moduel_code = m.module AND ar.assessement_id =a.id AND extract( year from a.assessement_date ) IN (2017,2018)
Order by ar.marks*m.credit desc rownum =1;Ans: 7
Select l1.fname from lecturer l1 where l1.salary = (select max(a_salary) from lecturer )
Order by ar.marks*m.credit desc rownum =1;Ans: 8
Select id from lecturer where id NOT IN (select lecturer_id form lecturer_molule where academic_year = 2017 OR academic_year = 2018);