Assessor (Assessor_ID [PK], Assessor_Name, Qualification, Date_of_Qualification)
- The primary key being identified in this relation is Assessor_ID that helps to identify an Assessor. Only one ID will be assigned against each Assessor name as only record will be there against a particular assessor. The qualification may be common for different assessors thus it has not been set as a primary key as well two or more assessors may also have the same name. There is also a possibility that two or more assessors have common Date_of_Qualification. From, the relation it has been identified there is no occurrence of repeating attributes thus it can be said that it is in 1st Normal Form.
- Assessor_ID is the primary key as it cannot be ensured that there is an Assessor with unique name means there is a possibility that two or more assessors may have same names. The qualification is also not unique for assessors as two or more assessor may have same qualification. This means that every other attribute of the Assessor Table can be determined with the help of Assessor_ID. The primary key is single valued attribute and there is no partial dependency hence it can be said that the relation is in 2ndNormal Form.
- The Assessor_Name cannot be used for determining the other attributes of the Assessor Table as it is not unique. It has been identified that there are no transitive dependencies within the table hence it can be said that the relation is in 3rdNormal Form.
Claim (Claim_ID [PK], Claim_Date, Assessor_ID [FK], Policy_Number [FK], Claim_Status)
- Claim_ID has been identified as the primary key in this relation that defines the attributes related to Claim. The Claim_ID has been chosen as the primary key as there will be only one policy will associated with per claim. From, the relation it has been identified there is no occurrence of repeating attributes thus it can be said that it is in 1st Normal Form.
- Claim_ID is primary key in the identified relation and it has been chosen as the Claim_Date is not unique and it can be same for two or more claims. Thus, it has been identified that Claim_ID helps to determine the attributes in Claim Table and there is no partial dependency in the table. Hence, it can be said that the relation is in 2ndNormal Form.
- The Policy_Number in the table cannot be used separately for determining the other attributes within the Claim Table. It has been identified that there is no transitive dependency within the table. Thus, it can be said that the relation is in 3rdNormal Form.
The two interesting things learnt by doing this assignment on database design and development are presented as below:
- Creation of tables in MS Access Database along with defining of relationships within the different tables in the database.
- Developing queries by writing SQL statements to acquire the desired outputs from developed tables in the database.
Brdjanin, D., Banjac, G., Banjac, D. and Maric, S., 2017. Controlled experiment in business model-driven conceptual database design. In Enterprise, Business-Process and Information Systems Modeling (pp. 289-304). Springer, Cham.
Bugiotti, F., Cabibbo, L., Atzeni, P. and Torlone, R., 2014, October. Database design for NoSQL systems. In International Conference on Conceptual Modeling (pp. 223-231). Springer, Cham.
Coronel, C. and Morris, S., 2016. Database systems: design, implementation, & management. Cengage Learning.
Hoffer, J., Ramesh, V. and Topi, H., 2015. Modern database management (pp. 393-443). Prentice Hall.
Hogan, R., 2018. A Practical Guide to Database Design. Chapman and Hall/CRC.
Letkowski, J., 2015. Doing database design with MySQL. Journal of Technology Research, 6, p.1.
Parks, R.F. and Hall, C., 2016. Front-End and Back-End Database Design and Development: Scholar’s Academy Case Study. Information Systems Education Journal, 14(2), p.58.
Uduwela, W.C. and Wijayarathna, G., 2016. End-user Enable Database Design and Development Automation.
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
