Subject Code & Title :- BIS1002 Data And Information Management
Assessment Type :- Assignment 1
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.
BIS1002 Data And Information Management Assignment 1
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.
Assessment 2: Applied Project-1
Group/individual :- Individual
Word count / Time provided :- 2500 words
Weighting :- 30%
Unit Learning Outcomes :- ULO-1, ULO-2, ULO-3
Assessment Details:
A university college decided to create a database to schedule classes.
The database business rules are:
• The database stores information about each teaching Room with the following attributes: BuildingID, RoomID and Capacity.
• A Room can be one of two types: Lab and Classroom, and cannot be both simultaneously.
• Each Lab has an attribute LabType (Network, Web Design, Software Development etc.).
• Each Classroom has an attribute: BoradType (wall-mounted board, mobile whiteboard glass board, chalkboard etc.)
• A Room can have none, one or several Media with the following attributes: MediaType (projector, microphone, online media stream, printer, TV etc.).
• A Lab has computers of one or several types. A Computer has the following attributes:
ComputerType (Apple, Windows PC, Linux PC, Laptop, Tablet etc.), Description,MemorySize, ProcessorSpeed.
• A Teacher has the following attributes: ID, Name, Rank and Email.
• Each Session has the following attributes: DayOfWeek, StartTime and EndTime.
• Each course Unit has the following attributes: UnitID, UnitDescription and Credits.
• A Unit might have none, one or several prerequisites.
• A Unit can have one or several classes depending on a number of students.
BIS1002 Data And Information Management Assignment 1
• A Class has attributes: ClassID and EnrollmentLimit.
• A Teacher teaches none, one or several Classes of a Unit.
• A Session is assigned to a Teacher.
• A Schedule is created for each Year and Semester. A Schedule links Sessions, Rooms and corresponding Classes.
• A Teacher uses none, one or several types of Media.
Your assignment submission 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
BIS1002 Data And Information Management Assignment 1
Assessment 3: Applied Project-2
Group/individual: Group
Word count / Time provided: 2500 words
Weighting: 30%
Unit Learning Outcomes: ULO-1, ULO-2, ULO-3, ULO-4
The Building Tools 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 Applied Project 2 Access file provided for you.
The database consists of 6 tables:
• OfficeDim – 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.
BIS1002 Data And Information Management Assignment 1
The Building Tools 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 (fields, field datatypes, records etc.).
b. Identify dimension and fact tables. (You need to remember that the database structure is a database star schema).
c. For each table, identify and describe primary and foreign (if any) keys.
d. Draw the database diagram.
e. Copy from the database all queries to the Word document and explain them.
BIS1002 Data And Information Management Assignment 1
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 sale date. Devise your own data for this record.
c. Create a new sale fact related to the new date. You can choose any existing product ware house customer and sales office.
d. Display all product names, brands and prices.
e. Display all customer names from New South Wales.
f. Display the total amount of all company sales in the first quarter of 2016.
g. Display total amount of sales for each customer separately.
h. Display total amount of sales for each product and warehouse separately, i.e. the out put should contain 3 columns: product, warehouse division and total amount for this product and warehouse.
i. Display products sold from offices located in Victoria.
j. For each sale, display a customer name and a purchase date and a quarter.
k. Display a total number of sales (NOT AMOUNT!) for each product.
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
ORDER This BIS1002 Data And Information Management Assignment 1 NOW And Get Instant Discount
