1.3.2 Flashcards
(36 cards)
Entity
A category of object, person, event or thing of interest to an organisation about which data is to be recorded
Attribute
A named piece of data about a particular entity, with an associated data type. Also known as a field.
Flat File database
Database consisting of tables which have no links to one another. Each table is entirely standalone.
Primary Key
An attribute which uniquely identifies each entity in a table.
Secondary key
An attribute which can uniquely identify an entity but hasn’t been selected as the primary key. E.g. in a customer table, the Customer ID may be the primary key but the email could be considered a secondary key. They often are used to index a table so that searching is quicker.
Entity relationships
One to one
One to many
Many to many (must be avoided, can use an intermediary table)
Entity Relationship Diagram
Show the relationships between the different entities. A single line into a box shows ‘one’ and three lines in represents ‘many’.
Relational database
A database with multiple tables which link together through the use of foreign keys.
Foreign key
Attribute which creates a join between two tables. It is an attribute that exists as a primary key in the table being linked to.
Composite Key
Primary key which consists of more than one field.
Referential integrity
Making sure that the links between tables are always kept valid when records are added, updated or deleted. E.g. A User makes many IT Requests. If a User is deleted, all its IT Requests should also be deleted.
Normalisation
Process used to come up with the most efficient design for a relational database
- No data is unnecessarily duplicated
- Data is consistent throughout database
- Structure of the table is flexible enough to allow you to enter as many or as few items as required
- The structure should enable a user to make all kinds of complex queries relating data from different tables
First normal form
Table has a primary key. No duplicate attributes. No repeating groups and no composite attributes.
Second normal form
Contains no partial key dependencies. A partial key dependency is when an attribute depends on only part of the composite primary key. Must have no Many-to-Many relationships. Must also be in 1NF.
Third normal form
Contains no non key dependencies. Every attribute must depend on only the primary key and nothing but the primary key. Must also be in 2NF.
SQL
Structured Query Language, declarative language used to query and update tables in relational databases.
SELECT…FROM…WHERE
Using the WHERE clause with SELECT…FROM allows you to specify conditions that must be met for data to be selected.
SELECT CityName FROM City
WHERE Country = ‘Scotland’
Would return the CityName field of records from the city table with the value Scotland held in the country field.
ORDER BY
A SQL clause that is useful for ordering the output of a SELECT query (for example, in ascending or descending order). Multiple fields can be used to order and will be ordered in the order that they are presented.
JOIN
A method of combining rows from two or more tables based on a common field.
Defining a sql table
CREATE TABLE _____
(
value datatype
“ “
“ “
)
SQL Datatypes
CHAR string of fixed length n
VARCHAR string of variable length max n
BOOLEAN
INTEGER
FLOAT
DATE
TIME
CURRENCY
ALTER TABLE
The SQL command used to make changes to table structure. When the command is followed by a keyword (ADD, DROP or MODIFY), it adds a column, deletes a column or changes column characteristics.
Creating links between tables via Foreign Key Constraints
When defining a table add - FOREIGN KEY key REFERENCES table(key)
Inserting, updating and deleting SQL
INSERT - inserts an entity
UPDATE - updates and entity
DELETE - deletes an entity
