10 Fundamentals of Databases Flashcards
Define attribute
A property of an entity equivalent to a field in a table
Define entity
Any item in the system about which data is stored
Define primary key
An attribute that uniquely identifies each record in a table
Define composite key
A primary key that involves more than one attribute
Define foreign key
A primary key in another table to create a relationship
What are the properties of a first normal form?
- No repeating attributes or groups of attributes
- all attributes must be atomic (cannot consist of two data item e.g. first name and surname
- this would make it difficult to order by surname
What are the properties of a second normal form?
- In first normal form
- With no partial dependencies
- (this only occurs if primary key is a composite key)
- the value of the non-key attribute can be determined by a subset of the primary key but not the entire key
What are the properties of a third normal form?
- Second normal form
- No non-key dependencies
SQL syntax for retrieving data
SELECT attribute FROM table
SQL syntax for relational database
SELECT attributes FROM tables
JOIN table2
ON table1.primarykey = table2.foreignkey
SQL syntax to order results
SELECT attribute FROM table
WHERE attribute2 = 10
ORDER BY attribute ASC
SQL syntax to create a new table
CREATE TABLE Customers
(
CustomerID CHAR(4) NOT NULL PRIMARY KEY,
FirstName VARCHAR(20) NOT NULL,
Surname VARCHAR (50) NOT NULL,
PricePaid CURRENCY
Height FLOAT (5, 2) #float with 5 digits and max of two after decimal
)
SQL syntax to add attribute
ALTER TABLE Customers
ADD Age INT
SQL syntax to delete attribute
ALTER TABLE Customers
DROP Height
SQL syntax to alter data type of attribute
ALTER TABLE Customers
MODIFY COLUMN Surname VARCHAR(40) NOT NULL
SQL syntax to insert a new record
INSERT INTO Customers(CustomerID, Firstname, Surname)
VALUES (“8472”, “Alice”, “Wonderland”)
SQL syntax to delete a record
DELETE FROM Customers
WHERE CustomerID = “2342”
SQL syntax to update a record
UPDATE Customers
SET Surname = “Borderland”
WHERE CustomerID = “8462”
What are the benefits of normalisation?
- Easier to maintain and update a normalised database
- No unnecessary duplication of data
- Data integrity is maintained e.g. if a person changes their address, the update only needs to be made to one table
- Smaller tables and fewer fields means faster searches and saves storage
Define client-server database.
A system that provides simultaneous access to the database for multiple clients
Define normalisation.
The formal process of optimally designing data tables by reducing data redundancy and repetition by converting them into normal forms.
What are the strengths of client-server databases?
- Consistency of database is maintained as only one copy is held
- Backup and recovery - as the database is centralised, a server admin can create backups so that if there’s a failure or loss of data the database can be recovered with little or no consequences
- Centralised security - limiting and controlling access levels can preserve the integrity of the data in the database
What are the techniques used to handle simultaneous database transactions?
- Record locking
- Timestamp ordering
- Commitment ordering
Describe record locking.
Prevents simultaneous access to objects in a database to prevent updates being lost or inconsistencies in the data arising
- Record is locked when a user retrieves it for editing or updating
- Anyone else attempting to retrieve it is denied access until the transaction is completed
