Reference no: EM134016326

Database Systems

Assessment Overview

Objectives

This assessment item relates to the unit learning outcomes as in the unit descriptor. This assessment is designed to improve students' skills to analyze organization database requirements, develop a data model to reflect the organization's business rules. Students may refer to Database Management DBMS concepts while working on the assessment.

This assessment covers the following LOs.

Synthesize user requirements/inputs and analyse the matching data processing needs, demonstrating adaptability to changing circumstances.

Develop an enterprise data model that reflects the organization's fundamental business rules; refine the conceptual data model, including all entities, relationships, attributes, and business rules.

Derive a physical design from the logical design taking into account application, hardware, operating system, and data communications networks requirements; further use of data manipulation language to query, update, and manage a database.

Case Study

This assignment concerns a bookshop chain in Melbourne, called "Elite Books" (EB). The objective of this assignment is to develop a database system that will be used to centrally store and manage all relevant information for the branches of EB. The information to be stored includes details on different branches of EB (e.g., Melbourne CBD, Richmond, St Kilda, etc.), the types of books they sell (fiction, non-fiction, academic, etc.), staff they employ (Sales Assistants, Stock Managers, etc.), and EB Members (EB Loyalty Card holders).

The basic requirements gathered from the stakeholders are presented in the following four points. As is typically the case, these requirements are often underspecified. Use your judgment in interpreting them when required and keep a note of the assumptions you made.

Branch Information: The EB System shall keep information on each branch, including its name and address and the number of employees who work there. The system shall also contain information on which days (Mon-Sun) the branch is open and the opening hours.

Product Information: The system shall contain relevant information on books of different types at the "item level," such as book category (fiction/non-fiction/academic/...), format (hardcover/paperback/eBook), number of pages, price, and publisher (e.g., Penguin Random House), as well as current stock level.

Staff Information: The system shall record information on staff members who work at different branches of EB. This will include their roles, type of employment (e.g., permanent, casual), salary (annual or hourly depending on permanent or casual), as well as who they report to (i.e., supervisors).

Membership Information: The system shall record information on EB members, including type of membership (Platinum/Gold/Silver) and when the membership expires.

Assignment Requirements and Deliverables

Part A -

Task 1
Investigate and identify data requirement for the given study by providing list of required entities, list of attributes in each of these entities, and the unique identifier for each entity.

Task 2
Develop an Entity-Relationships Diagram for the given scenario (It must contain entities and unique identifiers in terms of keys, relationships between the entities including relationships constraints, and the attributes).

Task 3
Derive the physical design having detail of entities, primary and foreign keys, detail of all the attributes.

Assignment Requirements - Part B

Task 1 SQL Queries

Create a database and CREATE TABLE statements for all tables in EliteBooks (refer your Assignment 1 ER Diagram) system including primary and foreign keys.

INSERT INTO statements for populating the database.

Insert five rows of (made-up) data into each table. Make sure that the data you enter in these tables should be sufficient to return at least one row for each query in Task 3.

Select Statements.

List the branches (ID) of EB that have in stock at least 5 copies of "Harry Potter and the Philosopher's Stone."

SELECT statement to generate a list of all email addresses of members whose card will expire in the month after the coming month. Students working with SQL implementation may also find PL SQL resources relevant.

Task 2 Research and Discussion

Consider the relation schema Abnormal_Rel and determine whether or not the relation is susceptible to UPDATE anomaly. Support your determination with an adequate explanation and a small example.

Normalize/decompose the relation schema Abnormal_Rel until you get relations that are in 3NF.

Assessment 2

Assessment Description

In this group assessment, you will demonstrate skills to analyse database requirements, develop an entity relationship diagram, normalise the database, implement the database and use SQL to retrieve information from the database while also applying concepts of data privacy and security.

Activity 1: Normalization
Write a relational schema, and show the First Normal Form (1NF), Second Normal Form (2NF) and Third Normal Form (3NF) for the following tables.

Activity 2: SQL Queries

Case Study: Video Rental (VR) is a start-up business which allows customers to rent videos. They want to create a database to store the information about their videos, customer, renting details and rental fee charged.

Write the SQL queries to create the tables above. Use appropriate data types for each attribute and choose appropriate primary and foreign keys.

Write the Insert query to enter the sample data into the tables created.

Write the required SQL queries, create the view "BorrowDetails", and display the number of videos each customer has from the newly created view.

Presentation & Demonstration (Face-to-Face)

The presentation and demonstration will be conducted on campus in a face-to-face setting during Week 11 class. Each group member must demonstrate at least one task from Activity 2 and actively participate in the demonstration.

Assessment Item 3

Introduction

You will submit work in assignment activities during the study period. In this individual assessment, you will analyse requirements of a practice-based case study and write a report on the feasibility of replacing a sample database with a NoSQL database.

Requirements

For this assessment you will be required to select a published research article/academic paper (within last 4 years) which must cover one or more of the topics including Database modelling, designing, and implementing a DBMS including Data warehousing, Data Management, DB Security, Data Mining or Data Analysis.

The paper can be from any academic conference or other relevant Journal or online sources such as IEEE, ACM, Springer, Google Scholar, academic department repositories etc.

Students selecting topics related to analytics may also explore material associated with Data Mining when reviewing current database research trends.

Report

For this component you will prepare a critique on the paper you chose as mentioned above. Your report should be limited to approximately 1500 words (not including references). Citation of sources is mandatory.

Title Page: The title of the assessment, the name of the paper you are reviewing and its authors, and your name and student ID.

Introduction
Summary of the Article
Evaluation of Methodology
Analysis of Results
Discussion of Scientific Contribution
Critical Reflection
Conclusion
References and citations

Ensure that your critique is well-organized and supported with necessary references.