CMP-4010B Database Systems

Module: CMP-4010B Database Systems

Coursework: SQL Programming

Date set: 16th July 2024

Value: 50%

Date due: 7th August 2024, 3 PM

Returned by: 29th August 2024

Submission: Blackboard Learning Outcomes

Experience in the following:

- problem solving techniques using SQL, PostgreSQL; (SQL, Transferable skills)

- interpreting user requirement and defining solutions; (SQL, DBMS understanding)

- creating table definitions using SQL; (SQL, Relational Model)

- creating ER diagrams (SQL, DBMS understanding)

- manipulating table data using SQL; (SQL)

- SQL programming in PostgreSQL; (SQL)

- SQL transactions in PostgreSQL; (SQL, Transactions)

- managing time based on workload, deadlines, and distribution of effort. (Transferable skills)

Specification Overview

Aim:

To implement part of a database application by first completing the database table definitions, then writing interactive SQL statements.

Coursework Description

See Attached at Page 4.

Relationship to Formative Assessment

All SQL Labs build towards this exercise, with each lab helping you to perform

Transactions of Interest that contribute to building this solution. Labs include

expected query results so you can ensure you get correct answers and perform a form of self-assessment.

Summary of Deliverables

Part 1: A copy of your SQL data definition statements and test data should be produced and submitted electronically.

Part 2: Your SQL statements for each of the Transactions of Interest should be produced and submitted electronically together with evidence of testing for each statement on your own data and assessment data. There will be two documents – one with your own data and one with the assessment data using the assessment   template.

The Assessment Template file issued nearer the submission time (48 hours prior to submission deadline).

Resources

The necessary materials are in the lecture notes/lab materials. Links to relevant

PosgreSQL help pages are in the lecture slides. Recommended books in reading list are also useful.

Marking scheme

Assessment criteria

• Good use of SQL data definition language to complete the table definitions;

• Good use of SQL data manipulation language to write interactive queries;

•   Ability to interpret project specification correctly and accurately. This may include a little bit of independent thinking on what may make a table/report look good;

• Correct functionality and output as required by each requirement;

•    Neatly presented work with correct program output in the assessment template; Sufficiency and completeness of Submitted code and tests data.

Plagiarism, Collusion, and Contract Cheating

The University takes academic integrity very seriously. You must not commit plagiarism, collusion, or contract cheating in your submitted work. Our Policy on Plagiarism, Collusion, and Contract Cheating explains:

• what is meant by the terms ‘plagiarism’, ‘collusion’, and ‘contract cheating’

•    how to avoid plagiarism, collusion, and contract cheating

•    using a proof reader

•   what will happen if we suspect that you have breached the policy.

It is essential that you read this policy and you undertake (or refresh your memory of) our school’s training on this. You can find the policy and related guidance here:

https://my.uea.ac.uk/departments/learning-and-teaching/students/academic- cycle/regulations-and-discipline/plagiarism-awareness

The policy allows us to make some rules specific to this assessment. Note that:

In this assessment, working with others is not permitted. All aspects of your submission, including but not limited to: research, design, development and writing, must be your own work according to your own understanding of topics. Please pay  careful attention to the definitions of contract cheating, plagiarism and collusion in the policy and ask your module organiser if you are unsure about anything.

CMP-4010B Database Systems SQL Programming Coursework

Introduction

The Computing and Mathematics Professionals Society (CMPS) is a professional body that promotes the interests of computer scientists and mathematicians.  There   are two types of membership: student member and accredited member.  To advance from student member to accredited member it is necessary to be a graduate of a recognised university, and to pass several examinations organised by the society.

CMPS organises examinations once each year.  The examinations are held at a variety of locations throughout the country.  Student members enter examinations by contacting CMPS's office directly or through their website.  After the examinations have taken place, CMPS examiners award grades for the students' examination entries and these grades are posted on CMPS's website. If a student withdraws from the society, all his/her examination entries are cancelled.  Details of examinations, student members and examination entries are held in a database.  This information is accessed over the Internet and by using workstations available to CMPS office staff. The CMPS IT team refer to these various inserts, updates, deletes and queries as transactions of interest.

For this coursework exercise you are required to design a database for the CMPS examinations and write SQL statements for the transactions of interest. Your role is to prototype and test the functionality (transactions of interest) required for the system. Naturally the exercise is greatly simplified compared to a real application. A description of the tables and required functionality has been provided. A detailed specification of the work to be undertaken and the deliverables to be produced for assessment is given below.

The first stage in this process is to analyse the requirements and write SQL statements to perform these tasks. These statements can be tested using an interactive SQL interface to ensure correct functionality.

You may, of course, use your own facilities to develop your program but the final version must use PostgreSQL and run in the CMP labs.

System Functionality

The database consists of the following tables, each containing specific fields as listed below:

exam (excode, extitle, exlocation, exdate, extime) student (sno, sname, semail)

entry (eno, excode, sno, egrade)

cancel (eno, excode, sno, cdate, cuser) Notes:

The exam table holds details of each examination scheduled for the coming year.

H. Produce a table showing the result obtained by each student for each examination.  The table should be sorted by examination code and then by student name.  If the student is awarded a grade of 70% or more then the result is to be shown as 'Distinction', a grade of at least 50% but less than 70% is to be shown as  'Pass' and grades below 50% are to be shown as 'Fail'.  If the student has not taken the examination then the result is shown as 'Not taken'. The table should display the exam code, exam title, student name and exam result (e.g., 'Distinction', ‘Pass’, ‘Fail’, ‘Not taken’).

I. As H above but for a given examination.  The examination is specified by examination code.

To be able to provide support to the above CMPS IT intended transactions, you will have to use Data Definition Language to create the tables and add integrity constraints to have a consistent and reliable database for CMPS IT. In Part 1, your task is to design and develop the data definition language incorporating integrity  constraints.

Tasks:

Part 1: Database definition and data loading (approx. 25% of marks)

For consistency, use the following minimal database definition: A copy of this text can be found in the file Cw_Schema.txt in Blackboard under the Coursework folder.

Minimal database definition

CREATE TABLE exam (

cdate TIMESTAMP,

c user VARCHAR(200));

Add additional SQL clauses and/or statements to complete the definition of the database by specifying DDL statements including data integrity and constraints (e.g., primary keys, domain constraints, entity and referential integrity constraints), views, functions, triggers, comments. Note that you should NOT modify the name and type of the attributes in the minimal database definition. Save all your Data Definition Language (DDL) statements in a text file. Analyse the structure of the RoyalReads database and present an Entity-Relationship (ER) diagram of the   database.

At this stage the tables are empty. Load (e.g., using insert statements) a reasonable volume of data into the tables for testing the Transactions of Interest. The data should be reasonable to test the Transactions of Interest with their expected output and should provide a suitable environment in which to test normal operation as well as abnormal conditions (e.g., if you constraint an attribute by defining a primary key on the attribute, adding multiple rows with same value for that attribute would consider as an abnormal condition).

Part 2. Prototyping using Interactive SQL version of the transactions (approx. 70% of marks)

Prepare and test interactive SQL statements for the Transactions of Interest. Test these statements using the SQL Query Tool editor in pgAdmin.  The purpose is to test your SQL statements before real world deployment in the production (i.e.,

Prototyping Phase). You may need more than one execution of some of the Transactions of Interest to verify the correctness of your work (e.g., test primary keys, referential integrity, correct and incorrect execution). Please be prepared to   add the SQL statements for an assessment template which will be released to you 48 hours prior to the submission deadline with the assessment test data. The template will look like the exemplar template in the Appendix (see section Template for Submission with a Mock Question and Answer). The assessment test data will consist of insert statements for the seven database tables.

Quality of Submission and Showcase Your Technical Skills (approx. 5% of marks)

Clarity and professionalism of the template presentation.

Deliverables

•    Electronic submission to Blackboard (All files and subfolders to be collected in a folder named with your student number, zipped and submitted following instructions in the Blackboard):

o Part 1: A text file containing SQL data definition statements, together with your own data, and the ER diagram.

o Part 2: Your SQL statements for each of the Transactions of Interest should be produced and submitted electronically together with evidence of testing for each statement on your own data and assessment data. There will be two documents – one with your own data and one with the assessment data using the Assessment Template. The Assessment Template along with the assessment data will be issued 48 hours prior to submission deadline.

A sample submission folder is available on the Blackboard.

Note: If the submission folder does not include the mandatory, completed Assessment Template document, a score of zero will be assigned for the coursework. Your marks will be determined by the accuracy of the SQL statements in accordance with the assessment data and reports provided within the Assessment Templates.

Important notes

•   The document you submit should be complete and neatly formatted to ease reading.

•   This is an individual piece of coursework NOT a group project. Collusion/plagiarism checks may be carried out.

•   As you will be given the assessment data based on the tables in the minimal database definition, it is vital that you do not change the table names, field names, field types.