Entity-Relationship and Relational Database

Question: Describe about Entity Relationship Diagram,Relational Model and Dependency diagrams and Normalization? Answer: Entity Relationship Diagram The ER diagram is, The assumptions and relationship cardinalities etc. are listed below, 1. There is many to many relationship between SERVICE and CLIENT entity sets. Each client can have at least one service and a service can be offered to at least one client. A relationship set SERVICE_CLIENT represents the many to many relationship. It has a composite key consisting of the primary keys from participating entity sets. 2. There is many to many relationship between SECTOR and CLIENT entity sets. Each client can have zero or more sectors and a sector can have at least one client. A relationship set SECTOR_CLIENT represents the many to many relationship. It has a composite key consisting of the primary keys from participating entity sets. 3. There is one to many relationship between SERVICE and SERVICE_TYPE. The primary key from SERVICE is added to SERVICE_TYPE as a foreign key. 4. There is many to many ternary relationship among SERVICE_TYPE, CLIENT and EMPLOYEE. The relationship set BILL represents that. BILL has composite primary key having primary keys from participating entities and another attribute called date. 5. There is one to many relationship between EMPLOYEE and PAYMENT. The primary key of EMPLOYEE is added to PAYMENT as foreign key. 6. There is one to many relationship between EMPLOYEE_TYPE and EMPLOYEE. The primary key of EMPLOYEE_TYPE is added to EMPLOYEE as foreign key. 7. There is many to many relationship between EMPLOYEE and EXPERTISE entity sets. Each employee can have at least one expertise and an expertise can have at least one employee. A relationship set EMPLOYEE_EXPERTISE represents the many to many relationship. It has a composite key consisting of the primary keys from participating entity sets. 8. There is many to many relationship between EMPLOYEE and QUALIFICATION entity sets. Each employee can have at least one qualification and a qualification can have at least one employee. A relationship set QUALIFICATION_EMPLOYEE represents the many to many relationship. It has a composite key consisting of the primary keys from participating entity sets. 9. There is a role of AuditAdvisor that is a one to many relationship from EMPLOYEE to EMPLOYEE. A foreign key AuditAdvisor has been added in the EMPLOYEE to represent that. 10. There is one to many relationship between STRUCTURE and CLIENT. The primary key from STRUCTURE is added to CLIENT to represent the relationship. Relational Model The relational model shown the relational schema and data types for the relations developed from the ERD. 1. CLIENT (ClientNumber(INT), FirstName (VARCHAR(45)), LastName(VARCHAR(45)), Address(VARCHAR(255)), City(VARCHAR(45)), State(VARCHAR(45)), PostCode(VARCHAR(4)), ContactName(VARCHAR(45)), ABN(VARCHAR(11)), TFN(VARCHAR(9)), StructureType(INT)) 2. SECTOR (SectorCode(INT), Name(VARCHAR(45))) 3. SERVICE (ServiceCode(INT), Name(VARCHAR(45))) 4. TYPE_SERVICE (TypeCode(INT), Name(VARCHAR(45)), ServiceCode(INT)) 5. SECTOR_ CLIENT (SectorCode(INT),ClientNumber(INT)) 6. SERVICE_CLIENT (ServiceCode(INT), ClientNumber(INT)) 7. STRUCTURE (StructureType(INT), Name(VARCHAR(45))) 8. EMPLOYEE (EmployeeNumber(INT), FirstName(VARCHAR(45)), LastName(VARCHAR(45)), HourlyRate(INT), AuditAdvisor(INT), EmployeeType(INT) ) 9. EMPLOYEE_TYPE (EmployeeType(INT), Name(VARCHAR(45))) 10. PAYMENT(EmployeeNumber(INT), Date(DATETIME), OutstandingAmount(INT), PaidAmount(INT)) 11. QUALIFICATION (QualificationType (INT), Name(VARCHAR(45))) 12. EXPERTISE (ExpertiseCode(INT), Name(VARCHAR(45))) 13. EMPLOYEE_ EXPERTISE (ExpertiseType(INT), EmployeeNumber(INT)) 14. QUALIFICATION_ EMPLOYEE(QualificationType(INT), EmployeeNumber(INT)) 15. BILL (Date(DATE), ClientNumber(INT), EmployeeNumber(INT), TypeCode(INT), ServiceCode(INT), Charge(INT)) Dependency diagrams and Normalization 1. CLIENT relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is only one primary key, so the relation is trivially in 2NF. There is transitive dependency from non-key attribute to key attribute. So the relation is not in 3NF. 2. SECTOR relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is only one primary key, so the relation is trivially in 2NF. There is only one non trivial functional dependency and the left hand side is the super key. So the relation is in 3NF. 3. SERVICE relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is only one primary key, so the relation is trivially in 2NF. There is only one non trivial functional dependency and the left hand side is the super key. So the relation is in 3NF. 4. TYPE_SERVICE relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is only one primary key, so the relation is trivially in 2NF. There is no transitive dependency in the relation. So, this is in 3NF. 5. SECTOR_ CLIENT is in 1NF as all attributes has atomic values. Now it is trivially in 3NF, as it has only one composite attribute and no other attribute. 6. SERVICE_CLIENT is in 1NF as all attributes has atomic values. Now it is trivially in 3NF, as it has only one composite attribute and no other attribute. 7. STRUCTURE relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is only one primary key, so the relation is trivially in 2NF. There is only one non trivial functional dependency and the left hand side is the super key. So the relation is in 3NF. 8. EMPLOYEE relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is no partial or transitive dependency so the relation is in 2NF and 3NF respectively. 9. EMPLOYEE_TYPE relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is only one primary key, so the relation is trivially in 2NF. There is only one non trivial functional dependency and the left hand side is the super key. So the relation is in 3NF. 10. PAYMENT relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is no partial or transitive dependency so the relation is in 2NF and 3NF respectively. 11. QUALIFICATION relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is only one primary key, so the relation is trivially in 2NF. There is only one non trivial functional dependency and the left hand side is the super key. So the relation is in 3NF. 12. EXPERTISE relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is only one primary key, so the relation is trivially in 2NF. There is only one non trivial functional dependency and the left hand side is the super key. So the relation is in 3NF. 13. EMPLOYEE_ EXPERTISE is in 1NF as all attributes has atomic values. Now it is trivially in 3NF, as it has only one composite attribute and no other attribute. 14. QUALIFICATION_ EMPLOYEE is in 1NF as all attributes has atomic values. Now it is trivially in 3NF, as it has only one composite attribute and no other attribute. 15. BILL relation has the following dependency diagram. The relation is in 1NF as all attributes has atomic values. There is no partial or transitive dependency so the relation is in 2NF and 3NF respectively. References Coronel, C., Morris, S. (2014). Database Systems: Design, Implementation, Management. Cengage Learning. Silberschatz, A., Korth, H., Sudarshan, S. (2010). Database System Concepts (6th ed.). McGraw-Hill Education. Ullman, J. D., Garcia-Molina, H., Widom, J. (2011). Database Systems: The Complete Book (7 ed.). Pearson.