Data and Information Management Report 2018

This report discusses the database entities, tables and attributes, business rules, functional dependency, normalized tables, ER diagram, MS-Access database, and SQL queries related to data and information management.

Data and Information Management
May 29
2018

Report
1 A) Database Entities
1. TableParticipant: Participants detail is stored in this table.
2. TableWorkshop: Workshops detail is stored in this table.
3. TableOrganizer: Organizers detail is stored in this table.
4. TableWorkshopParticipant: WorkshopParticipants detail is stored in this table.
5. TableMeetingRoom: Meeting Rooms detail is stored in this table.
6. TableScheduleWorkshop: Workshop Schedule detail is stored in this table.
7. TableWorkshopRoom: Rooms in which workshops held is stored in this table.
1 B) Tables and Attributes
TableParticipant (FieldParticipantID, FieldParticipantName, FieldAddress, FieldState,
FieldEmail, FieldPhone)
TableOrganizer (FieldOrganizerD, FieldOrganizerName, FieldAddress, FieldState,
FieldEmail, FieldPhone)
TableWorkshop (FieldWorkshopID, FieldWorkshopName, FieldOrganizerID)
TableWorkshopParticipant (FieldParticipantID, FieldWorkshopID)
TableScheduleWorkshop (FieldWorkshopID, FieldStartDate, FieldEndDate, FieldDays)
TableMeetingRoom (FieldMeetingRoomID, FieldFloorNumber, FieldConferenceVenue,
FieldCapacity)
TableWorkshopRoom (FieldWorkshopID, FieldRoomNumber, FieldStartDate,
FieldEndDate)
2 | P a g e

1 C) Business Rules
- The participant has to participate in workshop. He can participate in more than one
workshop.
- The workshop will be in same room, even if it lasts more days.
1 D) Functional Dependency
FieldParticipantID  FieldParticipantName, FieldAddress, FieldState, FieldEmail,
FieldPhone
FieldOrganizerD  FieldOrganizerName, FieldAddress, FieldState, FieldEmail, FieldPhone
FieldWorkshopID  FieldWorkshopName, FieldOrganizerID, FieldStartDate,
FieldEndDate, FieldDays, FieldRoomNumber
FieldMeetingRoomID  FieldFloorNumber, FieldConferenceVenue, FieldCapacity
1 E) Normalized Tables
First normal form may be attained by following rules-
1. There is no repeated group in any table. Make separate tables for each group.
2. Make primary key in all tables.
Second normal form may be attained by following rules-
1. All tables are satisfying first normal form rules.
2. All fields are depending on the primary key value.
Third normal form may be attained by following rules-
1. All tables are satisfying second normal form rules.
2. Remove transitive dependencies from all tables.
3 | P a g e

3 NF Tables
TableParticipant (FieldParticipantID, FieldParticipantName, FieldAddress, FieldState,
FieldEmail, FieldPhone)
TableOrganizer (FieldOrganizerD, FieldOrganizerName, FieldAddress, FieldState,
FieldEmail, FieldPhone)
TableWorkshop (FieldWorkshopID, FieldWorkshopName, FieldOrganizerID, FieldStartDate,
FieldEndDate, FieldDays, FieldRoomNumber)
TableWorkshopParticipant (FieldParticipantID, FieldWorkshopID)
TableMeetingRoom (FieldMeetingRoomID, FieldFloorNumber, FieldConferenceVenue,
FieldCapacity)
1 F) ER Diagram
(Cinergix Pty Ltd. 2011)
(Lucid Software Inc. 2015)
Assumptions
- Organiser’s detail is also very important for data storage.
- Workshop start and end time is also very important for data storage.
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
Workshop detail is being shown in the query.
6 | P a g e

Query 2
This query is showing participants who do not belong to Victoria and participated in workshop.
Query 3
This query is showing workshop detail along with room number.
7 | P a g e

References
Cinergix Pty Ltd. (2011). Ultimate Guide to ER Diagrams [online]. Available from:
http://creately.com/blog/diagrams/er-diagrams-tutorial/ [Accessed May 2018].
Lucid Software Inc. (2015). ER Diagram Symbols and Meaning [online]. Available from:
http://www.sparxsystems.com/enterprise_architect_user_guide/modeling_languages/
data_flow_diagrams.html [Accessed May 2018].
8 | P a g e