Learning outcomes assessed
This assignment covers basic SQL queries and definition, as well as functional dependencies (FDs, for short), normalisation of relational data base sand (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.
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. Cus-
tomers 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 referential-
integrity constraints that should hold (such as foreign keys), taking care for attributes that should not be null.
BEN02 Planning and Presenting a Micro-Enterprise Idea BTEC Level 1/2
Read MoreBTEC Unit 35: Engineering Services Delivery Plan for Sector-Specific Organizations | HND Level 5 Assignment 2
Read MoreTQUK Level 3 Administering Medication and Monitoring Effects in Adult Care Assignment
Read MoreUnit 10: 3D Modelling and Assembly Drawing for Vice – Engineering Design Portfolio BTEC Level 3
Read MoreWhy is it important that you correlate the appropriate information of the patient when they arrive for their appointment?
Read MoreNCFE Level 3 Roles and Responsibilities in Health And Social Care
Read MoreMP3395 Turbocharger Performance Evaluation and System Analysis CW2 Assessment, AY2024-25
Read MoreKey Research Policies and Funding Models at University of Strathclyde
Read MoreCIPD Level 5 Associate Diploma Key Assessment Questions
Read MoreLaw Assignment Questions Critical Legal Analysis & Solutions
Read More