Introduction
This report shows the design of a database for the Care for Kids crèche database. This is a database which will be used to store information regarding the booking of the care center. The staff will manage to store information regarding the booking that will be made by the parents to the care center. It is important to understand the requirements of the database and how data will be related. The paper will assess the logical design and how the design will help create a database that will be used in the care center.
Design
There will be various tables that will be designed in the database. Before the formation of the different tables, there is a need to understand the logic that will be used to design the tables. Initially the table will consist of a large table that will have the attributes of all the entities and objects that will be used in the database.
Determinancy diagram for un-normalized form
There is a need to identify the repeating groups which will need to be eradicated from the table. The repeating groups are the rooms that the children will be staying. There is a need to have a separate
The table is clear that it is not in any normalization state. This is because there are groups of attributes which will repeat. There is a need to have some other table which will take care of the repeating groups.
1st Normal form
There is a need to put the table into 1NF so that repeating groups are eradicated.
Determinancy diagram for 1NF
The table has been set in first normal form. Even though this is the case, there are issues that are still lacking. One of the issues with the tables is that of redundancy. This is because of the fact that there is the repetition of staff name, parent name in case there is a parent with two kids. The name of the parent will repeat. Another problem is that of insertion and deletion anomalies. This is because a room cannot be inserted to the database unless there are children and staff who will be allocated in that room. Also, deleting a child who was located in one room and was alone would also delete the details of that room. This means that there will be no memory that the room ever existed. There is a need to have the table in 2nd Normal form.
2nd Normal form
The description of the second normal form will be based on the first normal form. There is a need to determine the functional dependencies that exist in the attributes.
Determinancy diagram for 2nd Normal Form
The design has been able to eradicate most of the design issues with the database. There are still issues that are seen in the design.
Problems with 2NF
There are still issues with the 2NF. This is because of the fact that there is still redundancy in the table. There could be parents who have more than one kid. In this case, the parent details will be eradicated. There are also insertion anomalies because staff will not be added to the database unless that staff member is responsible for a group of children. Another issue is that of deletion anomalies. This is because if we delete a staff member who is responsible for one child, the information about that staff member.
3rd Normal form
A table is said to be in third normal form is it is in 2NF but should also satisfy rule of non-transitive dependency which states that every non-key attribute should non-transitively be dependent on the primary key.
Determinancy diagram in 3NF
After the final design and alterations, the table is in third normal form and will eradicate redundancies. It is a good design as there will be no repetition.
There will also be the need to create a schedule table which will be printed for the parents. The scheduled table will have the schedule of the programs. The determinancy diagram for this table is as follows:
After the determinancy diagrams have been used to get the design in 3NF, it is now right to design the ERD diagrams for the Care for Kids database.
ERD diagrams
Attribute values
The attributes that will be used for the tables will have various characteristics and values. The table below gives a description of the relationships and the features of the attributes that will be used in the tables.
Analysis and verification
Conclusion
The design of the database has followed all the requirements and the design issues that need to be followed while designing a database. All these have been followed and the designer is assured that the database will function properly. The relationships that exist between the tables have been followed correctly.
References
Batini, C., Ceri, S., & Navathe, S. B. (1992). Conceptual database design: an Entity-relationship approach (Vol. 116). Redwood City, CA: Benjamin/Cummings.
Halpin, T., & Morgan, T. (2010). Information modeling and relational databases. Morgan Kaufmann.
Lightstone, S. S., Nadeau, T., & Jagadish, H. V. (2011). Database Modeling and Design: Logical Design. Elsevier.
Teorey, T. J., Lightstone, S. S., Nadeau, T., & Jagadish, H. V. (2011).Database modeling and design: Logical design. Elsevier.
Thonggoom, O., Song, I. Y., & An, Y. (2011, January). EIPW: A Knowledge-based Database Modeling Tool. In Advanced Information Systems Engineering Workshops (pp. 119-133). Springer Berlin Heidelberg.
