Writing component

CIS2360 – Relational Database and Web Integration

 

Assignment 1 (50% of the overall assessment for this module)

 

This document tells you:

  • What you need to hand in.
  • The criteria that will be used to assess your work.
  • When the assignment will be handed out and when it will be submitted for marking

 

You are reminded that it is your responsibility to read and understand the University regulations regarding assessment. Your attention is directed to the regulations regarding assessment in the Student’s Handbook of Regulations (available from the University website), and in particular to its guidance on academic integrity and plagiarism.

http://www.hud.ac.uk/library/finding-info/apa-referencing/

https://www.hud.ac.uk/registry/academicmisconduct/

Aims

To develop a relational database.

Outcomes to be Assessed

This coursework addresses ability outcomes 1.1 to 1.3 and 2.1 to 2.3 as described in the module specification.

Hand In

  • A cover sheet, obtainable from http://ecover.hud.ac.uk/assignments/, which is to be prepended to your assignment. The assignment should be submitted via the designated turnitin link on UniLearn in Word document or PDF format.

Hand Out Date

Monday 28 September 2015

Hand In Date

Part 1 – Friday 6 November 2015 (Logical Design)

Part 2 – Friday 4 December 2015 (Structured Queries)

Part 3 – Friday 18 December 2015 (Written Report)

All parts of the assignment should be submitted no later than 23:59 on the day of submission.

 

Note: A pre-designed database and test data will be provided for you to write and run the queries. The database will be available for you to build on Monday 16 November.

Resources Required

Access to the MySql database server.

A modelling environment such as MS Visio.

Assessment Criteria

 

Part 1 – Database Design (20%)

From the problem statement below you should create a Logical design for the proposed database.

 

Films are rented out in shops and there are several shops. Each shop has a unique distributor that supplies the shop with DVDs. A distributor may supply more than one shop. Each distributor has a name, an address, and a phone number. Each shop has a name, an address, and a phone number. For each employee we must keep the following information: the shop where the employee works, a name, a supervisor, an address, a phone number, NIN (National Insurance Number) and the DATE when the employee was hired. For each customer we have to keep the following information: a name, an address, and a phone number (if any). For each rental, we must keep track of which employee served the customer, which film and which copy (identified by a unique DVD ID) the customer rented, information about payments, the DATE and the time of the rental, the status (rented, returned in time, returned late), the rate (i.e. the price), and if applicable, due DATE and overdue charges. About the payment we have to keep which of the employees accepted the payment (does NOT have to be the same employee who rented the DVD), the type of payment (i.e. cash, cheque, credit card, direct debit – for each type you must provide for relevant information to be kept, e.g. credit card number if credit card is used), the amount of the payment, DATE + time of the payment, payment status (completed if cash or the money have been received, approved if debit or credit card go through, pending if the cheque has NOT cleared yet). About each DVD we have to keep information in what condition the DVD is and what film is on the DVD. About each film we have to keep its title, director’s name, simple description, the name of a (single) headline actor/actress, the film’s rating (e.g. U, PG, 12A etc.).

 

Note: You should design the database to the problem statement above. You have the opportunity to improve on the design in Part 3 of the assignment.

 

 

 

Part 2 – Implementation of the following queries (40%)

 

1.Find all the customers who live in Burnley. Display customer id and customer name.(1)
2.Display the total value of payments (for rentals only) that have been received by each employee, and sort by employee national insurance number (empnin).(2)
3.Display the total number of films that are rented out by each store, sort by storeid.(2)
4.The following queries are all related and can be built up from a – d. 
a.Display individual dvds that are rented out from every shop, show the filmid and dvdid, sort by filmid & dvdid.

This query will return 294 rows so set the LIMIT to 300

(1)
b.Display individual dvds that are rented out from every shop, show the filmid, dvdid, shop name and the film name, sort by filmid & dvdid.

This query will return 294 rows so set the LIMIT to 300

(2)
c.Display individual dvds that are rented out from every shop, show the filmid, dvdid, shop name, the film name and the number of times the film has been rented out from the individual shop, sort by filmid & dvdid.

This query will return 294 rows so set the LIMIT to 300

(3)
d.Display individual films that are rented out from any of the 12 shops, show the shop name, the film name and the number of times the film has been rented out from the individual shop, sort by filmid.(4)
5.Display all customers who did not rent any movie so far and sort by custid.(3)
6.Display the total amount received by different payment type, and sort by ptdescription.(3)
7.Display the number of movies rented out based on the movie rating, and sort by rating.(3)
8.Display the top 5 customers based on their total payment, and sort their total payment in decreasing order.(3)
9.List all the manager’s names and the names of the employee they manage. Sort by manager nin & employee nin.(3)
10.List all the movies rented by an individual customer. Display the columns: filmtitle, rsdescription (rental status), rentalrate, payment_empnin (the employee that loaned the film to the customer), filmrental_empnin (the employee to whom the film was returned), ptdescription (payment type) and pdescription (payment status)(10)

 

Written Component – Approximately 1750 words (40%)

  1. The company keeps information about films. The model answer to part one of the assignment has been designed as an inefficient solution in terms of film information. As such, you should discuss a better solution to the model answer provided for part one. (5%)

 

  1. Conceptual Design for the improved solution discussed above (5%)

 

  1. Discuss and model the information you could keep about films. For inspiration, look at imdb.com (5%)

 

  1. Discuss and evaluate alternative DBMS (10%)

 

  1. Discuss alternative data storage and management solutions that a data administrator may recommend as a solution to data provision and management. (10%)

 

  1. Discuss and analyze transaction processing across a distributed database, paying particular attention to transaction efficiency, concurrent access and data recovery. (5%)

 

 

Note: There is a +/- 10% tolerance on the word count.

 

Grade Indicators

 ABCDRefer/Fail
Logical

Model

 

Clear model containing attributes keys and cardinality.Clear model. Attributes keys and cardinality are mostly correct.Mainly correct tables but with a few errors in attributes keys and cardinalityMainly correct tables but with several errors in attributes keys and cardinalityDisjointed tables with many errors in attributes keys and cardinality
QueriesAll or majority of queries statements accurate and efficient with evidence of correct resultsMajority of queries statements are accurate with evidence of correct resultsMany queries statements are accurate with evidence of some correct resultsMost   queries statements accurate but lacks evidence of correct resultsFew queries statements accurate and no evidence of correct results
Written ComponentAn excellent, clear, well-written, well-structured discussion. Relevant issues are fully discussed and evaluated. Demonstrates a strong ability to think criticallyA very good, well-written Discussion. Relevant issues are fully discussed and evaluated. Demonstrates a good ability to think critically.A reasonable discussion which shows a good understanding of the issues involved. Demonstrates some ability to think critically.Shows evidence of some understanding of the issues involved.Largely descriptive, with little in-depth discussion and demonstrating little understanding of the issues.


Notes:

  1. The tools used for creating your diagrams are at your discretion but the diagram must show the required detail.

 

  1. All queries must be printed to file and show evidence of results. Screenshots of implemented queries and results from phpmyadmin can be used as evidence.

 

  1. Feedback and results will be provided through the attached marking scheme. Marks for each part of the assignment will be available three weeks after submission. Students with ECs or extensions longer than three weeks will have to submit an alternative assignment.

 

  1. Marks will be available through the Gradebook facility on UniLearn.

Dr David Wilson

October 2015

p(6)

Place your order now to enjoy great discounts on this or a similar topic.

People choose us because we provide:

Essays written from scratch, 100% original,

Delivery within deadlines,

Competitive prices and excellent quality,

24/7 customer support,

Priority on their privacy,

Unlimited free revisions upon request, and

Plagiarism free work,

 

Unlike most other websites we deliver what we promise;

  • Our Support Staff are online 24/7
  • Our Writers are available 24/7
  • Most Urgent order is delivered with 6 Hrs
  • 100% Original Assignment Plagiarism report can be sent to you upon request.

GET 15 % DISCOUNT TODAY use the discount code PAPER15 at the order form.

Type of paper
Academic level
Subject area
Number of pages
Paper urgency
Cost per page:
Currency:
Total: