BIS1002 Data And Information Management :

For solution: +610482078788

+61482073403

+61482072848

Assessment Brief: BIS1002 Data and Information Management Trimester-3 2022

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 Invigilated	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. The students have to implement the proposed design and demonstrate the use of SQL language.	Group	30%	Week 12		2500 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:

BIS1002 Data And Information Management 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:

The Blue Mountains Financial Services company provides financial services (banking, advisory, tax consulting, auditing, insurance, wealth management etc.) to customers.

The company has contracted an outsourcing IT company to develop an Information System. The System database should satisfy the following business rules:

• The company wants to keep employees and customers in the database under one entity Person. Each Person has at least one BankAccount. The Person attributes are: pID, pName, pAddress. The BankAccount attributes are: baID, baName, baType, baBalance.
• Each Person belongs to a category: Seller, Customer or Representative. Customer can also be Representative, i.e. he/she can provide services to customers.
• Seller can be one of the categories: category 1 or category 2.
• Customer can be one of two categories: private or corporate.
• Representative can be employed as casual or on a contract.
• Seller/Representative provide Services.
• Seller/Representative can have more than one Customer.
• Seller/Representative signs an Agreement with a Customer.
• Agreement contains at least one of the Services provided by the company.
• Agreement can be one of two types: a contract or an order.
• Seller/Representative issues Invoice to Customer for services
• Each Invoice contains Invoice Items.
• Each Invoice Item is one of the Services.

You must submit your assignment as a MS Word document. The diagrams must be drawn using app.diagrams.net online tool, saved as .png or .jpg file and inserted in the Word assignment document.

Don’t forget to include the assignment cover sheet !!!

Part 1. Conceptual Model

Please remember that the Enhanced Entity-Relationship diagram is only a part of your assignment. You need to describe the Conceptual Model, which must include the following elements:

• Entities and their attributes. For each entity you must also specify unique identifier attribute(s)
• Entity supertypes and subtypes. Describe inheritance types and the corresponding discriminators.
• Identify multi-valued and composite attributes (if any). In this case, they need to be shown in the ER diagram with the corresponding notations.
• Describe relationships between entities and their cardinalities. Remember that relationships can be one-to-one, one-to-many, many-to-many, and also optional or mandatory
• Draw the Conceptual Model Enhanced Entity-Relationship diagram using app.diagrams.net tool, save it as .png or .jpg file and insert it in the Word document

Part 2. Logical Model

Describe the algorithm of BIS1002 Data And Information Management convert the Conceptual Model into the Logical Model. Remember that your description should include the following elements:

• Entities in the Conceptual Model become relations (tables) in the Logical Model, and unique identifiers become primary keys.
• For one-to-one or one-to-many relationship between two entities you will need to create a foreign key in one of the relations (tables).
• If there is a many-to-many relationship between two entities, then you will need to create a new relation (table) with a composite primary key
• For each field (column) in a relation, describe a data type (integer, float, character, text etc.)
• If an entity contains a multi-valued attribute, then you will need to create a new relation in the Logical Model corresponding to this attribute.
• Draw the Logical Model diagram using app.diagrams.net tool, save it as .png or .jpg file and insert it in the Word document

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 (25%)	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 (25%)	Inadequate understanding and describing Logical 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 understanding and skills of creating the Conceptual Model diagram (25%)	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 (25%)	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 Global Trading company sells various building and gardening 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 SQLite database 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 company wants to retrieve information from the database for reporting and data analysis. To get the data, a database analyst writes SQL queries.

Your assignment consists of the following tasks:

1. In the MS Word document:
   1. Identify dimension and fact tables. (Remember that the database structure is a database star schema).
   1. Analyse and describe the tables (fields, field datatypes, primary and foreign keys).
   1. Draw the database diagram.
   1. Copy all queries you wrote in the SQLite Browser to the Word document and explain them.

• In the SQLite Browser, you need to write, save, and run the following SQL queries:
  • Open the ‘AppliedProject2.db’ database and save it as a project.
  • Create and run the queries to create primary and foreign keys. Save the queries in the project.
  • Insert a new customer. The record must contain your full name as a ‘CustomerName’, your

state where you currently live (VIC, NSW), your APIC email address as a ‘CustomerEmail’.

• Create a new sale fact related to the new customer. You can choose any existing product, warehouse, date and sales office.
  • Display all customer names, their addresses and states.
  • Display all customer names from NSW.
  • Display the total amount of sales for all companies in 2018.
  • Display total amount of sales for each customer separately.
  • 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.
  • Display products sold from warehouses located in QLD.
  • For each sale, display a customer name and a purchase date.
  • Display a total number of sales (NOT AMOUNT!) for each customer.
  • 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.
  • Calculate a number (not amount !) of sales in the first quarter of 2016.
  • Display the following sale information: Office branchAddress, customerName, productName, WarehouseDivision, SaleDate for all offices in Victoria and between 15 June 2017 and 15 October 2017.

Your submission should consist of the following documents:

• MS Word document containing answers to the Task 1
• SQLite Browser project file with stored SQL queries from the Task 2
• The AppliedProject2.db SQLite database with created primary and foreign keys. You will lose marks for not submitting any of the above files.

Don’t forget to include the assignment cover sheet !!!

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	Displays exceptional understanding of database design and database diagrams.

				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, functions and aliases. (30%)	Inadequate understandin g and skills of writing advanced SQL queries, including table joins, functions and aliases.	Basic understanding and skills of writing advanced SQL queries, including table joins, functions and aliases.	Exhibits breadth and depth of understanding and skills of writing advanced SQL queries, including table joins, functions and aliases.	Exhibits accurate and detailed breadth and depth of understanding and skills of writing advanced SQL queries, including table joins, functions and aliases.	Displays exceptional understanding and skills of writing advanced SQL queries, including table joins, functions and aliases.
Demonstrate document and SQL query writing skills (20%)	Inadequate document and SQL writing skills	Basic document and SQL writing skills	Exhibits breadth and depth of document and SQL writing skills	Exhibits accurate and detailed breadth and depth of document and SQL writing skills	Displays exceptional document and SQL writing skills

Visit:https://auspali.info/

Also visit:https://www.notesnepal.com/archives/767

For solution: +610482078788

+61482073403

+61482072848

For solution:

WhatsApp

+610482078788

+61482073403

+61482072848

[email protected]