Database Management System: Relational Data Assessment Answer

Answer:

(1) E-R diagram

 

(2) Relational DataStructures 

(A) It contains the different structures provided by CQR to the clients 

        CQRStructureTypes(

                                      Structure       varchar(50),

                                      Description    varchar(200) 

                                      primaryKey(Structure)

                                      );

(B) It contains the different sectors provided by CQR to group the clents         


"text-align: justify;">          CQRSectorTypes(

                                      Sector           varchar(50),

                                      Description    varchar(200)                                     


                                      primaryKey(Sector)

                                      );

(C) It contains the different services provided by CQR to the clients 

          CQRServicesTypes(

                             Service         varchar(50),

                             Description    varchar(200) 

                             primaryKey(Service)

                             );

(D) ParentClientID is just to have link for individuals who are part of any private client. 

          CQRClientsInfo(

                             ClientID                 varchar(10),

                             Name                    varchar(20),

                             Address                 varchar(100),

                             ContactNumber      varchar(20),

                             ABN                      varchar(20),

                             TFN                       varchar(20),  

                             Structure               varchar(50),

                             Sector                   varchar(50),

                             Service                  varchar(50),

                             ParentClientID        varchar(10) 

                             primaryKey(ClientID, Sector, Service)

                             foreignKey(Structure) referenced by  CQRStructureTypes(Structure)

                             foreignKey(Service) referenced by  CQRStructureTypes(Service)

                             foreignKey(Sector) referenced by  CQRStructureTypes(Sector)

                             );

(E) It contains information about different domains CQR supports 

          CQREmploymentDomains(

                              EmploymentDomain   varchar(20),

                              Description                varchar(200) 

                              primaryKey(EmploymentDomain);

                              );

(F) It contains information about different employee types CQR supports 

          CQREmpTypes(

                                      EmpType       varchar(20),

                                      Description    varchar(200) 

                                      primaryKey(EmpType);

                                      );

(G) It contains emploees information 

          CQREmployeesInfo

                                      EmpID                    varchar(10),

                                      EmploymentDomain varchar(20),

                                      EmpType                 varchar(10),

                                      BillingRate               float,

                                      SupervisorID           varchar(10) 

                                      primaryKey(EmpID, EmploymentDomain); 

                                      foreignKey(EmploymentDomain) references to                                                                                  CQREmploymentDomains(EmploymentDomain); 

                                      foreignKey(EmpType) references to                                                                                                CQREmpTypes(EmpType);

                                      );

(H) It contins the employee hour rates history 

          CQREmployeeHourRatesHostory(

                                      EmpID          varchar(10),

                                      StartDate      Date,

                                      EndDate        Date,

                                      BillingRate     float                  

                                      foreignKey(EmpID) references to  CQREmployeesInfo(EmpID)

                                      );

(I) It contains the work categories those can be done to client

          CQRWorkCategories(

                                      WorkCategory                   varchar(20),

                                      Charge                             flaot 

                                      primaryKey(WorkCategory)

                             );

(J) It contians the Client billing information 

          CQRClientBilling(

                                      ClientID                 varchar(10),

                                      EmpID                   varchar(10),

                                      WorkCategory        varchar(20),

                                      StartTime               Date,

                                      EndTime                 Date,

                                      Bill                         float 

                                      primaryKey(ClientID, EmpID, WorkCategory)

                                      foreignKey(ClientID) references to  CQRClientsInfo(ClientID)

                                foreignKey(EmpID) references to  CQREmployeesInfo(EmpID)

                             ); 

 (3) Normalisation of relations which identifies 

(A) Many clients can be mapped to same strucure type 

        CQRClientsInfo ----many-one------>  CQRStructureTypes

 

(B) same clients can be mapped many sectors and many clients mapped to many secotrs 

        CQRClientsInfo ----many-many------>  CQRSecotrsTypes

 

(C) Same clients can be mapped many services and many clients mapped to many services 

        CQRClientsInfo ----many-many------>  CQRServiceTypes

 

(D) Same employees mapped to many domains and many employees mapped to many domains 

        CQREmployeesInfo ----many-many------>  CQREmploymentDomains

 

(E) Many employees mapped to one employee type         

        CQREmployeesInfo ----one-many------>   CQREmpTypes 

 

(F) One client mapped to many works and many clients mapped one work. 

        CQRClientBilling ----many-many------>  CQRWorkCategories

 

(G) CQRClientsInfo is not in 1NF as address field is not atomic 

        CQRClientsInfo key (ClientID, Sector, Service), rest of the columns depending on ClientID which is part of the key. It is not in 2NF. 

        CQREmployeesInfo key (EmpID, EmploymentDomain), rest of the columns depending on EmpID which is part of the key. It is not in 2NF. 

        CQRClientBilling key (ClientID, EmpID, WorkCategory), rest of the columns depending on ClientID which is part of the key, it is not in 2NF. 

        Rest all other relations maintained in 3NF.

(4) Relational schema corrected to 3NF: 

        CQRStructureTypes(Structure, Description,

                                      primaryKey(Structure));         

        CQRSectorTypes(Sector, Description,                      

                                      primaryKey(Sector)); 

        CQRServicesTypes(Service, Description,

                                      primaryKey(Service)); 

        CQRClientsInfo(ClientID, Name, StreetName, City, Country, ContactNumber,

                                      ABN, TFN, Structure, ParentClientID,

                                      primaryKey(ClientID),

                                      foreignKey(Structure) referenced by  CQRStructureTypes(Structure));                                      

        CQRClientsSectors(ClientID, Sector,

                                      primaryKey(ClientID, Sector),

                                      foreignKey(Sector) referenced by  CQRSectorsTypes(Sector));         

        CQRClientsServices(ClientID, Service,

                                      primaryKey(ClientID, Service),

                                      foreignKey(Service) referenced by  CQRServicessTypes(Service));         

        CQREmployeeHourRatesHostory(EmpID, StartDate, EndDate, BillingRate,

                                      foreignKey(EmpID) references to  CQREmployeesInfo(EmpID)); 

        CQRWorkCategories(WorkCategory, Charge,

                                      primaryKey(WorkCategory)); 

        CQRClientBilling(ClientID, StartTime, EndTime,

                                      primaryKey(ClientID)); 

        CQRClientWorkCategories(ClientID, WorkCategory

                                      primaryKey(ClientID)

                                      foreignKey(WorkCategory) references to  CQRWorkCategories(WorkCategory)); 

        CQRClientEmployees(ClientID, EmpID

                                      primaryKey(ClientID)

                                      foreignKey(EmpID) references to  CQREmployeesInfo(EmpID)); 

Download Sample Now

Earn back money you have spent on downloaded sample



Upload Document Document Unser Evaluion Get Money Into Your Wallet



Cite This work.

To export a reference to this article please select a referencing stye below.

Assignment Hippo (2022) . Retrive from https://assignmenthippo.com/sample-assignment/database-management-system-relational-data-assessment-answer

"." Assignment Hippo ,2022, https://assignmenthippo.com/sample-assignment/database-management-system-relational-data-assessment-answer

Assignment Hippo (2022) . Available from: https://assignmenthippo.com/sample-assignment/database-management-system-relational-data-assessment-answer

[Accessed 21/05/2022].

Assignment Hippo . ''(Assignment Hippo,2022) https://assignmenthippo.com/sample-assignment/database-management-system-relational-data-assessment-answer accessed 21/05/2022.



Buy Database Management System: Relational Data Assessment Answers Online

Talk to our expert to get the help with Database Management System: Relational Data Assessment Answers from Assignment Hippo Experts to complete your assessment on time and boost your grades now

The main aim/motive of the finance assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignment help Australia. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at www.assignmenthippo.com are so much skilled, capable, talented, and experienced in their field and use our best and free Citation Generator and cite your writing assignments, so, for this, they can effectively write the best economics assignment help services.

Get Online Support for Database Management System: Relational Data Assessment Answer Assignment Help Online

Want to order fresh copy of the Sample Database Management System: Relational Data Assessment Answers? online or do you need the old solutions for Sample Database Management System: Relational Data Assessment Answer, contact our customer support or talk to us to get the answers of it.

Assignment Help Australia
Want latest solution of this assignment

Want to order fresh copy of the Database Management System: Relational Data Assessment Answers? online or do you need the old solutions for Sample Database Management System: Relational Data Assessment Answer, contact our customer support or talk to us to get the answers of it.


Submit Your Assignment Here

AssignmentHippo Features

On Time Delivery

Our motto is deliver assignment on Time. Our Expert writers deliver quality assignments to the students.

Plagiarism Free Work

Get reliable and unique assignments by using our 100% plagiarism-free.

24 X 7 Live Help

Get connected 24*7 with our Live Chat support executives to receive instant solutions for your assignment.

Services For All Subjects

Get Help with all the subjects like: Programming, Accounting, Finance, Engineering, Law and Marketing.

Best Price Guarantee

Get premium service at a pocket-friendly rate at AssignmentHippo

FREE RESOURCES

  • Assignment Writing Guide
  • Essay Writing Guide
  • Dissertation Writing Guide
  • Research Paper Writing Guide

FREE SAMPLE FILE

  • Accounts
  • Computer Science
  • Economics
  • Engineering

Client Review

I was struggling so hard to complete my marketing assignment on brand development when I decided to finally reach to the experts of this portal. They certainly deliver perfect consistency and the desired format. The content prepared by the experts of this platform was simply amazing. I definitely owe my grades to them.

Tap to Chat
Get instant assignment help