Databases Flashcards
Data model
An abstract model of which things to store and what information about them should be recorded
Entity
A thing about which data is to be stored
Attributes
Characteristics or other information about entities
Databases
Tables used to store multiple entities
Entity identifier
An attribute given to each entity that is unique within the table
Entity description
The name of the table is shown outside of brackets (which contain each of the attributes)
Relational databses
The tables in a database are connected by common attributes
Entity relationship diagrams
Used to graphically represent the relationships between tables in a database
Primary key
An attribute that provides a unique identifier for every entity in the database table
Foreign key
The attribute that is the primary key in the other, connected table
Composite primary key
Combining multiple attributes that act as a primary key
Many-to-many relationships
A new linking table must be created
Database normalisation
The process of making a database efficiency without compromising the integrity of the data
Advantages of normalisation
- Reduced redundancies
- Faster searching, sorting and querying of data
- Easier maintenance
- Improves data integrity
First normal form
No repeating attributes (data must be atomic)
Second normal form
- Be in 1NF
- No partial key dependencies
Partial key dependencies
When a non-key attribute doesn’t depend on the entire composite primary key
Third normal form
- Be in 2NF
- Have no non-key dependencies
Non-key dependencies
When a non-key attribute is dependent on other no-key attributes instead of the primary key
SQL
An easy to learn, declarative language used with databases
SELECT
- Used for retrieving data from a database table
- SELECT <attribute> FROM <table> WHERE <condition></condition></attribute>
UPDATE
- Used for modifying the attributes of an existing entity
- UPDATE <table> SET <attribute> = <value> WHERE <attribute> = <value></value></attribute></value></attribute>
DELETE
- Used for removing entities from a database
- DELETE FROM <table> WHERE <condition></condition>
INSERT
- Used to add new records to an existing table
- INSERT INTO <table> (<column1>, <column2>, …) VALUES (<value1>, <value2>, …)</value2></value1></column2></column1>
- INSERT INTO <table> VALUES (<value1>, <value2>, …)</value2></value1>
