CSE103 Assessment Data Models & ERM
Declaration on plagiarism. I confirm that I have read and understood the definitions of plagiarism and collusion given in Section 4.10 of the University’s Code of Practice on Assessment. I confirm that I have NOT committed plagiarism when completing this piece of work.
Signature:
Student Name (last, first) Student Id:
Answer the questions on this document.
Section A
Given the following tables (task 1 -3):
Task 1: Explain the difference between a Relation Schema and a Relation Instance; give an example of each using the above tables. (20 marks)
Task 2: For the Branch table, identify the column(s) that uniquely identifies a tuple. Is this a primary key? Is it a composite key? (15 marks)
Task 3: Using the Branch and Staff tables, explain how the two main relational integrity rules work? (20 marks)
Consider the following schema:
Suppliers (sid : integer, sname : string, address : string)
Parts (pid : integer, pname : string, colour : string)
Catalog (sid : integer, pid : integer, cost : real)
NB. The key fields are underlined and the domain of each field is listed after the field name.
Specify the following queries in relational algebra (in your answer use the following convention, $ for a NATURAL JOIN).
Task 4: Find the name of suppliers who supply some black parts. (15 marks)
Task 5: Find the sids of suppliers who supply some black or white parts. (15 marks)
Task 6: Find the sids of suppliers who supply some black parts, or are located at ‘88 South Road’. (15 marks)
Section B
Task 7:
For each of the descriptions given:
identify the entities involved and their attributes
identify the relationships and state what they are (1:m, m:n)
draw an ERM diagram representing the information given, decompose any m:n relations.
During the course of a season a football team plays a number of matches. Some of these are home others are away. Matches always involve two teams, one playing away and one at home. Teams belong to a variety of leagues each of which is subdivided into divisions. A team may only belong to one league and one division of the league. (30 marks)
A company maintains purchase order information. Customers place a number of orders. An order can only arise from a single customer. Orders consist of two types of information, the order header information (delivery address etc) and a number of order lines. An order line specifies which part is ordered and the quantity required. Parts are stored in more than one warehouse. (40 marks)
A University maintains information about the modules a student takes. A course consists of a number of modules. Each module is led by a member of staff who belongs to a school. A module is associated with one course only. A school employs a number of staff. A school runs a number of courses. A student takes a number of modules. A module is taken by many students. (30 marks)
Deadline for submission: Monday 13th November 2017 by 5pm; box by the elevator on the fourth floor.
Section A: / 100 Signature of Marker
Section B: / 100 Signature of Marker