Cork Institute of Technology Higher Certificate in Science in Computing in Information Technology Support – Award (NFQ Level 6) Spring 2007 Databases I (Time: 2½ Hours) Instructions: Answer question 1 and 3 other questions. All questions carry equal marks.

1.

Examiner:

Ms. L. O’Sullivan Mr. J. Greenslade Mr. J. Walsh

Consider the following relational database schema for a shipments database (the primary key attributes are underlined) – a sample instance of which is attached to the paper. Supplier(S#, Sname, Status, City) Part(P#, Pname, Size, Material, Colour) Job(J#, Jname, City, Company, Start_Date) Shipment(S#, P#, J#, Qty, U_Price) where Supplier contains supplier details Part contains part details Job contains project details Shipment contains shipment details With respect to these tables, write SQL queries for the requests listed (a) – (e). (a)

(b) (c) (d) (e)

List the part numbers of parts that have been shipped in quantities of between 50 and 100 units and whose unit price was in excess of €10, in increasing order of part number. (5 Marks) List the city, or cities, that have more than one supplier with a status of 20 in reverse alphabetical order of city (5 Marks) List full details of all jobs that have been supplied with blue parts by a supplier named Smith. (5 Marks) For each part that has been shipped that begins with the letter ‘S’, list the part number, its name and how many shipments it’s been involved in. (5 Marks) For every supplier, who is based in either London or Paris, list the supplier number, name and the number of different parts they supply, if any, – arrange the output in descending order of the number of different parts they supply. (5 marks)

2.

Using the relational database schema given in question 1, and keeping the following in mind, answer questions (a)-(e): • Suppliers relation o S#, and SName are required fields. o City can only take the values London, Paris, Athens or Rome with a default of Rome. (ii) Part relation o P#, PName are required fields. (iii)Job relation o J#, JName are required fields. (iv) Shipment relation o S#. P# and J# are required fields. o Qty is an integer value greater than 0 with a default of 1. o U_Price must be greater than 0 (a)

(b)

(c) (d)

(e)

Write the SQL statements to create the Supplier, Part, Job and Shipments relations, including all the necessary constraints defined above and using appropriate data types based on the sample instance attached to the paper. (12 Marks) Write a SQL statement to add the supplier Murphy with a supplier number of S6. You must ensure that such a record can be inserted based on your answer to part (a) above. (2 Marks) Write a SQL statement to increase the price of brass parts by 10% – will this update operation succeed or fail? If not why not? (3 Marks) Write a SQL statement to delete the supplier S2. Given the referential integrity constraints you chose for this schema, explain what happens when this statement is executed. (3 Marks) Consider the following SQL statement: CREATE VIEW London_Suppliers AS SELECT * FROM Supplier WHERE City = ‘London’ WITH CHECK OPTION; Is this view updateable or not? If not why not? Explain the effect of the WITH CHECK OPTION clause of the CREATE VIEW statement. (5 Marks)

3.(a)

(b)

(c)

4.(a)

(b)

(c)

(d)

In terms of the relational data model, define the following - use an example to illustrate your answer: (i) Relation (ii) Attribute (iii) Tuple (iv) Degree (v) Cardinality (5 Marks) In terms of the relational model, define the following: (i) Superkey (ii) Candidate Key (iii) Primary Key (iv) Alternate Key (v) Composite Key (vi) Foreign Key For the relational database schema given in question 1, for each relations list – a superkey, all candidate keys, the primary key, any composite keys, any alternate keys, any foreign keys (12 Marks) Define the two principle integrity rules for the relational model and discuss why it is desirable to enforce these rules - produce a set of sample tables for the Supplier, Part, Job amd Shipment relations, described in question 1 and created in question 2, that violate the entity and referential integrity rules and explain how each table violates the relevant rules. (8 Marks) Explain what is meant by each of the following terms: (i) File-based system (ii) Database management system (DBMS) (5 Marks) Some of the main disadvantages of the database approach include: (i) Complexity (ii) Cost (iii) Higher impact of failure Discuss each of these disadvantages. (9 Marks) In terms of the ANSI/SPARC three-level architecture for a DBMS, briefly explain what is meant by each of the following terms: (i) Logical Data Independence (ii) Physical Data Independence (6 Marks) In terms of architectures used to implement multi-user database management systems, compare and contrast the roles of the client and server between the two and three-tier client-server architectures. (5 Marks)

5.(a) (b) (c) (d)

In terms of the SQL DML, explain the function of each of the clauses in the SELECT statement and the order in which they are processed. (7 Marks) In terms of SQL DML, what is the difference between a correlated subquery and a noncorrelated subquery? Use an example to illustrate your answer. (5 Marks) Give two advantages and two disadvantages of using views. (8 Marks) In terms of the relational model, list five properties of a relation? (5 Marks)

Attachment 1 - Instance of the Shipment Database SUPPLIER S# S1 S2 S3 S4 S5

SNAME Smith Jones Blake Clark Adams

SHIPMENT STATUS CITY 20 London 10 Paris 30 Paris 20 London 30 Athens

PART P# P1 P2 P3 P4 P5 P6

PNAME Nut Bolt Screw Screw Can Cog

SIZE MATERIAL 12 Steel 17 Steel 17 Brass 14 Graphite 12 Brass 19 Graphite

COLOUR Blue Plain Gold Black Silver Blue

JOB J# J1 J2 J3 J4 J5 J6 J7

JNAME Sorter Punch Reader Console Collator Console Tape

CITY Paris Rome Athens Athens London Oslo London

COMPANY START_DATE IBM 1-Mar-00 Digital 12-May-00 Wang 25-Oct-00 Wang 1-Dec-00 IBM 15-Jun-01 Hewlett 17-Nov-01 Digital 21-Feb-02

S# S1 S1 S2 S2 S2 S2 S2

P# P1 P1 P3 P3 P3 P3 P3

J# J1 J4 J1 J2 J3 J4 J5

QTY U_PRICE 1500 0.25 1200 0.3 500 1.2 250 1.4 1200 1 900 1.05 750 1.15

S2 S2 S2 S3 S3 S4 S4 S5 S5

P3 P3 P5 P3 P4 P6 P6 P2 P2

J6 J7 J2 J1 J2 J3 J7 J2 J4

100 350 700 1100 1000 475 690 1300 1550

1.5 1.35 22.5 1 1.02 10 9.75 12.5 12.25

S5 S5 S5 S5 S5 S5 S5 S5

P5 P5 P6 P1 P3 P4 P5 P6

J5 J7 J2 J4 J4 J4 J4 J4

250 1340 1000 350 350 600 250 150

25 21.5 10 1.2 1.25 1.05 25 13