ICT7003 Database Management Assignment Help

ICT7003 Database Management Assignment Help

ICT7003 Database Management Assignment Help

This assessment explains about the information technology systems that are used on a daily basis by many organizations. In order to use the database management system, you have to collect the necessary requirements for the database by translating these requirements into the database design, create the database from the design, loading data into the database and performing manipulations of the database.

Assessment 2

This assignment has 15% value

This assignment carries 100 marks for all the given tasks

Aims

This assignment aims:

1. To comprehend and analyze a given diagram of ER and Relational Database schema based on the same concept.

2. To write different SQL statements that helps in data manipulation in database.

3. To write required SQL database in order to query the database.

Note – This is a individual assignment. You have to submit the file as 'a2-<Surname>.zip'.

 

Learning Objectives

During the process of assessment task, you have to:

1. Gain awareness based on the typical challenges that are highly related to the practical implementation of given databases.

2. Plan, schedule and implement different tasks of the project in order to improve your personal productivity.

3. Learn the methods of using Data Manipulation statements to find insert and update the queries of the database in the tables.

4. Learn the methods of using Data Manipulation statements in order to implement a database from a given ER diagram and the corresponding database schema.

 

Assignment instructions

Students have to create their own database according to this document. You need to ensure that the implementation must be consistent with the designs like your field names, table names, data types and writing different queries to create reports. You have to insert at least five records in each table and ensure that each query returns at least one record.

 

Follow the following formatting output:

1. Any query requiring names of people should be printed as GivenName, FamilyName (e.g. John Smith) in a column labeled NAME

2. Any query requiring addresses should be printed as Street, Suburb State Postcode (e.g. 123 Anzac Pde, Maroubra NSW 2038) in a column labelled ADDRESS

3. All monetary values should be printed with a dollar symbol ($), two digits after the decimal point.

 

Assessment Criteria

1. On the front page of your report, you should include a list of acknowledgements of all people who have assisted you with this assignment including fellow students.

2. It should be in accordance to our standards. You should write all the queries in consistent style and use indent format.

3. Please use appropriate data for your examples.

4. Please refer to the provided marking guide (below) to see the distribution of marks.

 

Implementation of the Database and Manipulation of the Data

 

Note – Students have to design a database according to marks given for under mentioned tasks

S.No.

Presentation

Total Marks

1

Title page and Acknowledgement

1 Mark

2

Table of contents and Automatically generated page numbers

2 Marks

3

Bibliography and Justification of not having one

2 Marks

4

Overall clarity and organization of presentation

3 Marks

 

Under this assignment, students have to perform following tasks:

1. Task 1: Create tables (20 Marks) Students have to create a text file as Create_<StudentID>.sql that will contain SQL statements to:

a. Create a database by giving file name as BigM_<StudentID>

b. Create the database tables according to the database schema given in the document.

1

For correctly creating following four tables (0.5 Mark each)

a. Brand

b. Job Type

c. Department

d. Address Type

2 Marks

2

For correctly creating following four tables (1.5 Marks each)

a. Address

b. Customer

c. Product

d. Store

6 Marks

3

For correctly creating the following six tables (2 Marks each)

a. Customer Order

b. Orderline

c. Store Department

d. Payslip

e. Inventory

f. Employee

12 Marks

Database system 2. Task 2: Insertion (30 Marks)In this task, you have to create a text file and give it name as Insert_<StudentID>.sql that will contain SQL statements to:

a. You have to insert at least five records in each of the table.

b. The test data inserted in the table should ensure that each query in Task 3, outputs at least one record.

 

1

For correctly inserting minimum 5 sample records in the following four tables (1 Mark each)

a. Brand

b. Job Type

c. Department

d. Address Type

4 Marks

2

For correctly inserting minimum 5 sample records in the following four tables (2 Marks each)

a. Address

b. Customer

c. Product

d. Store

8 Marks

3

For correctly inserting minimum 5 sample records in the following six tables (3 Marks each)

a. Customer Order

b. Orderline

c. Store Department

d. Payslip

e. Inventory

f. Employee

18 Marks

3. Task 3: Query (32 Marks)You have to create a text file as Query_<StudentId>.sql that will contain all the queries to display the following:

a. List of names and postal address of all employees sorted by their salary.

b. The date on which the most recent customer order has been made. The name of the customer and the date of the order will be sufficient.

c. List of all the store names and their addresses, sorted in dictionary order of the store name.

d. A list of all customers that have not placed an order yet. Displaying customer number and name will be sufficient.

e. A list containing the name of employees, which work as accountant.

f. A list containing the total quantity on hand for each product (product number and description) regardless of stores.

g. A list showing each product sold (picked) on specific date. Show product number, name and quantity sold, sorted by product number and then quantity sold.

h. A list of products (show product number, description and price) whose price is less than or equal to the average product price.

i. Increase each employee’s salary by 7.5% and show the updated salary of all employees (name and salary).

j. Show the pay information (employee name, hours paid, amount paid) of all employees in the most recent pay date.

k. Make a list of all products of brand “Armani” and their price in ascending order of price. Show the product description and price.

l. A list of supervisors (employee id, name) and all of their subordinates (employee id, name)

1

For each of the first eight queries (3 Marks each)

a. If the query is correct and the output produces at least one record (Award 3 Marks or deduct 1 Mark, if not)

24 Marks

2

For each of the last two queries (4 Marks each)

a. If the query is correct and the output produces at least one record (Award 4 Marks or deduct 1 Mark, if not)

8 Marks

Data Management4. Task 4: Transaction (10 Marks) – You have to create a text file as Update_<StudentId>.sql that will perform the following:

a. You have to insert additional data appropriately in the tables if needed.

Background

A customer named Daniel Ortega, from 11 Fuller Road, Marsden QLD 4132 and having a mobile number 0431xxx668, orders two shirts of size“XL” of brand “Prada” on 6 Sep 2018. The full order is ready on 8 Sep2018 for pickup, but the customer picks up on 10 Sep 2018.You should insert data into appropriate tables for the above order and make sure you update the data in the relevant tables after the order is picked up.

Note – The above mentioned case is just for the reference purpose and quoted as an example.

1

For correct insertion of data into the following tables. (0.5 Marks each)

a. Address

b. Customer

c. Product

d. Brand

e. Inventory

2.5 Marks

2

For correct insertion of data into the following tables. (2 Marks each)

a. Customer Order

b. Order line

4 Marks

3

For correctly updating the table (1.5 Marks or deduct 1 Mark, if done only once)

a. Order line

1.5 Marks

4

For correctly updating the table (2 Marks or deduct 1 Mark, if done only once)

a. Inventory

2 Marks

 

S. No.

Item

Marks

1

1. Presentation

How clear and well?presented your submission is

8 Marks

2

2. Creation of database and tables (Task 1)

Create the relevant tables: Database correctly named and created,

includes all of the PKs and FKs in the database

20 Marks

3

Insertion (Task 2)

Successfully inserts data into the tables. NO evidence exporting from GUI

30 Marks

4

Query (Task 3)

Use of appropriate query statements. They work as intended. Each query should output at least one row of valid data

32 Marks

5

Update (Task 4)

Use of appropriate data manipulation statements to perform the required actions. They work as intended.

10 Marks

6

Total

100 Marks

Challenges students might face

Students may face several problems or challenges in the completion of ICT7003 ? Database Management Assignment Help such as concept of database management, collecting important requirements for the database, etc. Experts and professionals at OZ Assignments provide assistance and guidance to the students in order to complete the assignments based on different topics at different academic level. Students can increase their grades by exploring and studying the topic deeply. With OZ Assignments, get necessary academic guidance and tutorial help in ICT7003 ? Database Management Assignment Help. Get feedback amendment without any additional cost.

Note: Price given for this assessment is valid only for this part. It will not work for other parts.