diff options
| -rw-r--r-- | assignments/2/README.md | 93 | ||||
| -rwxr-xr-x | assignments/2/run.sh | 4 | ||||
| -rw-r--r-- | assignments/2/run.sql | 42 | ||||
| -rw-r--r-- | doc/unit-5.md | 112 |
4 files changed, 217 insertions, 34 deletions
diff --git a/assignments/2/README.md b/assignments/2/README.md index a22dabc..9ddeda0 100644 --- a/assignments/2/README.md +++ b/assignments/2/README.md @@ -5,67 +5,92 @@ 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) + +## 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. +* 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) + +## 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? +* 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) +## 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. +* 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) +## Question 4 (18 marks) Consider the following database: -Employee(emp-no, name, department, salary), ProjAssigned(emp-no, proj-no, worked-hours) +```text +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. + +```sql +SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours" +FROM Employee e +INNER JOIN ProjAssigned pa ON pa."emp-no" = e."emp-no" +WHERE e.salary > 66000 +ORDER BY e.name ASC; +``` + +* Define indexes on selected attributes to speed up your query, and justify your selections. + +```sql +``` - 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. +* Write SQL queries to create the indexes you defined above. -Question 5 (40 marks) +## Question 5 (40 marks) Consider the following three relations: +```text 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”. +* 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”. diff --git a/assignments/2/run.sh b/assignments/2/run.sh new file mode 100755 index 0000000..f32b6ab --- /dev/null +++ b/assignments/2/run.sh @@ -0,0 +1,4 @@ +#!/bin/sh + +dir="$(dirname $0)" +./bin/console -f "${dir}/run.sql" diff --git a/assignments/2/run.sql b/assignments/2/run.sql new file mode 100644 index 0000000..24f1317 --- /dev/null +++ b/assignments/2/run.sql @@ -0,0 +1,42 @@ +-- \echo '--- Question 4 ---' + +ALTER TABLE IF EXISTS ProjAssigned DROP CONSTRAINT employees_fk; +DROP TABLE IF EXISTS Employee; +DROP TABLE IF EXISTS ProjAssigned; + +CREATE TABLE Employee( + "emp-no" bigint primary key, + name varchar(255), + department varchar(255), + salary decimal +); + +CREATE TABLE ProjAssigned( + "emp-no" bigint, + "proj-no" bigint, + "worked-hours" integer +); + +INSERT INTO Employee VALUES (1, 'A', 'Ruby', 200000); +INSERT INTO Employee VALUES (2, 'B', 'Golang', 300000); +INSERT INTO Employee VALUES (3, 'C', 'C', 400000); + +INSERT INTO ProjAssigned VALUES (1, 1, 40); +INSERT INTO ProjAssigned VALUES (2, 2, 80); + +EXPLAIN SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours" +FROM Employee e +INNER JOIN ProjAssigned pa ON pa."emp-no" = e."emp-no" +WHERE e.salary > 66000 +ORDER BY e.name ASC; + +ALTER TABLE ProjAssigned ADD CONSTRAINT employees_fk FOREIGN KEY ("emp-no") REFERENCES Employee("emp-no"); +-- CREATE INDEX employee_names_idx ON Employee(name); +-- CREATE INDEX employee_salary_idx ON Employee(salary); +CREATE INDEX employee_salaries_idx ON Employee(name, salary); + +EXPLAIN SELECT e."emp-no", e.name, pa."proj-no", pa."worked-hours" +FROM Employee e +INNER JOIN ProjAssigned pa ON pa."emp-no" = e."emp-no" +WHERE e.salary > 66000 +ORDER BY e.name ASC; diff --git a/doc/unit-5.md b/doc/unit-5.md index 142015c..38df362 100644 --- a/doc/unit-5.md +++ b/doc/unit-5.md @@ -10,6 +10,118 @@ Read Chapter 6: Introduction to SQL * The SQL Environment * Defining a database in SQL +ANSI SQL was first published in 1986. +Updated in: + +* 1989 +* 1992 +* 1999 +* 2003 +* 2006 +* 2008 + +Concepts were first articulated in 1970 by E.F. Codds paper +"A Relational Model of Data for Large Shared Data Banks". + +IBM devs started to build System R which was to demo an +implementation of a relational model in a dbms. + +They used a language named "Sequel" which was renamed SQL. + +SQL/DS: the first relational DBMS. +DB2: introduced in 1983 + +Original purpose of SQL standard: + +1. specify syntax and semantics of SQL DDL and DML +2. define data structures and basic operations for designing, accessing, maintaining, controlling and protecting a SQL database. +3. portability between database definition and application modules between DBMS's. +4. Specify minimal and complete standards +5. Extension point for handling referential integrity, transaction management, user defined functions, join operators and character sets. + +A standard provides + +* Reduced training costs +* Productivity +* Application portability +* Application longevity +* Reduced dependence on a single vendor +* Cross system communication + +The SQL Environment + +SQL is used to: + +* create tables +* translate user requests +* maintain data dictionary and system catalog +* update and maintain the tables +* establish security +* carry out back and recovery procedures + + +> RDBMS: a database management system that manages data as a collection of tables in which all data relationships are represented by common values in related tables. + +Each database is contained in a catalog, which describes any object that is a part of the database. + +> Catalog: A set of schemas that, when put together, constitute a description of a database. + +> Schema: A structure that contains descriptions of objects created by a user, such as base tables, views and constraints as part of a database. + + +Each catalog must also contain an information schema. + +* tables +* views +* attributes +* privileges +* constraints +* domains + +SQL commands can be classified into three types: + +1. DDL: Data definition language +2. DML: Data manipulation language +3. DCL: Data control language + +> DDL: Commands used to define a database, including those for creating, altering and dropping tables and establishing constraints. +> DML: Commands used to maintain and query a database, including those for updating, inserting, modifying and querying data. +> DCL: Commands used to control a database, including those for administering privileges and committing (saving) data. + +Each DBMS has a defined list of data types that it can handle. + +* numeric +* string +* date/time +* graphic data +* spatial data +* image data + +Defining a database in SQL + +```sql +CREATE SCHEMA database_name; +AUTHORIZATION owner_user mokha; +``` + +To define a portion of a database that a particular user owns. + +```sql +CREATE SCHEMA +``` + +To define a new table and columns + +```sql +CREATE TABLE +``` + +To define a new view + +```sql +CREATE VIEW +``` + ## Section 2: SQL as a Data Manipulation Language (DML) Read Chapter 6: Introduction to SQL |