COSC 3P32 Midterm Practice Questions

Q1



Given the portion of an ER diagram displayed below, write the SQL statements to create the
resulting SQL table schema.

Q2.

Design the ER diagram which most accurately represents the following scenario:
• We are going to start up an online book store. For each of our customers we want to record
their name, address and phone number.

• For each book we want to record the ISBN, title, author, price and the number of copies
which we have in stock.

• Each order which we receive will be given an order number and we will record the customer,
the order date and the credit card for payment.

• An order will consist of one or more books, so we will have to keep track of which books are
associated with an order and how many copies of each book are ordered. Our policy will be to
ship each book title when we have a sufficient number of copies in stock, so different books on
the same order may have different ship dates.


Q3.


For developing our queries, we will be using the following relational schema:

Student(SID, name, username, major)
Course(cname, description, department, weight)
Offering(OID, cname, year, term, duration)
Enrolled(SID, OlD, grade)

A course relation is the generic description of a course which is offered by a department. The
offering relation is the specific instance of a course being taught in a given term, and is the
entity which will be associated with the student enrollment. Students are enrolled in these
offerings and achieve a mark.
Express each of following queries in Relational Algebra:

Write a relational algebra query which will list the cname and description of all
the courses which are being offering in the Fall 2020 term.

Write a relational algebra query which will list the name of all the students who have earned
60% in both 'COSC2P12' and 'COSC2P13'.


Express each of the following queries in SQL:

Write a query which will list the course cname, and description for all the courses
being offering by Computer Science in 2019.

Write a query which will list the student name, course cname, and grade of all the students who
received a grade greater than 95 in more than one course in 2017.

Write a query which will list the student name and grade for all the students who have a mark
above the class average in the 2018 spring offering of COSC3P32.




























































学霸联盟