Data Model Development and Implementation Report - MN405 T1 2018

This report details the development and implementation of a data model, focusing on database entities, tables, and attributes. It outlines business rules, functional dependencies, and normalized tables, achieving 3NF. The report includes an ER diagram, MS-Access database implementation with participant, workshop, organizer, workshop participant, and meeting room tables. SQL queries are provided to demonstrate data retrieval, including workshop details and participant information. The document concludes with references to ER diagram resources, providing a comprehensive overview of the database design and implementation process. Desklib offers a wealth of similar solved assignments and past papers for students.

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