MN405 Data and Information Management: Data Model Development

This assignment solution for MN405 Data and Information Management focuses on data model development and implementation. It includes identifying database entities, defining tables and attributes, establishing business rules, and determining functional dependencies. The report covers normalization up to 3NF, presents an ER diagram, and showcases an MS-Access database implementation. Furthermore, it provides SQL queries for retrieving specific information from the database, such as workshop details and participant information. The document also includes references to external resources used in the development of the data model.

Data and Information Management MN405
Assignment 2
5/31/2018

Report
1 A) Database Entities
1. TabParticipant: It is enclosing the participant’s information.
2. TabWorkshop: It is enclosing the workshop’s information.
3. TabOrganizer: It is enclosing the organizer’s information.
4. TabWorkshopParticipant: It is enclosing the workshop and participant’s
information.
5. TabMeetingRoom: It is enclosing the meeting room’s information.
6. TabScheduleWorkshop: It is enclosing the workshop schedule’s information.
7. TabWorkshopRoom: It is enclosing the workshop and room’s information.
1 B) Tables and Attributes
TabParticipant (FParticipantID, FParticipantName, FAddress, FState, FEmail, FPhone)
TabOrganizer (FOrganizerD, FOrganizerName, FAddress, FState, FEmail, FPhone)
TabWorkshop (FWorkshopID, FWorkshopName, FOrganizerID)
TabWorkshopParticipant (FParticipantID, FWorkshopID)
TabScheduleWorkshop (FWorkshopID, FStartDate, FEndDate, FDays)
TabMeetingRoom (FMeetingRoomID, FFloorNumber, FConferenceVenue, FCapacity)
TabWorkshopRoom (FWorkshopID, FRoomNumber, FStartDate, FEndDate)
2 | P a g e

1 C) Business Rules
- A participate may involved in any number of workshops.
- One workshop will be in the same room from starting to end.
1 D) Functional Dependency
FParticipantID  FParticipantName, FAddress, FState, FEmail, FPhone
FOrganizerD  FOrganizerName, FAddress, FState, FEmail, FPhone
FWorkshopID  FWorkshopName, FOrganizerID, FStartDate, FEndDate, FDays,
FRoomNumber
FMeetingRoomID  FFloorNumber, FConferenceVenue, FCapacity
1 E) Normalized Tables
1 NF Tables
TabParticipant (FParticipantID, FParticipantName, FAddress, FState, FEmail, FPhone)
TabOrganizer (FOrganizerD, FOrganizerName, FAddress, FState, FEmail, FPhone)
TabWorkshop (FWorkshopID, FWorkshopName, FOrganizerID)
TabWorkshopParticipant (FParticipantID, FWorkshopID)
TabScheduleWorkshop (FWorkshopID, FStartDate, FEndDate, FDays)
TabMeetingRoom (FMeetingRoomID, FFloorNumber, FConferenceVenue, FCapacity)
TabWorkshopRoom (FWorkshopID, FRoomNumber, FStartDate, FEndDate)
3 | P a g e

2 NF Tables
TabParticipant (FParticipantID, FParticipantName, FAddress, FState, FEmail, FPhone)
TabOrganizer (FOrganizerD, FOrganizerName, FAddress, FState, FEmail, FPhone)
TabWorkshop (FWorkshopID, FWorkshopName, FOrganizerID, FStartDate, FEndDate,
FDays, FRoomNumber)
TabWorkshopParticipant (FParticipantID, FWorkshopID)
TabMeetingRoom (FMeetingRoomID, FFloorNumber, FConferenceVenue, FCapacity)
3 NF Tables
TabParticipant (FParticipantID, FParticipantName, FAddress, FState, FEmail, FPhone)
TabOrganizer (FOrganizerD, FOrganizerName, FAddress, FState, FEmail, FPhone)
TabWorkshop (FWorkshopID, FWorkshopName, FOrganizerID, FStartDate, FEndDate,
FDays, FRoomNumber)
TabWorkshopParticipant (FParticipantID, FWorkshopID)
TabMeetingRoom (FMeetingRoomID, FFloorNumber, FConferenceVenue, FCapacity)
(W3schools.in n.d.)
4 | P a g e

1 F) ER Diagram
Assumptions
- The information regarding workshop starting and end time will be stored into the
database.
- The information regarding ornaziers will be stored into the database.
5 | P a g e

2) MS-Access Database
(Freetutes.com n.d.)
Tables
Participant
Workshop Table
6 | P a g e

Organizer Table
WorkshopParticipant Table
MeetingRoom Table
3) SQL Queries
Query 1
This query is showing the complete information regarding workshop.
7 | P a g e

Query 2
This query is showing the information regarding participant out of Victoria and participated
in workshop.
Query 3
This query is showing the complete information regarding workshop and room number.
8 | P a g e

References
[1] W3schools.in, “Database Normalization,” n.d. [online]. Available:
https://www.w3schools.in/dbms/database-normalization/. [Accessed: May 31 2018]
[2] Freetutes.com, “ Types of attributes,” n.d. [online]. Available:
http://www.freetutes.com/systemanalysis/sa7-types-of-attributes.html. [Accessed: May 31
2018]
9 | P a g e