Management homework help
Management homework help. Assessment Cover Sheet and Feedback Form
2019/2020
Course Code:
UC1PR2101
Course Title:
Programming & Databases
Lecturer:
Johan van Niekerk
Rayne Reid
Assignment
No:
2 of 3
Total number of pages,
including this page: 8
Maximum Word Count:
Not specified
Assignment Title:
Relational Database Design
Date Set:
03.04.2020
Submission Date:
24.04.2020
Feedback Date:
08.05.2020
Section 1: Submission
Record of Submission and Plagiarism Declaration
In submitting this work, you declare that this assignment is your own work and that
any collaborative work or existing material has been specifically indicated in the text.
In submitting this assignment, you agree that this work may be submitted for
plagiarism testing.
Work should be submitted:
Electronically Via Moodle in the first case. If problems are encountered, then via
email to the course staff.
Assignments submitted in digital form MUST use the following file naming convention:
COURSECODE SURNAME ITEMNUMBER, where ‘item number’ refers to the
number order of each file begin submitted for this assessment.
For example, UC1PR2101 ENGELAND 1
Digital assignments must be in a commonly readable format (.png, .jpeg, .pdf, .doc,
.docx) unless the assignment details specify that other formats are acceptable.
Late submissions will result in zero grade, unless agreed with course leader in
advance.
IT IS YOUR RESPONSIBILITY TO KEEP A COPY OF ALL SUBMITTED WORK.
2
Section 2: Marking and Assessment
This assignment will be marked out of: 100%
Contribution to the final course mark: 30%
Estimated number of hours to be spent on this assignment: 20
The pass grade for this assignment is 40%
Feedback for this assignment and a breakdown of the final grade will be
provided via the LMS (Moodle).
Section 3: Assignment Details
Consider the following scenario and produce a report for the target audience
including a logical schema for a relational database solution.
This report will include: A discussion of how your initial design was formulated (see Task 1). This
discussion should include the identified entities and attributes in 1NF,
2NF and 3NF. An appropriate Entity Relationship Diagram (ERD) for the final 3NF
design (Task2). For each entity type in your final ER diagram you should also include an
appropriately completed Entity Description Form (EDF) as per example
provided at the end of this document. Solutions must be word processed with the complete assignment compiled into
a single, structured document for submission. Hand-drawn/written submissions
will not be accepted (i.e. will not be graded).
Scenario
Examine the following sales invoice and internal reports (figure 1):
Figure 1: Sample Invoice
4
KameelPerd
Hydraulic Equipment Company
Timbuktu, Mali
Salesperson #: 3462
Name: Waters
Sales Area: West
Customer
Number
Customer Name Delivery
Company
Number
Delivery Company
Location
Sales
18765 Delta Systems 4 Pretoria 13540
18830 Levy and Sons 3 Stellenbosch 10600
19242 Rainer Company 3 Stellenbosch 9700
Figure 2: A user report from the Kameelperd Well Hydraulic Equipment Company
A log from the current paper-based system(figure 3) shows the following sample
of information:
Salesperson
Number
Salesperson
Name
Sales
Area
Customer
Name
Delivery
Company
Number
Delivery
Company
Location
Sales
Amount
3462 Waters West 18765 4 Pretoria 13540
18830 3 Stellenbosch 10600
19242 3 Stellenbosch 9700
3593 Dryne East 18841 2 Durban 11560
18899 2 Durban 2590
19565 1 East London 8800
Figure 3: Sample of the Log
You have been hired as consultant for Kameelperd Hydraulic Equipment
Company. They are a business which has been operating for several years and
has a well-established reputation in industry. They have been extremely
successful, and the business has grown over the past few years. However,
despite this growth, they have never updated their business management
systems to be more efficient than their original paper-based system.
Figure 2 Shows a user report from the business. The report shows the
Salesperson Number, Salesperson Name, and the Sales Area. The body of the
report shows the Customer Number and the Customer Name. Next is the
Delivery Company Number that will ship products that was ordered to the
customer, followed by the Delivery Company location, which is the city in which
the company is located. The final information contained in the user view is the
Sales Amount. This Sales Amount is used to manage the performance of
salespersons and is calculated monthly based on the total amount of sales made
by individual salespersons to each of their customers.
5
In this business there are several salespeople employed. They each cover a
single sales area, although they compete against each other in that area, as
many of them target the same areas for customers. Naturally the salespeople
can have many customers. Some of them have had the same customers for
years with many transactions, but they are also always scouting for new
customers. A key to the business’s success has been the good relationships
and communication channels which the salespeople build with their customers.
Each customer has their dedicated salesperson on a sale transaction (invoice)
and often, although not always, on most of their transactions. (Note: It is still
possible for a customer to make use of a different salesperson.)
A salesperson can write many invoices, each invoice represents one sale made
to a single customer, to be shipped using a specific Delivery Company, to a
specific address. An invoice has one or more invoice lines. An invoice line
records the sales information for a single product, namely: Product Number,
Description, Quantity Sold, and Unit Price. Each product is provided by a specific
supplier. A supplier can supply many different products.
Useful operations knowledge: The delivery company nearest to the customer will always be the one used to
service their order.
6
Task1:
Based on the provided documentation and information you are required to begin
to design a more efficient relational database which can be used to store all this
information in a more accessible and useable manner.
The first step of this design would be to show how the data could be more
efficiently structured in the database. This would be achieved through
normalization.
Your task is to clearly demonstrate the normalization process and the different
normalized forms of the data. Transform the complex user views and data stores
to a set of smaller, stable data structures.
Note: That during the identification of possible entities and attributes it might be
necessary to make assumptions where requirements are not completely clear or
obvious. These assumptions should be clearly documented, as stated, consider
your assumptions to be the issues you would wish to discuss with the customer
on a return visit. You should also note any business rules you may require if you
were to implement this database.
Show 1NF, 2NF and 3NF.
Task 2:
Represent the now normalized information in a data model. Show first an ERD,
and the EARD. Remember to also complete an Entity Description Form (EDF)
for each of your identified entities. (See template after the marking rubric at the
end of this document)
Additional Advice The information provided is incomplete. In order to develop your Logical
Schema, you will therefore need to make some assumptions. When you
do this, you must note them explicitly in your submission Make sure you follow the required modelling standard (as covered in the
course). You should also ensure you name all entity types, attributes and
relationships clearly and appropriately. Carefully consider the presentation of your report. Your ability to organize,
structure and present your work is also being assessed as well as your
ability to design a relational database solution.
NOTE: This is an individual assignment. You are therefore required to work
independently on your solutions. Group work will not be accepted.
7
*Note: Failure in this section can result in an overall fail grade being awarded to this assignment
Section 4: Marking and Assessment Criteria
Criteria F (< 40) (Fail) E (40 – 49%) D (50 – 59%) C (60-79%) B (80-89%) A (90-100%)
Fail Sufficient Satisfactory Good Very good Excellent
Data Modelling
[70]
Poor choice of relations.
Poor understanding of
normalisation. Weak
analysis of scenario and
assumptions missing
Key relations identified.
Assumptions listed but
limited. Normalisation
has some significant
errors. EDF(s)
attempted, incomplete
with significant errors.
All relations identified.
Assumptions
documented.
Normalisation complete
with minor errors. EDF
attempted, either with
significant errors or
incomplete.
All relations identified.
Assumptions clearly
documented.
Normalisation complete
with only very minor
errors. All EDFs
attempted with some errors. All relations identified correctly
and in 3NF, with
inconsequential errors.
Assumptions carefully
reasoned and clearly stated.
Database design suitable for
organisation, and
implementable with little more
work. All EDFs completed with
minor errors.
All relations identified
correctly and in 3NF.
Assumptions carefully
reasoned, clearly stated,
and complete. Database
design fit for purpose for
the organisation and
implementable. All EDFs
completed with no errors.
Presentation,
Spelling and
Grammar
[10]
Unclear structure to the
report. Many spelling
mistakes in each
paragraph. Writing style
unclear. Not presently
suitable for the
organisation.
Some structure to
report. Numerous
spelling mistakes in the
report. Basic sentence
construction rules
followed. Some text may
be verbose. Generally
suitable for the
organisation, after major
revisions.
Clear structure to the
report. Some spelling
mistakes in the report.
Text has a clear
message. Basic
sentence construction
rules followed.
Generally suitable for
the organisation, after
additional revisions.
Well-written, clearly
designed report. Some
spelling mistakes.
Sentences of
appropriate length and
punctuation used. Brief
but informative writing
style. Generally suitable
for the organisation.
Well-formulated structure to
report. Few / minor spelling
mistakes. Clear well
punctuated sentences. Writing
style clear and informative
without being verbose.
Appropriate for the
organisation after additional
revisions.
Excellent, professional
structure to report. No
spelling mistakes. Clear
and appropriate grammar
and punctuation. Writing
style clear, informative and
professional. Appropriate
for delivery to the
organisation.
Submission
Quality: Logical
organisation of
thoughts and
arguments;
brevity, clarity, &
understanding
[20] *
Little originality. Has
failed to get to grips with
the subject. Inadequate
discussion. Limited
understanding or
requirements. Not
presently suitable for the
organisation.
Submission instructions
not followed, or with
significant errors.
Arguments reasonably
clear, but underdeveloped. Has shown
an adequate
understanding and a
degree of original
thinking. Some errors.
Generally suitable for
the organisation, after
major revisions.
Submission instructions
mainly followed.
A theme is developed
some weak aspects.
Insight into implications.
Clear or concise
arguments. Contains
few errors. Generally
suitable for the
organisation, after
additional revisions.
Submission instructions
followed.
A clear theme is
developed. Insight into
implications. Clear and
concise arguments.
Contains very limited
errors. Generally
suitable for the
organisation.
Submission instructions
followed with minor errors. Excellent understanding and
exposition of relevant issues
that shows insight and draws
together the chosen subject
key features into a theme, no
errors. Appropriate for the
organisation after additional
revisions. Submission
instructions followed with no errors. Excellent understanding
and exposition of relevant
issues that shows insight
and draws together the
chosen subject key
features into a theme, no
errors. Appropriate for
delivery to the
organisation. Submission
instructions followed
perfectly.