University Data and Information Management Assignment 2 Report

This report provides a comprehensive solution to a data and information management assignment. It begins by defining database entities such as Participant, Workshop, Organizer, ParticipantWorkshop, MeetingRoom, WorkshopSchedule, and WorkshopRoom. The solution then details the tables and attributes for each entity, followed by a discussion of the business rules governing the database design. Functional dependencies are identified, and the process of normalizing the tables to the first, second, and third normal forms is explained. An ER diagram visually represents the relationships between the entities. The report includes the design of an MS-Access database and demonstrates SQL queries to retrieve and manipulate data from the database. Finally, the report provides references to external sources consulted during the assignment.

Student ID –
Date -
Assignment 2
Data and Information Management
Module Tutor –
1 | P a g e

Report
1 A) Database Entities
1. Participant: It is storing the data related to participants.
2. Workshop: It is storing the data related to workshops.
3. Organizer: It is storing the data related to organizers who are organizing the
workshops.
4. ParticipantWorkshop: It is storing the data related to participants and their
workshops.
5. MeetingRoom: It is storing the data related to meeting rooms in which the workshops
held.
6. WorkshopSchedule: It is storing the data related to schedule of workshops e.g. start
date, end date etc.
7. WorkshopRoom: It is storing the data related to workshops and meeting rooms in
which the workshops held.
1 B) Tables and Attributes
Participant (ParticipantID, ParticipantName, Address, State, Email, Phone)
Organizer (OrganizerD, OrganizerName, Address, State, Email, Phone)
Workshop (WorkshopID, WorkshopName, OrganizerID)
Participant Workshop (ParticipantID, WorkshopID)
WorkshopSchedule (WorkshopID,StartDate, EndDate, Days)
MeetingRoom (MeetingRoomID, FloorNumber, ConVenue, Space)
WorkshopRoom (WorkshopID, RoomNumber, StartDate, EndDate)
2 | P a g e

1 C) Business Rules
Following business rules are used in the database design-
- Each participant will participate in at least one workshop.
- The participant may participate in more than one workshop.
- An organizer may organize any number of workshops.
- The workshop may last for more than 1 day.
- The workshop will held in one room for all days.
1 D) Functional Dependency
ParticipantID  ParticipantName, Address, State, Email, Phone
OrganizerD  OrganizerName, Address, State, Email, Phone
WorkshopID  WorkshopName, OrganizerID, StartDate, EndDate, Days, RoomNumber
MeetingRoomID  FloorNumber, ConVenue, Space
(Janalta Interactive Inc. 2015)
1 E) Normalise Tables
First Normal Form
First normal form is achieved by applying following rules-
- Delete the repeated group from tables and make new tables.
- Set primary key in all newly created tables.
Second Normal Form
Second normal form is achieved by applying following rules-
- Make sure that all tables are in first normal form.
- All fields depend upon the primary key only.
Third Normal Form
Third normal form is achieved by applying following rules-
3 | P a g e

- Make sure that all tables are in second normal form.
- Delete all transitive dependencies.
Participant (ParticipantID, ParticipantName, Address, State, Email, Phone)
Organizer (OrganizerD, OrganizerName, Address, State, Email, Phone)
Workshop (WorkshopID, WorkshopName, OrganizerID, StartDate, EndDate, Days)
ParticipantWorkshop (ParticipantID, WorkshopID)
MeetingRoom (MeetingRoomID, FloorNumber, ConVenue, Space)
WorkshopRoom (WorkshopID, RoomNumber, StartDate, EndDate)
1 F) ER Diagram
(CS Odessa Corp. 2016)
Assumptions
4 | P a g e

- The organizer detail will be stored into the database.
- The workshop start date and end date will be stored into the database.
2) MS-Access Database
Participant Table
Workshop Table
Organizer Table
5 | P a g e

ParticipantWorkshop Table
MeetingRoom Table
WorkshopRoom Table
6 | P a g e

3) SQL Queries
Query 1
Select * from Workshop;
Query 2
select participantName, email from Participant where participantID in (select ParticipantID from
ParticipantWorkshop) and State<>'VIC';
Query 3
SELECT Workshop.WorkshopName, MeetingRoom.RoomNumber, MeetingRoom.FloorNumber,
MeetingRoom.Capacity FROM Workshop INNER JOIN (MeetingRoom INNER JOIN WorkshopRoom
ON MeetingRoom.RoomNumber = WorkshopRoom.RoomNumber) ON Workshop.WorkshopID =
WorkshopRoom.WorkshopID;
7 | P a g e

References
CS Odessa Corp. (2016). Design elements – ERD (crow’s foot notation) [online]. Available
from: http://www.conceptdraw.com/examples/crowfoot-notation [Accessed: 24-May-2018]
Janalta Interactive Inc. (2015). Functional Dependency [online]. Available from:
http://www.techopedia.com/definition/19504/functional-dependency [Accessed 24-May
2018].
8 | P a g e