database normalization and ERD diagram

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 under- developed. 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.

8

Entity Description Form Database Name

Entity Name:

Description:

Attribute Name Type Required Default Constraints