Assessment Brief: BIS1002 Data and Information Management
Assessment Overview
Assessment Task Type Weighting Due Length ULO
Assessment 1: Database
Interrogation
Practical exercises assess students’
ability to apply theoretical learning to
practical, real world situations on a
weekly basis.
Individual 40% Weeks 2,
4, 6, 8,
10
2500
words
ULO-1
ULO-2
ULO-3
ULO-4
Assessment 2: Applied Project -1
Design a relational database system
for a specify organization. The design
covers ERD, modelling the rules of
organisation, entities and attributes
and relationships.
Individual 30% Week 7 2500
words
ULO-1
ULO-2
ULO-3
Assessment 3: Applied Project –2
Design and implement and physical
data-based system according to
specified requirements. The design
convers the process of logical and
physical design. Assessment Brief: BIS1002 Data and Information Management. The students have to
implement the proposed design and
demonstrate the use of SQL language.
Group 30% Week 12 2500
ORDER A PLAGIARISM-FREE PAPER NOW
words
ULO-1
ULO-2
ULO-3
ULO-4
Assessment 1: Database Interrogation
Due date: Weeks 2, 4, 6, 8, 10
Group/individual: Individual
Word count / Time provided: 2500 words
Weighting: 40%
Unit Learning Outcomes: ULO-1, ULO-2, ULO-3, ULO-4
Assessment Details:
Database Interrogation are practical exercises that assess students’ ability to apply
theoretical learning to practical database questions. This assessment will improve student’s
ability to design databases and write SQL queries.
Students will not be assessed on work that the tutor has not seen them produce in class so
that attendance is required as part of this assessment. Students are required to submit the
work that they have completed during the lab session. The details of the lab work and
requirements are provided on the online learning system.
Marking Information: The assessments will be marked out of 100 and will be weighted
40% of the total unit mark.
Assessment 2: Applied Project-1
Due date: Week 7
Group/individual: Individual
Word count / Time provided: 2500 words
Weighting: 30%
Unit Learning Outcomes: ULO-1, ULO-2, ULO-3
Assessment Details:
Reline Engineering is an engineering firm with approximately 600 employees.
A database is required to keep track of all employees, their skills, projects assigned, and
departments worked in.
Every employee has a unique number assigned by the firm and is required to store his or her name
and date of birth. If an employee is currently married to another employee of Reline Engineering.,
the date of marriage and who is married to whom must be stored; however, no record of marriage is
required if an employee’s spouse is not also an employee.
Each employee is given a job title (e.g., engineer, secretary, and so on). An employee does only one
type of job at any given time, and we only need to retain information for an employee’s current job.
There are 10 different departments, each with a unique name. An employee can report to only 1
department. Assessment Brief: BIS1002 Data and Information Management. Each department has a phone number.
To procure various kinds of equipment, each department deals with many vendors. A vendor
typically supplies equipment to many departments. We are required to store the name and address
of each vendor and the date of the last meeting between a department and a vendor.
Many employees can work on a project. An employee can work on many projects (Project A, Project
B etc.), but he/she can only be assigned to at most one project in a given city. For each city, we are
interested in its state and population.
An employee can have many skills (preparing material requisitions, checking drawings, and so on),
but she or he may use only a given set of skills on a particular project. (For example, an employee
WILLIAMS may prepare requisitions for the Project D and prepare requisitions as well as check
drawings for Project E.)
Employees use each skill that they possess in at least one project. Each skill is assigned a number,
and we must store a short description of each skill.
Projects are distinguished by project numbers, and we must store the estimated cost of each
project.
Your assignment consists of two parts.
Part 1. Conceptual Model
• Identify Entities
• Identify entity supertypes and subtypes and their types, inheritance, and
discriminators.
• Describe relationships between entities and their cardinalities
• Identify entity primary keys
• Draw a Conceptual Model Entity-Relationship diagram
Part 2. Logical Model
• Describe data attributes and their types for each entity
• Redesign the Conceptual model removing many-to-many relationships (if any)
• Identify primary, composite (if any) and foreign keys (if any) for each entity
• if there are any one-to-one relationships then describe them
• Draw a Logical Model diagram
Marking Information: The assessments will be marked out of 100 and will be weighted
30% of the total unit mark.
Marking Criteria Not
satisfactory
(0-49%) of
the criterion
mark)
Satisfactory
(50-64%) of
the criterion
mark
Good
(65-74%) of
the criterion
mark
Very Good
(75-84%) of
the criterion
mark
Excellent
(85-100%) of
the criterion
mark
Demonstrate an
understanding and
describe the
Conceptual Model
design processes
(20%)
Inadequate
understanding
and describing
Conceptual
Model design
process
Basic
knowledge only
of
understanding
and describing
Conceptual
Model design
process
Exhibits breadth
and depth of
understanding
and describing
Conceptual
Model design
process
Exhibits
accurate and
detailed breadth
and depth of
understanding
and describing
Conceptual
Model design
process
Displays
exceptional
understanding of
concepts and
their practical
application in
Conceptual
Model design
process
Demonstrate an
understanding and
describe the
Logical Model
design processes
(20%)
Inadequate
understanding
and describing
Logical Model
design process
Basic
knowledge only
of
understanding
and describing
Conceptual
Exhibits breadth
and depth of
understanding
and describing
Conceptual
Exhibits
accurate and
detailed breadth
and depth of
understanding
and describing
Displays
exceptional
understanding of
concepts and
their practical
application in
Model design
process
Model design
process
Conceptual
Model design
process
Conceptual
Model design
process
Demonstrate
understanding and
skills of creating
the Conceptual
Model diagram
(30%)
Inadequate
understanding
and skills of
creating the
Conceptual
Model diagram
Basic
understanding
and skills of
creating the
Conceptual
Model diagram
Exhibits breadth
and depth of
understanding
and skills of
creating the
Conceptual
Model diagram
Exhibits
accurate and
detailed breadth
and depth of
understanding
and skills of
creating the
Conceptual
Model diagram
Displays
exceptional
understanding
and skills of
creating the
Conceptual
Model diagram
Demonstrate
understanding and
skills of creating
the Logical model
diagram (30%)
Inadequate
understanding
and skills of
creating the
Logical Model
diagram
Basic
understanding
and skills of
creating the
Logical Model
diagram
Exhibits breadth
and depth of
understanding
and skills of
creating the
Logical Model
diagram
Exhibits
accurate and
detailed breadth
and depth of
understanding
and skills of
creating the
Logical Model
diagram
Displays
exceptional
understanding
and skills of
creating the
Logical Model
diagram
Assessment 3: Applied Project-2
Due date: Week 12
Group/individual: Group
Word count / Time provided: 2500 words
Weighting: 30%
Unit Learning Outcomes: ULO-1, ULO-2, ULO-3, ULO-4
The Trade Master company sells various tools. It has sales offices and warehouses all over
Australia. The company developed a data warehouse to store information about sales,
customers and products. These data are stored in the AppliedProject2 Access file provided
for you.
The database consists of 6 tables:
• CompanyDim – with the sales office address, state and email
• ProductDim – product information
• WarehouseDim – warehouse locations
• CustomerDim – information about customers
• DateDim – sales dates
• SaleFacts – information about sales
Please notice that all data (customers, sales, dates, warehouses and products) are randomly
generated. Table and field names are self-explanatory.
The Global Trading company wants to retrieve information from the database for reporting
and data analysis.
You must do the following tasks
1. In the MS Word document:
a. Analyse and describe the tables.
b. Describe dimension and fact tables. (You need to remember that the Data
Warehouse tables are organised into a fact and dimension tables, and this
structure is called a database star schema).
c. Identify and describe primary and foreign keys.
d. Draw the database diagram.
e. Copy from the database all queries to the Word document and explain them. Assessment Brief: BIS1002 Data and Information Management.
ORDER A PLAGIARISM-FREE PAPER NOW
2. In the SQLite Browser, you need to write, save and run the following SQL queries:
a. Create all primary and foreign keys.
b. Insert a new customer. Devise your own data for this record.
c. Create a new sale fact related to the new customer. You can choose any existing
product, warehouse, date and sales office.
d. Display all customer names, their addresses and states.
e. Display all customer names from Victoria.
f. Display the total amount of all company sales in 2018.
g. Display total amount of sales for each product separately.
h. Display total amount of sales for each product and state separately, i.e. the output
should contain 3 columns: product, state and total amount for this product and
state.
i. Display products sold from warehouses located in Victoria.
j. For each sale, display a customer name and a purchase date.
k. Display a total number of sales (NOT AMOUNT!) for each customer.
l. For each sales office (branch), display: branchKey, branch state, a customer name (a
customer purchased in this office), a product name purchased by this customer, and
a month number of the purchase.
Your submission should consist of the following documents:
• MS Word document containing answers to the Task 1
• SQLite Browser file with stored SQL queries from Task 2
Marking Criteria and Rubric: The assessment will be marked out of 100 and will be
weighted 30% of the total unit mark
Marking Criteria Not
satisfactory
(0-49%) of
the criterion
mark)
Satisfactory
(50-64%) of
the criterion
mark
Good
(65-74%) of
the criterion
mark
Very Good
(75-84%) of
the criterion
mark
Excellent
(85-100%) of
the criterion
mark
Demonstrate
understanding of
database design
and ability to draw
database diagrams
(20%)
Inadequate
understandin
g of database
design and
database
diagrams.
Basic knowledge
only of
understanding
and of database
design and
database
diagrams.
Exhibits breadth
and depth of
understanding
of database
design and
database
diagrams.
Exhibits
accurate and
detailed breadth
and depth of
understanding
of database
design and
database
diagrams.
Displays
exceptional
understanding of
database design
and database
diagrams.
Demonstrate
understanding of
database primary
and foreign keys,
and database
referential
integrity. (10%)
Inadequate
understandin
g of database
primary and
foreign keys,
and database
referential
integrity.
Basic knowledge
only of
understanding
of database
primary and
foreign keys,
and database
referential
integrity.
Exhibits breadth
and depth of
understanding
of database
primary and
foreign keys,
and database
referential
integrity.
Exhibits
accurate and
detailed breadth
and depth of
understanding
of database
primary and
foreign keys,
and database
referential
integrity.
Displays
exceptional
understanding of
database primary
and foreign keys,
and database
referential
integrity.
Demonstrate an
understanding and
ability to write and
run simple SQL
DDL and DML
queries (20%)
Inadequate
understandin
g and ability
to write and
run simple
SQL DDL and
DML queries
Basic knowledge
only of
understanding
and ability to
write and run
simple SQL DDL
and DML queries
Exhibits breadth
and depth of
understanding
and ability to
write and run
simple SQL DDL
and DML queries
Exhibits
accurate and
detailed breadth
and depth of
and ability to
write and run
simple SQL DDL
and DML queries
Displays
exceptional
understanding
and ability to
write and run
simple SQL DDL
and DML queries
Demonstrate
understanding and
skills of writing
advanced SQL
queries, including
table joins, MS
Access functions
and aliases (50%)
Inadequate
understandin
g and skills of
creating the
Conceptual
Model
diagram
Basic
understanding
and skills of
creating the
Conceptual
Model diagram
Exhibits breadth
and depth of
understanding
and skills of
creating the
Conceptual
Model diagram
Exhibits
accurate and
detailed breadth
and depth of
understanding
and skills of
creating the
Conceptual
Model diagram
Displays
exceptional
understanding
and skills of
creating the
Conceptual
Model diagram. Assessment Brief: BIS1002 Data and Information Management.
