A software developer has built a relational database model for the
Learning Management System of an online learning
website. Various relations (tables) are defined
to store data records related
to each entity in a separate relation.
Three of the Relations (STUDENT, COURSE, and ENROLLMENT) defined
in that database
model along with their attributes are given below:
STUDENT |
|||
StudentID |
StudentName |
DegreeProgram |
ResidentCity |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
COURSE |
||||
CourseCode |
CourseTitle |
TeacherID |
DegreeProgram |
CreditHours |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
… |
ENROLLMENT |
||
StudentID |
CourseID |
Semester |
… |
… |
… |
… |
… |
… |
… |
… |
… |
The developer needs to extract the following information given in Table-1 from his defined database
model using the relational database
queries that consist
of SELECT, PROJECT
and JOIN operators.
Table-1: Required Information
Sr. No. |
Information required to be Extracted |
1 |
Extract all the students from the STUDENT table belonging to a specific city. |
2 |
Extract all the students from
the STUDENT table
who are enrolled in “BSCS” degree program. |
3 |
Extract all the records in the
columns “Student Name”
and “Degree Program” of the STUDENT table. |
4 |
Extract all the records in the columns
“Course Code”, “Course
Title” and “Degree Program” of the
COURSE table. |
5 |
Extract all the courses from the COURSE table having three or more
credit hours. |
6 |
Join STUDENT and COURSE tables using the
Degree Program attribute. |
Now, in the below given table (Table-2), some relational database
operations (Queries) related to the above
database model are defined along
with the name of
resulting new relation.
The name of the
resulting new relation is also used as reference to identify that query (i.e.
each query is referenced as A, B,
C, D, E, F, G, H, I, J, K, and L).
Table-2: Relational Queries
Relational Query (Operation) |
Name of the Resulting Relation (also a Reference Label) |
A <- SELECT
from STUDENT where
ResidentCity='Lahore' |
A |
B <- PROJECT
from STUDENT where
City='Lahore' |
B |
C <- PROJECT
[CreditHourse>=3] from COURSE |
C |
D <- JOIN STUDENT and COURSE where STUDENT. DegreeProgram=COURSE.DegreeProgram |
D |
E <- PROJECT
CourseCode, CourseTitle, DegreeProgram from COURSE |
E |
F <- SELECT
from STUDENT where
DegreeProgram = 'BSCS' |
F |
G <- JOIN
COURSE and STUDENT on Column.DegreeProgram |
G |
H <- PROJECT from COURSE Columns[CourseCode, CourseTitle, CreditHours] |
H |
I <- PROJECT StudentName, DegreeProgram
from STUDENT |
I |
J <- SELECT Columns[StudentName, DegreeProgram] from STUDENT |
J |
K <- SELECT
[DegreeProgram = 'BSCS'] from
STUDENT |
K |
L <- SELECT
from COURSE where
CreditHours >=3 |
L |
This table (Table-2) contains
both syntactically correct and incorrect relational database queries (referenced
as A to N). Out of these twelve (12) queries, six (6) syntactically correct
queries can be used to extract the information required in the table-1.
You are
required to identify the correct relational database query from Table-2: Relational Queries for each of the “Required
Information” entry in Table-1
and mention its reference label in
the 2nd column of the below given table against the Sr. no. of Required
Information. For example, the answer to “Sr. no. 1” entry
of “Required Information” column is query “A” which is solved in the below given
table.
Now, you need to fill the remaining five (5) entries in the below given
table with the Reference label of correct relational database against each Sr. No.
of the required information table.
(Provide your Answer only in
the below given Table)
Sr. No. of Required
Information given in Table-1 |
Reference label of the Correct Relational Query {Out of A, B,
C, D, E, F, G, H, I, J, K, L} from Table-2 |
1 |
A (solved) |
2 |
? |
3 |
? |
4 |
? |
5 |
? |
6 |
? |
Note: You don’t need to copy the assignment statement or write any
queries in your solution file. Just mention
the “reference label”
of the correct relational database
query from table-2
against the Sr. No.
of “Required Information” in the above table.
Solution:
CS101 Assignment 2 Solution
ID: Abdul Hadi E Services
(Provide your Answer only in
the below given Table)
Sr. No. of
Required Information given in Table-1 |
Reference label of the Correct Relational Query {Out of A, B,
C, D, E, F, G, H, I, J, K, L} from Table-2 |
1 |
A (solved) |
2 |
F |
3 |
J |
4 |
E |
5 |
L |
6 |
G |
Want more info, please press the below button
`
0 Comments
Please write us. Please write in detail that how may we help you