COIT20247 - Database Design And Development, Central Queensland University, Australia

HIRE PROFESSIONAL WRITER FROM EXPERTSMINDS.COM AND GET BEST QUALITY COIT20247 - DATABASE DESIGN AND DEVELOPMENT ASSIGNMENT HELP AND HOMEWORK WRITING SERVICES!

Part A - Database implementation using MS Access

1. Normalization

a) Map the ERD, from the sample solution, into a set of relations in at least Third Normal Form (3NF). You must ensure that your relations meet 3NF. There is no need to show your working.

Answer: The customer table has customer address and contact number which are not fulfill the normalization so the customer table can be split has customer, customer address, customer contact. All three tables depend on customer id as primary key. All above three tables has relationship with customer id. Similarly, parcel table we can write as two tables parcel details, parcel status. The remaining tables are fulfill the 3NF.

b) Select any two of your relations from the previous step, 1a), and perform the following for each of those two relations.

Answer: The primary key is customerID which identifies a customer. There will be one customer name per customerID because only one nameis recorded for each customer. Even though a customer may have multiple addressesfor instance. a home address and a work address. In case staying states that there is a need to record the home address only and hence there will be only one address for each customer. Hence, this relation is in 1NF.

The primary key is customerID. This is the only candidate key since Name is not guaranteedto be unique and two different customers may have the same name. Address is also notguaranteed to be unique.This means that customer ID functionally determines every other attribute inthe table. The candidate key, customer Id, is a simple and single valued attribute. Therefore, no partial dependencies are possible. Hence, the relation is in 2NF.

Name cannot be used to functionally determine any other attribute in the table since two different customers may have thesame name; likewise, for address. Therefore, there are no transitive dependenciesin the table. Therefore, it meets the requirements of first, second and third normalform.

ORDER NEW COPY OF COIT20247 - DATABASE DESIGN AND DEVELOPMENT ASSIGNMENT AND SECURE HIGHER MARKS!

2. Relational database implementation

1. ER Model

2. Data Integrity

3. Information Requests

Answer: SELECT count(ParcelStatus.ParcelID) as NoOfDelivered
FROM ParcelStatus
WHERE (((ParcelStatus.Status)='Delivered'));

SELECT Lost.LostID, ParcelStatus.Status
FROM (ParcelDetails INNER JOIN (Lost INNER JOIN Claim ON Lost.LostID = Claim.LostID) ON ParcelDetails.ParcelID = Claim.ParcelID) INNER JOIN ParcelStatus ON ParcelDetails.ParcelID = ParcelStatus.ParcelID;

SELECT Max(ParcelStatus.Charges) AS maximumcharges
FROM ParcelDetails INNER JOIN ParcelStatus ON ParcelDetails.ParcelID = ParcelStatus.ParcelID;

SELECT Employee.EmployeeID, Employee.EmpName
FROM Employee;

SELECT Employee.EmpName
FROM Employee
WHERE (((Employee.EmpName)like'%wheel%'));

4. Query

Answer: 1. For each kind of delivery statusfind out the number of parcels. Display the delivery status and the corresponding number of parcels. Note: Delivery status can be different based on your assumption, however they could be collected or transit, delivered, returned, transit and so on.

2. Display the number of parcels that have been lost but have not been claimed yet for any compensation.

3. Display the details of the customers who have contributed the maximum amount of charges to CQPS through all oftheir parcel delivery orders.

4. List the details of employees who have handled/involved with at least five parcels and at the same time have not been involved with any of the lost parcels. Display their details in ascending order of their name.

5. Display the details of employee(s) whose name contains the word "wheel".

5. Report

Answer: SELECT Lost.LostID, ParcelDetails.ParcelID, ParcelDetails.CustomerID, Employee.EmployeeID, Employee.EmpName
FROM (ParcelDetails INNER JOIN (Lost INNER JOIN Claim ON Lost.LostID = Claim.LostID) ON ParcelDetails.ParcelID = Claim.ParcelID) INNER JOIN Employee ON ParcelDetails.ParcelID = Employee.ParcelID;

GET READYMADE COIT20247 - DATABASE DESIGN AND DEVELOPMENT ASSIGNMENT SOLUTIONS - 100% PLAGIARISM FREE WORK DOCUMENT AT NOMINAL CHARGES!

Acquire exclusive Central Queensland University, Australia assignment help and assessment help service for its major courses and units, such as:

• COIT20262 - Advanced Network Security Assignment Help
• COIT20270 - App Development for Mobile Platforms Assignment Help
• COIT13230 - Application Development Project Assignment Help
• COIT13240 - Applied Cryptography Assignment Help
• COIT20252 - Business Process Management Assignment Help
• COIT13229 - Applied Distributed Systems Assignment Help
• COIT29223 - Blockchain Technologies for Sustainable Business Applications Assignment Help
• COIT13232 - Business Analysis Project Assignment Help
• COIT20253 - Business Intelligence using Big Data Assignment Help
• COIT20260 - Cloud Computing for Smart Applications Assignment Help