Assignment 2
Due: After Unit 5
Total: 100 marks
Weight: 15% of your final grade
Answer all questions in this assignment. Submit your completed work to your Academic Expert for marking and feedback using this Assignment Drop Box.
1. (12 marks) Answer the following questions (150 words max/question).
Using an example, explain how referential integrity is established during the creation of database tables?
Explain the factors that need to be considered for creating an index on a specific table column.
Discuss the difference between triggers and stored procedures.
2. (40 marks) Consider these three relations and then write SQL statements to answer the questions that follow.
TRAVEL_AGENT (TA_no, name, age, salary)
CUSTOMER (C_ID, name, departure_city, destination, journey_class)
TRANSACTION (number, C_ID, TA_no, date, amount_paid)
Compute the number of different customers who have a transaction.
Display the name of the oldest travel agent.
List the total number of transactions for each travel agent. Consider only those transactions where the amount paid exceeds 1000.
Display the names and ages of the travel agents who have arranged journeys for customer “John Smith”, in descending order of age. (Use a subquery.)
Display the names and ages of travel agents who have arranged journeys for customer “John Smith”, in descending order of age. (Do not use a subquery.)
Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa”. (Use a subquery.)
Display the age of travel agents who have arranged journeys for customer “John Smith” to “Ottawa”. (Do not use a subquery.)
Display the names and salaries of all travel agents who have not arranged journeys for customer “John Smith”, in ascending order of salary.
Display the names of travel agents who have five or more transactions.
Display the names of all travel agents who have arranged at least ten journeys to “Ottawa”.
3. (8 marks) Consider the following Realtor database with three relations. Write SQL statements for creating a view called AgentsOfTheYear that will display the name and address of realtors who have sold a total amount of 5 million dollars’ worth of properties in 2021.
RealtorAgent (realtor_no, name, age, address, commission_percentage)
Customer (C_ID, name, property_id, property_address, description, property_tax, property_value) Transaction (realtor_no, C_ID, property_id, price_sold, date_sold)
4. (10 marks) Consider the following relations:
Student (student_no, name, address, GPA)
Course (course_no, title, texbook)
Instructor (instructor_no, name, phone_no, specialization) Registration (student_no, course_no, instructor_no, session)
Qualified_to_teach (instructor_no, course_no)
Determine the relationships among these relations. List the primary keys for each relation and any foreign keys necessary to establish the relationships and maintain referential integrity.
Write SQL query to list the names of instructors who have taught COMP 378 but not COMP 418 during the following sessions: Winter 2020 and Winter 2021.
5. (16 marks) Answer the following questions (250 words max/question).
Discuss the distribution of application logic components across clients and servers in the client/server environment.
What are the most important security features for data management software?
What threats to data security must be addressed in a comprehensive data security plan?
What is the difference between shared locks and exclusive locks?
6. (6 marks) Consider the concurrent execution of the following transactions. List two problems that may occur from the concurrent execution of these two transactions.
T1 T2
Read A
Read B
Read B
Write A
Write C
Read C
Write A
Write B
Commit
Commit
7. (8 marks) Consider the two application environments represented in Figure 1 and Figure 2. Identify the various security considerations that are relevant to each environment.
Figure 1: Static page request
Figure 2: Dynamic page request
