MN405 - Data & Information Management - Data Model Development and Implementation sample

MN405 - Data & Information Management

 

This assignment consists of Part A and Part B.

In Part A: you will identify organisational information requirements and create conceptual data model using Entity Relationship diagram (ERD). Then you will implement it by converting the conceptual data model into relational data model.

 In Part B: You will apply fundamental principles of the networking to devise a networking solution.

 

Data Model Development and Implementation

Part A (60 Marks)

 

1. Complete the information-level design for a database that satisfies the following constraints and user view requirements.

 

The organisers of the AI_2020 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 given below 1(a) – (e)

 

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 given 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

 

Note: Write down any assumptions you make if they are not explicitly described here in user requirements.

 

Answer questions given below 1 (a) – (e)

 

1. 1. Analyse all user requirements given above and identify and list entities described in these user requirements.                                                                                                           (5 Marks)

                Answer:

              According to above business rules and to get the complete information from the database, I have listed the followings entities.

              1) Organizers

              2) Meeting_Rooms         

              3) Workshops

              4) Participants

              5) Workshop_Attendees

 

Here, I used extra entity as Organizers , which includes the whole information about the organizer. It would be used  to user in future to organize future workshops and events. And sometimes, participants  prefer workshops against the name of the event organizer. So to take report, it would be used.

 

I have created Participants entity which includes participants detail and Workshop_Attendees entity, which includes participant details against workshop. Both entities are used to create a report.

 

1. 2. Add attributes to these entities and represent them as a collection of tables and attributes (Database schema):        

Eg.        Participant (Participant_ID, P_name, email, W_name.)

Note: Select and underline suitable primary key for each table.                                           (5 Marks)

 

Answer:

                       1) Organizers(Organizer_ID, O_Name,  O_Email, O_State)

                     2) Meeting_Rooms(MeetingRoom_ID, Floor_Number,  Room_Number)         

                     3) Workshops(Workshop_ID,W_Name, Total_Days, MeetingRoom_ID, Organizer_ID)

                     4)Participants (Participant_ID, P_Name, P_Email,P_State)

                     5) Workshop_Attendees(Attendee_ID, Participant_ID, Workshop_ID, StartDate, EndDate)

I have added some attributes , which are not mentioned in above business rules but it may be useful  in future requirements so I have used them. See table details for this.

 

1. 3. Outline 3 business rules that describe the 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

 

Answer:

1) One organizer can associated with multiple workshops

2) Every workshop associated with at least one Organizer

3) Every workshop assigned with one meeting room

4) One meeting room can associated with multiple workshops, if workshop date will different

5) One participant associated with multiple workshops

6) Every participant associated with at least one workshop

 

 

1. 4.  Determine the functional dependences.                                                                                       (5 Marks)

Eg.  Participant_ID  à  name, address,  ......,  ......

 

Answer:

Organizer_ID  à O_Name, O_Email,  O_State

Participant_ID  à  P_Name, P_Email,P_State

MeetingRoom_ID à Floor_Number,  Room_Number

Workshop_ID à W_Name, Total_Days

 

Workshop_ID à MeetingRoom_ID, Organizer_ID 

Attendee_ID  à Workshop_ID, Participant_ID , StartDate, EndDate

 

1. 5. Then normalise these tables. Make the normalization to 3NF.  State for every step in the normalization, which functional dependency causes it.                                                         (5 Marks)

Answer:

1) Workshop_Attendees Table:

 

Workshop_Attendees
Attendee_ID	Participant_ID	Workshop_ID	StartDate	EndDate

 

Workshop_Attendees_Details Table:

 

Attendee_ID

StartDate

EndDate

 

 

Participants Table:

 

Participant_ID

P_Name

P_Email

P_State

 

Workshops_Details Table:

 

Workshop_ID

W_Name

Total_Days

MeetingRoom_ID

Organizer_ID

 

 

 

2) Workshops_Details Table:

 

Workshops_Details
Workshop_ID	W_Name	Total_Days	MeetingRoom_ID	Organizer_ID

 

 

Workshops Table:

 

Workshop_ID

W_Name

Total_Days

 

 

Meeting_Rooms Table:

 

Meeting_Rooms_ID

Floor_Number

Room_Number

 

 

Organizers Table:

 

Organizer_ID

O_Name

O_Email

O_State

 

 

2. Represent the structure of your database visually by using an entity-relationship (E-R) diagram.  If you make any assumptions about data that are not explicitly given in the problem, these must be described.

Note: You need to use Visio, draw.io [1] or Lucid chart [2] or any other software tool, to create the ER diagram.                                                                                                                              (15 Marks)

 

Answer: ER Diagram file is attached.

 

 

 

3. Model building
   1. Build this model using MS Access/ SQLite by creating these tables and Relationships.  

Answer: I have used SQLite for creating table and run the queries related to table. See Answer of below task.

1. 2. Populate these tables with appropriate data; include at least 2 records in each table.  Include the following in your MS Word document.                                                                                            (10 Marks)
      1. If you are using MS Access include following screen shots

• • • Relationship diagram created in Access. (Select database tools àRelationships in the Access menu)
    • Data sheet view and design view of your tables.

 

Figure 2 Design View

 

Figure 1 Datasheet view 1

• 1. 2. If you are using SQLite
• Screen shots of CREATE TABLE command and you can use INSERT INTO commands to add records.

Figure 3 CREATE TABLE command in SQLite

Answer:

 

Create table  via query:

 

1) Organizers Table:

CREATE TABLE IF NOT EXISTS Organizers (

Organizer_ID INTEGER PRIMARY KEY,

O_Name TEXT NOT NULL,

O_Email TEXT NOT NULL,

O_State TEXT NOT NULL

);

 

 

 

2)Meeting_Rooms Table:

 

CREATE TABLE IF NOT EXISTS Meeting_Rooms (

MeetingRoom_ID INTEGER PRIMARY KEY,

Floor_Number TEXT NOT NULL,

Room_Number TEXT NOT NULL

);

 

 

 

3)Workshops Table:

CREATE TABLE IF NOT EXISTS Workshops (

Workshop_ID INTEGER PRIMARY KEY,

W_Name TEXT NOT NULL,

Total_Days NUMBER NOT NULL,

MeetingRoom_ID INTEGER NOT NULL,

Organizer_ID INTEGER NOT NULL,

 

FOREIGN KEY (MeetingRoom_ID) REFERENCES Meeting_Rooms (MeetingRoom_ID)

FOREIGN KEY (Organizer_ID) REFERENCES Organizers (Organizer_ID)

);

 

 

 

4)Participants Table:

CREATE TABLE IF NOT EXISTS Participants (

Participant_ID INTEGER PRIMARY KEY AUTOINCREMENT,

P_Name TEXT NOT NULL,

P_Email TEXT NOT NULL,

P_State TEXT NOT NULL

);

 

 

5)Workshop_Attendees Table:

 

CREATE TABLE IF NOT EXISTS Workshop_Attendees (

Attendee_ID INTEGER PRIMARY KEY AUTOINCREMENT,

Participant_ID INTEGER NOT NULL,

Workshop_ID INTEGER NOT NULL,

StartDate DATE NOT NULL,

EndDate DATE NOT NULL,

 

FOREIGN KEY (Participant_ID) REFERENCES Participants (Participant_ID)

FOREIGN KEY (Workshop_ID) REFERENCES Workshops (Workshop_ID)

);

 

 

 

 

Inserting data into table via query:

 

INSERT INTO Meeting_Rooms (MeetingRoom_ID,Floor_Number,Room_Number) VALUES (1,'1st','108'),(2,'2nd','203'),(3,'3rd','305'),(4,'4th','403');

 

 

 

INSERT INTO Organizers (Organizer_ID,O_Name,O_Email,O_State) VALUES (101,'ABC Edutech','[email protected]','VIC');

 

 

INSERT INTO Organizers (Organizer_ID,O_Name,O_Email,O_State) VALUES (102,'InfoSol','[email protected]','NSW');

 

 

 

 

INSERT INTO Workshops (Workshop_ID, W_Name, Total_Days,MeetingRoom_ID,Organizer_ID)

VALUES (1011,'Softskill Learning',1,1,101),

(1012,'Management skill',3,1,102),

(1013,'Soft Skill',2,2,101);

 

 

 

 

INSERT INTO Participants (Participant_ID, P_Name, P_Email, P_State)

VALUES (110,'John Thomas','[email protected]','QLD'),(111,'Sam Lee','[email protected]','NSW'),

(112,'Xi Lee','[email protected]','VIC'),(113,'John Peter','[email protected]','QLD');

 

 

INSERT INTO Workshop_Attendees (Attendee_ID, Participant_ID,Workshop_ID, StartDate,EndDate)

VALUES (1,110,1012,'2020-05-06','2020-05-08'),(2,110,1013,'2020-05-05','2020-05-06'),

(3,111,1011,'2020-05-05','2020-05-05'),(4,112,1011,'2020-05-09','2020-05-09'),(5,113,1012,'2020-05-10','2020-05-12');

 

 

4. Report Generation

 

1. 1. Write an SQL query to generate one example of useful information that can be obtained from this database.                                                                                                                                    (5 Marks)

Answer:

1) Find participant count against workshops.

 

SELECT W_Name as 'Workshop Name',count(P_Name) as 'Total Participants',Total_Days as 'Workshop Days',Floor_Number as 'Floor',

Room_Number as 'Room No.' FROM (Workshops,Participants,Workshop_Attendees,Meeting_Rooms)

WHERE Workshop_Attendees.Workshop_ID = Workshops.Workshop_ID

AND Workshop_Attendees.Participant_ID = Participants.Participant_ID

AND Workshops.MeetingRoom_ID = Meeting_Rooms.MeetingRoom_ID

AND Workshop_Attendees.Workshop_ID = 1011 ;

 

 

2) Find workshop details against participant_ID=110.

 

SELECT P_Name,W_Name,StartDate, EndDate,Total_Days, MeetingRoom_ID FROM (Participants, Workshops,Workshop_Attendees)

WHERE Workshop_Attendees.Workshop_ID = Workshops.Workshop_ID AND Workshop_Attendees.Participant_ID = Participants.Participant_ID

AND Workshop_Attendees.Participant_ID =110;

 

 

3) Find participant against workdhop_ID=1011

 

SELECT W_Name, P_Name,StartDate, EndDate,Total_Days, MeetingRoom_ID FROM (Participants, Workshops,Workshop_Attendees)

WHERE Workshop_Attendees.Workshop_ID = Workshops.Workshop_ID AND Workshop_Attendees.Participant_ID = Participants.Participant_ID

AND Workshop_Attendees.Workshop_ID=1011;

 

 

 

1. 2. For additional 5 marks you can execute more queries (at least 2) on the database you created in Question 3 and include the screen shots of the outputs and all SQL statements.                                                                                                                                                                                                     (5 Marks)

1)To find how many table are in database, we can use below query:

SELECT * FROM sqlite_master;

 

 

2) Show workshop details whose start date is 05/05/2020 or end date is 06/05/2020.

SELECT * FROM Workshop_Attendees WHERE StartDate='2020-05-05' OR Enddate='2020-05-06';

 

 

3)Show workshop details whose Meeting Room ID=1 and Organizer_ID=101

 

SELECT * FROM Workshops WHERE MeetingRoom_ID=1 AND Organizer_ID=101;

 

 

 

4) We can found how many workshops are in this event?

 

SELECT * FROM Workshops;

 

By using of above result, we can also find how many meeting rooms are available for allocation.

 

5) We can find state wise participant details.

 

SELECT * FROM Participants WHERE P_State='NSW';

 

 

6)We can see participant details against workshop, so we can know the total number of participants against workshops

 

SELECT * FROM Workshop_Attendees WHERE Workshop_ID=1011;

 

7) We can find how many workshops against participant ID as below.

 

SELECT * FROM Workshop_Attendees WHERE Participant_ID=110;

 

 

 

Part B (30 Marks)

 

GreenTech has 200 networked computers and five servers and uses a star topology wired network to reach employees' offices, with a bus interconnecting three floors in its office building. Because of a staggering influx of Internet business, the network administrator's task is to boost network performance and availability as much as possible. The company also wants a network design which is easy to reconfigure and add more computers to the existing network change because workgroups form and disband frequently and their membership changes regularly.

 

All computers must share sensitive data and control access to customer files and databases. Aside from the customer information and billing databases, which run on all servers, employees' desktop computers must run standard word-processing and spreadsheet programs.

 

Use the following write-on lines to evaluate the requirements for this network.

 

 

1. What type of topology should be used in this network?                                           (5 Marks)

 

Answer: We will use hybrid topology which is a combo of ring and bus topology in this network.

 

2. Will the network be peer to peer or server based? Explain your answer.                 (5 Marks)

 

Answer: According to above requirement, we need a Server Based  Network because data sharing speed is high in server based network and secondly , we handle everything from centre.

 

Server based network is more secured and maintenance of network is also easy.

 

We can access servers remotely from various platforms in the network and upgradation of network is easy.

 

As per our requirement, we can serve multiple clients at a time.

 

3. Search online and specify networking devices necessary to configure this network design. Justify your answer in terms of selecting these devices.                                       (10 Marks)

 

Answer: To implement this network , we will use different switches against requirement of ports of 1GBPS speed capacity. We will keep all switches in a stack so we can easily manage the network. We will keep 1 GBPS speed between two switches to manage our stack connection. Here, we are not using router because router is costly than switches. If needed, we can use router and wireless hub against requirement of network and building design.

 

4. Sketch the network design you think best suits GreenTech’s needs.

Remember: High performance and easy reconfiguration are your primary design goals!

 

Answer: See Network diagram file. I have attached .vpd file and .jpeg file for same. In this diagram bus topology for server 2 to server 5 is optional. It would be implemented against requirement of office.

(10 Marks)

 

 

 

 

Bibliography

x

[1]	Draw.io. (2020, March). [Online]. https://www.draw.io/
[2]	Lucidchart. (2020, March) ER Diagram Tool. [Online]. https://www.lucidchart.com/pages/tour/ER_diagram_tool

x