Databases Flashcards

1
Q

Foreign key

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

Entity

A

A category of object, person, event or thing of interest to an organisation about which data is to be recorded

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

Flat file database

A

A single table on a single plain text file. Only useful for one entity- relationships cannot be represented

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

Attributes

A

Characteristics/ details about entities

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

Limitations of flat file databases

A
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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

How to extend functionality of flat file

A

Attach to external files e.g. Text editors

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Primary key*

A

Composed of one or more attributes that uniquely identify a particular record in the table- an entity identifier

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

Entity description e.g.

A

Dentist(dentistID, title)
Dentist is entity
DentistID should be underlines as the primary key

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How to search databases quickly

A

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 well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

How indexes make searching faster*

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

Entity relationship diagram

A

Diagrammatic way of representing relationships between entities in a database

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

Relational database

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Tables can’t directly link which relationships

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

Composite key

A

A primary key consisting of more than one attribute- a&raquo_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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

DBMS database management system

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

Database

A

Persistant organised collection of data stored in tables that are» linked through foreign keys.<

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What can a DBMS do

A
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
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

Query

A

Request for data or information from a database table or combination of tables- can go across tables and be used to&raquo_space;add, delete or change data<

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Principles for a dbms

A
Separation of programs and data
Security for different users
Referential integrity
Removal of redundant duplication
Queries supported e.g. SQL
Concurrency control
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

DBMS principal: separation of data and programs

A

If you change a program you don’t need to change data, and don’t always need all data whenever using program

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

DBMS principal: Removal of redundant duplication

A

When the same data is in more than one database table. Increases&raquo_space;efficiency&laquo_space;and reduces risk of inconsistencies (e.g. If you change one and not the other) by removing duplication.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

DBMS principal: referential integrity

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

DBMS principal: security for different users

A

Single source of data and administrator decides which tables and fields in table certain people can see

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

DBMS principal: Concurrency control

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

E.g. Of when concurrency control used

A

Data freezes whilst 2 people trying to buy last tickets- have a certain amount of time to complete purchase
ATMs when taking out cash

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Validation

A

Input data checked by computer, against a set of rules, makes sure data is sensible, reasonable, COMPLETE and within acceptable boundaries

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

Types of validation

A
Presence check
Length check
Range check
Format check
Type check
Lookup
Check digit
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

Presence check

A

Ensures a critical field isn’t left blank

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

Length check

A

Ensures certain data has the correct number of characters e.g. Postcode

30
Q

Range check

A

There’s an upper and lower limit for numerical data

31
Q

Format check

A

Used if data consists of the same pattern e.g. LLNN NLL

32
Q

type check

A

Ensures the correct type of data is being entered

33
Q

Lookup

A

When a field contains a limited list of items, to reduce errors. It speeds up entry, improves accuracy and limits options to choose from

34
Q

Check digit

A

Ensures a RANGE of data has been entered correctly. A certain digit is the check digit and calculations done on digits and check digit should be outputted

35
Q

Form

A

A user interface used to input data into a database, prompting you with the data required

36
Q

Report

A

Used to export data, format and summarise and present it in a way which is easy to read. Can choose to display some information over others.

37
Q

Advantages of relational databases

A
Better security
Easy to install software with forms
Easy to add files
Normalisation
Technological support online
Saves SPACE i.e. no redundant duplication 
Improves integrity and consistency 
Easy to change data format
38
Q

Normalisation

A

Process used to come up with the best possible design for a relational database, making it easier to maintain and change. Tables should be organised in such a way that:

  • no unnecessary duplication (i.e. The same data item held in more than one table
  • consistent data throughout database. ie maintained data integrity. Automatic consequence of having no duplication
  • structure of each table is flexible enough to allow you to enter as many or few data items as required
  • structure should enable a user to make all kinds of complex queries relating data from different tables
39
Q

capturing data

A

before entered into a database: data has to be captured or input, manually (e.g. transcribing-market research/items from a catalogue) or auto (e.g. magnetic ink character recognition on cheques at bottom with acc number, bank number for e.g. and amount is manually entered, optical mark recognition, optical character recognition for multiple choice and lottery, smart card readers, airport passport scanners, barcode scanners)

40
Q

selecting and managing data

A

data selected before inputted in database: ensuring meets criteria e.g. speed camera only takes photos if above speed limit
data selected after in database via SQL: select data from different tables acc to criteria: reports produced, letters sent out, stock items reordered, records added/deleted/updated

41
Q

exchanging data

A

transferring data from diff computer systems often via internet EDI electronic data interchange.
using standardised message formatting, documents are exchanged electronically
eg transaction software, exam boards sending results, insurance companies checking licenses

42
Q

create a table of employee with EmpID (compulsory int field, primary key), Name (compulsory character field of length 10)

A
CREATE TABLE
(
EmpID INTEGER NOT NULL, PRIMARY KEY
EmpName VARCHAR(20) NOT NULL
)
43
Q

CHAR(n)

A

Character string of fixed length, n eg ProductCode CHAR(6)

44
Q

VARCHAR(n)

A

Character string of variable length, max n eg Surname VARCHAR(20)

45
Q

BOOLEAN

A

TRUE or FALSE eg Review BOOLEAN

46
Q

INTEGER/INT

A

Integer eg Quantity INTEGER

47
Q

FLOAT

A

number with floating decimal point eg Length FLOAT(10,2) 10 digits before and 2 after decimal point max

48
Q

DATE

A

Stores, Day Month Year eg HireDate DATE

49
Q

TIME

A

Stores hour, minute, second values eg Racetime TIME

50
Q

CURRENCY

A

Formats numbers in the currency used in your region

51
Q

ALTER TABLE used to

A

add, delete and modify columns

52
Q

adding a column

A

ALTER TABLE Employees

ADD Department VARCHAR(10)

53
Q

delete a column

A

ALTER TABLE Employees

DROP COLUMN HireDate

54
Q

change data type of column

A

ALTER TABLE Employees

MODIFY COLUMN EmpName VARCHAR(30) NOT NULL

55
Q

Create a linking table called CourseAttendance with composite key: CourseID and EmpID with own field of CourseDate

A
CREATE TABLE CourseAttendance
(
CourseID CHARACTER (6) NOT NULL,
EmpID INTEGER NOT NULL,
CourseDate DATE,
FOREIGN KEY CourseID REFRENCES Course(CourseID)
FOREIGN KEY EmpID REFRENCES Employees(EmpID),
PRIMARY KEY (CourseID, EmpID)
)
56
Q

Inserting a new record

A

INSERT INTO tablex(Name, EmpID, Salary, HireDate)
VALUES(“John”, “122”, 18000, #1/12001#)
If fields added in correct order, don’t need to specify field names in first bracket

57
Q

Update all Technical departments salary by 10%

A

UPDATE Employee
SET Salary= Salary*1.1
WHERE Department= “Technical”

58
Q

Update John’s department to Admin

A

UPDATE Employee
SET Department= “Admin”
WHERE EmpID= “122”

59
Q

Delete record for John

A

DELETE FROM Employees

WHERE EmpID= “122”

60
Q

1NF

A

no repeated attributes or repeated groups of attributes

61
Q

2NF

A

in 1NF and no partial dependencies on the primary key (only relevant for composite keys)

62
Q

3NF

A

in 2NF and no non-key dependencies: all the attributes are dependent on the key, the whole key and nothing but the key

63
Q

normalisation advantages

A
  • no redundant duplication (same data appearing in more than one database table)
  • maintained data integrity: CONSEQUENCE of no duplication
  • faster searching, sorting, and index operations because smaller table with fewer fields and less data. saves storage also
  • prevents you from deleting related records e.g. will not allow records in a table on the ‘one’ side of a one-to-many relationship be deleted
64
Q

why add a link table between many many

A

it makes it 3NF/normalised, reduces data inconsistencies

65
Q

SQL

A

declarative language used to query and update and create tables in a relational database

66
Q

SELECT statement

A

used to extract a collection of fields from a given table

67
Q

transaction

A

single logical operation on data. database responsible for ensuring it is not possible to complete part of a transaction. ACID are properties ensuring reliable processing of transactions

68
Q

atomicity

A

transaction must be processed in its entirety or not at all. guarantees that in any situation (eg power cuts/hard disk crash) its not possible to process part of a transaction

69
Q

consistency

A

ensures no transaction violates any of the defined validation rules for maintaining the integrity of the database. when the database is created, referential integrity rules will be specified between linked tables

70
Q

hashing function

A

provides a mapping between an arbitrary length input and a fixed, often smaller output. It is one way so good for storing encrypted pins and passwords. Hash total is generated from the whole message so any slightest change provides a completely different total. Low chance of two strings hashing to the same total

71
Q

hash total/checksum/digest

A

a mathematical value calculated from unencrypted message data

72
Q

digital certificate

A

verifies a sender’s public key is formally registered to that sender, verifying trustworthiness of website/sender. These are issued by official certificate authorities eg Symantec/Verisign. Th is allows certificate holder to use public key infrastructure. It contains serial number, expiry date. name of holder and copy of public key and CA digital signature so it can be authenticated as real. Operate within transport layer of TCP/IP