Database Management System: Relational Data Assessment Answer
Answer:
(1) E-R diagram
(2) Relational DataStructures
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 AustraliaWant 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.