Week 3

• Databases


  A collection of data that is organized so that its contents can easily be accessed, managed, and updated
• Features covered at different design levels

  • Entity Names
  • Entity Relationships
  • Attributes
  • Primary Keys
  • Foreign Keys
  • Table Names
  • Column Names
  • Column Data Types
• Attributes


  The properties or characteristics of an entity
• Attributes have the following properties: data type, required or optional, simple or composite, single or multi-valued, stored or derived, identifiers (keys) or non-key attributes
• Data type


  Indicates the amount of storage needed for each field and dictates what functions can be performed on the data
• Data types include integer, text, date/time, etc.
• Required or Optional


  Indicates whether an attribute must have a value or can be left blank (null)
• Null


  Equivalent to nothing, an attribute that exists but has no value
• Not-Null


  The attribute should always have an explicit value of the given data type
• Simple (Atomic) attribute


  The attribute is self-contained and cannot be divided into smaller sub-attributes
• Composite attribute


  The attribute is made up of other attributes and can be divided into smaller sub-attributes

  
• Single-valued attribute


  Each attribute only contains one value
• Multi-valued attribute


  An attribute that has a set of values for each entity instance
• Stored attribute


  Data that are entered by users and stored in the database system
• Derived attribute


  Data that are not stored in the database system, but are calculated from stored attributes
• Storing derived attributes like age in the database is wrong because they can become outdated
• Derived attributes


  Data that are NOT stored in the database system, but are derived from stored attributes
• Storing the Derived attribute Age in the database is wrong
• Stored attributes


  Data that are stored in the database system
• Derived attributes are calculated from stored attributes when querying (report generating)
• Primary key


  The main key for the relation, a single column that can uniquely identify a row
• Unique keys


  Keys that ensure each value in the column (or a set of columns) is unique across all rows of a table
• Surrogate key


  A system generated unique identifier for each row in a table, an auto-incremented number, and not derived from the business data
• Composite key


  Consists of two or more columns, used together as a single key, to uniquely identify each row in a table
• Candidate key


  A column, or a set of columns, that can uniquely identify each row in a table
• Natural key


  A key that is a unique identifier for each row in a table, based on data that is naturally present in the dataset
• Every table can have multiple candidate keys, but only one can be chosen as the primary key
• Non-key attributes are columns in a database table that are not used to uniquely identify a row
• Non-key attributes provide descriptive information about records, can be duplicated, can have NULL values, and are subject to change over time
• Non-key attributes are not used to define relationships between entities