Question

1. Consider the following ER diagram. Stung StuName Grade SubCode SubName STUDENT has an ENROLMENT > -Fora + SUBJECT a. Convert this ERD to a Relational Schema. Indicate all Pks and FKs. b. What is the PK of ENROLMENT? C. Write the SQL statement to create a student: STU101 Henry Bloggs d. Write the SQL statement to create a subject: INF10004 Database Analysis and Design e. Write the SQL statement to create an enrolment: Enrol Henry into Database Analysis and Design. f. After doing very poorly in the Final Exam, Henry scores a "N" (a fail) Write the SQL statement that records this information in the database g. Some months later, Henry sits another exam for INF10004 and obtains a "D" (a Distinction) Write the SQL statement that records this information in the database h. Can this database design record the fact the Henry scored an "N" in one semester and then a "D" in the next semester? Why?

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using SQL Server 2014.

Question a:

create table Student(
StuNo varchar(10) primary key,
StuName varchar(50)
);

create table Subject(
SubCode varchar(10) primary key,
SubName varchar(50)
);

create table Enrolment(
StuNo varchar(10),
SubCode varchar(10),
Garde char(1),
primary key(StuNo,SubCode)
);

****************************

Question b:

• Combination of StuNo and SubCode is primary key in Enrolment table.

******************************

Question c:

insert into student values('STU101','Henry Bloggs');

******************************

Question d:

insert into subject values('INF10004','Database Analysis and Design');

******************************

Question e:

insert into Enrolment (StuNo,SubCode) values ('STU101','INF10004')

***************************

Question f :

update Enrolment set Garde='N' where StuNo='STU101';

****************************

Question g :

update Enrolment set Garde='D' where StuNo='STU101';

************************************

Question h :

• In the given relational database there is column which can record the semester details.
• using query in question g record will be updated but Grade N will be replaced by N.

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.