SWD602 Database Design and Development

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

SWD602 Database Design and Development

Course aim

This course provides students the skills and knowledge to produce conceptual data models, develop logical  and  physical  database  design  from  given  requirements  and  conceptual  designs.  SQL  and procedural language will be used to solve complex data management problems. Concepts and issues such  as  multi-user  databases,  distributed  database  architectures,  transaction  management,  data storage,  Security,  concurrency  control,  query  optimisation,   back-up  and   recovery,  access   paths, application tuning and data warehousing are covered in depth.

Purpose

The  purpose of this assessment  is to  assess students’ ability to control concurrency  in  multi-user transaction-based environment by using procedural database programming language, optimise the efficiency of data manipulation language and demonstrate understanding of the basic concepts of distributed database system and techniques to build and use a data warehouse.

Assessment Information

1.   This is an individual open-book assessment to be completed in an agreed environment which is worth 50% of your total weighting of the entire course.

2.   This is aresource-based assessment. This means that you may have access to any relevant resources  to  assist  you.  This   could  include,  for   example,  you’re   learning   materials, information on the internet, and soon. However, all work must be your own with no help from any other person.

3.   To achieve a pass in this assessment you will need to score a minimum of 50% for the whole assessment

Submission Instructions

You are required to ensure you have carried out the following before submitting your assessment:

•    Signed assignment coversheet

•    Assessments submitted online should use the following naming convention:

o Course Code_ Assessment Number_ Assessment Name_ Student

Number_Document number o For example, SWD602_A2_Database Design

and Development _1800XXXX_1

•    All answers must be written in your own words.

•     Proofread and spell check all written assessment work carefully.

•     DO  NOT  email  your  document  to  your  tutor,   it   must  be  uploaded  to  the   NZSE  LMS   • Upload a copy of the database file to the NZSE LMS

Plagiarism

•    All cases of plagiarism and/or cheating will be investigated and dealt with according to A08: Misconduct in Assessment Policy.

Learning Outcomes

This assessment is mapped to the following learning outcomes for this course:

LO 4

Use procedural database programming language to control concurrency in a multi-user transaction-based environment.

LO 5

Optimise the efficiency of data manipulation language statements to improve query performance.

LO 6

Discuss the concepts of distributed database system and techniques to build and use a data warehouse.

GPO’s (this assessment is mapped to)

 

1

Analyse requirements, apply appropriate modelling tools, SDLC and HCI principles, to design and  document software solutions for  a  range  of  problems  in  an  organisational  context, including creating accurate and clear technical and user documentation/resources.

 

4

Apply data management and storage technologies to support the software application and the development process to match the application domain.

 

5

Integrate security and privacy principles throughout software development to ensure application security and system integrity using current best practices.

ASSESSMENT

Part A                                                                                                                                                   [Total 40 Marks]

LO 4: Use procedural database programming language to control concurrency in a multi-user transaction-based environment.

Task 1:

a.    Discuss the ACID properties of a database transaction with appropriate example/s   (10 Marks)

b.    Describe the three  most common concurrent transaction execution problems. Explain how concurrency control can be used to avoid those problems.                                          (12 Marks)

Task 2:

Your colleague wrote an application that interacts with the backend database management system. The application enables customers to register for taxi booking. Looking through the code, you notice that the application performs the following sequence of operations.

1.    Prompt the user for a customer ID and password.

2.   Start a new transaction.

3.    Lookup the customer in the database.

4.    If the customer ID is not in the database or the password is incorrect, abort the transaction. 5.    Lookup the cars recommended for the customer.

6.    Display the carson the

screen. While the user does

not choose QUIT Prompt the

user to select a car.

7.    If the car is available, then book it for the customer.

8.    End while

9.    Commit the transaction.

Identify the problem in the above scenario, explain why this is a problem, and explain how to fix the problem.  You   need  to   write  the   required   solution   that   allows   concurrency   in   a   multi-user transactionbased environment.                (18 Marks)

Part B                                                                                                                                                    [Total 40 Marks]

LO 5: Optimise the efficiency of data manipulation language statements to improve query performance.

LO 6: Discuss the concepts of distributed database system and techniques to build and use a data warehouse.

Task 1:

Consider the following tables from Harikoa Company Database

Employee ( EmpId, EmpFName, EmpLName, EmpAge, EmpSalary, EmpPosition, EmpPhone, EmpEmail, EmpCity, DepId) Department ( DepId, DepName, ProId)

Project (ProId, ProName, ProLocation, ProDuration, ProCategory)

SQL Query:

Select EpmName from Employee A, Department B, Project C

Whre A.DepId = B.DepId

AND B.ProId = C.ProId

AND C.ProName = “NZSEG”

AND A.EmpSalary >60000

This query needs to display the names of all employees working for a project named “NZSEG” and having a salary greater than 60000 dollars.

1.   Analyse and fix the given query for any lexical, syntactical, and semantic errors.              (2 Marks)

2.    Create the initial query tree for the given SQL query.                                                               (2 Marks)

3.    Use heuristic query optimization to create an improved query tree and briefly explain each query tree that you have created:

a.    Apply selection operations.              (4 Marks)

b.   Apply more restrictive selection operations.           (4 Marks)

c.    Replace cartesian product and selection operations with JOIN operations.       (4 Marks)

d.   Apply and move projection operations down the query tree.       (4 Marks)

Task 2:

Your task is to find what indexes should be created to speed up the processing of SELECT statements listed below.                                                                                                                                       (8 Marks)

1.   Select EmpId from Employee where EmpAge>50 AND EmpSalary>=48000.

2.   Select EmpId from Employee where EmpAge>50 AND EmpSalary>=58000.

3.   Select EmpId from Employee where EmpAge>50 AND EmpSalary>=68000

Choose a single simple index on one attribute that is most likely to speed up all three queries. Write down the CREATE Index statement and explain why you have chosen that index. Use the Task 1 Schema to create the Database, populate with your same data and execute your SQL query with index to show the query execution improvement.

Task 3:

Consider the following relations EMPLOYEE and DEPARTMENT

Calculate  the  minimum  amount  of  data  transferred  to  execute  a  query  that  finds  the  name  of employees and their Department Name when the query is submitted at Site 3. The two relations i.e., Employee and  Department  are  not  residing  at  Site  3,  hence  you  need  to  come  up  with  multiple strategies and choose the minimum amount of data transfer to execute the query.             (12 Marks)

Part C                                                                                                              [Total 20 Marks]

LO 6: Discuss the concepts of distributed database system and techniques to build and use a data warehouse.

Task 1:

a) Define and explain the concept of Distributed Database System with the help of a diagram and identify an example in the organization where the concept is applicable or can be applied.                 (10 Marks)

b) Compare and contrast data warehouse and data mining                                                            (10 Marks)


2023-11-23