BN204 Database Technologies Page | 7 of 7
Assessment Details and Submission Guidelines
Unit Code
BN204
Unit Title
Database Technologies
Assessment Type
Assignment 2
Term, Yea
T1, 2019
Assessment Title
Data model development and implementation
Purpose of the assessment (with ULO Mapping)
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:
. Make an informed and critical assessment of database management systems.
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
20% of total assessment for the unit
Total Marks
100 = 30 (for online quiz- Part A XXXXXXXXXXfor Part B)
Word limit
No specific word limit
Due Date
Lab class week 11, Saturday 1st June 2018, 5.00PM
Description of this assignment:
This assignment consists of two parts:
Part A: An online quiz Assignment 2, which includes 30 questions. Students may have 3 attempts at this quiz. The system will select the highest grade.
Part B: The creation of an ER diagram and implementation
Submission Guidelines
· All work must be submitted on Moodle by the due date. Write your answers in this document underneath the question and save as
“BN204_T1_2019_Assigment2_your_name.doc”.
· The assignment must be in MS Word format, 1.5 spacing, 11-pt Cali
i (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.
· Submit both the database that you created and the word document.
Extension
· If an extension of time to submit work is required, a Special Consideration Application must be submitted directly to the School's Administration Officer, in Melbourne on Level 6 or in Sydney on Level 7. You must submit this application three working days prior to the due date of the assignment. Further information is available at:
http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/specialconsiderationdeferment
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: http:
www.mit.edu.au/about-mit/institute-publications/policies-procedures-and-guidelines/Plagiarism-Academic-Misconduct-Policy-Procedure. For further information, please refer to the Academic Integrity Section in your Unit Description.
1.
Assignment Description
Part A: (30 marks)
Paste below the summary of your Moodle Assignment 2 quiz. The quiz will be open after week 8
Part B: (70 marks)
1. Database Design
Knox private Hospital contracted with GreenIT services to develop and design their hospital database, the hospital IT Manager provided the following business rules to the GreenIT services software developer:
User view 1 requirement : Patients are identified by a PatientID, and their names, addresses, and ages must be recorded.
User view 2 requirement : Doctors are identified by a DoctorID. For each doctor, the name, specialty, and years of experience must be recorded.
User view 3 requirement : Every patient has a primary physician/doctor. Every doctor has at least one patient.
User view 4 requirement : Doctors prescribe drugs for patients. A doctor could prescribe one or more drugs for several patients, and a patient could obtain prescriptions from several doctors. Each prescription can have more than one drug.
User view 5 requirement : For each drug, identified by DrugID and the trade name and formula must be recorded.
User view 6 requirement : Each pharmacy sells several drugs and has a price for each. A drug could be sold at several pharmacies, and the price could vary from one pharmacy to another.
User view 7 requirement : Each pharmacy has a unique name, address, and phone number.
Complete the information-level design for the database that satisfies the above constraints and user view requirements. In order to complete this information-level design you are required to answer questions given below 1(a) – (f).
Analyse the all user requirements given above and answer the following questions.
a. Identify and list entities described in all user requirements given above. (5 Marks)
. Add attributes to these entities and represent them as a collection of tables and attributes as below.
Eg. Patient (PatientID, name, …)
NB: Select suitable primary key for each table and underline them. (10 Marks)
c. Outline at least 2 relationships between those entities. (5 Marks)
Eg. Every patient has a primary physician/doctor. - One-to-many,
d. Determine the functional dependences. (10 Marks)
Eg. PatientID name, address, age, ......
e. Normalise these tables. (10 Marks)
2. Represent the structure of your database visually by using the entity-relationship (E-R) diagram. If you make any assumptions about data that are not explicitly given in the problem, these must be described.
NB: You need to use Visio, draw.io [] or Lucidchart [] or any other software tool, to create the ER diagram. (15 Marks)
3. Build this model using MS Access/SQLite by creating these tables and Relationships. Populate these tables with appropriate data, at least 2 records in each table. Include following into your MS Word document. (5 Marks)
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. Eg.
Figure 1 Data Sheet View Figure 2 Design View
II. If you are using SQLite
· Screen shots of CREATE TABLE command and INSERT INTO commands. Eg.
Figure 3 CREATE TABLE command in SQLite 1
4. Report Generation
a. Write a query to generate one example for useful information that can be obtained from this “Knox private Hospital” database. (5 Marks)
. Execute the query on the database and attach the screenshot of your output XXXXXXXXXX Marks)
Bibliography
x
[1] Draw.io. (2018, December). [Online]. https:
www.draw.io
[2] Lucidchart. (2018, December) ER Diagram Tool. [Online]. https:
www.lucidchart.com/pages/tou
ER_diagram_tool
x
Marking criteria:
Example of marking criteria is shown in the following table. Marks are allocated as follows:
Section to be included in the report
Description of the section
Marks
PART A
Assignment 2 Quiz marks
Marks for Assignment 2 Quiz
30
PART B
1. Database Design
a. Identify all entities
Identify entities in all user requirements
5
b. User Views as Tables
Represent the User Views as a collection of Tables
10
c. Relationships
Determine the relationships between entities
5
d. Functional dependences.
Determine Functional dependences.
10
e. Normalise tables
Analyse above tables and normalise these
10
2. ER Diagram
Represent the structure of your database visually by using the ER diagram. Evaluate your proposed solutions
15
3. Model building
Model building on MS Access or MS SQL Serve
5
4. Report Generation
Example report
a. Write a query
5
b. Execute the query
5
Total marks or the Report
70
TOTAL
100
Example Marking Ru
ic for Assignment 2 Report: Total Marks 70 for the report
Grade
Mark
HD
100%
DISTINCTION
80%
CREDIT
60%
PASS
40%
Fail
0-40%
Identify all
Entities /5
Ability to think critically.
Relevant and soundly analysed.
Generally relevant and analysed.
Some relevance and
iefly presented.
Not relevant.
Represent user Views as Tables
10
Analysed clearly.
Soundly analysed.
Generally relevant and analysed.
Some relevance and
iefly presented.
Not relevant to the assignment question.
Relationships
5
Demonstrated excellent ability to think critically and identified relationships.
Demonstrated good ability to think critically and identified relationships.
Demonstrated ability to think critically and identify some relationships.
Demonstrated some ability to think critically.
Did not demonstrate ability to think critically.
Functional Dependency /10
All elements are present and very well integrated.
Components present and well integrated
Components present and mostly well integrated
Most components present
Representation lacks structure.
Normalise table
10
Logic is very clear and easy to follow.
Logic is clear and convincing.
Mostly consistent and logical.
Adequate cohesion and conviction.
Arguments are confused and disjointed.
ER Diagram
15
Extremely good effort. All elements are present and very well integrated.
Outstanding
Good effort. Most components are present and well integrated.
Good effort made but not outstanding.
Made some effort with inaccuracies.
Very little attempt. Lazy effort with inaccuracies.
Model building /5
Extremely good effort.
Good effort.
Good effort.
Made some effort with inaccuracies.
Very little attempt.
4. Report Generation
a. Write a query
5
All elements are present.
Most components are present.
Made but some components are missing.
Made some effort with inaccuracies.
Lazy effort with inaccuracies.
4. Report Generation
. Execute the query
5
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 Dr. Sharly Halder April,2019
Prepared by: Dr. Deepani Guruge XXXXXXXXXXModerated by: Dr. Sharly Halder April, 2019