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 the assessment is to assess students’ ability to design and implement a database to solve a business problem ensuring the security controls and features are implemented.

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 a resource-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_A1_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 1

Apply conceptual design methodologies and normalise database to solve a business problem

LO 2

Implement physical database structure using SQL to meet the performance needs of a business.

LO 3

Implement integrity constraints and security policy to ensure accuracy and control multi-user access.

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 1           [40 Marks]

LO 1: Apply conceptual design methodologies and normalise database to solve a business problem

LO 3: Implement integrity constraints and security policy to ensure accuracy and control multi-user access.

Scenario:

Imagine that you have been assigned to a team that will be developing new database systems. As part of the project, you are to design a database maintained by AAA Insurance company. The data should  include  information  about customers (such  as their name, address, and phone number, etc.,), information about insured cars (such as their Vehicle Make, Vehicle Model, Manufactured Year, Vehicle ID, License ID, and Insurance Rate , etc.,), information about insured homes (such as their Address, Insured Value, and Insurance Rate, etc., ), and information about claims made on insured cars and homes (such as Date of Claim, Date of Settlement, and Amount of Settlement, etc.,). You may assume that all insured cars and homes are owned by a single customer, but you should allow a customer to own several cars and homes.  Please  state  any additional entities  (like for example employees),  attributes, and assumptions you would   like to include in your design. You can also gather further

requirements from your Tutor.

Task 1- 1: Introduction

In  your   documentation  write  about  the   organisation’s  background,  activities  involved, problems, objectives, purpose of the database, user requirements, and assumptions made. (5 marks)

Task 1-2: Conceptual Design

Based on your project description, design the ER diagram (25 marks)

a)   use Crow’s foot or Chen notation.

b)   contains at least 5 entities, it’s attributes and their relationships

c)   Indicate primary keys, foreign keys, cardinality constraints, weak entities (if any), etc., d)   There might be extra constraints that cannot be captured by the ER diagram, make sure you mention them below the diagram.

e)   List any assumptions you make in the process.


Task 1-3: Logical Design

Create the  logical  database  design  in  continuation  with  your  conceptual  design,  Apply  3 normalizations on each of the entities in your database design, and perfect the relational database schema.                        (10 marks)

Part 2                                                                                               [60 Marks]

LO 2: Implement physical database structure using SQL to meet the performance needs of a business

LO 3: Implement integrity constraints and security policy to ensure accuracy and control multi-user access.

Task 2- 1: Physical Design & Data Population

a)   Translate the schema from Part A into database tables using SQL Data Definition Language (DDL) statements. Make sure that the translation captures all the key constraints (primary  keys and foreign keys if applicable) and other constraints. (20 marks)

b)    Model the data stored in the database (minimum 10 records in each table) (8 marks)

Task 2-2: Data Manipulation & Access

a)    Demonstrate your design by executing at least 10 complex queries that meet business/user requirements for the database and a summary of its relevance to the business scenario. At   least 8 queries must extract the data from a minimum of 2 tables. Provide a description and

SQL statement for each query.                               (20 marks)

b)   Write four different SQL data control commands granting and revoking access privileges to

the above database. Explain the purpose of each command.                               (12 marks)

Provide evidence of query execution on your database. (Screenshot of the query along with the result, your SQL file with all the queries written properly).

You are to submit database file with full documentation in accordance with the requirements

provided in this assessment. Make sure correct format, structure, spelling, and grammar are used.

2023-10-23