Database Development


Database Development

(COMP08002)

Coursework

Group Version

Group work: Group Work (2 -3 students)

Contribution: 60% of final mark

Submission date: Upload by 4pm Tuesday 8th December (Week 10)

Coursework Overview

This document describes what you and your group (size 2-3 students) are required to do to for the Database Development coursework (worth 60%). The coursework is based on an extended version of the Stream2U database.

Some of the information describing the extension to the Stream2U database is given here and it is part of your assessment to add the missing information and build the extended database.

On page 2 of this document you should add the requested details and then follow Tasks 1 to 6 shown (boxed) throughout this document.

Before submitting this document – delete this page so that Page 2 becomes the front page of your coursework.

Database Development (COMP08002)

Coursework for Stream2U Case Study (Extended)

Group Version

Enter details of group members

Student Name(s) BannerID(s)
DHANUSH MANCHIKALAPUDI B00377716
SRI HARSHA AKULA B00377045


Enter details for group database

Database Name Location? (UWS Server/Student Laptop)


STUDENT LAPTOP

Enter details of your lecture and campus

Lecture Name Your Campus? (Lanarkshire or Paisley)


PAISLEY

Summary of your Coursework Marks & Feedback

(Please include this page (just as you see it below) but with student names/initials against each section).

Section Who Responsible for the work? Section Marks
Your Marks Your Grade (E, D, C, B2, B1, A1-3)
Relational Schema Enter Name(s) 20

Feedback (Summary)







Data Dictionary Enter Name(s) 20

Feedback (Summary)









Create Database Enter Name(s) 30

Feedback (Summary)









Insert Records Enter Name(s) 10

Feedback (Summary)









SQL Statements Enter Name(s) 20

Feedback (Summary)









Presentation Enter Name(s)


Feedback (Summary)






Final Total & Grade
100

  1. ER model for Stream2U

Examine the extended version of the ER model for the Stream2U database.

2. Relational Schema for Stream2U Database

Update the relational schema to match the extended ER model for Stream2U. For tables with foreign key(s), add appropriate UPDATE and DELETE rules. Give at least one example that uses: NO ACTION, CASCADE, SET NULL, SET DEFAULT

tblMembershipType (mTypeID, mTypeName, minFilmsStream, mBenefit)

Primary Key mTypeID

tblMember (memberID, firstName, lastName, dob, gender, dateJoined, fullName, mTypeID)

Primary Key memberID

Foreign Key mTypeID references tblMembershipType(mTypeID)

Calculated fullName

tblStream (streamID, memberID, filmID, memberRating, requestView, startView, requestToStart)

Primary Key streamID

Calculated requestToStart

Foreign Key memberID references tblMember(memberID)

Foreign Key filmID references tblFilm(filmID)

tblFilm (filmID, title, genre, classification, rentalCharge)

Primary Key filmID

3. Data Dictionary for Stream2U Database

Update the data dictionary to match the extended Stream2U relational schema. Ensure for each column that you describe the meaning of the column; identify an appropriate data type; specify whether nulls are allowed; identify any special role played (i.e. PK, PPK or FK); any default value; any appropriate constraint or property associated with the column and finally the data classification (i.e. Restricted, Private or Public).[Note – PPK means partial primary key]

The MembershipType Table

Column Name Meaning Data Type Nulls PK / FK Default Value Constraint /Column Property (including data classification)
mTypeID Unique identifier of type
tinyint No PK
(Private)
mTypeName Name of membership type nvarchar(8) No

(Public) Only values ‘Bronze’, ‘Silver’, ‘Gold’, ‘Platinum’
minFilmsStream Minimal number of films streamed to qualify smallint No

(Public)
mBenefit Describes the benefit achieved on streaming more than a minimum number of films nvarchar(100) No

(Public)

The tblMember table

Column Name Meaning Data Type Nulls PK / FK Default Value Constraint /Column Property
memberID Uniquely identifies each member int No PK
(Private) Set identity specification seed as 1 and increment as 1.
firstName First name of member nvarchar (30) No

(Private)
lastname Last name of member nvarchar (30) No

(Private)
dob Date of birth of member date No

(Private)
gender Gender of member nchar(1) Yes
‘F’ (Private) Only hold ‘F’, ‘M’ or ‘U’ (U for unassigned)
dateJoined Date member joined date No
getDate() (Private)
fullName Displays first and last name of member together.



(Private) Use RTRIM (Calculated) and persistent storage.
mType Identifies the member’s current membership type tinyint No FK ‘Bronze’ (Private)

The tblFilm table

Field Name Meaning Data Type Nulls PK / FK Default Value Constraint /Column Property
filmID Uniquely identifies each film int No PK
(Private) Set identity specification seed as 101 and increment as 1.
title Title of film nvarchar (50) No

(Public)
genre Genre of film



(Public) Only hold: Only hold: ‘Action’, ‘Children’, ‘Comedy’, ‘Drama’, ‘Horror’, ‘Romance’ or ‘Sci-Fi’
classification British Board classification of film nvarchar (3) No
12A (Public) Only hold: ‘U’, ‘PG’, ‘12A’, ‘15’ or ‘18’
rentalCharge Nominal charge to stream a film. Used by company to calculate monetary value of films streamed. money No
3.00 (Private) Only hold between 1.00 and 10.00

The tblStream Table

Field Name Meaning Data Type Nulls PK / FK Default Value Add Constraint/Column Property
streamID Uniquely identifies each request to stream a film. int No PK
(Private) Set identity specification seed as 1001 and increment as 1.
memberID Identifies member requesting film. int No FK
(Private)
filmID Identifies film to be streamed. int No FK
(Private)
memberRating Member’s rating of film from 0.5 star (very poor) to 10 stars (brilliant). numeric(3,1) Yes
5 (Public) Only values 0.5 to 10.0 (Constraint).
requestView Date and time that member made request. datetime No
getDate() (Private)
startView Date and time that member started to view film. datetime No

(Private)
requestToStart Time (in seconds) between member’s request to rent (stream) film and member starting to watch film.



(Private) Use DATEDIFF() function and persistent storage.

4. Build the Stream2Udatabase

Update your database to match the description of each table given in your data dictionary. Create a database diagram of your extended Stream2U database and replace the diagrams shown below with your diagrams.

Tip! To copy your diagram to this document – right-click any blank space in your diagram to see ‘Copy to Clipboard’ option and to change the view of your tables – right-click on any table to see Table View options.

The Stream2U Database (Diagram shown using Standard Mode)

The Stream2U Database (shown in Table View – Standard) – Replace diagram with your Extended Version

The Stream2U Database (shown in Table View – Column Names) – Replace diagram with your Extended Version

5. Add Records into the Stream2Udatabase

Ensure that you have between 4 – 10 records in each of your tables except for some tables that will require hold more records such as the tblStream table with at least 30 records.

6. Querying your Stream2U database

Create 12 SQL queries and present the SQL code and output here. Your queries should include the following:

2 CREATE TABLE statements (for two of your new tables)

2 INSERT statements (to insert record(s) into two of your new tables)

6 SELECT queries (by adapting examples in Practical 4) using the newly added part of the Stream2U database.

6 SELECT queries (by adapting examples in Practical 5) using the newly added part of the Stream2U database.

For each of the 12 SELECT queries – State what the query does and explain why it is useful for Stream2U. Your choice of SQL examples should demonstrate your broad understanding of SQL.

Present each of your queries using the format shown on the next page.

Tip! To copy your diagram to this document – right-click any blank space in your diagram to see ‘Copy to Clipboard’ option and to change the view of your tables – right-click on any table to see Table View options.

Action: Selects all columns and all records from the tblFilm table.

Purpose: This query displays all details of Stream2U’s films and would be useful for members browsing through the available list for ideas.

Select * — Selects all columns and all records of the tblFilm table

From tblFilm;

Do not use this query as one of your examples as the SQL is too basic.

7. Coursework Marking Scheme

Section Marks (Out of 100)
Relational Schema 20
Data Dictionary 20
Create Database 30
Insert Records 10
SQL Statements 20

8. Submit Your Coursework

You should save this coursework as a pdf document and upload to Moodle using a link (which will appear) in the Formal Assessment – Coursework (60%). The date/time deadline for submission will be published in this section.

Only one pdf document need be uploaded to Moodle per group. Please ensure that the details of all group members are on the front page.

[Button id=”1″]

Thanks for installing the Bottom of every post plugin by Corey Salzano. Contact me if you need custom WordPress plugins or website design.

Looking for a Similar Assignment? Our ENL Writers can help. Get your first order at 15% off!

Order

Hi there! Click one of our representatives below and we will get back to you as soon as possible.

Chat with us on WhatsApp
%d bloggers like this: