DATABASED 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,
FirstName VARCHAR(20) NOT NULL,
Surname VARCHAR (50) NOT NULL,
PricePaid CURRENCY
Height FLOAT (5, 2)
PRIMARY KEY (CustomerID)
#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
Outline serialisation and timestamp ordering
- Serialisation ensures that transactions do not overlap in time
- Every object in the database has a read and a write timestamp which are updated when an object is read or written
- When a user tries saving an update, if the read timestamp is not the same as the start of the transaction, we know that another user has accessed it and will cancel the update
Explain commitment ordering.
- Transactions are ordered in terms of their dependencies on one another as well as the time they were initiated
Outline the system life cycle approach.
Each stage has to be completed before the next one can begin
Outline the agile approach.
- More modern and flexible approach
- Gets feedback from user throughout development of a system
- Adjustments can be made at each stage
Benefits of agile approach.
- User is not sure what is possible so they cannot clearly define requirements
- Analyst may misunderstand the wishes of the user/client
- Usually it is easier to identify drawbacks with the prototype rather than expressing what you expect in advance
- Expensive and time-consuming to make changes to a final product
