1.3 Databases

• Entity:

  • A category of object, person, event or thing of interest to an organisation
• Flat file:

  • Also called a simple database
  • Contains information about a single entity
  • Contains a single table
  • E.g holding data about club venues
• Identifiers:

  • needed to uniquely identify the entity
• A primary key:

  • A field with a unique key
  • Used to identify a particular record in a relationship database
  • unique identifier for each record in the table
  • Shown by an underline
• Secondary key:

  • Needed to search the database quickly
  • The primary key field is automatically indexed so it can be found very quickly
• Composite primary key:

  • A key which consists of more than one attribute
  • Sometimes 2 or more attributes are needed to uniquely define a record
  • OrderLine(OrderNumber, OrderLine, ProductID)
  • OrderNumber and OrderLine is a composite primary key
• The 3 different degrees of relationship between 2 entities is

  • One to one
  • One to many
  • Many to many
• Foreign key:

  • An extra attribute that creates a link between 2 tables
  • Attribute that is common to both tables.
  • Primary key from one table used as an attribute in another table
• A problem of linking tables in a many to many relationship is that tables cannot be directly linked, therefore an extra table is needed to link the 2 tables
• Database:

  • An organised collection of data
• CSV:

  • Comma separated value files
  • Each record is stored on a separate line in the file, and each field is separated by a comma
• Disadvantages of a flat file database:

  • Takes up unnecessary space due to redundant data
  • Be slow to query
  • Become difficult to maintain
  • Data may be inconsistent
• A one to one degree in an E-R(entity relationship) diagram is shown by a straight line

  
• A one to many relation is shown in an E-R diagram:

  • Shown by a straight line from the entity which is the one relationship to a crows foot in the entity which is the many

  
• A many to many relationship is shown on an E-R diagram:

  • By 2 crows foot linking the entities

  
• Different ways of capturing data:

  • Paper-based capture forms
  • Optical character recognition(OCR) - This technology automatically reads text by interpreting the shape of the letters
  • Optical mark recognition(OMR)- This technology is used for multiple-choice tests and lottery tickets - very fast and efficient way of collecting data
• Automated ways of capturing data:

  • Barcode scanner
  • QR codes
• DBMS(Database management systems):

  • Provides a layer of abstraction for the user and the programmer
  • Prevents the creation of duplicate primary keys
  • Enforces validation rules
  • Provides encryption
• 2 ways of exchanging data(Common formats):

  • XML
  • JSON
• Describe the differences between a flat file and a relational database:

  Flat file

  • May have redundant data
  • Flat file harder to update
  • No specialist knowledge needed to operate

  Relational database

  • Data Integrity
  • Linked tables
  • Easier to change format
  • Provides security features
• Disadvantages and advantages of indexing data:

  • Advantage: Searches of Artist can be performed more quickly
  • Disadvantage: The index takes up extra space in the database
• Atomicity

  • all transactions either succeed or fail
  • never partially processed
• Consistency

  • each transaction obeys validation rules
• Durability

  • once transaction starts, it must be finished
• One-to-one

  • each entity can only be linked to one other entity
  • such as relationship between a husband and wife
• One-to-many

  • one table can be associated with many other tables
  • such as a mother having multiple children
  • multiple child entities can be linked to the same mother entity
• Many-to-many

  • one entity an be associated with many other entities and the same applies the other way round.
  • an example is students with courses - each student enrolls in more than 1 course and each course has more than 1 student