The provided ERD has been divided into 3NF and you can see it by counting number of tables or relations made for our new access database.
Select any two (2) of your relations from the previous step, 1a), and perform the following for each of those two relations
Demonstrate that the person table meets Third Normal Form (3NF).
Read more about Database Management Assignments help
PERSON (PersonID, person_name, street_number, street_name, suburb, state, postcode)
- The primary key is person_id. There will be one person associated with one person_id, since only one name is recorded for each person. There will be one address per person_id; even though a person may have multiple addresses the case study states that there is a need to record just one address (street_number, street_name, suburb, state, and postcode). Thus, there are no repeating groups. Hence, this relation is in 1NF.
- The primary key is person_id. This is the only candidate key since Name is not guaranteed to be unique (two different persons may have the same name). Address is also not guaranteed to be unique. This means that person_id functionally determines every other attribute in the table. There is a single simple candidate key (person_id) and therefore no partial dependencies are possible. Hence, the relation is in 2NF.
- Person_Name cannot be used to functionally determine any other attribute in the table since two different persons may have the same name; likewise for street name, suburb, state and postcode. Therefore there are no transitive dependencies in the table. Therefore it meets the requirements of first, second and third normal
Demonstrate that the person_type table meets Third Normal Form (3NF).
Person_type (person_type_id, type_detail_id, certification_date, ABN_number,)
- The primary key is person_type_id. There will be one type associated with each person and hence has only one person_type_id. The certification date may be same for many persons or their particular type but for one person_type there will be one date. Thus, there are no repeating groups. Hence, this relation is in 1NF.
- The primary key is person_type_id. This is the only candidate key since certification_date, can have several person associated with. There is a single simple candidate key (person_type_id) and therefore no partial dependencies are possible. Hence, the relation is in 2NF.
- Certification_date cannot be used to functionally determine any other attribute in the table since two different persons may have the same certification date. Therefore there are no transitive dependencies in the table. Therefore it meets the requirements of first, second and third normal
Read more about Database Management Assignments Help
The two interesting thing/procedures, which we learnt at the time of doing this assignment is implementation of normalization and generation the queries.
Read more about Database Assignment Help
General Definition of the Functional dependencies
The functional dependencies are used to define the forms. It is important to understand that a functional dependencies means, the right most left attributes completely determines the left most attributes every time, there is no matter which type of extra data added into the database. In database development every tables has its set of functional dependencies which is completely based on to the attributes meaning, functional dependencies works on to the concept of the super key concept. Every table has a super key and all the other attributes of the table are functionally dependent.
A functional dependency will work when one unique attribute determines other attributes. This is symbolically represent as A->B, means B is functionally dependent on to attribute A.
Implementation of the functional dependencies in project database design and development
As doing assignment database design and development I developed a database in Microsoft access database development engine, I learned the concept of functional dependency and also learn that how all the attributes is dependent onto the unique attribute of a table. In project of Database design and development in table “Application” all the three attribute application date, total charges and application type is functionally dependent is ApplicationId. Similarly I have clear this concept and successfully implement this in throughout the database.
I have clear the concept after doing this assignment that in table INSPECTION the three attributes inspection date, reports and result is functionally dependent on to the inspectionId.
General definition of Data integrity
Data integrity is actually a value which is used and stored into the data structure of the database application. Using some process the data integrity helps to produce a correct information continuously. The Microsoft access database development engine has four methods to maintain the data integrity. These four methods are normalizing data, defining business rules, providing referential integrity and data validation.
As doing assignment database design and development I developed a database in Microsoft access database development engine, I learned the concept of data integrity and implementation so that the database produce a correct each time whenever require. In project of database design and development, I have implement integrity constraints within the database to enforce the following requirements:
For the table TreeRemoval: Using data integrity I have apply validation rule on following requirements so that the output of the database could be correct.
- All fields must be entered.
- Charges should be positive and expressed 2 decimals only.
- Number of Trees should be positive round numbers only.
I also applied a strong validation using the data integrity concept to get correct output on to the table Inspection:
Date should not be null.
For the table Owner:
- Name should not be null.
- State value should be limited to Australian States/Territories only.
(No references used)
Any one requirement of the assignment that was relatively difficult / complex to solve but successfully completed by you
During the development of the database for this assignment database design and development I have faces difficulties to generate reports with correct output as per the requirement of the project database design and development as per the requirement. There is much more error arises when the final output of report is come out. I have to learn again and deeply, how to create a report as per the requirement deeply and then I able to create and generate the error free report as per the requirement. I have successfully created a simple report object that showing the details of all the inspections carried out by each inspector. This report now provides a correct result and have appropriate heading and display the details of inspections performed by each of inspector separately. I have to gain deep knowledge to create an effective report Now that provides output with correct details of inspection which is done by individual inspector and it is successfully displayed in ascending order of the date of inspection.
(No references used)
Read more about Computer Programming Assignment Help
Opentextbc.ca, (2015). Chapter 12 Normalization | Database Design. [online] Available at: http://opentextbc.ca/dbdesign/chapter/chapter-12-normalization/ [Accessed 30 Dec. 2015].
W3schools. (2014). Database Normalization. [online] Available at: http://www.w3schools.in/dbms/database-normalization/ [Accessed 23 Sep. 2016].
dummies. (2016). How to Build a New Database in Access 2016 – dummies. [online] Available at: http://www.dummies.com/software/microsoft-office/access/how-to-build-a-new-database-in-access-2016/ [Accessed 23 Sep. 2016].
Support.office.com. (2016). Access SQL: basic concepts, vocabulary, and syntax – Access. [online] Available at: https://support.office.com/en-us/article/Access-SQL-basic-concepts-vocabulary-and-syntax-444d0303-cde1-424e-9a74-e8dc3e460671 [Accessed 23 Sep. 2016].
Databasedev.co.uk. (2016). Primary & Foreign Key Constraints | Database Solutions for Microsoft Access | databasedev.co.uk. [online] Available at: http://www.databasedev.co.uk/primary_foreign_key_constraints.html [Accessed 23 Sep. 2