Securing Higher Grades Costing Your Pocket? Book Your Assignment at The Lowest Price Now!

Computer Science ER Diagram Add in library

209 Download17 Pages 4,118 Words

Question:

To give  you  practical experience in using Entity-Relationship and Relational  Database  modelling techniques
Project Specification: Alan Counting  Edward Quals and Peter  Rofit are in partnership in an accounting practice  CQR. tHE   practice  specialises  in taxation auditing  and financial  advice  for small to medium  sized clients. They started  the pracitce  in 2005  and it has grown to now employ 25 people . These employees include  other accountants and office staff.Alan, Edward  and Peter believe the practice  is headed  for  a period  of significant growth  and have  come to you to help them redevelop their client billing system so that it copes with their workflows  and supports future growth  opportunities.
 
 

Answer:

ER diagram

      Attached as PDF document.     

 Relational DataStructures

  1. It contains the different structures provided by CQR to the clients

CQRStructureTypes(

                                      Structure       varchar(50),

                                      Description    varchar(200)

  primaryKey(Structure)

                                      );

  1. It contains the different sectors provided by CQR to group the clents

    CQRSectorTypes(

                                      Sector           varchar(50),

                                      Description    varchar(200)

                                    primaryKey(Sector)

                                      );

  1. It contains the different services provided by CQR to the clients

  CQRServicesTypes(

                             Service         varchar(50),

                             Description    varchar(200)

 

                           primaryKey(Service)

                             );

 

 

  1. 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)

  1. It contains information about different domains CQR supports

 CQREmploymentDomains(

                                                EmploymentDomain   varchar(20),

                                                Description             varchar(200)

 

                                                primaryKey(EmploymentDomain);

                                                );

 

  1. It contains information about different employee types CQR supports
 

CQREmpTypes(

                                      EmpType       varchar(20),

                                      Description             varchar(200)

 

                                      primaryKey(EmpType);

                                      );

 

  1. It contains emploees information  CQREmployeesIn
  2. 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);

                                      )

  1. It contins the employee hour rates history

  CQREmployeeHourRatesHostory(

                                      EmpID          varchar(10),

                                      StartDate      Date,

                                      EndDate        Date,

                                      BillingRate     float

                  

                                      foreignKey(EmpID) references to CQREmployeesInfo(EmpID)

                                      );

  1. It contains the work categories those can be done to client
 

          CQRWorkCategories(

                                      WorkCategory                   varchar(20),

                                      Charge                             flaot      primaryKey(WorkCategory));

 

  1. 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)

                             );

 

 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.

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

 

 References

          Studytonight, 2014, DataBase Management System

          Vangie Beal, 2014, Normalization

       

 

         

OR

MyAssignmenthelp.com delivers assignment help to millions of students of USA. We have in-house teams of assignment writers who are experts on wide ranges of subjects. We have appointed teams of native writers who provide assignment help to students in New York City and all over the USA. They are skilled assignment writers who successfully cater to search terms like do my assignment in the USA

Most Downloaded Sample of Programing

168 Download2 Pages 309 Words

These Items Act As Barriers To Intercultural Communication

1. How do these items act as barriers to intercultural communication? Answer:  The communication barriers Linguistic, Cultural and Emotional act as barriers for...

Read More Tags: Australia Arlington Management Management University of New South Wales Management 
65 Download2 Pages 429 Words

The Asch And Milgram Experiments Assignment

A just and fair decision always reflects an objective process, however, not all decisions are fair and just because they have influences and impacts that is actuall...

Read More Tags: Australia Arlington Management Management University of New South Wales Management 
166 Download10 Pages 2,301 Words

Digital Forensic Case Study

Question - Write a case study on Digital Forensic methodology?     Contents Executive Summary. Introduction.       Global F...

Read More Tags: Australia Arlington Management Management University of New South Wales Management 
40 Download10 Pages 2,338 Words

Professional Skills For Information And Communication Technology Portfolio

  Table of Contents Introduction Week 3: Critical Thinking Week 5: SWOT analysis of presentation skills Week 7: Reflective Writing Week 11: Personal Ethi...

Read More Tags: Australia Arlington Management Management University of New South Wales Management 
90 Download16 Pages 3,848 Words

Role Of Information Technology On Services Of Aldermore Bank

Question-Write A report based on role of information technology of financial corporate sector company Aldermore bank?     Executive summary The report i...

Read More Tags: Australia Arlington Management Management University of New South Wales Management 
Next
Free plagiarismFree plagiarism check online on mobile
Have any Query?