a) identify the keys
In order to create a proper database design, first the architect of the data base must determine the objectives for the database and also the high-level requirements.
1. The first step that would be helpful is creating a sketch while exposing the logical database design. Based on the input information, tables with their attributes must be defined. Tables which are called also entities, represents logical grouping of related information. Inside the tables, fields must be defines, which are the attributes for that table, being individual data elements inside the table. For defining the tables in the database, taking into consideration that a table is group related information, the list with the input must be scanned up and the related information must be grouped in the same entity.
2. The second step after identifying the entities and the attributes is defining the ERD (Entity Relationship Diagram) which will expose the relations between the entities and its afferent description. Detailed analysis upon those relationships must be performed in order to correctly identify the right relationship.
o Primary key: is a field or set of fields that uniquely identifies each record in a table
o Foreign key: is a key comprised of a field or multiple fields that link to the primary key of another table
b) identify relationships
o One-to-one relationship: in which each record in a table may relate to only one record in the other
o One-to-many relationship: in which any record from a table can relate to multiple records in a second table
o Many-to-Many relationship: in which many records form one table can relate to multiple records in the second table
3. The third step is normalizing the data where Normalization is the process of simplifying the database design to achieve an optimum format.
4. The last step is to test the database functionality, populating databases, creating views, triggers and stored procedures