Databases and Software Development Flashcards
(72 cards)
1
Q
Database
A
Organised collection of data that is structured in a way to facilitate efficient retrieval, updating and analysis of data
2
Q
Data model
A
Describes data, its structure, its relationships and constraints for a given system
3
Q
Factors to consider when modelling entity relationships
A
- Data needed to be stored
- What real-world entities this data relates to
- Relationships between entities
- Constraints on data
4
Q
Entity
A
Object about which data is being stored
5
Q
Entity representation in databases
A
As tables
6
Q
Attribute
A
Property of an entity
7
Q
Attribute representation in databases
A
As fields/columns
8
Q
Instance
A
Details of a particular occurence of an entity
9
Q
Instance representation in databases
A
As records/columns
10
Q
Relationship types
A
- One-to-one (e.g. a school has one headteacher; a headteacher only has one school)
- One-to-many
- Many-to-one
- Many-to-many (resolved by creating two one-to-many relationships)
11
Q
Relational database
A
Type of database where:
* Separate table created for each entity
* Where relationship exists, foreign key links two tables
12
Q
Entity identifier
A
(Collection of) attributes that uniquely identifies each instance of an entity
13
Q
Primary key
A
Practical implementation of entity identifier
14
Q
Foreign key
A
Attribute that creates a join between two tables (is primary key of another table)
15
Q
Entity descriptions
A
Entity(Primary key, Attribute2 …)
16
Q
Entity relationship diagrams
A
- Diagrammatic way of representing the relationships between the entities in a database
- Crow’s feet = many side
17
Q
Problem with many-to-many relations
A
Storing multiple foreign keys per instance -> Unnormalised entity -> Inefficiency
18
Q
Resolving many-to-many relations
A
- Composite/link entity created
- Each instance represents each relation by storing both foreign keys
- Create two one-to-many relations
19
Q
Composite primary key
A
Consists of more than one attribute
20
Q
Relation
A
Entity that is linked to other entities
21
Q
Tuple
A
Row within relation (represents instance)
22
Q
Normalisation
A
Process of structuring data in a relational database to reduce redundancy, increase accuracy and increase efficiency
23
Q
Key features of normalisation
A
- No redundant data
- Each relation represents a single concept
- Data is stored at its atomic level (can’t be decomposed further)
24
Q
Redundant data
A
Unnecessarily duplicated data
25
Why is redundant data bad?
- **Redundant data** → Wasted space and slower searching → **Inefficiency**
- **Redundant data** → ↑ Chance of incorrect copying → **Inconsistent and inaccurate data**
26
Atomic-level data
Data has been **fully decomposed** into **multiple attributes**
27
Levels of normal form
1. First normal form (1NF)
2. Second normal form (2NF)
3. Third normal form (3NF)
28
First normal form (1NF)
Data is fully atomic and doesn't have multiple values for any attributes (repeating groups)
29
Second normal form (2NF)
Separates non-key attributes that don’t relate to whole primary key (partial dependencies) into their own relations (only occurs when primary key is composite)
30
Third normal form (3NF)
- All attributes are dependent on the key, the whole key and nothing but the key
- Achieved by removing non-key attributes that depend on other non-key attributes from a relation via creating more relations
31
Advantages of normalisation
* Maintaining and modifying database
* Faster sorting and searching
* Deleting records
32
Maintaining and modifying the database
* **Data integrity maintained** (no unnecessary duplication of data) -> No possibility of inconsistencies
33
Faster searching and sorting
* Produces smaller tables with fewer fields -> Faster searching, sorting and indexing (less data involved)
* Holding data once -> Saves storage space
34
Deleting records
* Won't allow record on 'one' side of one-to-many relationship to be accidentally deleted
*
35
Structured Query Language (SQL)
Declarative language used for querying, updating and creating tables in a relational database
36
SELECT statement
Returns data from listed fields where condition is met
37
SELECT syntax
```
SELECT field1, field2, etc.
FROM table1, table2, etc.
WHERE condition
ORDER BY field1, field2, etc.
```
38
SELECT from multiple tables
```
SELECT Song.SongTitle, Artist.ArtistName, Song.MusicType
FROM Song, Artist
WHERE (Song.ArtistID = Artist.ArtistID) AND (Song.MusicType = "Art Pop")
```
OR
```
SELECT Song.SongTitle, Artist.ArtistName, Song.MusicType
FROM Song
WHERE Song.MuscType = "Art Pop"
JOIN Artist ON Song.ArtistID = Artist.ArtistID
```
39
Not equal to
<>
40
IN
Equal to value within set of values
41
LIKE
Equality when using wildcards
42
BETWEEN x AND y
Equal to value within set of values defined by limits x and y
43
IS NULL
Field doesn't contain a value
44
%
* Represents zero or more characters
* E.g. bl% finds black, blue, etc.
45
_
* Represents a single character
* E.g. b_t finds bat, but, etc.
46
[]
* Represents any single character within brackets
* b[au]t finds only bat and but
47
[^]
* Represents any character not in brackets
* E.g. b[^au]t finds any combination except bat and but
48
-
* Represents any character within brackets specified by range
* E.g. b[a-u]t finds any combination of letters from a to u
49
Data types
* CHAR(n) (fixed length string)
* VARCHAR(n) (variable length string max size n)
* ...
* DATE (stores day, month and year values)
* TIME (stores hour, minute and second values)
* CURRENCY (formats numbers into currency format of current region)
50
CREATE TABLE syntax
```
CREATE TABLE Employee
(
EmpID INTEGER NOT NULL PRIMARY KEY,
EmpName VARCHAR(20) NOT NULL,
HireDate DATE,
Salary CURRENCY
)
```
51
Defining linked tables
```
FOREIGN KEY CourseID REFERENCES Course(CourseID)
```
52
Add field
```
ALTER TABLE Employee
ADD Department VARCHAR(10)
```
53
Delete a field
```
ALTER TABLE Employee
DROP COLUMN HireDate
```
54
Modify field
```
ALTER TABLE Employee
MODIFY COLUMN EmpName VARCHAR(30) NOT NULL
```
55
UPDATE statement
Updates record in a table
56
UPDATE syntax
```
UPDATE table
SET column1 = value1, column2 = value2, ...
WHERE columnX = value
```
57
DELETE statement
Deletes a record from a table
58
DELETE syntax
```
DELETE FROM table
WHERE columnX = value
```
59
Aggregate functions
Used in conjunction with SELECT statement (replaces fields)
60
Aggregate function examples
* MIN(field)
* MAX(field)
* COUNT(field)
* SUM(field)
61
GROUP BY
* Separates output values based on the record's value in the given field
* Displays aggregate function values clearer
62
Client-server application model
* Consists of central server (abstraction) and multiple clients connecting to it
* Core data and services stored and provided server-side
* Interaction with data via lighter, 'simpler' client-side applications
63
Advantages of client-server model
* Data stored in one place -> Consistency of database
* Data easily shareable across multiple devices
* Data, backup and recovery can be centrally managed
* Central security management
64
Disadvantages of client-server model
* If server goes down, risk of data becoming lost or inaccessible
* Multiple users accessing server simultaneously -> Congestion and poor performance
* Requires conflict management
65
Relational Database Management System (RDBMS)
Provides client-server support, including handling concurrent access
66
Lost update problem
When two users attempt to update the same record simultaneously -> One update being lost or both being applied incorrectly
67
Record locks
* Lock applied to record when a transaction (read/write) on it is started
* Prevents other users from reading from or writing to it
68
Problem with record locks
**Deadlock** - where two users attempt to access the same record or access a pair of records such that they are both locked out
69
Serialisation
Method of ordering transactions into a queue
70
Timestamp ordering
* Each transaction's timestamp is recorded in DB
* To prevent timestamps being lost, every object has **read and write timestamps**
* T_w should be aborted if read/write timestamp on record is greater (more recent)
* T_r should be aborted if write timestamp on record is greater
71
Commitment
Process of writing changes to a database permanently
72
Commitment ordering
* Extends timestamp ordering
* Concurrent transactions commited in particular order -> Avoids data update issues and risk of deadlock
* Order of commits determined by interaction of commitments & dependencies on common data
