联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-23:00
  • 微信:codinghelp

您当前位置:首页 >> Database作业Database作业

日期:2019-12-02 10:07

CS2855 Assessed Coursework 2

This assignment must be submitted via the anonymous submission script

Due date: Tue 3 Dec 2019 at 14:00

Learning outcomes assessed

This assignment covers basic SQL queries and definition, as well as functional

dependencies (FDs, for short), normalisation of relational databases

and (lossless-join) decomposition of tables.

The assessed learning outcomes are implementing a relational model in

SQL and developing queries of different complexity, and understanding of

the basic notions and applications of table normalisation.

Instructions

You must submit two files:

1. For the SQL part (Part 1), you must submit a text file named

“queries.sql” that contains the SQL queries in order. Comment

lines can be added to the file as lines beginning with the double

dash (“- -”) symbol.

2. For the normalisation part (Part 2), submit a PDF file, with a clearly

written solution. Solutions that are presented in an unclear format

(i.e., not a PDF), low-resolution pictures, or haphazard handwriting

will not be marked (since we will not be able to asses their correctness).

NOTE: All the work you submit should be solely your own work. Coursework

submissions are routinely checked for this.

1

Assignment

Part 1: SQL

Consider the following relational model that represents part of an online

shop’s database:

product(prod id, product name, product price, product manufacturer)

customer(cust id, customer name, customer address)

rating(prod id, cust id, rating date, rating stars)

Foreign Key: prod id references prod id in product table

Foreign Key: cust id references cust id in customer table

sale(sale id, prod id, cust id, sale quantity, sale cost)

Foreign Key: prod id references prod id in product table

Foreign Key: cust id references cust id in customer table

These schemas represent product information, including sales. Customers

can provide ratings for products in a 0 to 5 stars fashion. It is

possible for clients to rate the same product more than once.

1. Give an SQL definition of this database, including the referentialintegrity

constraints that should hold (such as foreign keys), taking

care for attributes that should not be null.

2. Define queries in SQL to obtain the following information:

(a) The names of all products manufactured by ‘Samsung’, in ascending

ordered by price.

(b) The names of all products that had at least one rating of 3 or

more stars. No duplicate names should be returned.

(c) For all products, their name, average star rating, and most recent

rating date. The results should be in descending order of average

star rating.

(d) For all cases in which the same customer rated the same product

more than once, and in some point in time gave it a lower rating

2

than before, return the customer name, the name of the product,

and the lowest star rating that was given.

Part 2: Normalisation

Question 3. Let F be the following collection of functional dependencies

for relation schema R = (A, B, C, D, E):

(i) Compute three non-trivial new FDs in the closure F + of F (i.e., FDs

that are not already in F and also are not trivial). Describe briefly how

you computed these new FDs (namely, how you applied Armstrong’s

axioms, step by step, to computed them).

(ii) Provide at least one candidate key for R, and briefly explain how

you obtained it (namely, explain briefly why it is indeed a candidate

key).

Question 4. Let ebay prod vendor=(prod id, price, model num, vendor,

delivery price, storage) be a relation scheme. And let the following be

the FDs that hold on ebay prod vendor:

prod id → price model num

vendor storage → deliveray price

Decompose ebay prod vendor into a (set of) relational schemes via a

lossless-join decomposition, and explain why it is indeed a lossless-join decomposition

(namely, show briefly that the criterion for lossless-join decomposition

described in class holds on your relations).

Question 5. Let R = (A, B, C, D) be a relation schema and let F = {C →

D, C → A, B → C} be a set of FDs. Is R in Boyce-Codd normal form

(BCNF) with respect to F? If it is explain why, otherwise decompose R

into a set of schemas in BCNF.

3

Marking criteria

This coursework is assessed and mandatory and is worth 5% of your total

final grade for this course. Some marks will be given if the approach was

correct even if the final answer is not complete.

4


版权所有:留学生编程辅导网 2020 All Rights Reserved 联系方式:QQ:99515681 微信:codinghelp 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。 站长地图

python代写
微信客服:codinghelp