Data and Information Management – Data model development and implementation Purpose of the…

Data and Information Management – Data model development and implementation

Purpose of the assessment (with ULO Mapping)

The  purpose of this assignment is to develop data models and map Database  System into a standard development environment to gain understanding of  data model development. Then implement the data model using a  commercially available database management system development tool.

On completion of this assignment students will be able to:
a. Understand the fundamental principles of the networking and data requirements of a network.
b. Identify organisational information requirements.
c. Model organisational information requirements using conceptual data modelling techniques.
d.  Convert the conceptual data models into relational data models and  verify their structural characteristics with normalisation techniques.
e. Implement and utilise a relational database using a database system.

Description  of this assignment: This assignment is to develop a data models, map  and implement a Database System in a standard development environment to  gain understanding of data model development and implementation.

Assignment Description

1. 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)  to1(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 workshopswhich 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. Meetingsrooms are identified by a floor and room number.
• Every workshop needs an allocated meeting room; where a workshop lasts for two days, it willuse the same room on both days

Questions:

a. Analyse the all user requirements given above, identify and list all entities described in each user requirement.

b. Add  attributes to these entities and representthese entities (or Tables) and  attributesas a collection of Tables and attributes/ database schema.  You are required to arrange them as given in the example below.
Note:Select suitable primary key for each table and underlineit.
Eg.Participant(Participant_ID, P_name, email, W_name.)

c. Outline all business rules that describe relationships between entities.
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

d. Determine the functional dependences.

Eg.Participant_ID → name, address, ……

e. Then  normalise these tables. Make the normalization to 3NF. State for every  step in the normalization, which functional dependency causes it.

f.  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: Youare required to use Visio or any other software tool to create the ER diagram.

2.  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.

3. Write SQL queries for the following questions and execute themon 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 Accessby creating these tables  and Relationships. Populate these tables with appropriate data, at least  3 records in each table.

a. List details of all workshops.

b. The  management is interested in knowing names and email addressesof  participantsoutside of Victoria who have already registered for the  workshops.

c. 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.

“Place this order or a similar order with Essay Writers 4Life and get an amazing discount”

Source link