Assessment Details and Submission GuidelinesUnit Code
Unit Title
Assessment
Type
Term, Year
Assessment
Title
Purpose of
the
assessment
(with ULO
Mapping)
BN204
Database Technologies
Assignment 2 (Group)
T2, 2023
Data model development and implementation
The purpose of this assignment is to develop data models and map Database System into a
standard development environment to gain understanding of data model development.
Then implement the data model using a commercially available database management
system development tool.
On completion of this assignment students will be able to:
a. Describe issues related to the integrity and security of database (DB) systems.
b. Make an informed and critical assessment of database management systems
(DBMS).
c. Develop data models and implement DB systems.
d. Analyse business decisions related to DB information systems.
e. Demonstrate skills in building a database application using a commercially
available database management system development tool.
Weight
Total Marks
Word limit
Due Date
Description
of this
assignment:
25% of total assessment for the unit
Report 70 (Part A) + Part B Performance evaluation (10 marks)
Total mark will be calculated based on the marks you scored for Part B
No specific word limit
week 11 : 1st Oct 2023 11:55PM
This assignment consists of two parts:
Part A: The creation of an ER diagram, checking data integrity and implementation.
Submit both the database that you created and the word document.
Part B: Performance Evaluation- each team member will present their contribution
to the assignment to the tutor during week 11
Submission
Guidelines
•
All work must be submitted on Moodle by the due date. Submit both the database that
you created and the word document.
• Write your answers in a document and save as
“BN204__Assigment2_Group_no.doc”.
• The assignment must be in MS Word format, 1.5 spacing, 11-pt Calibri (Body) font and
2.5 cm margins on all four sides of your page with appropriate section headings.
• Reference sources must be cited in the text of the report, and listed appropriately at the
end in a reference list using IEEE referencing style.
Prepared by: Dr. Deepani Guruge
Moderated Dr. Praneel Chand
April 2023
BN204
Database Technologies
Page | 2 of 2
Extension
• If an extension of time to submit work is required, a Special Consideration Application
must be submitted online to AMS follow the link below you may require to login. You
must submit this application three working days prior to the due date of the assignment
• https://online.mit.edu.au/ams/PublicDocs/Student/MIT_Special_Consideration_Applica
tion.pdf
Academic
Misconduct
• Academic Misconduct is a serious offence. Depending on the seriousness of the case,
penalties can vary from a written warning or zero marks to exclusion from the course or
rescinding the degree. Students should make themselves familiar with the full policy and
procedure available at:
•
https://www.mit.edu.au/about-mit/institute-publications/policies-procedures-andguidelines/MIT-Student-General-Misconduct-Policy-Procedure
• For further information, please refer to the Academic Integrity Section in your Unit
Description.
Prepared by: Dr. Deepani Guruge
Moderated by: Dr. Praneel Chand
Aug2023
BN204
Database Technologies
Page | 3 of 3
Assignment Description
➢ Answer all questions. Write your answers in the separate word file.
➢ Do not write questions from the assignment in your answer file to avoid plagiarism detection.
➢ Clearly write section and question number as per this assignment in your answer file
Part A: (70 marks) – Database Design
1. Database Design
The School of Information Technology & Engineering (SITE) at MIT would like to help its Bachelor of
Networking students with placement in various companies and firms. To facilitate this, they need to
design a database. This database will store details of scheduled interviews, and facilitate searches by
students and companies that are looking for candidates. MIT’s IT Manager provided following business
rules to the software developer:
Assume that you are the database developer for this task, complete the information-level design for
the new database “BNet_training” that satisfies the constraints and user view requirements given
below.
User view 1 requirement: The students who want to find placements in various companies and firms,
need to sign up by entering Student’s first name, last name, student id number, driver’s license
number and expected graduation date.
User view 2 requirements:
Registered companies post one or more job openings. For each job posting, the date, application
deadline of the posting, title of the position, base salary, minimum requirements, and description are
recorded.
User view 3 requirements: One student can apply for many open positions (Job postings) in different
companies, and the date and time of each application is recorded. Students should also be able to see
the status of their application (whether there will be a follow-up interview and whether they were
turned down for the position).
User view 4 requirements: The database should have information about companies, interviewers,
and interviews, students, and job openings (Job postings). A company has an identifier, name, main
contact number and main fax number.
User view 5 requirements:
Interviews can be facilitated by MIT and interviews are conducted by the respective companies. It is
required to keep track of the date and time of the interview, conference room location, which
employees interviewed which students, as well as the result of the interview. A company can reserve
MIT conference rooms, and the company needs to specify the resources needed (e.g. computer,
projector, etc.).
User view 6 requirements: An interviewer is an employee of a company, and he/she has an employee
id, telephone number and position title.
Prepared by: Dr. Deepani Guruge
Moderated by: Dr. Praneel Chand
Aug2023
BN204
Database Technologies
Page | 4 of 4
Note: Write down any assumptions you make if they are not explicitly described here in user
requirements.
a. ER diagram –
Create a Crow’s Foot Notation Entity Relationship Diagram (ERD) to support the business
operations.
I. If you make any assumptions about data that are not explicitly given in the problem,
these must be described.
[5 Marks]
II. You are required to use a software tool to create the ER diagram. You can use
draw.io[2], Lucidchart [1] Visio or any other software tool to create the ER diagram.
[25 Marks]
2. Data Integrity
a. Represent entities and attributes you listed above as collection of tables and attributes.
Eg. Student (ID, Sname,…….
NB: Select suitable primary key for each table and underline them.
[5 Mark
b. Determine the functional dependences.
[5 Marks]
c. Normalise these tables. Make the normalization to 3NF and explain every step in the process.
[10 Marks]
3. Model Building
[ 10 Marks]
o Build this model using MS Access/SQLite by creating these tables and relationships.
o Populate these tables with appropriate data, at least 2 records in each table.
o Include following into your MS Word document.
I. If you are using MS Access include following screen shots
▪ Relationship diagram created in Access. (Select database tools →Relationships in the
Access menu)
▪ Data sheet view and design view of your tables as given below in Figure 1 and 2. For
customer_Name and email you need to use your MIT email and your name as one
data record.
Figure 1 Data Sheet View
Figure 2 Design View
II. If you are using SQLite
• Include screen shots of CREATE TABLE command and INSERT INTO commands.
Eg.
Prepared by: Dr. Deepani Guruge
Moderated by: Dr. Praneel Chand
Aug2023
BN204
Database Technologies
Page | 5 of 5
Figure 3 CREATE TABLE command in SQLite 1
4. Report Generation
a. Each team member write at least one query to extract useful information from this database.
Team members name and the queries should be listed separately in the assignment. You also
need to execute this query on the database and attach the screenshot of your output.
[10 Marks]
Part B: (10 marks)
PERFORMANCE
EVALUATION
5. PERFORMANCE EVALUATION
[10 Marks]
Project Plan Demonstration
In addition to your written report, you should demonstrate your work to your tutor during week 12
lab classes. Each team member will present their contribution to the assignment
This will allow tutors to assess your understanding of skills that are required in your assignment:
1. Model organisational information requirements using conceptual data modelling techniques.
2. Convert the conceptual data models into relational data models and verify their structural
characteristics with normalisation techniques.
3. Implement and utilise a relational database using a database system.
You will be evaluated by the teacher on your ability to perform specific tasks and the products you
created as described in the previous section. Your final mark for each assignment will be calculated
based on this evaluation.
Your Final marks will be calculated based on your demonstration
NOTE:
If you fail to demonstrate the skills required to complete the assignment you will get zero for the
assignment.
Prepared by: Dr. Deepani Guruge
Moderated by: Dr. Praneel Chand
Aug2023
BN204
Database Technologies
Page | 6 of 6
Bibliography
[1] Lucidchart, “ER Diagram Tool,” [Online]. Available: https://lucid.app/documents#/dashboard.
[Accessed Dec 2022].
[2] Draw.io. [Online]. Available: https://www.draw.io/. [Accessed August 2023].
Marking criteria:
Example of marking criteria is shown in the following table. Marks are allocated as follows:
PART A
Section to be included in the report
Database Design
1. ER diagram
Marks
any assumptions about data that are
not explicitly given in the problem,
Represent the structure of your
database visually.
5
25
a. Data Integrity – Evaluate
your proposed solutions
Represent entities and attributes as
collection of tables
5
2. functional dependences (FD)
Determine the functional
dependences
Analyse above tables and normalise
Model building on MS Access or MS
SQL Server
Example for useful report
5
10
Total marks for the Report
70
a. Normalise tables
b. Model building
4. Report Generation
PART B
Description of the section
PERFORMANCE EVALUATION
10
10
10
Final mark =
Total group report marks x (your
mark for the performance /10)
FINAL MARK
Example Marking Rubric for Assignment 2 Report: Total Marks 70 for the report
Grade
Mark
HD
100%
Prepared by: Dr. Deepani Guruge
DISTINCTION
80%
CREDIT
60%
PASS
40%
Moderated by: Dr. Praneel Chand
Fail
0-40%
Aug2023
BN204
Database Technologies
Assumptions
/5
Demonstrated
excellent ability
to think critically
and identified
relationships.
ER Diagram
Identify all
Entities and
Relationships,
Bridge table
/25
Extremely good
effort. All
elements are
present and very
well integrated.
Outstanding
Represent user
Views as Tables
/5
Page | 7 of 7
Demonstrate
d good ability
to think
critically and
identified
relationships.
Good effort.
Most
components
are present
and well
integrated.
Demonstrated
ability to think
critically and
identify some
relationships.
Demonstrated
some ability to
think critically.
Did not
demonstrate
ability to think
critically.
Good effort
made but not
outstanding.
Made some
effort with
inaccuracies.
Very little
attempt. Lazy
effort with
inaccuracies.
Analysed clearly.
Soundly
analysed.
Generally
relevant and
analysed.
Some relevance
and briefly
presented.
Not relevant to
the assignment
question.
Functional
Dependency
/5
All elements are
present and very
well integrated.
Representation
lacks structure.
Logic is very clear
and easy to
follow.
Components
present and
mostly well
integrated
Mostly
consistent and
logical.
Most
components
present
Normalise table
/10
Components
present and
well
integrated
Logic is clear
and
convincing.
Adequate
cohesion and
conviction.
Arguments are
confused and
disjointed.
Model building
/10
Extremely good
effort.
Good effort.
Good effort.
Made some
effort with
inaccuracies.
Very little
attempt.
4. Report
Generation
Write a query
/10
All elements are
present.
Most
components
are present.
Made but some
components
are missing.
Made some
effort with
inaccuracies.
Lazy effort with
inaccuracies.
Prepared by: Dr. Deepani Guruge
Moderated by: Dr. Praneel Chand
Aug2023
