CET315 Portfolio Exercise 1 – September 2015 CET315 Advanced Databases - Portfolio Exercise 1 Relational Algebra and SQL This is an individual exercise. Complete the following tasks below: 1. A number of tables are given in Appendix 1. Create an SQL script file which will create these tables in Oracle. Ensure that you do not create any additional attributes or data. All primary and foreign keys must be correctly defined, and you should use data types which accurately reflect the type of data that is being

stored, and are not wasteful of storage. 2. For each of the following natural language query descriptions, create the corresponding query in both Relational Algebra and SQL. Ensure that you show

all output from your SQL queries: a. Output the item and quantity of all items ordered for order number 625; b. For each customer that has made an order, print all customer's details

(including their balance), the order number and date, as well as the quantity and name of each item ordered; c. List the name and address of each customer who has not made an order. 3. For the following natural language query description, create the corresponding

query in SQL. Ensure that you show all output: a. At the end of the month the organisation likes to produce a report which details each customer's real balance taking into account, firstly their

current balance, and then all orders made during that month. They always ensure that each item ordered comes from the cheapest supplier. The report must include the following information: customer's name, address,

old balance, new balance and number of items that have been ordered by that customer during that period (you do not need to count the number of

orders for any one customer). Your task is to produce the report for September 2015, assuming that current balances shown are correct as of

the end of August 2015. Each customer should be shown as one row of your result, and the report must include all customers, whether or not they have made any orders. Note that the report should be written using the minimal number of SQL statements possible. You must not alter any of the data within the tables. The query should be constructed only by retrieving data from the tables (although you may create and use

additional views if you think necessary). It is important that for each query, it is written in such a way that it could deal with a larger (more general) data set than the example partial data set given. This means that

you should not make any assumptions about the data (e.g. names of customers) in your queries which would stop that query working for a larger data set. CET315 Portfolio Exercise 1 – September 2015

Submission Requirements

This element will be part of your final portfolio submission. You will be expected to submit one PDF document which contains your algebra and SQL queries (each SQL query must include output, preferably using screenshots, to demonstrate that it gives the correct result). You must also include, in an appendix, a copy of your SQL script file within this PDF

document. You can use either SQL*Plus or SQL Developer where necessary, but all commands for creating tables, inserting data and queries must be written by yourself in Oracle SQL (i.e. not using any assistive features of the application that you are using). Your PDF file must be submitted according to the submission guidelines given for the whole portfolio. Marking Scheme

This element will be marked as part of your final portfolio submission. The marking criteria for each element is given in the overall portfolio guidelines. However, this element will be judged according to the criteria given on the following page: CET315 Portfolio Exercise 1 – September 2015

CET315 Advanced Databases - Portfolio Element 1 Name

Task Description Weight Not attempted Major

Errors in solution

Minor errors

in Solution Correct

Solution 1a Script File correctly models tables and attributes given

10% 1b Script file uses valid data

types and contains valid

integrity rules 10% 1c Script file inserts data correctly 5%

1d Script file contains no errors and would work in Oracle 5% 2a Algebra query 5% 2a SQL query and output 5%

2b Algebra query 10% 2b SQL query and output 10%

2c Algebra query 10% 2c SQL query and output 10% 3a SQL query and output 20%

CET315 Portfolio Exercise 1 – September 2015 Appendix 1

customers orders name address balance order_no order_ date

cust Shaun Turtle 4 Family Avenue -200 624 10/09/15 Shaun Turtle Samantha Salamander 53 Lois Lane -50 625 20/09/15 Ruth Raptor Ruth Raptor 21 Stony Street 43 626 25/09/15 Shaun Turtle

includes supplies order_no item qty name item price

624 Cheddar 3 Acme Cheddar 3.49 624 Chardonnay 6 Acme Chardonnay 5.19 625 Cheddar 5 Acme Walnuts 0.06

625 Rocket 1 Ajax Cheddar 3.90 625 Walnuts 1024 Ajax Chardonnay 5.09 626 Walnuts 2048 Ajax Rocket 0.69

CET315 Portfolio Exercise 3 – September 2015 TeCET315 Advanced Databases - Portfolio Exercise 3 Object-Relational Databases This is an individual portfolio exercise.

During previous tutorials on object-relational databases you have developed part of an object-relational database based on a hospital case study. If you had not previously completed this tutorial then it is strongly recommended that you do so before attempting

this portfolio element. This exercise is based on a tennis database and the UML class diagram for this case study is given in Appendix A. Using SQL Developer, your tasks for this portfolio element are to:

1. Create an Oracle 11g type that will implement the AddressType type. 2. Create an Oracle 11g type that will implement the PlayerType type. As part of this you are required to write a method which will print a player's address as

spaces, e.g. '12 Winstone Avenue, Durham, SR2 1AB, UK'. 3. Subsequently create a Player table which will store player's details and insert some (i.e. at least six) rows of data into this table. 4. Create an Oracle 11g type that will implement the TeamType type. To do this

will require you to do some investigation into use of the object-relational REF construct (for defining object-references between tables) for relating Teams to Players. You will also need to investigate the use of the object-relational feature of VARRAYs to store a list of telephone numbers for the team. There is no need

to define or implement the get_tel_num method at this point. 5. Subsequently create a Team table which will store team details, and insert some (i.e. at least six) sample rows of data into this table. 6. Develop queries which will demonstrate each of the object-relational features that you have implemented, i.e. a. Use of the method for displaying player's addresses;

b. Joining tables by use of object references; c. Displaying the contents of arrays (i.e. telephone numbers) as part of a

query; 7. A decision has been made by the designers to store a team's telephone numbers with a separate dialing code and international prefix. For example, instead of storing each telephone number as a simple 13 character string (e.g. 0191

1230000), you should store each telephone number as three separate values

should also be able to store an unspecified number of telephone numbers per team (rather than the previous restriction of three per team). You should also be able to store the type of each telephone number, e.g. home, fax, mobile, etc. Using an appropriate structure: a. Change your Team type so that it can cope with this new telephone number format (note, you must replace the original team type in the

CET315 Portfolio Exercise 3 – September 2015 database, i.e. do not simply create a team type with a new name, and you

must demonstrate that there are no Oracle errors); b. Implement the get_tel_num method so that it accepts as a parameter a

string specifying the telephone number type (e.g. business, mobile, fax) and returns the corresponding telephone number (as a string, with each

part separated by a '-' sign, e.g. +44-191-1231234). c. Develop an appropriate query that will display all telephone numbers for all teams in a suitably structured format. d. Develop an appropriate query that will select and display a specific

telephone number (chosen by the type, e.g. business, mobile, fax) for a

particular team. You will only gain full marks if you use the method that you implemented in task 7b. Submission Requirements This element will be part of your final portfolio submission. You will be expected to submit one PDF document which contains all of the SQL commands as solutions to the above tasks, and copies of output of all the above commands and query output (all SQL must include output to demonstrate that it works correctly).

You must also include a printed copy of your SQL script file within this PDF document. You can use either SQL*Plus or SQL Developer where necessary, but all commands for creating tables, inserting data and queries must be written by yourself in Oracle SQL (i.e. not using any assistive features of the application that you are using).

Your PDF file must be submitted according to the submission guidelines given for the whole portfolio. Marking Scheme This element will be marked as part of your final portfolio submission. The marking

element will be judged according to the criteria given on the following page. CET315 Portfolio Exercise 3 – September 2015 CIF302 Portfolio Exercise 3

Name Task Description Weight Not

attempted Major errors Minor errors

Correct solution

1 Address Type created 5% 2a Player Type created 5% 2c Method to print player's

10% 3 Player table created and relevant data inserted 5%

4a Team Type created 5% 4b Correct use of REF construct for team captain 5% 4c Correct use of VARRAY

numbers 5% 5 Team table created and

5% 6a Query showing use of methods

5% 6b Query showing use of REF 5%

6c Query showing use of VARRAY 5% 7a Suitable structure created for telephone numbers 5%

7a Re-developed Club type 5% 7a Re-developed Club table

and appropriate data inserted 5% 7a New Club type and Club

table created in Oracle

without error

7b Implementation of get_tel_num method 5%

7c SQL query to retrieve all telephone numbers 5% 7d SQL query to retrieve an

individual telephone number 10% CET315 Portfolio Exercise 3 – September 2015 Appendix A – Department Store Case Study

+street : VARCHAR2(30) +town : VARCHAR2(20) +postcode : VARCHAR2(8) +country : VARCHAR2(2) +player_no : NUMBER(2) TeamType +player_first_name : VARCHAR2(30) +team_number : VARCHAR2(3)

+player_last_name : VARCHAR2(25) +team_location : AddressType +national_ranking : NUMBER(2) +team_captain : REF Player +matches_won : NUMBER(3) +team_contact : ARRAY(3) OF VARCHAR(13)

+home_address : AddressType +get_tel_num(t_type : STRING) : VARCHAR(13) +phone_number : VARCHAR2(13) +get_home_address() : VARCHAR2 PlayerType

1..1 1..*

0..* 1..1