Normalisation, ER modelling, DDL, DML In Database | Hire Relational Database Expert

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) 

  1. Suggest the attributes that should become part of the primary key.

  2. List all functional dependencies.

  3. Provide all reasons why this relation is not in the first normal form.

  4. Provide all reasons why this relation is not in the second normal form.

  5. 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 form

Ans: 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|B


Like 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:


  1. Find all student pairs born in the same place. Note: Do not double count the students.

  2. Find all student-lecturer pairs who live in the same place.

  3. Find a total number of students per module in the academic year 2017/2018.

  4. Find the average mark for each module in the academic year 2017/2018.

  5. Find all students who failed a module in the academic year 2017/2018.

  6. Find the best performing student(s) in the academic year 2017/2018 based on the number of credits and the marks achieved.

  7. Find the highest paid lecturer(s).

  8. 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);

19 views0 comments

Recent Posts

See All