[SOLVED] CSE103 Assessment Data Models & ERM

CSE103Assessment 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 Universitys 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 fieldname.

Specify the following queries in relational algebra (in your answer use the following convention, $ for a NATURALJOIN).

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: / 100Signature of Marker

Section B: / 100Signature of Marker