Database design and development

• What is a database?

  A database is a computerized system that makes it easy to search, select, and store information.
• What is involved in the creation and maintenance of databases?

  A great deal of work goes into how these systems are created and maintained.
• What are the main functional requirements of a database?

  • Store data
  • Search for specific data
  • Sort data into order
  • Perform calculations based on stored data
• What do end user requirements refer to in a database?

  They refer to what users expect to be able to do on the system.
• What are some examples of end user requirements?

  • Particular information to help the user work the database
  • Menus and instructions
  • Interaction methods like buttons, shortcuts, forms, or reports
• What does the purpose of a database encompass?

  It includes what the database is being developed to do and how it is designed.
• What are the components of database design?

  • Data design: what data is required and its type
  • Architecture design: how the database is structured (tables, relationships)
  • Interface design: appearance and interaction methods
  • Procedural design: how the database will function
• What is a file in the context of a database?

  A file is a collection of records, also called an entity or table.
• What does a record contain in a database?

  A record contains all the information on one person or thing within the topic of the file.
• What is a field in a database?

  A field is a single piece of data such as name, date of birth, or telephone number.
• What is a flat file database?

  • A type of database that stores data in a single table
  • Data is often repeated, leading to larger file sizes
• What is a relational database?

  • Contains at least two tables
  • Tables are linked using key fields
  • Maintains data integrity by splitting data into different entities
• What are the typical data or attribute types in a database?

  • Text
  • Memo
  • Number
  • Currency
  • Date/Time
  • Yes/No
  • Hyperlink
  • Attachment
  • Auto-number
  • Calculated
  • OLE Object
• What is the significance of attribute size in a data dictionary?

  Only text fields need to state the size, which can reduce storage size and improve efficiency.
• What is attribute validation in a database?

  Validation checks that the data entered meets the requirements of each attribute.
• What are the four types of validation that can be added to a field?

  • Presence check
  • Length check
  • Range check
  • Restricted choice
• What does a presence check ensure in a database?

  It ensures that the user enters something in the field and cannot leave it blank.
• Why is a presence check always added to a primary key?

  To ensure each record has a unique value entered.
• What does a length check do in a database?

  A length check restricts the number of characters entered into a field.
• What is a range check in a database?

  A range check ensures that data entered is within certain limits, typically a minimum and maximum value.
• What does restricted choice validation provide to the user?

  It gives the user a list of options to select from, reducing mistakes.
• What are the two types of keys in a database?

  • Primary key
  • Foreign key
• What is a primary key in a database?

  A primary key is data that is unique to each record, preventing duplicates.
• How is a primary key indicated in a database?

  A primary key is signified by underlining its name.
• What is a foreign key in a database?

  A foreign key is a primary key from one table that appears in another table to link the two together.
• How is a foreign key indicated in a database?

  A foreign key is signified by an asterisk.
• What is a data dictionary?

  A data dictionary is a description of the structure of a database.
• What are the headings typically found in a data dictionary?

  • Entity (table name)
  • Attribute (field name)
  • Key type (PK or FK)
  • Attribute type and size (size is for text field only)
  • Unique (field is not found anywhere else)
  • Required (is it needed for any database functions)
  • Validation (with type of validation and boundaries)
• What is a relationship in the context of databases?

  A relationship is the natural connection between entities, showing how they are linked together.
• What are the three types of relationships in databases?

  • One to one
  • One to many
  • Many to many
• What is a one-to-one relationship in a database?

  A one-to-one relationship exists when one row in a table is linked with only one row in another table.
• What is a one-to-many relationship in a database?

  In a one-to-many relationship, one record in a table can be associated with one or more records in another table.
• What is a many-to-many relationship in a database?

  A many-to-many relationship involves multiple records in a table being associated with multiple records in another table.
• What is an entity relationship (ER) diagram?

  • A graphical representation of the relationships between entities
  • Shows each table and its relationship to other tables
  • Includes table names, fields, relationships, and keys
• What are the steps to draw an ER diagram?

  Look at the PKs, find matching FKs, draw tables, add relationships, label lines, and underline PKs.
• What is referential integrity in a database?

  Referential integrity ensures that the foreign key value has a matching value in the corresponding primary key.
• What does the GDPR set out for organizations?

  It sets out how organizations should use and protect personal information.
• What are the implications of GDPR for businesses?

  • Must have a data protection officer
  • Ensure rules are followed by the business and employees
  • Implement proper data security measures
• What principles must businesses abide by under GDPR?

  • Process data lawfully, fairly, and transparently
  • Ensure data is collected for specified, legitimate purposes
  • Limit data collection to what is necessary
• What is the purpose of an ER diagram?

  • To visually represent the relationships between tables
  • To help in database design
  • To clarify data structure