Reference no: EM132305431

Individual Project

Course Learning

To gather, critically analyse, manage and present in meaningful ways information and data, and

To act as an ethical practitioner while demonstrating skills in data analysis, database design, system design, web design and software development and testing.

To implement and document user experience analysis, design and testing.

Unit Learning

Design and build relational databases.

Define views and formulate efficient queries using a query language, and

Be knowledgeable about issues relating to data access and retrieval, storage, ethics and privacy.

ASSESSMENT DESCRIPTION:
This assessment is an individual Project.

International Parcel Service (IPS) has asked you to design and implement their system. The company wants to store the records of customers, employees, retail centres and items being shipped. The heart of IPS system is the item being shipped.

Description

The shipment process starts when items are received into the system at a single retail centre. Retail centres are characterized by their type, uniqueID, address and employees that are working in that centre. The system records the name of the employee who has received the item at the retail centre. Shipped items can be characterized by item number (unique), weight, dimensions, insurance amount, destination, final delivery date and any special instructions given by the customer regarding handling of the item.

Once an item is received, a receipt is generated which is given to the customer. The receipt contains the information such as item number, date on which item is received, and the charges paid. Shipped items make their way to their destination using many transportation modes such as flights or trucks. The transportation of every single item is recorded with transportation events which are characterized by a unique scheduleNumber, a type (e.g, flight, truck), and a deliveryRoute.

Tasks to be completed

Given the above information:

1. Create a complete ERD with all entities and relationships to support the IPS needs as described above. The entities should have appropriate attributes and suitable data types. Every relationships should have appropriate connectivity and cardinalities.

2. Convert the ERD to a set Relational tables in at least 3NF and draw a dependency diagram.

3. Create a database using MS ACCESS - with primary keys, foreign keys and other attributes mentioned for each entity using proper constraints.

4. Create a form to input/update data for ITEMS.

5. Input some significant data in each table showing your understanding of the scenario.

6. Create three queries - SELECT, JOIN and one NUMERIC Function query; you can use any tables and any fields.

7. Create a report using any one of the above queries.

Attachment:- Database Fundamentals.rar