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)
a. Analyse all user requirements given above and identify and list entities described in these user requirements.
(5 Marks)
b. 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)
c. 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
d. Determine the functional dependences.
(5 Marks)
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.
(5 Marks)
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)
3.
Model building
a. Build this model using MS Access/ SQLite by creating these tables and Relationships.
b. Populate these tables with appropriate data; include at least 2 records in each table. Include the following in your MS Word document.
(10 Marks)
I. 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 1 Datasheet view 1
II. 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
4. Report Generation
a.
Write an SQL query
to generate one example of useful information that can be obtained from this database.
(5 Marks)
b. 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)
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.
a. What type of topology should be used in this network?
(5 Marks)
b. Will the network be peer to peer or server based? Explain your answer.
(5 Marks)
c. Search online and specify networking devices necessary to configure this network design. Justify your answer in terms of selecting these devices.
(10 Marks)
d. Sketch the network design you think best suits GreenTech’s needs.
Remember: High performance and easy reconfiguration are your primary design goals!
(10 Marks)
Bibliography
x
x