MN405 Data and Information Management – Assignment 1: Managing Data in Databases

Assessment Overview

Unit code: MN405 – Data and Information Management

Assessment type: Assignment 1 – Managing Data in Databases

Weighting: 20% of unit assessment (60 marks for Part B + 30 marks from online quiz component)

Delivery context: Undergraduate/Graduate Certificate level database and information management unit in an Australian IT program, aligned with relational data modelling and SQL skills taught using a commercial DBMS tool such as Microsoft Access or a similar platform.

Due: Week 8 lab class

Word/page limit: No specific word or page limit; focus is on correctness, completeness, and clarity of database implementation and SQL queries.

Unit Learning Outcomes (ULO Mapping)

On successful completion of this assignment, students will be able to:

• Model organisational information requirements using conceptual data modelling techniques such as entity–relationship diagrams and attribute specification.
• Convert conceptual data models into a relational data model that satisfies key integrity and dependency constraints.
• Implement a relational schema in a commercial DBMS and manipulate data using SQL Data Definition Language (DDL) and Data Manipulation Language (DML).
• Identify and reason about functional dependencies in relational tables as a basis for normalisation and sound database design.

Assessment Components

This assessment consists of two connected parts that assess conceptual understanding and practical SQL skills:

• Part A – Online Quiz (30 marks): Individual online quiz delivered via the learning management system (LMS) covering foundational relational model concepts (keys, constraints, basic relational algebra-style reasoning, and introductory SQL syntax). Students are allowed up to three attempts; the system retains the highest grade.
• Part B – Database Implementation and SQL Queries (60 marks): Individual practical task requiring implementation of a supplied data model in MS Access (or equivalent DBMS), population of sample data, and execution of SQL queries including aggregate functions, joins, and selection based on conditions and functional dependencies.

Submission Requirements

• Submit a single MS Word document (.doc or .docx) via Moodle by the Week 8 lab deadline.
• Formatting: 1.5 line spacing, 11‑pt Calibri (Body) font, 2.5 cm margins on all sides, and clear section headings.
• Include screenshots of:
  • Database tables in Design View showing field names, data types, and keys.
  • Sample data in Datasheet View.
  • SQL query design and output for each question in Part B.
• Reference any external material used (texts, articles, manuals) using IEEE or APA 7th style as specified by your local campus or unit guide; be consistent throughout.

File naming convention: MN405_Assignment1_StudentID_FirstnameLastname.docx

Academic Integrity

You must complete all work individually. Collaboration on conceptual understanding is acceptable, however sharing files, code, or full query solutions constitutes academic misconduct. The University monitors submissions using similarity detection tools and may require you to explain or reproduce your work.

• Do not copy SQL code or explanations directly from websites, classmates, or AI tools.
• Do not share your database file or Word document with other students.
• Cite any external resources (e.g., database design tutorials, textbooks) you consult.

For full details, consult your institution’s Academic Integrity and Misconduct Policy and associated procedures.

Extension Requests

Students seeking an extension must submit a Special Consideration or equivalent application to the School office at least three working days before the deadline, with appropriate supporting documentation, as per institutional policy.

Assignment 1 – Detailed Description

Part A – Online Quiz: Relational Model Concepts (30 marks)

The online quiz tests your understanding of the relational model and basic SQL concepts introduced in Weeks 1–5.

• Timing: Quiz opens after Week 5 and closes at the start of Week 8.
• Format: 30 questions (multiple choice, true/false, short answer) drawn from a question bank.
• Content coverage:
  • Relational concepts: relations, tuples, attributes, primary keys, foreign keys, and integrity constraints.
  • Basic SQL: SELECT, FROM, WHERE, simple joins, and aggregate functions (COUNT, SUM, AVG).
  • Introductory functional dependencies and normalisation concepts (1NF–3NF).
• Attempts: Up to three attempts; highest score recorded.

Submission for Part A: After completing the quiz, copy and paste the quiz summary page (showing your final score and attempt details) into your Assignment 1 Word document under the heading “Part A – Quiz Summary”.

Part B – SmartProjects Database and Functional Dependencies (60 marks)

In this part you will implement a small database for a fictional organisation called SmartProjects and answer SQL and design questions using MS Access (or an approved alternative DBMS with equivalent functionality).

Scenario: SmartProjects

SmartProjects is an IT consulting organisation that manages employees, sales projects, and departmental structures. The company maintains information about employees, projects, the work each employee performs on each project, and organisational departments. The following four base tables capture this information:

• EMPLOYEE – stores employee demographic and employment details (e.g., employee number, first name, last name, department, salary).
• PROJECT – stores project details including project number, name, and budget.
• WORK – records which employees are assigned to which projects, along with hours worked or role information.
• DEPARTMENT – stores department-level data such as department number, name, and office location.

A conceptual relationship diagram provided in class links these tables via primary and foreign key relationships aligned with standard relational database design practice.

Task 1 – Implementing the SmartProjects Database (15 marks)

(a) Create the SmartProjects Database in MS Access (5 marks)

Using Microsoft Access (or an approved equivalent DBMS):

1. Create a new database file named SmartProjects.accdb.
2. Define the four tables EMPLOYEE, PROJECT, WORK, and DEPARTMENT using the attribute names, data types, and keys supplied in the assignment handout and sample data sheets.
3. Set primary keys on each table and enforce relationships via foreign keys to maintain referential integrity between:
   • EMPLOYEE and DEPARTMENT.
   • WORK and EMPLOYEE.
   • WORK and PROJECT.
4. Ensure data types are appropriate (for example, numeric types for salary and budget, and text types for names and codes).

Evidence required in Word document:

• Screenshot(s) of each table in Design View showing fields, data types, and primary key icons.
• Screenshot of the Relationships window showing all four tables and their relationships with referential integrity enforced.

(b) Populate the SmartProjects Database with Sample Data (10 marks)

Using the sample data tables provided for this assignment in your unit LMS or handout, populate all four tables:

1. Enter the sample rows for EMPLOYEE, PROJECT, WORK, and DEPARTMENT exactly as provided.
2. Verify that all foreign key values used in WORK match existing primary key values in EMPLOYEE and PROJECT.
3. Check that no referential integrity errors occur when saving or updating records.

Evidence required:

• Screenshot of each table in Datasheet View showing at least 5–10 rows of inserted data (or the complete sample dataset if small).

Task 2 – SQL Query Writing and Execution (45 marks)

For each of the following questions, write a single SQL query and then execute it against the SmartProjects database. Copy both the SQL query and the resulting output (screenshot or copied result set) into your Word document under the relevant subheading.

(c) Projects with Budget Greater than $85,000 (5 marks)

Question: How many projects have a project budget greater than $85,000?

Requirements:

• Use an aggregate function to count projects with a budget greater than 85000.
• Output a single value labelled appropriately (e.g., ProjectCount).

Evidence: Include your SQL statement and a screenshot of the query output in Access.

(d) Employees with Salary Greater than the Average Salary (10 marks)

Question: Display the first name and last name of all employees with a salary greater than the average salary.

Requirements:

• Use a subquery or aggregate function to calculate the average salary over all employees.
• Select employees whose salary is strictly greater than that average.
• Return at least two columns: FirstName and LastName, and you may include Salary for clarity.

Evidence: Include the SQL statement and output screenshot.

(e) Employees Assigned to Project N2 (10 marks)

Question: Find the first name, last name, department number, and salary of employees who are assigned to project N2.

Requirements:

• Join EMPLOYEE, WORK, and PROJECT to relate employees to their assigned projects.
• Filter on the correct project identifier for project N2 (for example, ProjectNo = ‘N2’, depending on your schema).
• Output: FirstName, LastName, DepartmentNo, Salary.

Evidence: Include SQL and output screenshot.

(f) Employees in the Computer Science Department (10 marks)

Question: Find details of employees attached to the Computer Science department.

Requirements:

• Join EMPLOYEE and DEPARTMENT on the appropriate key(s).
• Filter rows where the department name (or code) corresponds to “Computer Science” as per your sample data.
• Return a meaningful set of employee attributes (for example, EmpNo, FirstName, LastName, DepartmentName, Salary).

Evidence: Include SQL and output screenshot.

Task 3 – Functional Dependencies in STUDENT Table (10 marks)

The STUDENT relation is defined as:

STUDENT (no, Sname, courseno, course_name, address, course_instructorID, InstructorName, Instructor_office)

Question: Determine the functional dependencies that exist in the STUDENT table, based on reasonable assumptions about uniqueness and key structure.

Requirements:

• Identify a plausible primary key (for example, no, courseno if students can enrol in multiple courses).
• List all non-trivial functional dependencies in a formal notation (for example, no → Sname, address).
• Explain, in 3–5 sentences, the rationale behind your identified dependencies, including any assumptions about how courses and instructors are managed.
• Optionally discuss whether this table is in 1NF, 2NF, or 3NF, and specify which dependencies lead to anomalies.

Marking Criteria – Assignment 1 Part B (60 marks)

Criteria and Performance Levels

Criterion	Excellent	Very Good	Good	Satisfactory	Unsatisfactory
Query writing skills	SQL queries are syntactically correct, efficient, and return fully accurate results; appropriate use of joins, subqueries, and aggregates.	Queries mostly correct with minor inefficiencies or small issues that do not affect results.	Queries work but may contain redundant clauses or inconsistent formatting.	Queries show partial understanding; some produce incorrect or incomplete results.	Queries frequently fail to run or return incorrect results; limited evidence of SQL understanding.
Identification (conceptual understanding)	Demonstrates clear, well-justified identification of keys, constraints, and functional dependencies; arguments show high-level critical thinking.	Identifies most dependencies and constraints correctly with minor gaps in explanation.	Shows reasonable understanding with some correct dependencies but limited depth in justification.	Provides partial or vague identification of dependencies; explanations lack clarity.	Misidentifies key dependencies; little or no reasoning is provided.
Analysis	Reasoning is logically structured, easy to follow, and convincingly links design choices to data integrity and query results.	Argumentation is mostly logical and coherent with small inconsistencies.	Analysis is generally sound but occasionally jumps or omits key steps.	Arguments show some logic but are fragmented or incomplete.	Arguments are confused, disjointed, and difficult to follow.
Design / Presentation	All components present and well integrated; screenshots, SQL listings, and explanations are clearly organised and labelled.	Most components present and clearly formatted with minor issues.	All required components included but presentation is inconsistent or cluttered.	Most components present but some missing or poorly formatted.	Submission lacks structure; key components are missing or unreadable.
Evaluation / Justification	Provides strong critical reflection on design choices, query logic, and assumptions; clearly justifies functional dependencies and schema decisions.	Offers thoughtful justification with some critical insight.	Provides basic justification; limited evaluation of alternative approaches.	Justification is brief, descriptive rather than critical.	Little or no attempt to justify choices; explanations are inaccurate or absent.

Sample SQL and Functional Dependency Answer Help

SmartProjects SQL Query Help – Sample Answer

An effective response to Part B begins with a clean relational implementation of the SmartProjects schema, followed by focused queries that target specific business questions such as high-budget projects and above-average earners. A typical solution uses COUNT(*) with a WHERE clause to obtain the number of projects exceeding an $85,000 budget threshold, which mirrors common textbook examples for aggregate filtering in introductory database units. For the salary question, students usually define a subquery that calculates the overall average salary and then select employees whose salary exceeds that value; this pattern models standard solutions found in database teaching materials such as “Database System Concepts” which emphasise the use of nested queries for comparative filtering (Database System Concepts). In practice, a correct query for project N2 must join EMPLOYEE, WORK, and PROJECT on their key relationships, and then restrict the result set to rows where the project identifier equals ‘N2’; this approach tests understanding of both equi-joins and selective predicates. Finally, a solid answer to the Computer Science department query joins EMPLOYEE and DEPARTMENT using the DepartmentNo attribute and filters on the department name, which checks whether students can correctly relate semantic requirements to their stored data model.

Relational Design and Dependency Insight

The functional dependency analysis of the STUDENT table typically assumes that the combination of student number and course number forms the primary key, which leads to dependencies such as no → Sname, address and courseno → course_name, course_instructorID. A high-quality submission explains that instructor-related attributes such as InstructorName and Instructor_office depend on course_instructorID, not directly on the student, and therefore the table exhibits transitive dependencies that can cause update anomalies. Good answers map these dependencies to normal forms by showing that the table may be in 1NF but violates 3NF, which motivates decomposition into distinct STUDENT, COURSE, and INSTRUCTOR relations, a design strategy consistently recommended in modern database design texts like Connolly and Begg’s “Database Systems: A Practical Approach to Design, Implementation, and Management” (Database Systems: A Practical Approach). Stronger scripts sometimes go further and discuss the impact of partial dependencies on composite keys and why careful identification of determinants helps maintain data integrity over time.

• Clarifying that InstructorName and Instructor_office should not be repeated for every student-course row prevents redundant storage and reduces the risk of inconsistent instructor details.
• Recognising that course_name depends only on courseno encourages a cleaner COURSE table that can be reused across many enrolment scenarios.

Common SQL and Modelling Questions

Many students later search for guidance on why their SQL query for “employees earning more than the average salary” returns no rows or incorrect results, and the answer usually lies in how they structure the subquery and alias their aggregate value. A reliable pattern is to compute the average salary in a scalar subquery in the WHERE clause rather than trying to use the aggregate directly alongside non-aggregated columns, which conflicts with SQL grouping rules in most DBMSs. Others ask whether the STUDENT table really needs a composite key when a single surrogate key appears simpler; here, instructors often point back to the functional dependencies, emphasising that natural keys derived from domain rules remain important for understanding how entities relate and for designing robust constraints. When model revisions are considered, a helpful teaching strategy is to compare the original unnormalised STUDENT table with its decomposed version and show concrete examples of insertion, update, and deletion anomalies that disappear after normalisation, as highlighted in practical case examples in database pedagogy literature (Teaching Database Design). These follow-up clarifications, when embedded with worked SQL examples, tend to align well with students’ search behaviour for “sample SQL query above average salary” or “functional dependency example for student course instructor table”.

1. Students often benefit from writing the average salary subquery independently, testing it, then embedding it in the main query once they confirm the value.
2. Rewriting the STUDENT table into separate entities provides a concrete pathway from messy real-world data to a clean relational design that supports reliable reporting.
3. Comparing results from pre- and post-normalisation schemas in a small test dataset can help students see how redundancy and anomalies disappear in practice.

Suggested References / Learning Resources

Use recent, peer-reviewed or authoritative sources to support your understanding of relational modelling, SQL, and functional dependencies. Examples include:

• Elmasri, R., & Navathe, S. B. (2016). Fundamentals of Database Systems (7th ed.). Pearson. Available via Google Books and major academic libraries: https://books.google.com/books?id=NMiQoQEACAAJ
• Silberschatz, A., Korth, H. F., & Sudarshan, S. (2019). Database System Concepts (7th ed.). McGraw-Hill. DOI-style resource page: https://dl.acm.org/doi/10.5555/3291121
• Connolly, T., & Begg, C. (2015). Database Systems: A Practical Approach to Design, Implementation, and Management (6th ed.). Pearson. https://doi.org/10.4324/9781315184034
• Sirait, E. R., & Hermanto, D. (2020). Design of relational database using normalization and functional dependency. Journal of Physics: Conference Series, 1456(1), 012006. https://doi.org/10.1088/1742-6596/1456/1/012006
• Coronel, C., & Morris, S. (2019). Database Systems: Design, Implementation, & Management (13th ed.). Cengage Learning. Accessible through institutional eBook platforms.

Ensure you adapt the referencing style (IEEE or APA 7th) to match your unit outline or campus requirements and remain consistent throughout your assignment.

Write an MN405 Assignment 1 submission that implements the SmartProjects database, answers SQL queries, and explains functional dependencies, in a clear, well-structured report with quiz summary included.

Prepare a 3–5 page MN405 Data and Information Management Assignment 1 report that documents your SmartProjects MS Access tables, SQL query results, and functional dependency analysis for the STUDENT table.

MN405 SmartProjects database and SQL assignment brief with quiz summary, SQL examples, and functional dependency guidance.

 Assessment – Week 9 / Assignment 2

Assessment: Assignment 2 – Normalisation and Advanced SQL (Weeks 9–10)

Overview/Description (proposed): Building on Assignment 1, students will normalise an extended version of the SmartProjects and STUDENT schemas to at least Third Normal Form, justify the resulting design, and then implement additional tables and advanced SQL queries. The task will require a short written explanation (approximately 800–1000 words) that describes each decomposition step and links specific functional dependencies to the chosen normal forms, along with a set of SQL scripts that demonstrate use of GROUP BY, HAVING, nested subqueries, and outer joins. Submissions will include updated ER and relational diagrams, revised MS Access (or equivalent) implementation screenshots, and a brief reflection on how normalisation improved data integrity and query reliability.