ICT701 | RELATIONAL DATABASE SYSTEMS ASSIGNMENT

HI6006 Competitive Strategy
BSBLDR402 | ASSIGNMENT 1 LEAD EFFECTIVE WORKPLACE
February 21, 2018
HI6006 Competitive Strategy
ITC 542 | ASSESSMENT ITEM 4 NETWORK DESIGN IMPLEMENTATION|
February 22, 2018

ICT701 | RELATIONAL DATABASE SYSTEMS ASSIGNMENT

HI6006 Competitive Strategy

ICT701 | RELATIONAL DATABASE SYSTEMS ASSIGNMENT | DATABASE

IT Assignment Help

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).
  1. 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.
  2. 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.

Part B (Total 45 Marks)

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

  • 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)

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

 

  • 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)
  • Write the SQL code that will save the changes made to the EMPLOYEE table. (1 mark)
  • 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)
  • 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)
  • 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

 

  • Write the SQL command sequences that will display first and last names of customers who haven’t ordered any books. (4 marks)
  • 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)
  • 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)
  • Write the SQL command sequences that lists the ISBN and Book Title for the most expensive book in the Technical Books database. (5 marks)
  • Write the SQL command sequences that determines the profit for each book (display ISBN, Title and Profit) (4 marks)
  • 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)
  • Write the two SQL command sequences required to:
  1. Create a temporary table named TEMP_1 whose structure is composed of the CUSTOMER attributes CUST_ID and CUST_LEVEL.
  2. Copy the matching CUSTOMER values into the TEMP_1 table. (4 marks)
  • Write the SQL command that will delete the newly created TEMP_1 table from the database. (1 mark)

Appendix A

  Items Maximum   Marks
  Marks   Obtained
     
  PART A: DESIGN (40 marks made up of) 40      
  –   ER Diagram (20 marks)        
  Completeness (participation & cardinality constraints &        
  all relevant data represented)        
  Accuracy        
  –   Relational Schema & normalisation (15 marks)        
  –   Assumptions/Additional  Information (5 marks)        
  PART B: SQL STATEMENTS: Marks as per assignment 45      
  documentation for Part B. Marks will be allocated on correctness and        
  completeness of SQL statements.        
  Total = 85      

Punjab Assignment Help

Buy Online Assignment Help services for IT Assignments with Punjab Assignment Help at punjabassignmenthelp@gmail.com

Punjab Assignment help
Punjab Assignment help
Punjab assignment help is Australia's leading assignment help company with the widest range of assignment help services with subject range from Accounting, finance, economics, statistics, human resource, marketing, Programming, Java, IT, database and many more. Connect with us at punjabassignmenthelp@gmail.com

Leave a Reply

Your email address will not be published. Required fields are marked *