CSG1207/CSI5135, Systems and Database Design Assignment Help

Assignment 1: Normalisation and E-R Modelling

Background Information

This assignment tests your knowledge and understanding of the two data modelling techniques we have covered in this unit – Normalisation and Entity Relationship Modelling. Both techniques aim to identify and organise a set of data in an efficient manner that minimises redundancy and establishes the structure of related groups of data. These are important first steps in constructing databases.

The assignment consists of four tasks, focusing on normalisation and entity-relationship modelling. Attempt all questions, and be sure to show all relevant stages/working and state any assumptions.

A small amount of marks are dedicated to presentation, correct notation and advanced solutions.

A Note Regarding Assumptions

It is not possible to define every single aspect of all systems in the assignment brief, nor is it beneficial to attempt this. Part of the process of completing the assignment tasks is making (and declaring) assumptions that define any grey areas or unspecified details in the scenarios. It is up to you to make these assumptions, but feel free to discuss them with your tutor if you are uncertain of their validity. The tutorial of Module 1 (recording available in Blackboard) discusses assumptions.

There is no “universally correct” set of assumptions for the tasks. Think about the problem, and make assumptions as you encounter the need for them. All assumptions should affect or be enforced/implemented by your database design, and they should not contradict something that is specified in the assignment brief. Some assumptions may make a task easier, but avoid always making assumptions that make the task easier, as this is a questionable practise and is likely result in an assignment that does not demonstrate a deep understanding. Always remember to clearly state all assumptions you make.

The most common assumptions are likely to regard irrelevant or derived data (Tasks 1 and 2) or the cardinality of relationships between entities (Tasks 3 and 4).

Task 1 – Normalisation 1 (9 marks)

Task 2 – Normalisation 2 (9 marks)

The form below depicts an invoice for a purchase from a homewares store.

Normalise this form to the third normal form, clearly showing the stages of 0NF, 1NF, 2NF and 3NF. State any assumptions you make. Use relational symbolic notation as indicated in the second lecture, and name your resultant data sets upon reaching 3NF.

CSI5135 Additional Requirement

If you are in CSI5135, the following additional requirements apply. If you are in CSG1207, you do not need to do this (but you are welcome and encouraged to do so if you want).

Once you have normalised the table to 3NF and named your data sets, convert your results into a physical E-R diagram. Remember to indicate all cardinality, attributes, primary and foreign keys. Remember to state any assumptions that are relevant to the E-R diagram.

Task 3 – Entity-Relationship Modelling 1 (9 marks)

You have been hired to design a database system for an online store. The database must encompass the customers, items, item categories and the orders made. You have the following information about the way the store operates:

 Customer details must be recorded. This includes a customer number, first name, last name, email address and password.

 The store wishes to implement a “referral system” to reward customers who tell others about the store. Therefore, customer details should also include a “referrer” column, which will contain the customer number of the customer who referred them, if applicable (not all customers are referred by someone).

 The store wishes to ensure that each customer has a different email address. This won’t influence your E-R model, but will be relevant in Assignment 2.

 Item details must be recorded. This includes an item number, name, description and price.

 A list of item categories must be recorded, and the database must keep track of which items are in which categories. All items are in at least one category, but can be in several of them.

 The only category details required are a category ID number and category name.

 Obviously, a category can have many items in it.

 Details of orders made by customers must be recorded. This includes an invoice number, the date/time of the order, a delivery address and a billing address, as well as the customer number of the customer who made the order.

 For each order, the database must also record details of ordered items and the quantity ordered. Each order must contain at least one item, and an item can be in multiple orders.

Based on the details above, you are required to draw both a logical E-R diagram for this database and then a corresponding physical E-R diagram. Clearly show all cardinality, primary and foreign keys, attributes and relationships as appropriate.

Adhere to the distinctions between logical and physical E-R diagrams defined in Lecture 3.

Use enhanced E-R model notation where/if appropriate.

Remember to state any assumptions you make.

Task 4 – Entity-Relationship Modelling 2 (9 marks)

The store from Task 3 now requires you to update and expand the previous database you designed for them, in order to record some additional information. You must update and expand your E-R models from Task 3 in order to incorporate these new requirements:

 Customers can now choose whether or not they wish to receive an email newsletter from the store. A new column in the customer entity is needed to store their choice.

 To receive more relevant newsletters, customers can specify which item categories they are interested in. Which customers are interested in which categories must be stored.

 The newsletter categories are the same as those which are linked to items.

 Customers can now define addresses which are stored in the database. A customer can define multiple addresses, and each address is associated with a single customer via their customer number. As well as specifying the address itself, customers can specify a name for the address, e.g. “Home”, and an address number is used to uniquely identify each address.

 Rather than storing addresses directly in the order details, the order details should now contain two foreign keys referencing the address entity – one for the delivery address and one for the billing address.

Create updated and expanded versions of your logical and physical E-R diagrams from Task 3 that incorporate these new requirements. You should not need to modify very much from your solution to Task 3 to complete this task; you mainly need to expand the diagrams by adding the new attributes, entities and relationships.

Remember to state any assumptions you make, use enhanced E-R model notation where/if appropriate, and show cardinality on both diagrams. Ensure that you submit separate solutions to Tasks 3 and 4 – a total of four E-R diagrams.

Assignment 1 Marking Key

If you are looking for CSG1207 assignment help, CSI5135 assignment help, Systems and Database Design Assignment Help you can place your order at plagfree.com.