Database Management System Database Management System

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.