Data and Information Management Assignment 2 Report

This report covers database entities, tables and attributes, business rules, functional dependency, normalized tables, ER diagram, MS-Access database and SQL queries for Data and Information Management Assignment 2.

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