辅导DTA(M)留学生、辅导SQL语言、讲解Database Theory、辅导SQL设计

日期：2018-11-29 11:00

Assessed Coursework

Course Name Database Theory & Applications DTA(M)

Coursework Number 1/1

Deadline Time: 16h30 Date: 03/12/2018

% Contribution to final

course mark

20%

Solo or Group Solo Group

Anticipated Hours Average 20 hours

Submission Instructions

Submission of only one PDF document; please read

in the description.

Please Note: This Coursework cannot be Re-Assessed

Code of Assessment Rules for Coursework Submission

Deadlines for the submission of coursework which is to be formally assessed will be published in course

documentation, and work which is submitted later than the deadline will be subject to penalty as set

out below.

The primary grade and secondary band awarded for coursework which is submitted after the published

deadline will be calculated as follows:

(i) in respect of work submitted not more than five working days after the deadline

a. the work will be assessed in the usual way;

b. the primary grade and secondary band so determined will then be reduced by two

secondary bands for each working day (or part of a working day) the work was

submitted late.

(ii) work submitted more than five working days after the deadline will be awarded Grade H.

Penalties for late submission of coursework will not be imposed if good cause is established for the late

submission. You should submit documents supporting good cause via MyCampus.

Penalty for non-adherence to Submission Instructions is:

2 bands

You must complete an “Own Work” form via

https://studentltc.dcs.gla.ac.uk/ for all coursework

Practical Exercise & Technical Report

Description

You are about to model, create, and program the ‘Band’ database that contains information about

bands, their members and the song releases over the years. A music band has a name, a band webpage

and a series of releases. Each band release includes the title of the release, the year of the release, a

release rating (which is a number from 1(bad) to 10(excellent)) and a release type (which is one of the

following types: “album”, “live”, “single”). The band produces many songs. Each song has a title and is

associated with a very specific band release. Moreover, a song either receives an award or not from the

Musical Academy. In addition, a musician is identified by their name and a personal webpage. A

musician for a specific time period is a member of a specific band only. This time period is identified by

the start-year and the end-year of membership. During this time period of membership, the musician

has a specific role in the participating band, which is one of the following roles: “vocals”, “guitar”,

“bass”, “keyboards”, “drums”. We are making the following assumptions for the start-year and end-year

of membership: The end-year attribute is NULL if the corresponding musician is still a member of the

band. The start-year is NULL if the corresponding member has been in the band since the band’s

creation.

Report 1: Entity-Relationship Conceptual Modelling

Identify entities, their attributes and the relationships from the database description along with their

cardinality ratios/degrees. Then, draw an E-R Diagram representing your conceptual modelling.

Deliverable 1: Document with the E-R Diagram accompanied with a brief description (1 or 2

paragraphs) of your conceptual model approach.

Report 2: Relational Schema Modelling

Map your E-R components to a Relational Schema by defining the relations, attributes, and

relationships. Define the Primary and Foreign Keys of your relational schema explaining your decisions.

Identify the Functional Dependencies and normalize the relational model to BCNF.

Deliverable 2: Document with the Relational Schema at BCNF and explanation of your relational

modelling approach including the required steps you followed to normalize your schema at BCNF.

Report 3: SQL Statements

Report 3.1: SQL CREATE

Provide the SQL CREATE TABLE statements of your schema and enhance the CREATE statements with

Referential Integrity Constraints in terms of UPDATE and DELETE CASCADE options. Explain the

constraints you declared.

Deliverable 3.1: Document with the SQL CREATE statements and explanation of your proposed

constraints.

Report 3.2: SQL SELECT

SQL 1: Show the names of all current band members of the band ‘The CURE’.

SQL 2: Find out if there exists a band with the same name as any member’s name of any band.

SQL 3: Show the number of members in the database who have played “bass”.

? SQL 4: Show the discography of each drummer (a musician with role ‘drums’) sorted by their

name and year of release.

Deliverable 3.2: Document with the SQL SELECT queries.

Report 4: Relational Algebra & Optimization

Report 4.1: Relational Algebra Tree

Provide the relational algebra trees for your provided SELECT statements: SQL1, SQL2, SQL3, and SQL4

from Report 3.2.

Deliverable 4.1: Document with the relational algebra trees of SQL 1, SQL2, SQL3, SQL4.

Note: use any software tool for visualizing your trees (e.g., use MS PowerPoint, Paint, …) but definitely

not hand-drawn sketches as they won’t be accepted for marking.

Report 4.2: Heuristic Optimization

Apply the Optimization Inferential Rules over your relational algebra trees of the SELECT statements:

SQL1, SQL2, SQL3, and SQL4 from Report 4.1 and provide their corresponding optimal relational algebra

trees. For each relation algebra tree, show the inferential rules you are applying.

Deliverable 4.2: Document with the optimal relational algebra trees of SQL 1, SQL2, SQL3, SQL4 and the

inferential rules applied per tree.

Report 5: JBDC Program

Create a simple Java program with name DTA that:

Loads the JDBC PostgreSQL driver and connects to the ‘Band’ database via the Connection object;

Consider the query that shows the band which has the maximum number of current members

among all bands in the database. In the Java program, write the code to execute this query and the

code that prints out the corresponding results on the display (standard output).

Deliverable 5: The source file, i.e., the DTA.java only, with comments in the code.

Note: For testing your SQL queries in Report 3.2 and your program in Report 5, you can populate your

‘Band’ database with imaginary tuples to your designed tables in Report 2; this is entirely optional, and

you are not required to submit any tuples or a populated database.

Submission

Submit a front page indicating your details and the seven deliverable documents in one PDF document.