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)); 



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.


); }