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 |
- 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.