Our Services

Get 15% Discount on your First Order

[rank_math_breadcrumb]

Database System

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

ASSESSMENT 1- ( Part A & Part B)

ICT503 – Database Systems

Semester 1, 2024

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

Assessment Details for Assessment Item 1:

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. This assessment covers the following LOs.

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

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

3. 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

Assessment tasks Learning Outcome Mapping
Assessment ID Assessment Item When due Weighting ULO# CLO# for

MITS
CLO# for
GDITS

CLO# for
GCITS

1

Case Study – Part A
Entity – Relationship
Diagram (Individual)

Session 4 20% 1 1 1 1

Case Study – Part B
Normalisation
(Individual)

Session 6 20% 2 1 1 1

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

Case Study

This assignment concerns a liquor shop chain in Sydney, called the A-one liquor (AL). 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
AL.
The information to be stored include information on different branches of AL (e.g., Bankstown, Hornsby, etc.), types of
drinks they sell (beers, wines, cedars, etc.), staff they employ (Retail Assistants, Shelving Assistants, etc.), AL Members (AL
Loyalty Card holders). The basic requirements gathered from the stake holders is presented in the following four points.
As 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.

1. Branch Information: The AL 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 opening hours. It will also keep information on opening hours (e.g., Mon-Fri 10:00AM-5:30PM;
Sat 9:00AM-9:00PM; Sun Closed).

2. Product Information: The system shall contain relevant information on products of different types at the “item
level”, such as: (wine/beer/spirit/…), packaging info (can/bottle/…), volume (e.g., 375ml X 6 pack), price, and brand
(e.g., Tooheys Old Dark Ale), as well as current stock level.

3. Staff Information: The system shall record information on staff members who work at different branches of AL. 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).

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

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

Assignment Requirements and Deliverables:

Part A – 20% (Due in Session 5)

Submitted as a MS Word Document
Task 1: (Weightage 20%)
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: (Weightage 60%)
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: (Weightage 20%)
Derive the physical design having detail of entities, primary and foreign keys, detail of all the attributes.

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

Marking Criteria/Rubric

Marking Guide: Unacceptable Acceptable Good Excellent
Task 1

(Weightage 20%)

.

1.1 Entities

(Weightage 10%)

Does not adequately have all
required Entities in ER diagram

Partially have all required Entities
in ER diagram

Most of the required Entities are
there in ER diagram

All Entities are there in ER diagram

1.2 Attributes

(Weightage 5%)

Attributes not identified or
incorrectly identified.

A few of the required attributes for
each of the required entities are
identified.

Most of the required attributes for
each of the required entities are
identified.

All the required attributes for each of
the required entities are identified.

1.3 Unique
Identifiers

(Weightage 5%)

Unique identifier not identified
or incorrectly identified.

Unique identifier for a few of the
required entities is identified.

Unique identifier for most of the
required entities is identified

Unique identifier for all of the required
entities is identified

Task 2

(Weightage 60%)

2.1 Entities
(Weightage 10%)

Does not adequately
demonstrate required entities in
an ER diagram.

A few of the required entities are
demonstrated correctly in an ER
diagram.

Most of the required entities are
demonstrated correctly in an ER
diagram.

All the required entities are
demonstrated correctly in an ER
diagram.

2.2 Attributes
(Weightage 10%)

Does not adequately
demonstrate required attributes
in an ER diagram.

A few of the required attributes for
each of the required entities are
demonstrated correctly in an ER
diagram.

Most of the required attributes for
each of the required entities are
demonstrated correctly in an ER
diagram.

All the required attributes for each of
the required entities are demonstrated
correctly in an ER diagram.

2.3 Unique
Identifier
(Weightage 10%)

Does not adequately
demonstrate required Unique
identifier in an ER diagram.

Unique identifier for a few of the
required entities is demonstrated
correctly in an ER diagram

Unique identifier for most of the
required entities is demonstrated
correctly in an ER diagram.

Unique identifier for all of the required
entities is demonstrated correctly in an
ER diagram.

2.4 Relationships
(Weightage 30%)

Does not adequately identify and
demonstrate relationships
among entities in an ER diagram.

A few of the required relationships
among entities are identified and
correctly demonstrated in an ER
diagram.

Most of the required relationships
among entities are identified and
correctly demonstrated in an ER
diagram.

All of the required relationships among
entities are identified and correctly
demonstrated in an ER diagram.

Task 3

(Weightage 20%)

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

3.1 Tables
(Entities)

(Weightage 5%)

Relational schema does not
adequately cover the required
tables, and key and non-key
attributes.

Relational schema covers a few of
the required tables, and key and
non-key attributes for those tables.

Relational schema covers most of the
required tables, and key and non-key
attributes for those tables.

Relational schema covers all required
tables, and key and non-key attributes
for those tables.

3.2 Foreign Keys

(Weightage 10%)

Relational schema does not
adequately cover foreign keys.

Relational schema covers required
foreign keys in a few of the
required tables.

Relational schema covers required
foreign keys in most of the required
tables.

Relational schema covers required
foreign keys in all required tables.

3.3 Column
(attributes)
information
(Weightage 5%)

Does not adequately cover
information about data columns.

Provide required detail information
for all data columns of a few of the
tables.

Provide required detail information
for all data columns of most of the
tables.

Provide required detail information for
all data columns of all required tables.

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

Assignment Requirements and Deliverables:
Part B – 20% (Due in Session 6)

Submit a single plain text file with filename as “studentid_studentname_AL_SQL” containing all SQL implementation. Your
SQL queries must work on MS SQL Server and be able to be demonstrated. SQL code required (Use MS SQL Server):

1. Create a database and CREATE TABLE statements for all tables in your ERD (Part A) including primary and foreign
keys.

2. INSERT INTO statements for populating the database
i. 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. AL should hold at least 5 bottles of Penfold
Grange 2010 in some branch or other.

3. Select Statements
i. List the branches (ID) of MA that have in stock at least 5 bottles of Penfold Grange 2010.

ii. SELECT statement to generate a list of all email addresses of members whose card will expire in the month
after the coming month. Thus, for instance, if the query is run in November 2121, it will list the emails of all
members whose membership will expire in January 2122. The emails should be ordered by Branch ID, then
by expiry date, and then by the email address, all in ascending order.

Research and Discussion
Submitted as a MS Word or PDF Document
4. Consider the following relation schema as the join of a few tables from Assignment 1 ERD

Abnormal_Rel ( ProductID, BranchID, campaignID, MemberID,
ProductType, PackageType, YearProduced, Price, Brand,

StockLevel, CampaignStartDate, CampaignEndDate, FirstName, LastName, eMail, MembershipLevel,
MemberExpDate, Discount )

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

Determine for UPDATE anomaly whether or not the relation Abnormal_Rel is susceptible to that anomaly. Support
your determination with adequate explanation and a small example.

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

illustration to aid the understanding of your work

Marking Rubric/Criteria

Marking Guide: Case
Study 1

Unacceptable Acceptable Good Excellent

1. Create table statements

(Weightage 1 Mark)

Incorrect syntax, tables
are not in line with
Assignment 1 ERD.

Partially correct syntax, tables
are somewhat in line with
Assignment 1 ERD.

Syntactically correct and most
tables are in line with
Assignment 1 ERD.

Code is correct and all tables
are in line with Assignment 1
ERD.

2. Insert Into statements

(Weightage 1 Mark)

Incorrect syntax Partially correct syntax and
records are partially inserted
as per the requirements

Syntactically correct and most
of the records are inserted as
per the requirements.

Code is correct and records
are inserted as per the
requirements.

3. Select statements

(Weightage 3.5 Marks)

3.1 Select statement to list
the branches (ID) of MA
that have in stock at least 5
bottles of Penfold Grange
2010.
(1.5 Marks)

Incorrect syntax Partially correct syntax and
gives output somewhat near
to requirements.

Syntactically correct and gives
output very near to
requirements.

Code is correct and used
JOIN, etc. and gives required
output

3.2 SELECT statement to
generate a list of all email
addresses of members
whose card will expire in
the month after the coming
month.

Incorrect syntax Partially correct syntax and
gives output somewhat near
to requirements.

Syntactically correct and gives
output very near to
requirements.

Code is correct and used
JOIN, etc. and gives required
output

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

(2 Marks)

4. Anomalies

(Weightage 1 Mark)

Does not adequately
identify anomalies.

Identify a few of the
anomalies and remove those
anomalies from the tables.

Correctly identify most of the
possible anomalies and remove
those anomalies from the
tables.

Correctly identify all of the
possible anomalies and
remove those anomalies
from the tables.

5. Normalization in
relation to the case
study

(Weightage 3.5 Marks)

4.2 discuss how 1NF was
achieved for each entity.
(0.5 Mark)

Does not adequately
identify repeating
group of columns and
not transform
unnormalised tables
into 1NF tables.

Identify a few of the repeating
group of columns and
correctly transform a few of
the unnormalised tables into
1NF tables.

Identify most of the repeating
group of columns and correctly
transform most of the
unnormalised tables into 1NF
tables.

Identify all of the repeating
group of columns in each
table and correctly
transform all of the
unnormalised tables into
1NF tables.

4.3 discuss how 2NF was
achieved for each entity.
(2 Marks)

Does not adequately
identify partial
dependencies and not
transform 1NF tables
into 2NF tables.

Identify a few of the partial
dependencies and correctly
transform a few of the 1NF
tables into 2NF tables.

Identify a few of the partial
dependencies and correctly
transform a few of the 1NF
tables into 2NF tables.

Identify all of the partial
dependencies in each table
and correctly transform all of
the 1NF tables into 2NF
tables.

4.4 discuss how 3NF was
achieved for each entity.
(1 Mark)

Does not adequately
identify transitive
dependencies and not
transform 2NF tables
into 3NF tables.

Identify a few of the transitive
dependencies and correctly
transform a few of the 2NF
tables into 3NF tables.

Identify most of the transitive
dependencies and correctly
transform most of the 2NF
tables into 3NF tables.

Identify all of the transitive
dependencies and correctly
transform all of the 2NF
tables into 3NF tables.

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

Submission Instructions

All submissions are to be submitted through Turnitin. Drop-boxes linked to Turnitin will be set up in Moodle. Assessments
not submitted through these drop- boxes will not be considered. Submissions must be made by the end of session 4 (Part
A) and session 6 (Part B). The Exact dates will be communicated on LMS.

The Turnitin similarity score will be used to determine any plagiarism of your submitted assessment. Turnitin will check
conference websites, Journal articles, online resources, and your peer’s submissions for plagiarism. You can see your
Turnitin similarity score when you submit your assessments to the appropriate drop-box. If your similarity score is of
concern, you can change your assessment and resubmit. However, re-submission is only allowed before the submission
due date and time. You cannot make re-submissions after the due date and time have elapsed.

Note: All work is due by the due date and time. Late submissions will be penalized at 20% of the assessment final grade
per day, including weekends.

Referencing guides

You must reference all the sources of information you have used in your assessments. Please use the IEEE referencing
style when referencing in your assessments in this unit. Refer to the library’s referencing guides for more information.

• encing%20-

%20IEEE%20-%2007042020.pdf

Academic misconduct

VIT enforces that the integrity of its students’ academic studies follows an acceptable level of excellence. VIT will adhere

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

to its VIT Policies, Procedures and Forms where it explains the importance of staff and student honesty in relation to
academic work. It outlines the kinds of behaviours that are “academic misconduct”, including plagiarism.

Late submissions

In cases where there are no accepted mitigating circumstances as determined through VIT Policies, Procedures and
Forms, late submission of assessments will lead automatically to the imposition of a penalty. Penalties will be applied as
soon as the deadline is reached.

Short extensions and special consideration

Special Consideration is a request for:

• Extensions of the due date for an assessment, other than an examination (e.g., assignment extension).

• Special Consideration (Special Consideration in relation to a Completed assessment, including an end-of-unit
Examination).

Students wishing to request Special Consideration in relation to an assessment the due date of which has not yet passed
must engage in written emails to the teaching team to Request for Special Consideration as early as possible and prior
to start time of the assessment due date, along with any accompanying documents, such as medical certificates.

For more information, visit VIT Policies, Procedures and Forms.

Inclusive and equitable assessment

Reasonable adjustment in assessment methods will be made to accommodate students with a documented disability
or impairment. Contact the unit teaching team for more information.

10

Victorian Institute of Technology www.vit.edu.au CRICOS Provider No. 02044E, RTO No: 20829

Contract Cheating

Contract cheating usually involves the purchase of an assignment or piece of research from another party. This may be
facilitated by a fellow student, friend or purchased on a website. Other forms of contract cheating include paying
another person to sit an exam in the student’s place

Contract cheating warning:

• By paying someone else to complete your academic work, you don’t learn as much as you could have if you did
the work yourself.

• You are not prepared for the demands of your future employment.
• You could be found guilty of academic misconduct.
• Many of for pay contract cheating companies recycle assignments despite guarantees of “original, plagiarism-

free work” so similarity is easily detected by TurnitIn.
• Penalties for academic misconduct include suspension and exclusion.
• Students in some disciplines are required to disclose any findings of guilt for academic misconduct before being

accepted into certain professions (e.g., law).
• You might disclose your personal and financial information in an unsafe way, leaving yourself open to many risks

including possible identity theft.
• You also leave yourself open to blackmail – if you pay someone else to do an assignment for you, they know you

have engaged in fraudulent behaviour and can always blackmail you.

Share This Post

Email
WhatsApp
Facebook
Twitter
LinkedIn
Pinterest
Reddit

Order a Similar Paper and get 15% Discount on your First Order

Related Questions

Connecting Everything – The Internet of Things

1) How can existing and emerging technologies (IoT, Artificial Intelligence, Cloud Computing, Machine Learning, Collaboration Technologies, etc,) inform and enhance the administration and delivery of Public Sector services, i.e., 2) What is possible? 3) How to maximize?

Information Systems Advances in Technology Assignment

Advances in Technology Assignment Overview In this assignment, you will examine how advances in technology and telehealth have improved health care in rural communities. Transportation, health monitoring, timelines, and communication within the health care system are key areas where telehealth can reduce or minimize challenges and burdens with patient encounters.

Telehealth and Virtual Medicine

 2 paragraphs, include reference. Student response will be posted under NOTE PAD.  How have health care providers taken advantage of telehealth and virtual medicine to promote access to patient care during the COVID-19 pandemic? What are some advantages and disadvantages of virtual health care? Be sure to respond to at

Assignment 1

Due 5/23/2024 A professional and appealing document is expected. As required by the Code of Student Conduct and Academic Responsibility, please make sure to document appropriately your references and state ideas in your own words or use quotation marks. image1.png image2.png image3.png

Information Systems Assignment 1-2

Written Essay Assignment 1-3: Due May 23,2024 No more than 3 pages · Relate the Health Insurance Portability and Accountability Act (HIPAA) Privacy and Security rules to NIST standards and encryption technologies to ensure confidentiality of ePHI transmission · Evaluate the requirements for a health care organization to become compliant

1-1

Written Essay Assignment 1-1: Due May 23,2024 No more than 3 pages · Research the Enron fraud case as well as SOX requirements · Identify the key elements of fraud committed by Enron that led to the Sarbanes-Oxley (SOX) compliance law. · Identify other compliance laws in the United States

INTRODUCTION TO OM FOR DX

Assignment #2 Assignment Submission: Submit your assignment on d2l that has a SINGLE ZIP file that has 1. All Visio/draw.io/Modelio Diagrams 2. The PDF document that has all the diagrams and the answers inline below every question including the essay/textual questions. Assignment Deliverables: Provide your answers for the following requirements

DNS, IP, and TCP Traffic Streams

Please see file Preparation 1. Download and install the TCP/IP packet capture program 2. Decide on a website of your choice for which you will capture and analyze TCP/IP traffic streams and identify the URL of your selected website (e.g., 3. Use the  Week 7 Assignment Template  Download Week 7

Response 2

200 word response due 5/17/2024 Franco 2a When recruiting employees for cybersecurity focused work, the vetting process is very important. These prospective employees are more thoroughly vetted than in non-security positions because of the nature of the work. A cyber employee’s main objectives will be to strengthen the cybersecurity posture

Response 1

200 word response due 5/17/2024 2a. Why is it incumbent on employers of cybersecurity personnel to vet prospective employees more thoroughly than in non-security positions? Employers, in charge of hiring cybersecurity professionals are required to assess employees more than those in no security roles. This rigorous vetting process is crucial

Topic 2

Topic 2 Answer the following questions Due 5/17/2024 2a. Why is it incumbent on employers of cybersecurity personnel to vet prospective employees more thoroughly than in non-security positions? (answer with 100 words) 2b. To what extent should the workplace peruse applicants through social media sites? (i.e. Facebook, LinkedIn). At what

Response 2

200 word response 1 reference/intext citation Due 5/16/2024 Jackson The Sarbanes-Oxley Act, or Sox, is a United States law “to protect investors by preventing fraudulent accounting and financial practices at publicly traded companies” (Fruhlinger, 2020). Specifically, the section of the SOX compliance law that requires proper controls and integrity of

Response 1

300 word response 1 reference/intext citation Due 5/16/2024 Quintana HIPAA regulations require health care organizations to have an incident response plan and team that handles potential security incidents and breaches in order to protect and safeguard patient privacy and information and to properly respond to security incidents. The data and

Discussion 1

Discussion #1 The Scope of an IT Compliance Audit 300 word response 1 reference/intext citation Due 5/14/2024 Under the HIPAA Security Rule, it is a requirement for a health care organization to have a security incident response plan and team to handle potential security incidents and breaches. Why is this

Data Analysis Using R

2 paragraph, include reference. 1 student response  Describe what R programming language is and how R programming is used in health care. What are the advantages and disadvantages of R in daily health care IT? Be sure to respond to at least one of your classmates’ posts.

R Programming Activity

Week 7 Activity – R Programming Overview In this learning activity, you will explore the use of R programming in health care. R is an open-source free software used for statistical computing and graphics. You will write an essay to examine how R is used for data analytics and decision-making

Summative assessment

Summative assessment UU – MBA – 714 Summative Assessment Point 2 1 | P a g e Summative Assessment Point 2 Brief, Guidelines and Marking Criteria For this assignment, you are required to find and promote an innovative product/service within an organization of your choice. In your assignment, you need

Database Analysis Using Python

2 paragraph include reference and 1 student response List the components of a relational database and how they are used for Python programming. How is Python programming used in health care IT? List some advantages and disadvantages of Python programming. Be sure to respond to at least one of your