design (database)

• Entity


  In database design, entities represent what will become tables during implementation
• Entities


  • Person
  • Place
  • Object
  • Thing
• Entities are made up of many attributes
• Attribute


  The specific characteristics of an entity. Attributes will become fields during implementation
• Attributes of a country entity


  • Name
  • Population
  • Continent
  • GDP
  • Area
  • Currency
• Attribute size


  A measure of how much space the field will take up when stored in main memory or backing storage
• The size of a Text attribute is the number of characters which will be stored
• If the attribute size is too small, it may result in data being lost
• When designing a database, care should be taken to ensure that the attribute size is not too small or too big
• GDPR (General Data Protection Regulation)


  Legislation which sets out rules for the protection of personal data
• Individuals' rights under GDPR


  • Be informed about how their personal data will be used
  • See any data held about them
  • Have any inaccurate data held about them updated
  • Have information about them deleted once there is no longer a reason for holding it
  • Object to being included in direct marketing campaigns
• Businesses' obligations under GDPR


  • Store all data securely
  • Report any data breach within 72 hours
  • Ensure that any data held about an individual is accurate
  • Provide access to data held about an individual when requested by the individual
• Primary key


  A field which uniquely identifies a record in a database
• Foreign key


  A primary key from one table which is used in a second table to link the tables together
• A table in a database holds the attributes for one entity only. Data can then be added in the form of records
• Rather than storing information in one table, relational databases store data across several tables
• Using relational databases that link tables using primary and foreign keys helps to avoid issues like insertion, deletion, and update anomalies
• One-to-many cardinality


  A one-to-many relationship exists when one entity can be present in many different instances of another entity
• Example of one-to-many relationship

  • One surface can be used on many different sports areas
• One-to-many relationships are the most common relationships in correctly implemented relational database management systems
• It can be beneficial to show the attributes of each entity as part of an entity relationship diagram
• Data dictionaries are created during the design phase to define the structure of a database
• Metadata in a data dictionary


  • Name of each entity
  • Name of all attributes associated with each entity
  • Type of data that will be held by each attribute
  • Size of each attribute
  • Indication of attributes that will be used as primary or foreign keys
  • Validation rules that are to be applied to attributes
• Validation checks


  • Presence
  • Restricted choice
  • Field length
  • Range
• Presence check


  Makes the person using the database enter something in this field. They cannot leave it blank.
• Restricted choice


  Cuts down on mistakes by only letting you select an option from a menu or list
• Field length


  Restricts the number of characters typed
• Range check


  Makes sure data entered is within certain limits
• It is important to take time to design any queries that may need to be implemented later
• Query design does not need to include specific SQL (Structured Query Language) commands
• Elements of query design


  • Fields
  • Tables
  • Criteria
  • Sort order
• Ascending (ASC)


  Sort order
• Descending (DESC)


  Sort order
• Relational databases


  Databases that store data in tables that can be related to each other. Each table has its own set of columns and rows, and data is organized in a structured way.
• Linking tables


  In a relational database, tables can be linked together using keys. This allows data to be related and accessed in a meaningful way.
• Insertion, deletion, and update anomalies


  Problems that can occur when data is stored in a database without the use of keys. These issues can be avoided by using primary and foreign keys to link tables together, ensuring that your data is consistent, accurate, and easy to access.