A database is a saving bank that is used to store data or information which is crucial in organizations daily activities and for record purposes. It allows users to store data in a precise organized manner, once the data has been stored; it is easy to retrieve the information using certain criteria. The manner in which information is stored in any database determines how easy it is to get the information searched, based on several criteria. It makes it easy to add information to the database, remove and modify and at the same time sort or filter.
Entities relate with each other through the use of foreign keys. Foreign key is used to reference a given attribute of the entity containing a corresponding primary key. For example, a student entity has a registration number as the primary key for a student and department entity with department ID as the primary key and can be related to each other by use of student registration number. Therefore, student’s registration number will be the foreign key for the department entity and the student number is the primary key for the student entity.
An entity is an object with physical existence, for example, department, lecturer, and course and an object with conceptual existence. An attribute describes an entity, for example, student’s name and address are the attributes. So the attributes for student entity are; Student name, Registration number and address. For the course entity, the attributes are; course ID and course name. Department entity will have; department ID and department name as attribute. Lecturer entity will have; lecturer’s name, lectures ID and course ID that he teaches. Therefore, student registration number in the course table is the foreign key referencing student registration number in student table. So to develop a college’s database to maintain students, course and lecturers information, it should be store and provide data on students such as when was the student admitted; is the student still in college; which department does the student belong; who is his/her lecturer etc.
There are some rules that can impact database structure. Some of these rules include the fact that the data should be accurate, and constraints. This data should be applied at the column level. Data entered to a given column should be verified, and the end user should be able to manage the entered data. Also, data should be protected. In addition, the database security must be established to restrict access of unauthorized users to the data. The database should have acceptable performance mainly because performance is the paramount issue for any database; Minimal redundancy, this means that data should only be stored once in the database; data should be readily available to the end user during all time of operation, and also storage needs should be met, this is the main objective of any database.
Conceptual model of the database help us understand the different entities and how they are related to each other, this will include the following information: Entity names and entity relationships. In this model attributes are not specified, the relationship is just stated. The physical model of the database represents how the database is build, includes the following information: Primary keys, foreign keys, table names, column names and column data types. Physical data model is designed as follows; entities are converted into tables, relationships converted to foreign keys, attributes to columns and finally modifying it based on physical constraints/ requirements.
References
Dittrich, Gatziu and Geppert (1995). “The Active Database Management System. Athens: Greece.
Plew (2001). Database Design. Indiana, USA: Sams publishing.
Konana (2000). Relational Database and Entity-Relationship Modeling. Texas; USA.