question archive Assignment 2 Deadline: Tuesday 15/11/2021 @ 23:59 [Total Mark for this Assignment is 5] Fundamentals of Databases IT403 College of Computing and Informatics 1
Assignment 2 Deadline: Tuesday 15/11/2021 @ 23:59 [Total Mark for this Assignment is 5] Fundamentals of Databases IT403 College of Computing and Informatics 1
Subject:Computer SciencePrice: Bought3
Assignment 2
Deadline: Tuesday 15/11/2021 @ 23:59
[Total Mark for this Assignment is 5]
Fundamentals of Databases
IT403
College of Computing and Informatics
1.5 MarksQuestion One
Learning Outcome(s):
LO3
Create Entity-Relationship model, Relational model, and write SQL queries
From the Salesman and Customer tables below, write SQL statements to do the following:
a) List the salesman id, rating and names of those salesmen who get rating from 3 to 5, sorted DESCENDING by rating.
b) List all the customers’ information whose name starts with ‘F’ and the fourth character is ‘I’, rests may be any character.
c) List the name and country of those customers and salesmen who belongs to the same country.
Salesman table:
Customer table:
Question Two
1.5 Marks
Learning Outcome(s):
LO3
Create Entity-Relationship model, Relational model, and write SQL queries.
Write SQL statements to do the following on the database schema shown below.
a) Insert a new record in the course table with the following data values
course number = 'COSC4390'
course name = 'Knowledge Engineering'
course credit hours= ‘3’
department = ’COSC'>.
b) Change the class of student 'Ahmed' to 4.
c) Delete the record of the student whose name is 'Ahmed' and his number is 22.
Question Three
2 Marks
Learning Outcome(s):
LO3
Create Entity-Relationship model, Relational model, and write SQL queries.
Map the database schema shown in the following figure into a relational database schema.
