ICT701 Relational Database Systems Assignment

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.

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)

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)

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)

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