NIT5130 | Database Analysis and Design Assignment 2 | Data Analysis

23.55 Scenario for assignment

Suppose the Victoria University, International Office (IO) needs a database to assist simple and fast data entry and print reports. The key information is collected from the students during their enrolment. In order to make this process fast and easy for the students, the data entry has to be as capable as possible. If the data entry takes too long the student may become unconcerned in joining the university.

Order Now

The director at IO requires reporting features to facilitate them to help with rostering, roles and membership email lists. Some such reports include: organising a roster for the volunteers, finding all of the people interested in organizing a social function, printing a list of contacts etc. This will be achieved by creating a database using PhPMyAdmin and WordPress (for forms and reports design) and making use of supporting features like: forms, queries and reports. A user manual is necessary to this project as the managers are novice computer users and will require assistance for using the database.

IO Background

IO, as the acronym recommends, is planned to help the students by providing a friendly place where they can meet other part-time or full-time students. Facilities and services include: a pool table, tea and coffee, a kitchen, few games and a number of social events for the students to attend. If the students have an hour break among classes then they can come to the IO lounge and relax on the couches, play pool, chat over a coffee and so on. In regard for these facilities to stay hygienic and well maintained, certain rosters are set up for the student members. These rosters are generated from the database of students wishing to volunteer for a particular job or jobs. From time to time notices and documents are sent out to the members to remind them of their allocated duties and to inform them of upcoming events and activities.

A range of jobs that need to be accomplished at the IO lounge require volunteers to complete certain tasks like, washing windows, vacuuming floors, mopping etc, and these may need to be done at certain times of the day and/or week. IO would like the database to keep track of the tasks required in each job. They would also like a record of who does what job on what day and whether it was completed or not. Moreover, University faculties, schools and the course, that students are in, are very significant to IO so that they can categorize people into study groups and for mentoring purposes. A new member can be introduced to people doing the same course or in the same faculty or school. IO would like the database entry screens to be designed for simple and easy navigation of information in the database. The first screen should contain contact details. The second screen should be about what IO can do for them, and the third screen should be what the student can do for IO

How IO process work

At the time of enrolment, the interested students fill out a form giving their contact details to IO. They also indicate whether they would like to help out in the duties involved within the university. The databases are updated with new members every year. This guarantees that the current database is up-to-date. Reports are then generated from the database that helps the IO team synchronize and locate volunteers.

The database reports make it easy and simple to generate rosters, mailing lists, organise social functions and meet nutritional needs of the students. IO would like the make the data entry as simple and easy as possible for the student enrolment. They would like the reports to be easy to produce. They also recommended that each school in the university should be linked to its associated Faculty. So that somebody cannot enter “Faculty of Information Technology” and “School of Law” as this is incorrect.

Task 1: Database Modelling (Total marks: 50%)

1.1.       Develop an Entity Relationship (ER) model to characterize the information requirements of business illustrates in the case study mentioned above. The diagram of your ER model must: -Marks: 25%

  1. Demonstrate all necessary entities
  1. Demonstrate all attributes
  1. The relationships between entities
  1. Demonstrate unique identifiers and nature of interaction among entities

1.2.

Discovered business rules for your ERD

-Marks: 5%

1.3.

Map the Entity Relationship Diagram (ERD) into a set of relations in at least Third Normal

 

Form (3NF). You should show that each relation satisfies the rules for 1NF, 2NF, and 3NF.

 

-Marks: 20%

 

 

Data dictionary:

Your report you must also include a data dictionary. You should briefly describe all entities and attributes are included, descriptions are clear, reasonable field formats, etc. For example,

 

 

 

Data

 

Other

Attributes

Meaning

Type

 

 

 

 

 

 

Student No.

A   unique

Integer(8)

Index – Yes

 

identifier

 

 

(No Duplicate)

 

of Students

 

 

 

Given Name

First  name

Text(20)

 

Example,

 

of students

 

 

“Reuben”

Family

Family

 

Text(20)

 

Example,

Name

name

of

 

 

“Steel”

 

students

 

 

 

 

Phone No.

Phone

no.

Integer(12)

Not Null

 

of students

 

 

 

Street No.

Street

no.

Integer(6)

Not Null

 

of students

 

 

 

City

City where

Text(10)

 

Example,

 

students

 

 

 

“Melbourne”

 

live

 

 

 

 

State

State

 

Integer(6)

Example,

 

where

 

 

 

“Victoria”

 

student

 

 

 

 

 

belong

 

 

 

 

Post code

Postcode

Integer(8)

Not Null

 

of students

 

 

 

Job Type

Job type of

Text(15)

 

Example,

 

students

 

 

 

“Part –Time”

Positions

Positions

Text(20)

 

Example,

 

of Students

 

 

“Mentoring”

Roaster

Schedule

Date

&

DD/MM/YYYY

 

of work

 

Time

 

 

Facility

Type

of

Text(20)

 

Example,

Type

facility

 

 

 

“pool-table”

 

Task 2: Database Implementation (Total Marks: 50%)

  1. Implement your data dictionary to create a relational database in PhPMyAdmin.
  1. Populate the database with sample data. Adequate sample data must be included to show that the queries, view(s), and report(s) work properly
  1. Design the following three forms and two reports, requiring a separate query to assure every of the following information needs:
  1. A Student information entry form
  1. A form to allow the director to change Member details and update the job and roster information without losing data integrity
  1. A report that prints the students address details
  1. A form for the director to fill in the jobs for the up-coming week with the volunteers available
  1. A report that lists all of the people with particular dietary requirements so that this can be given to the chef before social function

Grade

Description

 

 

A

Confirmed a clear understanding of the concepts and issues applicable to the

 

tasks, and their wider implications, in a substantiated view that was organised and

 

cohesive.  Demonstrated  a  very  high  level  of  scholarship.  Used  fluent  and

 

persuasive language with assurance and precision, to achieve a highly effective

 

communication with the reader.

B

Demonstrated  understanding  of  the  complexities  of  the  concepts  and  issues

 

relevant  to  the  tasks.  Achieved  effective  selection  and  ordering  of  ideas.

 

Demonstrated a high level of scholarship. Presented a view on the proposition with

 

confidence, using fluent and persuasive language

C

Recognized major aspects of the tasks. Demonstrated an adequate level of

 

scholarship. Achieved a coherent ordering of ideas and expressed a view in clear

 

and accurate language

D

Recognized some aspects of the tasks. Demonstrated some evidence of

 

scholarship. Presented some ideas relevant to the proposition. Demonstrated

 

adequate organisation and language control

 

 

Information of your Group Member

  • Identify the members of your team with full names and student numbers. Groups are to consist of three to four members only.
  • Regulate your own group efforts. Any problems that arise, such as group members not contributing a fair amount of the work, must be resolved within the group. Lecturer and tutors will not intervene – so select your group members carefully

Submission Procedures and Required Forma

Your team’s assignment report is to be written in English, on A4 pages. The report should be written in clear, plain language, so that non-experts can easily understand it. Your report should be addressed to the Managing Director of your proposed organisation, and should briefly re-state the tasks you were required to perform and detail the material produced. Include brief instructions on how to view your system structures, design and implemented. Please note that the Managing Director is familiar with computers, but has a very limited understanding of technologies such as PhPMyAdmin and WordPress. Attach all relevant designs and support materials in an appendix.

Assignment submission will be online to VU Collaborate, your lecturer will guide you further about the process. All sources quoted in your report should be detailed in a Reference Section.

The report must be your own group’s work, i.e., it must be the result of your own reading, thought and discussion, and the expression of the ideas must be yours.

The submitted work will therefore comprise:

  1. Your group’s report
  1. Relevant designs and support material

Solution