Databases Topic 1

Databases Topic 1

1. Keys

   • SuperKey
     • A superkey is an attribute or combination of attributes containing unique values for each tuple.
   • Candidate Key
     • A minimal superkey.
   • Primary Key
     • The value of the primary key: MUST be UNIQUE and MUST NOT be NULL.
   • Alternate Key
     • Any candidate key that is not chosen as the primary key of the relation.
   • Foreign Key
     • An attribute in one relation that is used to reference the primary key in another relation.
   • Secondary Key
     • An Attribute or set of attributes that are used for data retrieval purposes.
     • Not required to be unique.

2. Constraints

   • Data Integrity Constraint
     • Data integrity means that the data held in the database must make sense.
   • Domain Constraint
     • Domain constraints specify: The value of each attribute must be an atomic value from a specified domain.
       • Domains can be standard data types - Integer, character, string, date
       • Or a subrange of a standard data type - Integer > 47 && <75
       • Or an enumerated data type where there is an explicit list of values - (a,b,c,d,e,f,g)
   • Entity Integrity Constraint
     • The Entity integrity constraint specifies that: No primary key value can be NULL and the primary key value must be UNIQUE.
   • Referential Integrity Constraint
     • Applies to two relations: Maintains consistency between relations as well as If a foreign exists in a relation, its value must either refer to an existing record in the relation it references or be wholly null.
   • Enterprise Constraint
     • aka "Business Rules", also known as general constraints.
       • Eg. There may be a maximum if 20staff members in a department

3. The Three Level Architecture

   • External Level
     • The users' view of the database
       • Each user has a view of the 'real world' that is relevant to them.
       • The user does not need to be aware of other tables, columns etc that are not part of their view.
       • Allows for different representations of the same data.
   • Conceptual Level
     • This level contains logical structure of the database.
       • Entities, their attributes and relationships.
       • Constraints
       • Semantic information about the data
       • Security information
     • It supports the external level in that every view must be derivable from the conceptual level.
       • It does not include any storage-dependent details.
   • Internal Level
     • Covers the physical implementation of the database to achieve optimal run-time performance and storage space utilisation
       • Data structures, file organisations
       • Interfaces with the OS
   • Objectives of the 3LA
     • Primary objective is to separate each user's view of the database from the way it is physically represented.
       • Each user should be able to access the same data, but have a view that is relevant to them.
       • Users should not have to deal with physical database storage issues such as file structures.
       • The DBA should be able to change the database storage structures without affecting the users' views.
       • The internal structure of the database should be unaffected by changes to the physical aspects of storage, such as moving to a new storage device.
       • The DBA should be able to change the conceptual structure of the database without affecting all users.