Data and Information Management MN405 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 MN405 Assignment 2.

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