MN405 Data Management: Data Model Development and Implementation T1

This report presents a data model development and implementation project. It identifies database entities such as Participant, Workshop, and Organizer, and defines tables with attributes including primary keys. Business rules and functional dependencies are outlined, followed by normalization to 3NF. The report includes an ER diagram, MS-Access database implementation, and SQL queries to demonstrate data retrieval. The solution covers the MN405 Data and Information Management assignment requirements, focusing on data modeling, database design, and implementation using a commercial database management system.

Data and Information Management
Assignment 2
Student ID:
Module Tutor:
5/24/2018

Report
1 A) Database Entities
1. Participant: this table is accumulating the data correlated to participants.
2. Workshop: this table is accumulating the data correlated to workshops.
3. Organizer: this table is accumulating the data correlated to organizers.
4. WorkshopParticipant: this table is accumulating the data correlated to participants
and related workshops.
5. MeetingRoom: this table is accumulating the data correlated to meeting rooms.
6. ScheduleWorkshop: this table is accumulating the data correlated to workshop
schedule.
7. WorkshopRoom: this table is accumulating the data correlated to workshops and
related meeting rooms.
1 B) Tables and Attributes
Participant (FldParticipantID, FldParticipantName, FldAddress, FldState, FldEmail,
FldPhone)
Organizer (FldOrganizerD, FldOrganizerName, FldAddress, FldState, FldEmail, FldPhone)
Workshop (FldWorkshopID, FldWorkshopName, FldOrganizerID)
WorkshopParticipant (FldParticipantID, FldWorkshopID)
ScheduleWorkshop (FldWorkshopID, FldStartDate, FldEndDate, FldDays)
MeetingRoom (FldMeetingRoomID, FldFloorNumber, FldConferenceVenue, FldCapacity)
WorkshopRoom (FldWorkshopID, FldRoomNumber, FldStartDate, FldEndDate)
2 | P a g e

1 C) Business Rules
- A participant must have to participate in one workshop at least.
- A participant can participate in more workshops also.
- A workshop may last in 1 day or more days.
- Any number of days a workshop will held, the room will not be changed. It will be in
same room.
1 D) Functional Dependency
FldParticipantID  FldParticipantName, FldAddress, FldState, FldEmail, FldPhone
FldOrganizerD  FldOrganizerName, FldAddress, FldState, FldEmail, FldPhone
FldWorkshopID  FldWorkshopName, FldOrganizerID, FldStartDate, FldEndDate,
FldDays, FldRoomNumber
FldMeetingRoomID  FldFloorNumber, FldConferenceVenue, FldCapacity
(Adrienne Watt. n.d.)
1 E) Normalized Tables
First Normal Form
1. Make separate tables for related records.
2. Set one primary key for each table.
Second Normal Form
1. All tables are in 1 NF.
2. The primary key is the only dependent in each atbel.
Third Normal Form
1. All tables are in 2 NF.
2. Eliminate all transitive dependencies.
3 | P a g e

Tables converted to 3 NF
Participant (FldParticipantID, FldParticipantName, FldAddress, FldState, FldEmail,
FldPhone)
Organizer (FldOrganizerD, FldOrganizerName, FldAddress, FldState, FldEmail, FldPhone)
Workshop (FldWorkshopID, FldWorkshopName, FldOrganizerID, FldStartDate,
FldEndDate, FldDays, FldRoomNumber)
WorkshopParticipant (FldParticipantID, FldWorkshopID)
MeetingRoom (FldMeetingRoomID, FldFloorNumber, FldConferenceVenue, FldCapacity)
1 F) ER Diagram
(Oracle. 2000)
Assumptions
- The information of organizers is also necessary to be stored into database.
- Starting and ending time of all workshops is also necessary to be stored into database.
4 | P a g e

2) MS-Access Database
Participant Table
Workshop Table
Organizer Table
5 | P a g e

WorkshopParticipant Table
MeetingRoom Table
3) SQL Queries
Query 1
Select * from Workshop;
6 | P a g e

Query 2
SELECT participantName, email FROM Participant WHERE participantID in (select ParticipantID from
WorkshopParticipant) and State<>'VIC';
Query 3
SELECT Workshop.WorkshopName, Workshop.RoomNumber, MeetingRoom.FloorNumber,
MeetingRoom.Capacity FROM MeetingRoom INNER JOIN Workshop ON
MeetingRoom.RoomNumber = Workshop.RoomNumber;
References
Oracle. (2000). Drawing the Entity Relationship Diagram [online]. Available from:
http://docs.oracle.com/cd/A87860_01/doc/java.817/a81358/05_dev1.htm / [Accessed 4 May
2018].
Adrienne Watt. (n.d.). Chapter 11 Functional Dependencies [online]. Available from:
http://opentextbc.ca/dbdesign/chapter/chapter-11-functional-dependencies/ [Accessed: 2-
May-2018].
7 | P a g e