Database Server Management

Database Server Management

In this assignment you will build a small database to support the needs of a private company called The Concert Office. To do this you will need to create tables to support the following scenario. [see next few pages].

Please use Oracle DBMS and SQL*Plus on the University’s Oracle database Server to complete the assessment tasks. You may hand draw or use a suitable drawing tool [e.g. Oracle Modeller] to produce your Entity Design Diagram Only.

Submit ONE hard copy of the assessment to the Faculty of Science and Engineering (FSE) Student Registry Point (MI Block), no later than 4.00pm, Tuesday 14th April 2015. Please ensure the module code 6CI007 is clearly printed on the front cover, together with your student number[s].
The assessment is to be carried out in pairs [no more than two students per group]. All names and student numbers should be provided, together with a short statement of each individuals’ contribution [no more than 45 words each] on the title page. Include each of your signatures of agreement on the statements at the end of the page. Hand in hard copy, ONE report only.

In this project you will build a small database to support the needs of a company called TheConcertOffice that organises concerts in the UK. To do this you will need to create tables to support the following scenario.
TheConcertOffice run repeated scheduled concerts involving a single principal artist (or group) in large venues within the UK. These concerts have a name, a duration of 2,3,4,or 5 hours, a type of either classical, rock, or pop and a cost that varies between £25 and £450. The concerts are repeatedly run as events in a variety of venues. So, for example, the” REUNION” concert could run a number of times in different venues. Each of these runs is called an event for that particular concert. The date of each event and the venue are recorded. Customers book onto the events and TheConcertOffice need to record the customer name, gender, telephone number, and address. Each time a customer attends an event, their time of arrival is recorded, and if they have parked their car in the official car park, the registration number of the vehicle is also recorded. Customers will also give an evaluation each time they attend an event (the evaluation is a number from 0 to 5). TheConcertOffice need to record the evaluation from each customer each time they attend an event. It is not a requirement that all events have customers registered on them, and it is also not required that every customer has to have attended a concert. Each event is held at an approved venue and each venue has a name, a maximum capacity (between 1,000 and 60,000 people) and a postcode.

1. Construct a simple entity model based on the above scenario.

TheConcertOffice have produced a first draft model of the above scenario as follows

You should assume this model is a reasonable, but incomplete, draft and use it to develop a final improved entity model. Using your improved and complete entity model, construct a set of tables with suitably defined columns to support this scenario.
10 marks

Using Oracle DBMS and SQL*Plus only

2. Populate the tables with appropriate test data, bearing in mind the following :

a. There may be some concerts for which events have yet to be arranged.
b. There may be some customers who have yet to book on an event.
c. All events are uniquely identified by a single key column called event_id
d. Records of customers attending concerts (known as a booking) are identified by a combination of foreign keys (not by a single column surrogate key)
e. There may be some venues that are yet to be allocated to an event.

Hint: In order to keep the amount of test data to a minimum, we will assume that the company is unlikely to run with more than six different concerts and that the number of customers about which they have details is less than 11.

Take a look at the query specifications in question 3. You will need to have data values that are relevant to these queries. BUT the design of your tables and test data should not be compromised merely to support just these queries.

The creation of a good, representative set of test data is very important and is worth 15 marks
15 marks

3. Create appropriate primary and foreign key constraints on the tables.

(IMPORTANT: do NOT use ALTER TABLE command to add these. All constraints should be included during CREATE TABLE commands).

15 marks
6CI007 Database Server Management Assessment (continued)

4. Develop a set of queries as follows, showing all output that they produce :

a. Find details of all concerts being held in Edinburgh

b. Find details of the concert that runs the most number of times.

c. Find the total number of customers attending each type of concert.

d. Write and test a query to list event ID, customer ID, and the evaluation that the customer awards. Use a CASE construct to display evaluation as text rather than numbers by transforming the values 0, 1, 2, 3, and 4 into the words TERRIBLE, BAD, MEDIOCRE, FAIR, and GOOD, respectively. Any other value should be displayed as is.

e. Show details of customer names and the names of the concerts that they have attended.

f. Write and test a query to list the name and cost of each concert. Add a column that compares the cost of the concert to the average cost of all concerts i.e., shows the difference between the concert cost and the average cost of all concerts.

g. List all customers who have not attended an event held in London.

h. Find the events of each concert that have a number of attendances below the average attendance at events of that same concert.

i. For those concerts that actually happen to have events within the past 200 days, show the concert ID and name of the concert along with the date of each event and the location name, postcode and maximum capacity. Any concerts that do not have events within the last 200 hundred days must still have their names and IDs appearing in the output.
Remember, EVERY concert ID and name that you have in your concert table should be present in your report .

j. Find names of all female customers who have participated in a concert in 2003

k. Find full details of the venues that are allocated to less than two events. (You should bear in mind that there may be some venues that have no events allocated.)

l. Show details of all the events for the top three most expensive concerts. The query must also show the name of the concert and handle any tie situations.

35 Marks
6CI007 Database Server Management Assessment (continued)

5. a. Develop a view that shows concert details along with the details of the
events of those concerts. Do not include concerts for which there are no
events. Display the contents of the view. Do not include details of the
venues allocated to the events.

The view MUST involve a join. This join MUST be of only two tables.

b. Test the view for its ability to support DML activity. The testing should involve deletes, inserts and a number of update statements affecting different columns.
You should then examine any error messages and explain why they arise.

So – the tests should involve :

i Separate updates on each individual column within the view
ii Inserts of new rows into the view
iii Deletes of rows from the view

Comment on the outcomes with respect to the ‘updatability’ of the view

Hint : You may wish to rollback any changes you have made to the data in order to obtain a consistent set of test data ready for question 6.
10 Marks

6. Create triggers that enforce the following business rules :

a Ensure that concerts cannot be run during July.

b If a customer gives a zero evaluation, the details of their attendance (customer name, which concert, the date of the event, venue and evaluation) must be placed in an audit table.

Hint: You will need to construct an audit table with the appropriate columns of correct datatype.

c Ensure that event records can not be deleted if the date of the concert is not later than the current date.

An essential part of this question is that having successfully compiled the triggers, you must construct a set of SQL statements and use them to test out the workings of your triggers.

For instance, if you have created an UPDATE trigger you will need to execute at least two UPDATE statements on the table on which the trigger is based – one that fires the trigger (and probably generates any error message from the trigger), and one that does not. In your testing, you must test all of your triggers, show their code and the output that they generate.

15 Marks

6CI007 Database Server Management Assessment (continued)

The hand-in

Your report should be produced to a professional standard, structured with appropriate titles/question numbers/labels/ contents page/page numbers and should include:-

i A simple entity model (Extended Entity Relationship diagrams are not necessary)

ii All table creation statements

iii The SQL used to create your constraints

iv Listings of all test data and an explanation of the chosen test data. (Use SELECT * FROM <<your_table_name>>; to show the test data. Do not show the INSERT statements)

v The view definition and how its data can be changed (and why)

vi The trigger definitions and demonstrations of how they work.

vii The output of all queries (properly formatted and presented to avoid any wrap around effect caused by SQL*Plus) along with the code used to generate the output.

Hint : IMPORTANT : Use courier new font for presenting all code and test data (see style adopted in lecture notes)

Avoid wraparound effects when displaying output in SQL*Plus (use column formatting commands and LINESIZE commands)