Types of Database

Database Design Process

• Identify Entities: Determine the main subjects you need to store data about, such as 'Books' and 'Authors'. Each distinct entity should generally have its own table in a relational model to maintain organizational clarity.
• Define Attributes and Types: Assign specific fields to each table and define their Data Types, such as Numeric, String, or Boolean. Choosing the correct type ensures data validation and optimizes storage space for the system.
• Establish Relationships: Use Foreign Keys to connect related tables, such as linking an 'AuthorID' in a 'Books' table to the primary key in an 'Authors' table. This step is critical for transforming a collection of lists into a functional relational database.

Data Manipulation

• Sorting: Data can be organized in Ascending (A-Z, 1-100) or Descending (Z-A, 100-1) order. Advanced sorting may use multiple criteria, such as sorting by 'City' first and then by 'Last Name' within each city.
• Querying with Wildcards: Use symbols like '' or '%' to search for patterns within strings. For instance, searching for 'Sm' would retrieve records starting with 'Sm', such as 'Smith' and 'Smyth', which is useful when exact data is unknown.

• Flat-File vs. Relational Models: The choice between these models depends on the complexity of the data and the scale of the application.

• Primary Key vs. Foreign Key: A Primary Key uniquely identifies a record within its own table. A Foreign Key is a field that points to the primary key of another table, serving as a connector to relate data across the database.

• Identify the Relationship: When presented with two tables, always look for the field they share to identify the link. The shared field will be a Primary Key in its 'home' table and a Foreign Key in the table where it is used as a reference.

• Check Data Types: If asked to define a database structure, ensure you select the most restrictive and accurate data type. For example, 'Boolean' is better than 'String' for a 'Paid' status, and 'Numeric' is required for any field involving calculations.

• Sorting Order Logic: Remember that Ascending means going up (1 to 10) and Descending means going down (10 to 1). In alphabetical sorts, A is 'smaller' than Z in ascending order.

• Redundancy Analysis: If a question asks why a flat file is poor, focus on the term Data Redundancy. Explain that repeating data leads to larger file sizes and a higher risk of Inconsistency if only some instances of the data are updated.

• Non-Unique Primary Keys: A common mistake is selecting a field as a primary key that might not be unique, such as a 'Surname'. Always look for an ID or code field that is guaranteed to be different for every single record.

• Confusing Records and Fields: Students often mix up rows and columns. Remember that a Field is a single attribute (column) and a Record is a collection of all fields for one entity (row).

• Calculation Errors: Attempting to perform math on 'String' fields will fail. Ensure that fields intended for calculations, like 'Price' or 'Quantity', are explicitly set to a Numeric data type.