Databases Flashcards
Foreign key
An ATTRIBUTE that creates a join between two tables, it is the attribute common to both tables and the primary key in one and the foreign key in the table to which it is linked.
Entity
A category of object, person, event or thing of interest to an organisation about which data is to be recorded
Flat file database
A single table on a single plain text file. Only useful for one entity- relationships cannot be represented
Attributes
Characteristics/ details about entities
Limitations of flat file databases
Harder to query Often redundant duplication Often inconsistencies as NO AUTOMATION between flat files Integrity is compromised Limited data can be inputted Data format difficult to change LESS SECURE
How to extend functionality of flat file
Attach to external files e.g. Text editors
Primary key*
Composed of one or more attributes that uniquely identify a particular record in the table- an entity identifier
Entity description e.g.
Dentist(dentistID, title)
Dentist is entity
DentistID should be underlines as the primary key
How to search databases quickly
Index of all primary keys , GIVING the location of each record according to its primary key. Its automatically maintained by the database software
Secondary keys for larger tables and indexes and if e.g. One doesn’t know their primary key like patient number (attribute indexed if often used as search criterion )
How indexes make searching faster*
Cuts down on records to be examined as position of each record is given by its primary key else have to search SEQUENTIALLY
»>Only stores values in that specific column else like copying whole table = inefficient
Entity relationship diagram
Diagrammatic way of representing relationships between entities in a database
Relational database
A database structured to recognise relations between stored items of information. Allows access and reassembling of data in DIFF ways without having to reorganise the database tables. Consists of a COLLECTION of tables (aka relations ) in which relationships are modelled by shared attributes, linked through foreign keys . Separate tables created for each entity identifier
Tables can’t directly link which relationships
Many to many so need an extra table to link them , which then has a composite key, from each of the two tables being linked
Composite key
A primary key consisting of more than one attribute- a»_space;combination of two + columns in a table uniquely identifying each row in the table
When linking a many many relationship and when there’s no unique identifier
DBMS database management system
SYSTEM software made up of a set of programs used for creating and managing databases, handling the queries, storage, retrieval/access, updating and manipulation of data
Database
Persistant organised collection of data stored in tables that are» linked through foreign keys.<
What can a DBMS do
Store data in one central location Allows data to be shared by many users Provides user interfaces Controls who can >>access and edit<< Creates backups
Query
Request for data or information from a database table or combination of tables- can go across tables and be used to»_space;add, delete or change data<
Principles for a dbms
Separation of programs and data Security for different users Referential integrity Removal of redundant duplication Queries supported e.g. SQL Concurrency control
DBMS principal: separation of data and programs
If you change a program you don’t need to change data, and don’t always need all data whenever using program
DBMS principal: Removal of redundant duplication
When the same data is in more than one database table. Increases»_space;efficiency«_space;and reduces risk of inconsistencies (e.g. If you change one and not the other) by removing duplication.
DBMS principal: referential integrity
Ensures no orphan records, prevents you from deleting related records
Ensuring relations in a database are consistent and a foreign key agrees with the referenced primary key
DBMS principal: security for different users
Single source of data and administrator decides which tables and fields in table certain people can see
DBMS principal: Concurrency control
Freeze/ locking of data, database, tables or records so data cannot be changed so others can access data at same time and all have a consistent view of data
E.g. Of when concurrency control used
Data freezes whilst 2 people trying to buy last tickets- have a certain amount of time to complete purchase
ATMs when taking out cash
Validation
Input data checked by computer, against a set of rules, makes sure data is sensible, reasonable, COMPLETE and within acceptable boundaries
Types of validation
Presence check Length check Range check Format check Type check Lookup Check digit
Presence check
Ensures a critical field isn’t left blank