Add README for assignment 2

1 files changed, 71 insertions, 0 deletions

diff --git a/assignments/2/README.md b/assignments/2/README.md
new file mode 100644
index 0000000..a22dabc
--- /dev/null
+++ b/assignments/2/README.md
@@ -0,0 +1,71 @@
+Total: 100 marks
+
+Weight: 15% of your final grade
+
+Due: After you have completed Unit 6.
+
+Answer all of the following questions, and upload your completed work here.
+Question 1 (12 marks)
+
+Consider the following EER diagram for the Royal Victoria Hospital (RVH) database.
+EER Diagram
+
+Data volume and access for this diagram are as follows:
+
+    There are 1 000 patients and 500 items yielding a total of 10 000 usage records in the database.
+    There are 50 physicians and a total of 4 000 prescriptions in the database.
+    There are 200 treatments in the database.
+    There are 50 accesses per day for patient records; of these, 30 request access to both prescription and usage records.
+    There are 20 accesses per day for physician records; of these, 20 request access to prescriptions.
+    There are 50 accesses per day to item records; of these, 10 request access to usage records.
+    There are 5 direct accesses per day for treatment records.
+    Of the total accesses to prescription records, 20 request access to patients, 30 request access to physicians, and 35 request access to treatment.
+    Of the total accesses to usage records, 10 request access to patients and 30 request access to items.
+
+Draw a composite usage map for the RVH database.
+Question 2 (12 marks)
+
+Answer the following questions (250 words max/question).
+
+    What are the typical integrity controls performed in both data integrity and referential integrity?
+    Using an example for each situation, illustrate the three common situations that suggest relations should be denormalized.
+    What are the advantages and disadvantages of horizontal and vertical partitioning?
+
+Question 3 (12 marks)
+
+Answer the following questions (250 words max/question).
+
+    What factors should be considered when choosing a file organization?
+    What is the purpose of clustering data in a file?
+    Compare hashed file organization versus indexed file organization. List two advantages of indexed over hashed, and two advantages of hashed over indexed.
+
+Question 4 (18 marks)
+
+Consider the following database:
+
+Employee(emp-no, name, department, salary), ProjAssigned(emp-no, proj-no, worked-hours)
+
+    Write one SELECT SQL query to list the numbers and names of all employees with a salary greater than 66 000 who are assigned to projects, the projects they are assigned to, and the corresponding hours worked. Your list should be sorted by employee name.
+    Define indexes on selected attributes to speed up your query, and justify your selections.
+    Write SQL queries to create the indexes you defined above.
+
+Question 5 (40 marks)
+
+Consider the following three relations:
+
+TRAVEL_AGENT (name, age, salary)
+CUSTOMER (name, departure_city, destination, journey_class)
+TRANSACTION (number, cust_name, travel_agent_name, amount_paid)
+
+Write SQL statements to answer the following questions.
+
+    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 1 000.
+    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 did not arrange 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”.