NIT5130 | Database Analysis and Design Assignment | Database

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 entitie

VU Sydney

  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%

 

For example STUDENT (StudentID, FirstName, SecondName, Phone). This relation is in 1NF as it does not contain any repeating groups and show primarykey.

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. A.Implement your data dictionary to create a relational database in PhPMyAdmin.
  1. B.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. C.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 functions

Solution