Assessment and Submission Details

Marks: 40% of the Total Assessment for the Course

Due Date: as per Course outline

Submit your assignment to the link under Assessment->Task 2 on Blackboard. The submission link will be open a week before the due date. Please follow the submission instructions provided.

The assignment will be marked out of a total of 85 marks and forms 40% of the total assessment for the course. ALL assignments will be checked for plagiarism by SafeAssign system provided by Blackboard automatically.

Refer to your Course Outline or the Course Web Site for a copy of the “Student Misconduct, Plagiarism and Collusion” guidelines.

Assignment submission extensions will only be made using the official Faculty of Arts & Business Guidelines.

Requests for an extension to an assignment MUST be made to the course coordinator PRIOR to the date of submission and requests made on the day of submission or after the submission date will only be considered in exceptional circumstances.

Specific Instructions

You must use MySQL to develop the database required in Parts B. MS Access is not appropriate for any section of this assignment and is not to be used.

You must use the ER notation that was taught in ICT701in Part A. Penalties will apply to incorrect notations.

Any assumptions made in creating the ERD in Part A must be explicitly stated in your report.

Submission Format

For Part A you are to include a word document or PDF that contains:

1. Discussion of your approach to the modellng exercise

2. The ER Diagram

3. The Relational Schema (including primary & foreign keys)

4. Supplementary design requirements (e.g. any information on length of identifiers, postcodes, names, what data attributes are compulsory, structure and or format of any columns etc.)

5. All assumptions that explain important design choices you made

For Part B a single plain text file, name <studentNumber>.sql. In this file you are to include all the SQL for your answers for Part B. Separate each of the tasks of Part B with a comment line. Failure to include all statements required to answer a task in Part B will results in the loss of some or all marks for that task.

Submission

The completed assignment is to be submitted to Blackboard by the due date.

The assignment will be assessed according to the marking sheet. Late submission will be penalised according to the policy in the course outline. Please note Saturday and Sunday are included in the count of days late. ICT701 Relational Database Systems Task 2

Part A (40 marks)

Requirements

Based on the 2 tables given in the spreadsheet provided, students are to create a database design specification (entity relationship diagram (ERD) and relational data model (RDM)) from a given business description below. Students are to present this in a report (as specified in the submission format) which also includes a short discussion of the approach for the solution. This assignment is to be carried out and reported individually.

The following defines the minimum information that should be reported: 1.

A brief discussion of your solution, i.e. how you approached the modelling problem and any issues you may have encountered. This includes any assumptions and suggestions for the new design (maximum of ½ page).

2.

The Entity Relationship Diagram (ERD) (produced using a drawing tool such as those found in MS Word or PowerPoint, or a chosen CASE tool) for the system. Your ERD must use the ER notation that was taught in ICT701. Any assumptions made in creating the ERD must be explicitly stated in your report.

3.

The Relational Data Model (RDM) which corresponds to the ERD. That is, the set of tables in 3rd normal form that could be used to represent the whole of this data model. Your RDM should identify primary, alternate and foreign keys.

Business Description

Technical Texts is an online reseller of textbooks to colleges and TAFEs. They deal with a number of publishers and sell a range of books in the Computers, Business, Medical and Sports category. They started off as a relatively small business only selling to a local TAFE. However, they quickly grew and now have a range of customers from all over Australia. When they started the business, they used a spreadsheet to manage the orders. However, as they grew, they realized that the spreadsheet was no longer able to meet their needs. They needed a better way to manage their orders. In the Excel spreadsheet books.xls you will see a sample of their orders table. They also have another table where they started storing the customer details. They hope to get a complete database up and running soon. ICT701 Relational Database Systems

Task 2

Part B (Total 45 Marks)

Complete the following SQL tasks using MySQL. The marks available for each SQL statement are indicated.

1) Write the SQL code that will create the table structure for the CUSTOMER Table. The basic CUSTOMER table structure is summarized in the table below. (1 mark)

ATTRIBUTE (FIELD) NAME

DATA DECLARATION

CUST_ID

CHAR(6)

CUST_LNAME

VARCHAR(15)

CUST_FNAME

VARCHAR(15)

CUST_ADD

VARCHAR(25)

CUST_CITY

VARCHAR(15)

CUST_STATE

CHAR(3)

CUST_PCODE

CHAR(4)

 

2) Having created the table structure in Part B1 (1) above, write the SQL code to enter all the data in the table below into your EMPLOYEE table. (2 marks)

Customer

CustomerID

FirstName

Surname

Address

City

State

Postcode

AA0621

Aaron

Adamson

23 Chardonnay Crt

Wilson

WA

6107

GY0001

Gareth

Yardley

88 Graham Rd

Launceston

TAS

7250

HA8870

Harriet

Annerley

6 First Ave

Belmont

QLD

4153

JB0012

Jordan

Black

12 Blackbird Lne

Vermont

VIC

3133

JB1165

John

Brown

54 Georgette St

Battery Point

TAS

7004

JB1302

Jonathon

Brown

45 Quail Crt

Buderim

QLD

4556

JB6544

Juliet

Bardensley

7 Jamieson Circ

Belmont

NSW

2280

SA0010

Sally

Adams

187 Main Rd

Broadbeach

QLD

4218

3) Assuming the data shown in the CUSTOMER table has been entered; write the SQL code that will list all attributes for a Postcode of 4556. (2marks)

4) Write the SQL code that will save the changes made to the EMPLOYEE table. (1 mark)

5) Write the SQL code to change the postcode to 6501 for the person whose Customer ID is AA0621. After you have completed the task, examine the results, and then reset the postcode to its original value. All SQL code to complete this full task must be shown in your submission. (3 marks)

6) Write the SQL code to create a copy of CUSTOMER, naming the copy CUST_2. Then write the SQL code that will add the attributes CUST_LEVEL and CUST_DISCOUNT to its structure. (4 marks)

The new attribute characteristics are:

CUST_LEVEL CHAR(1)

CUST_DISCOUNT NUMBER(4,2) ICT701 Relational Database Systems Task 2

7) Write the SQL command sequences to change the CUST_LEVEL and CUST_DISCOUNT values as shown in the table below for each of the following customers. (2 marks)

Customer

CustomerID

Cust_Level

Cust_Discount

AA0621

1

0.05

GY0001

1

0.05

JB0012

3

0.15

JB6544

2

0.10

 

8) Write the SQL command sequences that will display first and last names of customers who haven’t ordered any books. (4 marks)

9) Write the SQL command sequences that lists all customers who have ordered books. The statement should return the Customer ID, Customer Name (Surname and First Name combined), Order Number, Order Date and Delivery Date. (4 marks)

10) Write the SQL command sequences that will increase the retail of all books by $20 if they are computer books or they have a cost of more than $40. (4 marks)

11) Write the SQL command sequences that lists the ISBN and Book Title for the most expensive book in the Technical Books database. (5 marks)

12) Write the SQL command sequences that determines the profit for each book (display ISBN, Title and Profit) (4 marks)

13) Write the SQL code required to list all customers whose last names start with Smith. In other words, the rows for both Smith and Smithfield should be included in the listing. Assume case sensitivity. (4 marks)

  1. 14) Write the two SQL command sequences required to: a. Create a temporary table named TEMP_1 whose structure is composed of the CUSTOMER attributes CUST_ID and CUST_LEVEL.
  2. b. Copy the matching CUSTOMER values into the TEMP_1 table.

15) Write the SQL command that will delete the newly created TEMP_1 table from the database. (1 mark)

Keyword - ICT701 Relational Database Systems Assignment, ICT701 Relational Database Systems Assignment help, ICT701

Related posts: