Databases Flashcards
What is a Database?
A database is an organised, persistent collection of data.
Organised: Data organised into tables, records and fields
Persistent: Non-volatile using secondary storage
Data: Raw info with no context
What does a database consist of?
Files, records and fields
What operations can be performed on databases?
- Can be organised/sorted in numerous ways
- Displayed/Printed in a variety of styles/layouts
- Searched for info and updated
- Backed up easily and automatically
- Local or distributed
- Stored/Moved easily
What is an attribute?
An individual fact, detail or characteristic of an entity (aka a field)
What is an entity?
A thing, person, object or relationship about which data can be collected
What is a flat file database?
A database containing a single table
What is a primary key?
A unique piece of information within a table to identify a record
What is a foreign key?
A field in the table which contains the value of a primary key in another table (links to one record)
What is redundancy?
Duplication of data within a database
What is a record?
A collection of fields/attributes about the same thing, person, object or relationship in a table
What is a relational database?
A database with multiple linked tables
What is a conceptual data model
a representation of the data requirements constructed in a way that is independent of any software used to construct the database
Click to view a relationship example
e.g.
Entities:
Student, Teacher, Class, Subject, Classroom…
Attributes:
Student: first name, second name, DOB, student number, year group, form ▪ Teacher: first name, last name, Subject, Teacher ID, DOB
Subject: Subject ID, Requirements, Max Intake
Classroom: Room ID, capacity, room type, facilities
Class: Class ID, Classroom ID, Subject ID, Teacher ID, Period, Time
Relationships:
Teacher teaches Subject
Student attends Class
Entities in a system like this are always singular. The entity describes the characteristics of one instance of the entity, a so-called entity occurrence.
What shape denotes an entity set?
A rectangle
What shape denotes an attribute?
An oval
What shape denotes a relationship?
A diamond
Primary Key Attributes (PK)
uniquely identify each entity occurrence, it is a field that is unique for each record within a table
Foreign Key Attributes (FK)
are found on the ‘many’ (or ‘one’ in a one-to-one) side of a relationship and always take the same values as the primary key attribute in a linked entity. They define the relationship.
Non-Key Attributes
describe characteristics of the entity
Problems with many to many relationships? how to solve them?
- Cause data redundancy (repeating data)
- Can cause error if implemented within a database software
To solve this, a bridging table can be added in between the two tables, called an associative entity which holds details of a single occurrence of the relationship between the two other entities
Put this list into database notation called Customer with CustomerId as the primary key;
-Customer ID
-Title
-Forename
-AddressLine1
-Town
tblCUSTOMER(CustomerId, Title, Forename, AddressLine1, Town)
—————–
(Underline CustomerId as it is the PK, overline any FK)
This is a simple flat data structure
Problems with flat file data structures
- Inefficiency
- Data Redundancy
- Data Inconsistency
Degree
Number of columns in a table
Attribute/field
1 column in a table
Domain
Allowed values
Relation
Whole table
Tuple
1 row
Normalisation
Normalisation is the process of organising data in a database, through converting a single flat-file entity into multiple separate entities/relations/tables. Normalisation also includes the removal of redundant data
1st Normal Form
Contains only atomic values (all or nothing) ;
NOT [red, green] à [Red],[Green]
I.e. each field (Colum) only has one value
- There are no repeating groups [Author1], [Author2], [Author3])
- Each row (Tuple/Record) holds unique set of data
After 1NF data has been flattened
This may create issues because of the duplicated data:
- Insertion Anomaly: Cannot insert due to requiring certain attributes for the PL
- Update Anomaly: If the name of something is altered, many changes would be req.
- Deletion Anomaly: Losing information not intended when certain attribute is deleted
2nd Normal Form
(in 1NF) and no partial dependencies on anything but the primary key.
Each column/field depends on the whole of the primary key
3rd Normal Form
(in 2NF) and no transitive dependencies (Non primary attribute is dependent on another non primary attribute)
All data items must depend on nothing but the key
The primary key must fully define all non-key fields/column, and non-key fields/columns must NOT depend on any other Key but the primary key.
What is a query language?
A query language is used when a user wants to search a database
Extract selected data from a database by stating criteria
E.g. SQL (Structured Query Language)
SQL can also create tables
Data Definition Language (DDL)
Provides commands that allow you to define and modify a database and its components (e.g. tables, relationships)
Data Manipulation Language (DML)
Provides commands that allow you to insert, update and delete data from a database
Data Query Language (DQL)
Provides commands that allow you to retrieve data from a database
SQL Operators
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<>, != not equal
BETWEEN - Between a range
LIKE - Search for a pattern
IN - Specify multiple possible values
ASC - Ascending order
DESC - Descending order
* - All
SQL Commands
SELECT - Extracts data
UPDATE - Updates data
DELETE
INSERT INTO - Inserts new data
CREATE DATABASE
ALTER DATABASE
CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
DROP INDEX
CHAR vs VARCHAR
CHAR;
Stores values in fixed lengths
Max 255 characters
Used when sizes of column data entries are consistent
VARCHAR;
Values variable length long, 1 byte or 2 byte length prefix
Max 65535 characters
Used when sizes of data entries change considerably
Create SQL table:
Called Manager
with fields;
- ManagerId
- Firstname
- Surname
- Date of Birth
ANSWER
CREATE TABLE Manager
{
ManagerID CHAR(5) NOT NULL,
Firstname CHAR(20) NOT NULL,
Surname CHAR(20) NOT NULL,
DOB DATE NOT NULL,
PRIMARY KEY (Manager ID)
}
Referential Integrity
If a value appears in a foreign key table, it must also appear in the primary key in another table. If a primary key of a record in one table is changed then it must be updated in all tables where it is used as a foreign key. To maintain referential integrity you need associations to avoid orphaned records
Client Server Database
Provides simultaneous access to the database for multiple clients through Database Management Systems (DBMS) with server w software running on network server and client software
Concurrent access
can result in the problem of updates being lost if two clients edit a record at the same time. This can be managed using record locks, serialisation, timestamp ordering and commitment ordering
Record Lock
A technique that prevents simultaneous access to objects in database in order to prevent update being lot or arising data inconsistencies
Serialisation
Ensures that transactions do not overlap in time and therefore cannot interfere with each other or lead to updates being lost and implemented using the Timestamp ordering
Timestamp ordering
A timestamp is given to each transaction so that if 2 transactions affect the same object (record) the transaction with the darker timestamp would be applied first. To ensure data are not lost every object in a database has a read and write timestamp which are updated whenever an object is read or written
Commitment ordering
Serialisation technique ensures safe transaction without any loss of data when >2 users are simultaneously accessing the same object in a database and ordered in terms of their dependencies on each other and the time they were initiated
