8 - data bases Flashcards by b m

database

structured collection of items of data that can be accessed by different applications programs

How well did you know this?

Not at all

Perfectly

table

group of similar data withe rows and columns

How well did you know this?

Not at all

Perfectly

record

a row - one instance if an entity (tuple)

How well did you know this?

Not at all

Perfectly

tuple

a row - one instance if an entity (record)

How well did you know this?

Not at all

Perfectly

field

column - data item stored for an entity eg name (attribute)

How well did you know this?

Not at all

Perfectly

entity

anything that can have data stored about it eg person place thing

How well did you know this?

Not at all

Perfectly

attribute

column - data item stored for an entity eg name (field)

How well did you know this?

Not at all

Perfectly

cadidate key

the smallest number of attributes where no row has the same value (all unique)

How well did you know this?

Not at all

Perfectly

primary key

unique identifier of a table

How well did you know this?

Not at all

Perfectly

secondary key

alternative to the primary key

How well did you know this?

Not at all

Perfectly

foreign key

set of attributes that refer to another tables primary key

How well did you know this?

Not at all

Perfectly

relationship

one table in a database has a foreign key that refers to a primary key in another table in the database
1:1
1:m
m:1
m:m
(the many always has the foreign key)

How well did you know this?

Not at all

Perfectly

referential integrity

database doesn’t contain any values of foreign key that don’t match the corresponding primary key

How well did you know this?

Not at all

Perfectly

index

a data structure built from one or more columns in a database table to speed up searching

How well did you know this?

Not at all

Perfectly

ER diagram

a graphical representation of a database and the relationships between

How well did you know this?

Not at all

Perfectly

normalisation

the process of organising data to be stored in a database into 2 or more tables and relationships between the tables (redundancy is reduces)

How well did you know this?

Not at all

Perfectly

1NF

data is atomic - cant be split further
no repeating attributes
each table has a primary key
each field name is unique

2NF

must be 1NF
remove partial key dependencies - where an attribute in a table depends on only a part of the primary key not the whole key

3NF

already in 2NF
remove transitive dependencies - where non key attribute depends on another non key attribute

composite key

set of attributes that form a primary key to provide a unique identifier for a table

limitations of file based approach

redundancy - storage space wasted when data is duplicated by separate applications
inconsistency - data is altered by one application and not another
dependencies - enquires can depend on the structure of data and software used

+ of relational databases

+ no redundancy - storage space not wasted as data only stored once
+ no inconsistencies - data altered in one app is available in another
+ independent - enquires arent dependent on the structure of the data and software

DBMS

system software for the definition, creation and manipulation of a database

how DBMS fixes file based approach

redundancy - data is stored in separate tables - flags errors when keys are deleted or data is repeated
inconsistency - stores most items once - allowing updates to be seen by all apps
dependency - each app only has access to what it requires - DBMS has facilities to query data

DBMS features

- data management - data dictionary - data modelling - data security - data integrity

data management

organisation and maintenance of data in a database to provide info required - entry, storage, alteration, deletion of data

data dictionary

a set of data that contains metadata for a database - definition of tables, attributes, relationships between tables and indexing or validation rules or data about the physical storage

data modelling

the analysis and definition of data structures required to produce a data model - shows struture by ER diagrams

logical schema

a data model for a specific database that is independent of the DBMS used to build that database

data security

- passwords - access rights - backup - encryption - creates audit trail/ activity log (to record actions by users)

DBMS software tools

- developer interfaces - query processor

developer interface

allows a developer to write queries in SQL rather than query by query

SQL

structured query language standard query lang used with relational databases for data definition and data modification

query processor

takes an SQL query and processes it includes a DDL interpreter, DML compiler and query evaluation engines

DDL

- used to create, modify and remove data structures DDL statements are interpreted and recorded in the data dictionary

DML

- used to add, modify delete and retrieve data in a relational database DML is compiled into low-level instructions and executed by the query evaluation engine- the query is optimised

DDL data types

- character - fixed length eg 5 - varchar(n) - variable length up to max - boolean - integer - real - date - time

DDL statements

- CREATE DATABASE - CREATE TABLE ( ,…PRIMARY KEY (attribute)); - ALTER TABLE ADD PRIMARY KEY () - ALTER TABLE ADD FOREIGN KEY REFERENCES () (references the table where the key is primary to create relationship)

DML query commands

- SELECT FROM

- WHERE - ORDER BY ASC/DESC - GROUP BY - INNER JOIN ON (combines rows) - SUM - COUNT (counts the number of rows where the column isnt NULL)

DML maintenance commands

- INSTERT INTO

VALUES () - DELETE FROM

WHERE have to delette from all tables - UPDATE

SET WHERE