8 - data bases Flashcards
database
structured collection of items of data that can be accessed by different applications programs
table
group of similar data withe rows and columns
record
a row - one instance if an entity (tuple)
tuple
a row - one instance if an entity (record)
field
column - data item stored for an entity eg name (attribute)
entity
anything that can have data stored about it eg person place thing
attribute
column - data item stored for an entity eg name (field)
cadidate key
the smallest number of attributes where no row has the same value (all unique)
primary key
unique identifier of a table
secondary key
alternative to the primary key
foreign key
set of attributes that refer to another tables primary key
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)
referential integrity
database doesn’t contain any values of foreign key that don’t match the corresponding primary key
index
a data structure built from one or more columns in a database table to speed up searching
ER diagram
a graphical representation of a database and the relationships between
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)
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 <name></name>
- CREATE TABLE <name> (<attributes> <TYPE>,…PRIMARY KEY (attribute));</TYPE></attributes></name>
- ALTER TABLE <name> ADD PRIMARY KEY (<keyname>)</keyname></name>
- ALTER TABLE <name> ADD FOREIGN KEY <keyname> REFERENCES <table>(<keyname>) (references the table where the key is primary to create relationship)</keyname></keyname></name>
DML query commands
- SELECT <field> FROM <table></field>
- WHERE <criteria></criteria>
- ORDER BY <field> ASC/DESC</field>
- GROUP BY
- INNER JOIN <field> ON <criteria> (combines rows)</criteria></field>
- SUM
- COUNT (counts the number of rows where the column isnt NULL)
DML maintenance commands
- INSTERT INTO <table> VALUES ()
- DELETE FROM <table> WHERE <criteria> have to delette from all tables</criteria>
- UPDATE <table> SET <thing> WHERE <condition></condition></thing>
