Mn405 Database Entities Answers Assessment Answers
The organisers of the GreenID2018 international multi-conference need to keep track of a large collection of workshops associated with the event. Initial requirements analysis brings out the following User view requirements / business rules about what needs to be recorded. Assume that you are given a task to model the database by using these business rules.
Complete the information-level design of the database that satisfies the following constraints and user view requirements. In order to complete this information-level design you are required to answer questions 1(a) to 1(f).
User view 1 requirement / business rule
- There are several participants, each of which may sign up to one or more workshops.
User view 2 requirement / business rule
- Each workshop has a name, ID and an identified organizer .
- Some workshops happen on a particular date and some workshops last more than one day. You may need separate entity to store days.
User view 3 requirement / business rule
- For each participant, it is important to record their name, email address, state (VIC, QLD,..) and the workshops which they wish to attend.
- Every participant must register for at least one workshop.
User view 4 requirement / business rule
- There are a number of meeting rooms at the conference venue, each of a fixed capacity. Meetings rooms are identified by a floor and room number.
- Every workshop needs an allocated meeting room; where a workshop lasts for two days, it will use the same room on both days
Questions:
- Analyse the all user requirements given above, identify and list all entities described in each user requirement. (5 Marks)
- Add attributes to these entities and represent these entities (or Tables) and attributes as a collection of Tables and attributes/ database schema. You are required to arrange them as given in the example below. (5 Marks)
Note: Select suitable primary key for each table and underline it.
Eg. Participant (Participant_ID, P_name, email, W_name.)
- Outline all business rules that describe relationships between entities. (5 Marks)
Note: You can make assumptions to create certain relationships, if it is not specifically mentioned in the given business rules.
Eg. Every workshop needs an allocated meeting room
- Determine the functional dependences. (5 Marks)
Eg. Participant_ID à name, address, ......
- Then normalise these tables. Make the normalization to 3NF. State for every step in the normalization, which functional dependency causes it. (5 Marks)
- Represent the structure of your database visually by using the entity-relationship (E-R) diagram. If you make any assumptions about data that doesn’t show from the problem, they must be described.
Note: You are required to use Visio or any other software tool to create the ER diagram. (15 Marks)
- Build this model using MS Access by creating these tables and Relationships. Populate these tables with appropriate data, at least 3 records in each table. (10 Marks)
- Write SQL queries for the following questions and execute them on the database you created in Question 4.
Include screen shots of the outputs and all SQL statements you used to answer following questions this model using MS Access by creating these tables and Relationships. Populate these tables with appropriate data, at least 3 records in each table.
- List details of all workshops. (3 Marks)
- The management is interested in knowing names and email addresses of participants outside of Victoria who have already registered for the workshops. (3 Marks)
- Explain how to formulate a query to print details of workshop name, room number, floor number and room capacity
Hint : You need to join tables using a suitable query. (4 Marks)
Answer:
1 A) Database Entities
- TableParticipant: Participants detail is stored in this table.
- TableWorkshop: Workshops detail is stored in this table.
- TableOrganizer: Organizers detail is stored in this table.
- TableWorkshopParticipant: WorkshopParticipants detail is stored in this table.
- TableMeetingRoom: Meeting Rooms detail is stored in this table.
- TableScheduleWorkshop: Workshop Schedule detail is stored in this table.
- 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)
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-
- There is no repeated group in any table. Make separate tables for each group.
- Make primary key in all tables.
Second normal form may be attained by following rules-
- All tables are satisfying first normal form rules.
- All fields are depending on the primary key value.
Third normal form may be attained by following rules-
- All tables are satisfying second normal form rules.
- Remove transitive dependencies from all tables.
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
(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.
2) MS-Access Database
Cinergix Pty Ltd. (2011). Ultimate Guide to ER Diagrams [online].
Lucid Software Inc. (2015). ER Diagram Symbols and Meaning
Buy Mn405 Database Entities Answers Assessment Answers Online
Talk to our expert to get the help with Mn405 Database Entities Answers Assessment Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
