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

Database Management System: Database Management System Add in library

236 Download6 Pages 1,363 Words

Question-

(1) E-R diagram:adherence to our standard, assumptions made, inclusion of correct primary ad foreign keys, approproate entities, reationships, and attributes.
(2) Relational data structures: correct and meaningful translation of your E-R diagram.
(3) Normalisation: appropriate interpretation of each normal form, arguments for leaving the schema in the normal from you consider optimal.
 
 

Answer:  

(1) Entity Relationship Diagram

(2)  Relational Model

The relations schemas and data types of the relations from the ERD are listed as,
(A) CLIENT (ClientNumber, FirstName, LastName, Address, City, State, PostCode, ContactName, ABN, TFN, Structure_StructureCode)

WAMP server data types are,

Attribute

Data Type

ClientNumber

INT

FirstName

VARCHAR(45)

LastName

VARCHAR(45)

Address

VARCHAR(45)

City

VARCHAR(45)

State

VARCHAR(45)

PostCode

VARCHAR(4)

ContactName

VARCHAR(45)

ABN

VARCHAR(11)

TFN

VARCHAR(9)

Structure_StructureCode

INT

 

B. EMPLOYEE (EmployeeNumber, FirstName, LastName, HourlyRate, AuditAdvisor, EmployeeType_EmployeeTypeCode )

WAMP server data types are,

Attribute

Data Type

EmployeeNumber

INT

Name

VARCHAR(45)

HourlyRate

INT

AuditAdvisor

INT

EmployeeType

INT

 

C. SECTOR (SectorCode, Name)

WAMP server data types are,

Attribute

Data Type

SectorCode

INT

Name

VARCHAR(45)

 

D. SERVICE (ServiceCode, Name)

WAMP server data types are,

Attribute

Data Type

ServiceCode

INT

Name

VARCHAR(45)

 

E. SECTOR_HAS_CLIENT (Sector_SectorCode, Client_ClientNumber )

WAMP server data types are,

Attribute

Data Type

Sector_SectorCode

INT

Client_ClientNumber

INT

 

F. SERVICE_HAS_CLIENT (Service_ServiceCode, Client_ClientNumber )

WAMP server data types are,

Attribute

Data Type

Service_ServiceCode

INT

Client_ClientNumber

INT

 

G. STRUCTURE (StructureCode, Description)

WAMP server data types are,

Attribute

Data Type

StructureCode

INT

Description

VARCHAR(45)

 

H. EMPLOYEE_TYPE (EmployeeTypeCode, Description)

WAMP server data types are,

Attribute

Data Type

EmployeeTypeCode

INT

Description

VARCHAR(45)

 

I. QUALIFICATION (QualificationCode, Description)

WAMP server data types are,

Attribute

Data Type

QualificationCode

INT

Description

VARCHAR(45)

 

J. EXPERTISE (ExpertiseCode, Description)

WAMP server data types are,

Attribute

Data Type

ExpertiseCode

INT

Description

VARCHAR(45)

 

K. EMPLOYEE_ HAS_EXPERTISE (Employee_EmployeeNumber, Expertise_ExpertiseCode )

WAMP server data types are,

Attribute

Data Type

Employee_EmployeeNumber

INT

Expertise_ExpertiseCode

INT

 

L. EMPLOYEE_ HAS_QUALIFICATION(Qualification_QualificationCode, Employee_EmployeeNumber)

WAMP server data types are,

Attribute

Data Type

Qualification_QualificationCode

INT

Employee_EmployeeNumber

INT

 

M. TYPE_SERVICE (TypeServiceCode, TypeServiceName, Service_ServiceCode)

WAMP server data types are,

Attribute

Data Type

TypeServiceCode

INT

TypeServiceName

VARCHAR(45)

Service_ServiceCode

INT

 

M. BILL (Date, ChargeService, TotalAmount, Type_Service_Type_Service_Code, Type_Service_Service_ServiceCode, Employee_EmployeeNumber, Client_ClientNumber)

WAMP server data types are,

Attribute

Data Type

Date

DATETIME

ChargeService

VARCHAR(45)

TotalAmount

VARCHAR(45)

Type_Service_Type_Service_Code

INT

Type_Service_Service_ServiceCode

INT

Employee_EmployeeNumber

INT

Client_ClientNumber

INT

 (3) Normalization

A. The dependency diagram for CLIENT relation is,
1. All attributes of the relation has atomic values. There is primary key in the relation. So it is in 1NF.
2. There in partial dependency, as the primary key has only one attribute. So, the relation is in 2NF.
3. There is transitive dependency on primary key through non-key attribute PostCode. So, the relation is not in 3NF.
 
B.The dependency diagram for EMPLOYEE relation is,
1. All attributes of the relation has atomic values. There is primary key in the relation. So it is in 1NF.
2. There in partial dependency, as the primary key has only one attribute. So, the relation is in 2NF.
3. There is no transitive dependency. So, the relation is in 3NF.
 
C. The dependency diagram for SECTOR relation is,
1. All attributes of the relation has atomic values. There is primary key in the relation. So it is in 1NF.
2. There in partial dependency, as the primary key has only one attribute. So, the relation is in 2NF.
3 There is no transitive dependency. Also, there is only one functional dependency in the relationship between one key and one non key attribute. So, the relation is trivially in 3NF.
 
D. The dependency diagram for SERVICE relation is,
 
 
1. All attributes of the relation has atomic values. There is primary key in the relation. So it is in 1NF.
2. There in partial dependency, as the primary key has only one attribute. So, the relation is in 2NF.
There is no transitive dependency. Also, there is only one functional dependency in the relationship between one key and one non key attribute. So, the relation is trivially in 3NF
 
 
E. The relation SECTOR_HAS_CLIENT is trivially in 3NF. There is no multivalued or composite attribute in the relation, so it is in 1NF. There are two attributes in the relation and both are part of primary key. So, there is only one composite primary key in the relation. So, it is trivially in 2NF and 3NF.
 
F. The relation SERVICE_HAS_CLIENT is trivially in 3NF. There is no multivalued or composite attribute in the relation, so it is in 1NF. There are two attributes in the relation and both are part of primary key. So, there is only one composite primary key in the relation. So, it is trivially in 2NF and 3NF.
 
G. The dependency diagram for STRUCTURE relation is,
 
1. All attributes of the relation has atomic values. There is primary key in the relation. So it is in 1NF.
2. There in partial dependency, as the primary key has only one attribute. So, the relation is in 2NF.
3. There is no transitive dependency. Also, there is only one functional dependency in the relationship between one key and one non key attribute. So, the relation is trivially in 3NF.
 
H. The dependency diagram for EMPLOYEE_TYPE relation is,
1. All attributes of the relation has atomic values. There is primary key in the relation. So it is in 1NF.
2. There in partial dependency, as the primary key has only one attribute. So, the relation is in 2NF.
3. There is no transitive dependency. Also, there is only one functional dependency in the relationship between one key and one non key attribute. So, the relation is trivially in 3NF
 
I. The dependency diagram for QUALIFICATION relation is,
1. All attributes of the relation has atomic values. There is primary key in the relation. So it is in 1NF.
2. There in partial dependency, as the primary key has only one attribute. So, the relation is in 2NF.
3. There is no transitive dependency. Also, there is only one functional dependency in the relationship between one key and one non key attribute. So, the relation is trivially in 3NF
 
J. The dependency diagram for EXPERTISE relation is,
 
1. All attributes of the relation has atomic values. There is primary key in the relation. So it is in 1NF.
2. There in partial dependency, as the primary key has only one attribute. So, the relation is in 2NF.
3. There is no transitive dependency. Also, there is only one functional dependency in the relationship between one key and one non key attribute. So, the relation is trivially in 3NF.
 
K. The relation EMPLOYEE_ HAS_EXPERTISE is trivially in 3NF. There is no multivalued or composite attribute in the relation, so it is in 1NF. There are two attributes in the relation and both are part of primary key. So, there is only one composite primary key in the relation. So, it is trivially in 2NF and 3NF.
 
L. The relation EMPLOYEE_ HAS_QUALIFICATION is trivially in 3NF. There is no multivalued or composite attribute in the relation, so it is in 1NF. There are two attributes in the relation and both are part of primary key. So, there is only one composite primary key in the relation. So, it is trivially in 2NF and 3NF.
 
M. The dependency diagram for TYPE_SERVICE is,
1. All attributes of the relation has atomic values. There is primary key in the relation. So it is in 1NF.
2. There in partial dependency, as the primary key has only one attribute. So, the relation is in 2NF.
3. There is no transitive dependency. So, the relation is trivially in 3NF.
 
 
N. The dependency diagram for BILL is,
1. All attributes of the relation has atomic values. There is primary key in the relation. So it is in 1NF.
2. There is partial dependency on prime attributes. So the relation is not in 2NF.

References

Harrington, J. L. (2002). Relational Database Design Clearly Explained. Morgan Kaufmann. 

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 
164 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?