1.3 Managing Databases

• A relational database:

  • A collection of tables in which relationships are modelled by shared attributes
• Normalisation:

  • A technique for designing a relational database to minimise duplication of information and eliminate redundant data
• The 4 features of normalisation:

  • No data is unnecessarily duplicated
  • Data is consistent
  • Structure of each table is flexible enough to allow you to enter as much as you want. Structure should enable a user to make all kind of complex queries
• The 5 features of the first normal form(1NF):

  • Values in fields should be atomic(cannot be broken down any further)
  • Each row record has a primary key
  • No two records can be identical
  • All field names must be unique
  • Values in fields should be from the same domain
  • No repeating attributes
• If some entities have a many-to-many relationship:

  • Make another table to link the 2 tables together
• The 2 features of the second normal form(2NF):

  • The table is already in the 1NF
  • Contains no partial dependencies
• A partial dependency:

  • One or more of the attributes depends on only part of the primary key
  • Which can only occur if the primary key is a composite key
• The 2 features of the third normal form(3NF):

  • It is in the 2NF
  • Contains no non-key dependencies
• A non-key dependency:

  • Where one value of an attribute is determined by value of another attribute which is not part of the key.
  • To be in 3NF, all attributes are dependent on the key, and nothing but the key
• Redundant data:

  • Data that appears in more than one database table which can cause inefficiencies and inconsistencies in data
  • unnecessary repetition of a field in multiple tables
• Advantages of normalisation:

  • No unnecessary duplication of data
  • Data integrity is maintained
  • Fewer fields - which lead to faster searches
• DBMS:

  • Database Management System
  • Ensures that data stored in the database remains consistent
• Data integrity:

  • Process of maintaining the consistency of the database is known as data integrity
• Referential Integrity

  • makes sure changes are consistent across a database
  • if a record is removed all references must be removed also
  • foreign key must have a corresponding primary key value in another table
• Transaction Processing

  • making sure any change in database conforms to ACID for reliable processing
• Record Locking

  • prevents simultaneous access to data by locking record when being edited or updated
  • otherwise inconsistencies may arise
• Indexing

  • creating an index of primary keys so the location can be retrieved
• Isolation

  • no transaction should overwrite transactions that are currently occuring.
• SQL

  • Structured Query Language
  • Declarative language used to manipulate databases
  • Enables creating, removing and updating of databases
• Example of SQL

  SELECT MovieTitle, DatePublished  

  FROM Movie 

  WHERE DatePublished BETWEEN #01/01/2000# AND#31/12/2005# 

  ORDER BY DatePublished;
• SELECT statement

  • SELECT statement is used to collect fields from a given table and can be paired with the FROM statement to specify which table(s) the information will come from
• WHERE statement

  • The WHERE statement can be used in conjunction to specify the search criteria.
• ORDER BY statement

  • The ORDER BY part of the code specifies whether you want it in ascending or descending order. Values are automatically placed in ascending order and adding ‘Desc’ to the end of statement will cause values to be displayed in descending order
  • e.g ORDER BY DatePublished Desc 
• Operators in the WHERE clause:

  • BETWEEN - between an inclusive range
  • IN - specify multiple possible values for a column
  • e.g WHERE price BETWEEN 5.00 and 10.00
• JOIN statement

  • provides method of combining rows from multiple tables
  • SELECT Movie.MovieTitle, Director.DirectorName, Movie.MovieCompany
  • FROM Movie  
  • JOIN Director   
  • ON Movie.DirectorName = Director.DirectorName
• CREATE statement

  • CREATE function allows you to make new databses
  • details which must be specified: primary keys, the data type, whether it must be filled in

  
• ALTER function

  • used to add, delete or modify columns in a table
• Adding a column using ALTER function:

  ALTER TABLE TableName

  ADD AttributeX and their dataTypes
• Deleting a column using ALTER function:

  ALTER TABLE TableName

  DROP COLUMN AttributeX
• Modifying the data type of a column

  ALTER TABLE TableName 

  MODIFY COLUMN AttributeX NewDataType