Intended Learning Outcomes covered:

1. Design the logical structure of a database using Entity-Relationship diagram.

2. Apply normalization techniques to reduce redundancy in a database.

Task 1:

Complete the work proposal in Microsoft Word file format (may include possible answers based on your initial understanding). Work proposal for the assignment must be submitted before 11.55 PM, August 18, 2016 and must include:

- What you will do with the given tasks: task 2, task 3 and the dates (timeline) by when they will be completed
- General overview of initial understanding of solutions to task 2 and task 3
- Identification of Literature Resources

Task 2:

Scenario:

Nissan Motors Company (NMC) has several brands. Each brand has several models. The details of brand are the brand name and number of employees. The details of model are model id (unique), model name, year, engine, transmission and color. A given model belongs exactly to one brand. A model has one or more vehicles. NMC has several dealers that are responsible for selling different types of vehicles to customers. A dealer is identified by a unique dealer id. The other attributes are dealer name, location, phone and email. A dealer makes sale agreement with one or many customers. Similarly a customer may have a sale agreement with one or more dealers. Customer is identified uniquely by a customer id. The other attributes are name, company name, gender, address, phone, email, annual income. A customer may own many vehicles or may not own a vehicle. A vehicle may be owned by only one customer or may not be owned by any customer. A vehicle is identified uniquely by a vehicle number and vehicle location. One or more dealers may have access to a vehicle or may not have access to it. A vehicle may be accessed by a dealer or may not be accessed. A vehicle is produced by only one manufacturer. A manufacturer may produce one or more vehicles. A manufacturer is uniquely identified by a manufacturer code. It also has other attributes such as name, address, phone, email.

a) Construct the Entity Relationship Diagram (ERD) for the above given scenario. Identify all the entities, attributes of each entity including primary key, relationship between the entities and cardinality constraints.

b) Describe how the arrived logical design in task 2a above can be improved or enhanced by providing your suggestions and the rationale for the same.

Task 3:

a) Normalize the below given Rental Form to First Normal Form, Second Normal Form and Third Normal Form.

Alamri Rentals No 43, Al Hail, Sultanate of Oman

Owner ID: 98723415                                                              Rental code: 34l921

Owner Name: Maryam                                                           Date:  19 July 2016

Owner address: No 64, Al Hail, Muscat                                 Agent ID: 792

Sultanate of Oman                                                               Agent Name: Khamis

                                                                                             Agent position: Supervisor

b) For the finally arrived normalized form in task 3a above, discuss how the redundancy has been minimized and what do you think will it achieve.

Task 4: Be ready for a written viva to demonstrate your knowledge with the different concepts used in preparing the assignment. Schedule for the written viva will be announced in the class and on Moodle/MEC mail. Marks for task 2 and task 3 will depend on the written viva.

Note: Task 4 is compulsory. No marks will be awarded to tasks 2 & 3 if written viva is not taken by a student.

Guidelines
Follow the guidelines mentioned below for your assignment.

- Submit a work proposal detailing aspects such as initial understanding of the given tasks, timelines to complete each task, literature sources for solving the tasks.
- Assignment should be typed and uploaded to Moodle and will undergo plagiarism detection test through Turnitin (a plagiarism detection tool)
- Handwritten assignments will not be accepted.

- Assignment should have a Title Page. Title Page should contain the following information.
 Assignment Name
 Class
 Student name
 Student ID
- It should have Table of Contents
- Use page numbers
- Assignment should be typed in your own words using Times New Roman font size 12.
- Heading should be with Font Size 14, Bold, Underline
- Use Diagrams and Examples to explain your topic.
- Copy paste from the Internet or other student work is strictly not acceptable.
- References should be included in the last page as follows
 Author name, Book Title, Publisher, Year in case of books
 In case of web site references type the full path of the web page with referenced date
 In case of journals/magazines/periodicals type article name, magazine name, Issue Number and date