SQL代写:SE3DB3 Relational Algebra and SQL

代写数据库作业,包括建表、读数据以及制作ER图。和普通数据库类作业稍微不同的地方在于,这个作业使用DB2作为数据库。

Requirement

This assignment will extend the previous work done with McMaster Travel Inc. to perform some simple data analytics. On the course webpage you will find the following:

  • a. createTables.ddl, a DDL file for CREATE TABLE statements.
  • b. loadData.ddl, a DDL file for INSERT TABLE statements.
  • c. asg2ER.pdf, a simplified schema depicted as an E-R diagram.

Please execute scripts createTables.ddl and loadData.ddl on your DB2 database SE3DB3 as you will use this schema for the questions below.

Relational Algebra

Written below are four categories, totalling 10 queries. These queries are to be translated from English to their Relational Algebra counterpart.

Customers

  • List all customers who have participated in exactly one tour.
  • List all customers who have participated in at least two tours.
  • List all customers and their city address, grouped by city.

Guides/Employees

  • Find all guides who worked on a tour last year (2015) and were also customers (unique).
  • Find all employees who have an email address. Return their age and gender only.

Providers

  • List all providers that do not provide any type of accommodation.
    NOTE: do not eliminate duplicates in the result.
  • List all providers who only provide accommodations or meals (not both).
    NOTE: do not eliminate duplicates in the result.
  • Find all providers who charge a $100 CAD or $200 CAD penalty. Return the provider’s ID as well as the type of service they provide.

Tours

  • Find the highest paid tour guides. Return the number of tours each guide has worked utilizing the label numTours.
  • Find all tours (unique) with guides making at minimum $5, 000 CAD. These guides must also be leading an in-progress (i.e. ‘I’) or future (i.e. ‘F’) tour.

SQL

Write and provide SQL statements for each of the queries in Part I. Execute each of your SQL queries against your database, and give the result of each query.

Indexes

This section is all about improving performance through the utilization of indexes. From the workload of the given queries in Part I., many display poor performance (i.e., increasing response times).

Your task is to improve the performance of these queries by defining two indexes that should be implemented on these tables. For each index, state:

  1. The attribute(s) the index is defined on.
  2. The properties of the index (e.g., type of index, clustered/unclustered, etc…)
  3. Which queries (q1 - q10) you think this index will help, and why.

Submission

All files are to be submitted using the Avenue to Learn platform (avenue.mcmaster.ca). Please ensure you submit all files with the correct names, as described below. Your submissions must be typed and clearly legible. You must include your name and student ID number in all files. Upload four files with the indicated file extensions (no compression based .tar, .zip, .rar files).

  • For Part I: Submit your relational algebra expressions in a file named ra.pdf.
    • Your relational algebra must be typed. No exceptions.
  • For Part II: Submit your SQL statements in a script file called queries.sql.
    • Ensure your SQL statements are syntactically correct and that they are executable on the DB2 servers. Non-executable queries will not be marked.
  • For Part II: Submit the corresponding query results in a file called queries.results.
    • Clearly label and comment which query corresponds to which result tuples.
  • For Part III: Submit your index recommendations in a file named index.pdf.