I select PERSON and PROPERTY relations. Here we get all the information related to PROPERTY by PropertyID. PropertyID also provides the information of PersonId then PersonId provides all information related to Person.
PropertyID-> {Address, Street, City, Postcode, PersonId }
PersonId-> {PersonName, Address, Street, City, Postcode, PersonType }
2. Relation in Third Normal Form (3NF)
PROPERTY
|
PropertyID
|
Address
|
Street
|
City
|
Postcode
|
PersonId*
|
Person
|
PersonId
|
PersonName
|
Address
|
Street
|
City
|
Postcode
|
PersonType
|
The above two relations are in 3NF because there is no transitive dependency. The relationship between property and person is one person has zero or many properties but each property takes care of one and only one person. Here in property relation, PropertyID is the primary key and PersonId is the foreign key. in Person relation, PersonId is the primary key.
3.Two interesting things: -
- How to create the report by using query and how to design a report in an effective manner. Firstly I create a query according to the report requirements. Then I create a report by using report wizard. In report wizard, I select query that I was created according to reporting requirements. Then I select only that information that required for the report. Then I used to group function group policy and property information. In detail section, I display claims information. I used sort function to order the claim by rejected date. Then I open the report in design view and format it in an effective manner.
- How to get correct result by using many functions in queries. In this assignment I used count function, also used queries as the column of a table. I also used sum function to calculate the amount according to requirements.
- 4. Complex requirement: -
When I try to solve the first and third query then I think how to count the child table's records by using parent table. Then I decide to type query as the column of the table and count the records of child table according to requirements. Then I execute the query. It displays right output but repeats the output. This output repeated equal to the number of records in the parent table. Then I try many conditions in the query and also try query by changing data in tables. But it displays repeat output. Then I used top 1 with select then it displays correct result.
References: -
Cronan, J. (2010). Microsoft Office Access 2010. New York: McGraw-Hill.
Eliot, G. (2016). The mill on the Floss. New York: Open Road Integrated Media.
Elmasri, R. and Navathe, S. (2017). Fundamentals of database systems. 1272 Seiten: [Verlag nicht ermittelbar].
Griffith, G., Bradly, T., Long, C., Ranlett, C. and Marshall, K. (2011). Microsoft Access 2010 24-hour trainer. Indianapolis, Ind.: Wrox/Wiley Pub.
MacWhinney, B. (2000). The database. Mahwah, NJ [u.a.]: Lawrence Erlbaum.
Munnelly, B. and Holden, P. (2002). ECDL3 for Microsoft Office 95/97. London: Prentice Hall.
O'Leary, T. and O'Leary, L. (2011). Microsoft Access 2010. New York, NY: McGraw-Hill.
O'Neil, P. (2014). Database. Elsevier Science.
SILBERSCHATZ, A. (2019). DATABASE SYSTEM CONCEPTS. [S.l.]: MCGRAW-HILL EDUCATION.
Tremblay, D. (2011). Access 2010. [Fairport]: Axzo Press.
