本次加拿大代写是一个数据库管理系统的assignment
Q 1: (7 points) Answer the following questions using the university schema discussed in class:
(a) The primary key for the advisor relation is s id. Suppose a student can have more than one suprvisors.
Would s id still be a primary key in advisor? If yes, why? If not, what would be a suitable primary key? (3 marks)
(b) The primary key for prereq is both attributes course id and prereq id. Why wouldn’t only course id work as primary key? (2 marks)
(c) Given the existing schema of teaches, two or more instructors can teach the same section. How can the primary key be changed to restrict a section to one instructor only? (2 marks)
Q 2: (12 points)
Consider the following bank database schema:
branch(branch name, branch city, assets)
customer(ID, customer name, customer street, customer city)
loan(loan number, branch name, amount)
borrower(ID, loan number)
account(account number, branch name, balance)
depositor(ID, account number)
Write an expression in relational algebra to nd the following:
(a) Find the cities that host branches that have a loan that is greater than $50000. (3 marks)
(b) Find the ID of each depositor who has an account with a balance greater than $50000 at the \Ottawa” branch. (3 marks)
(c) Find the names of customers who have at least one loan amount that is greater than at least one account balance. (6 marks)
Q 3: (33 points) Using the university database schema discussed in class, write the SQL statements that do:
(a) Create a new course (\Aces of Databases”) with ID (\COMP5118″) in the Computer Science department (\Comp. Sci.”) with 0 credit hours. (3 marks)
(b) Create a section ‘A’ for this course in the Winter of 2020 with no known location or time, yet. (4 marks)
(c) Enroll all students in the department into this course. (5 marks)
(d) One student with ID 12345 cannot take this course because of violating the prerequisite requirements (didn’t pass COMP3005). Unregister this student from the new section. (3 marks)
(e) For each student who took a course at least twice, show the course ID and the student ID. (5 marks)
(f) Find the ID and name of instructors who never gave a grade ‘A’ in the courses they taught (note that instructors who never taught a course satisfy this condition). (5 marks)
(g) Rewrite the previous query so that you make sure that the instructor taught at least one course. (5 marks)
(h) Find the lowest, across all departments, of the per-department maximum salary. (3 marks)