The report is giving complete overview about CQI database. The database is in normalized form and it is achieved by mapping the ER diagram to the relational database. All queries, database reports etc. are generated in CQI database and the RDBMS MS Access is used in developing the CQI database.
Normalization
ER Diagram Mapping
Person Table
Person (FldPersonID, FldPersonName, FldStreet, FldCity, FldPostcode, FldPersonType)
FldPersonNameàFldPersonID
FldPersonTypeàFldPersonID
All fields in the Person table are relying upon the primary key Person ID.
Property Table
Property (FldPropertyID, FldStreet, FldCity, FldPostcode, FldPersonID)
FldStreetàFldPropertyID
FldCityàFldPropertyID
FldPostcodeàFldPropertyID
FldPersonIDàFldPropertyID
All fields in the Property table are relying upon the primary key Property ID.
Normalisation
The database is completed as normalized data. Third normal form is achieved in making the CQI database. Following steps are followed in making the 3 NF
- Make separate tables each group of data.
- Make unique key or primary key to identify the records.
- Only primary key should uniquely identify the records not any other field.
- Remove transitive dependency if any.
All the tables in the CQI database are in 3 NF as per the rules defined above.
(Brombarg P. n.d.)
(Janalta Interactive Inc. 2015)
SQL Queries
Query 1
SELECT subQr1.Home_Policy as NumHomePolicy, subQr2.Home_Content_Policy as NumHomeContentPolicy FROM (SELECT Count(*) AS Home_Policy FROM Home_Building) AS subQr1, (SELECT Count(*) AS Home_Content_Policy FROM Home_Content) AS subQr2;
Learning Features
The assignment is giving so many learning features in detail-
- Database sub queries are specified in the assignment.
- Database queries are specified in the assignment.
- Database reports are specified in the assignment.
- Group by clauses are specified in the assignment.
- Aggregate functions are specified in the assignment.
- Normalization is specified in the assignment.
Complex Features
The assignment is having all the things in normal way. I faced some complexity in making the report and sub queries. But later I created the report by report wizard and solved the sub queries that are used in query 1 and query 3.
Conclusion
The report is giving complete overview about database normalization. It is explaining in detail about how the database should be in achieved I third normal form. Everything is shown in the report along with the all query results and database report also.
References
Brombarg P.( n.d.). SQL SERVER Database Normalization Basics for Developers.
Janalta Interactive Inc. (2015), Functional Dependency,
Order an Essay Now & Get These Features For Free:
Turnitin Report
Formatting
Title Page
Citation
Outline
