115.107 Management Information Systems

1
115.107
Management Information Systems
Assignment
The assignment is marked out of 100 marks, distributed as noted below, and is worth 25% of your
final grade
Due on Monday, 10 September 5pm NZ time
Deliverables
1. Submit 2 files via Stream assignment upload link
An assignment report (documentation) for parts 1 to 4 using either Microsoft Word or
Open Office Writer.
The assignment report is a “paper trail” of what you have done while working on the
assignment. It will include a contents page, and many screen shots of your progress.
A suggested template will be available on Stream.
Make sure that the report is well presented. To fully appreciate your work, markers
need to be able to find your answers, and to understand everything you did. How to
style and format your report is explained in part 5 of this document. Marks are awarded
for the correct use of word processing features and overall presentation.
A database file for parts 2 and 3 using either Microsoft Access or Open Office Base
You are given a partially completed database, Microsoft Access or Open Office Base
which you will download from Stream. In parts 2 and 3 you will add database tables, add
data and create queries / reports on data. This takes place within the given database.
Note: How to submit assignment files via Stream is covered in Module 1 exercises.
When assigning marks, markers will rely both on your report, and on the database. If a
feature that should be both in the database and in the report is present in only one of them
(for example, a database query present in the database, but not documented in the report),
you may lose marks for that feature
2. Completed Wiki pages (Part 5) on Stream
You are required to create a few Wiki pages and add some content to these pages via
Assignment Part 5 Wiki on Stream. Markers will be able to access your pages and mark your
work.
Note: within this document the terms
report, database and spreadsheet are
used.
These terms correspond respectively
to Word or Writer, Access or Base,
Excel or Calc, depending upon
whether you are using Microsoft
Office, or Open Office
2
Overview of this document
How to Approach this assignment
Part 1 – Data modelling a business scenario with Entity Relationship (ER) diagram
Part 2 – Implement the relational model in a database
Part 3 – Query data and report within the database
Part 4 – Export data to a spreadsheet, analyse and display information graphically
Part 5 and on-going – maintain a wiki
How to approach this assignment
The main aim of this assignment is to test the knowledge and skills you gain by completing software
exercises in modules 1 to 6 of this paper (over weeks 1 to 6). In the exercises you learn new concepts
and develop hands-on technology skills. You are expected to be working on the software exercises
and on the assignment more or less simultaneously: acquire skills by doing software exercises and
immediately apply them by completing the relevant parts of the assignment.
In addition, the assignment involves a small number of tasks not covered by the exercises, to offer
you an experience in resolving IT problems by relying on using software help systems and the
Internet as sources of technical information.
Carefully complete the exercises
It is impossible to fully understand what is required in the assignment (let alone, complete it) unless
you carefully complete the exercises first. Before attempting Part 1 of the assignment, complete the
relevant software exercises. Once you have attempted Part 1, complete the exercises relevant to
Part 2. And so on.
There is just one exception from this recommended sequential approach: it is recommended that
you briefly take a look at Part 5 from the very start, even before you start working on Part 1.
The Wiki pages you are going to create in Part 5 are devoted to various aspects of your experiences
in working on the assignment, so you might want to note your experiences as you proceed.
Take sufficient time (about 5 weeks)
Skills acquisition takes time and practice. You do need about 5 weeks to complete the exercises and
the assignment. Needless to say, attempts to cram 5 weeks’ work into one week are unlikely to
result in good outcomes.
Expect to do and re-do most of the artefacts
It is highly likely that you will revisit most of the assignment deliverables more than once.
For example, as you work on the rest of the assignment, you are likely to gain a better understanding
of both the meaning of ER notation and of the exact requirements for the ER diagram you complete
in Part 1. Therefore, you would go back to Part 1, and adjust the ER diagram to reflect your better
level of understanding. Expect to go over several versions of each artefact you create. Do not expect
to “get everything right” on the first attempt.
3
Part 1: ER diagram (25 marks)
Complete Module 3 Exercises before you start working on this part of the assignment.
A Simple Property Management System – Original Scenario

Figure 1: Property Management System – initial ER diagram
Table 1: Attributes for entity types in Figure 1

Note that the diagram in Figure 1 is simplified – an ER diagram describing the structure of a database
used in a real property management application is likely to be much larger.

is serviced by
is signed by

4
Tasks
1. Reproduce the ER diagram (figure 1) and the table of attributes (table 1) given above in your
report. When drawing the diagram use the techniques introduced in Module 3 exercises.
2. Read the enhanced scenario expanded below. Identify any new entity types, relationship
types, and attributes. Decide the multiplicities required for each relationship.
In your report, add to the ER diagram you have drawn and to the table of attributes these
entities, relationship types, multiplicities and attributes as appropriate.
Enhanced Scenario

Notes
When naming entity types, use singular, rather than plural. For example, the diagram
provided suggests that there can be any number of tenants – instances of Tenant, but the
name of the entity type is Tenant, rather than Tenants.
Feel free to make assumptions, in particular, to add attributes. But, do not make it too
complex.
Do not change the “business” sense / rules of the diagram we provided. It’s likely you may
need to move entities to permit you to add to the diagram, of course. Just add to it.
The 25 marks for Part 1 are allocated across the following:
o Original components of the ER model reproduced
o Extended ER diagram

Different students might make different assumptions, and extend the diagram we provided
in different ways. There is no single “correct answer”.
5
Part 2: The Relational model, tables and keys (20 marks)
Complete Module 4 Exercises before you start working on this part of the assignment.
In this part of the assignment you design and implement the Property Management Database.
Database design
Document the database design in your assignment report including tables, columns, primary and
foreign keys within the database and presented in tabular form in the assignment report, as in
Module 4 exercises. When naming the tables and their columns, you must follow the naming
convention rules (see below).
Tasks
1. Convert only the original ER model in Figure 1 into a relational data model. Consider which
tables and table columns you need and document them in your assignment report.
Note that sometimes you need more tables than there are entity types in the ER diagram –
for example, you need to consider the many-to-many relation in the original scenario. You
should not include entity types and relationship types that you added to the diagram to
accommodate the expanded description of the business scenario.
2. Your relational model must take into account the following additional user requirements
and document the necessary columns within the appropriate table:

[In part 5, Wiki, you are asked to justify your reasons for locating these columns where you
have]
Notes:
Adding information about primary and foreign keys in your report
In the report, for each relation / table, list its columns and indicate which column (or columns)
forms its primary key. For each of the foreign keys (if there are any) state the table to which it
points.
This should cover all tables (the tables you created and the tables we provided in the database).
Following naming conventions
In this assignment, we expect you to comply with the following naming conventions:
o A table representing an entity type should be named the same as the entity type
(with the omission of any spaces) + Tbl suffix. For example, the table representing
the Tenant entity type should be named TenantTbl.
o A table representing a many-to-many relationship type should be named the same
as the relationship type (with the omission of any spaces) + Tbl suffix. For example,
the table representing an “is serviced by” many-to-many relationship type should be
named ServicedByTbl.
o Table columns corresponding to attributes of an entity type should be named the
same as the attributes they correspond to (with the omission of any spaces).
6
o Table columns added to represent relationship types become components of foreign
keys. Whenever possible, such a column should be named either exactly the same as
the primary key column it matches (or as the matching primary key column with the
addition of the primary key table name as a prefix). For example, LeaseTbl table
representing the Lease entity type could have the following columns: LeaseID,
StartDate, EndDate, PropertyID and TenantID. Note that PropertyID and TenantID
here are foreign keys into, respectively, PropertyTbl and TenantTbl.
Database Implementation
Open the database we provided with the assignment. The database represents a part of the ER
data model introduced in Part 1. Complete the database according to your design in a desktop
database management system such as MS Access or OO Base.
Tasks
1. Add new tables and set up their columns or fields, and Primary keys
2. Create a relationships diagram to represent foreign keys
3. Adding test data to the new tables
Notes
Setting up columns or fields in database tables
When selecting data types for columns, use “Text” in most cases. You will note that in the
database provided with the assignment we used “Autonumber” for many primary keys and
“Currency” for dollar amounts, while the rest of the columns are “Text” data types.
Setting up primary and foreign keys for database tables
Do make sure that you set up primary and foreign keys in the database, as shown in the
exercises. To set up the foreign keys, you need to create a relationships diagram. To set up a
primary key consisting of more than one column, in table design view hold Ctrl as you click on
each column / field to include.
Adding test data
Fill the table columns you created with realistic test data (but do not change the data that we
provided). When adding new data, make sure that any foreign key value corresponds to the
appropriate primary key value. For example, a lease should be signed by an existing tenant, and
for an existing property. If you set up foreign keys in the database correctly, the software will
refuse to accept values that do not comply with this constraint.
7
Part 3: Database Queries (20 marks)
Complete Module 5 Exercises before you start working on this part of the assignment.
Tasks
1. Query 1: TenantList
Create a QBE or an SQL query listing all tenants. Details must include first and last names,
company, job title and business phone number. The output should be sorted alphabetically by
surname (A-Z) and by first name ascending where surnames are the same. This query should be
saved as TenantList within the database.
2. Query 2: LeaseMonthlyAmounts
Create a QBE or an SQL query listing details of property leases in the Wellington region. Details
required are; property ID, address, bedrooms, the monthly lease amount and security bond.
Save this query as LeaseMonthlyAmountsInWellington within the database.
3. Query 2 Report: LeaseReport
Create a report based on Query 2 and displaying all details output from that query. Alter the
report title to be “Monthly Leases” and place your name in the report header, but separate from
the report title. The name of the report in the database should be LeaseReport.
4. Query 3 LongTermLeases
Create a QBE or an SQL query listing details of properties where the lease end date is more than
6 months from now. That is, where the lease end date is over 180 days from the date of running
the query.
Details should include: propertyID, lease end date, address, region and monthly lease amount.
Save this query as LongTermLeases within the database.
5. Query 4 ThreeBedroomLeases
Create a QBE or an SQL query listing details of property leases with at least 3 bedrooms, the
monthly lease amount has to be less than $2,500 and the security bond less than $4,500. Details
required are property ID, address, tenant’s first and last names, and email address. Save this
query as ThreeBedroomLeases within the assignment.
Notes
Document these queries and the report in your report. For each query include (paste)
screenshots of the QBE or SQL of the query design, and another of the output from running
the query
Include (paste) a screenshot of the query report in your assignment report.
8
Part 4: Spreadsheet filters and pivot tables (15 marks)
Complete Module 6 Exercises before you start working on this part of the assignment.
Tasks
1. Copy or export data from the query (query 2) LeaseMonthlyAmountsInWellington that
you created in Part 3 into a spreadsheet.
For each task below, make you starting point the original copied data. You may need to copy the
data into different worksheets
2. Apply a filter, and customise it to show only lease amounts of $1,500 and less.
3. Use conditional formatting to show all bedrooms of 4 or more formatted with “double”
underlining.
4. Create a pivot table with property address as rows, number of bedrooms as columns,
and MonthlyLeaseAmount as data. Configure the pivot table to ensure that the lowest
value of the monthly lease is shown in the table (so that people looking to rent in
Wellington can see the value of the lowest lease amount for properties with bedrooms
they need).
5. Plot the data in the pivot table using a suitable chart to help tenants choose suitable
properties.
Notes
If you are unable to create the LeaseMonthlyAmounts, query 2 in Part 3, and so have no
data to use in this part of the assignment, contact the tutors who will give you some data to
work on. In this case, however, you will not receive any marks for the “Copy or export data
from the query to a spreadsheet”.
Include the screenshots of the copied spreadsheet data, the filtered data, the formatted
data, the pivot table, and the chart in your report.
9
Part 5: Finalizing the assignment report and reflecting on the
assignment (20 marks)
Style your assignment report and add table of contents and lists of figures
and tables
Before you start working on this part of the assignment, make sure that you complete Module 2
Exercises.
Tasks
1. Consistently style the report using the style features of the software, which is introduced
in Module 2 Exercises.
2. Assign numbers and captions to all tables and figures in your report (e.g., “Figure 1: ER
Diagram for the Property Management System”). Place table captions immediately
above tables, and figure captions – immediately below figures. At the beginning of the
report, create a table of contents, a list of all tables and a list of all figures by using the
relevant software features introduced in Module 2 Exercises.
Document your reflections by using Wiki pages
Before you start working on this part of the assignment, make sure that you complete Module 1
Exercises.
To complete this part of the assignment, you need to open Assignment Part 5 Wiki on Stream.
Tasks
1. The main or opening wiki page should have three headings. Each heading will be a link to a
new wiki page. These heading should be:
Reflections on Data Modelling
Reflections on the Assignment
Justification of New Attributes
Each of the following wiki pages should be formatted with a suitable heading.
2. Wiki page, Reflections on Data Modelling. Use this page to describe roughly how much time
it took you to complete each part of the assignment, and how many times you had to re-do
each of the main artefacts (such as the ER diagram, tables, queries and reports). The page
should include at least one table and at least one bulleted or numbered list (which should
support your description in a meaningful way).
3. Wiki page, Reflections on the Assignment. Use this page to describe your experiences in
working on this assignment: which parts you found difficult and why, which parts you found
easy, and your overall opinion about the assignment. Describe what you would have done
differently if you were to work on a similar project once again.
Within this Wiki page, Reflections on the Assignment, beneath its heading, create 2 links:
one permits the reader to link directly to the wiki page Reflections on Data
Modelling
the other permits the reader to link directly to the Wiki page Justification of New
Attributes.
10
4. Wiki page, Justification of New Attributes. In Part 2 of the assignment you were asked to
add new attributes to the scenario that permitted a description of the service provided to
any property, the date and cost of the service. These services are, for example, gardening,
cleaning, electrical, maintenance etc.
Use this wiki page to justify why you located each attribute where you did: which table(s)
and why.
5. Use your browser to access Wikipedia.org on the Web. Use Wikipedia’s search feature to
find the wiki page “Database normalization”. This is an interesting subject that you have,
without possibly knowing, been performing throughout this assignment. In the Justification
of New Attributes page create a hyperlink that points to this Wikipedia page. The text of the
link should show “Database Normalization”.
Note that the requirements listed in this section suggest a navigation structure as in Figure 2.
Figure 2: a navigation diagram for the Wiki pages
Check List Over Page>>
Assignment Wiki main page
Reflections on Data Modelling
Reflections on the Assignment
Justification of New Attributes
11
Checklist
1. Assignment report is complete
a. Assignment report is consistently styled by using the styles feature of the word processor
software (see Introduction).
b. Table of contents and lists of table and figure captions created using the relevant features of
the software (see Introduction).
c. ER diagram and the table listing attributes and keys. (see Part 1)
d. A table listing relations, columns and primary keys. (see Part 2)
e. Screenshots (or text in case of using SQL) of four queries; per query, paste the query design,
and the output from each query when run. A screenshot of the report is required. (Part 3)
f. A screenshot of spreadsheet data copied from the database, and screenshots showing the
pivot table and chart. (see Part 4)
2. All files submitted correctly
a. The assignment report is uploaded to Stream
b. The database created in Part 2, including queries and report created in Part 3, is uploaded to
Stream
c. Exactly two files are uploaded (the assignment report and the database) – not more. The files
are uploaded as two separate files – not as a ZIP archive (and not as an archive of some other
type).

The post 115.107 Management Information Systems appeared first on My Assignment Online.