Explain the difference between a table and a relational database.
A table is a collection of related data held in a structured format within a database. It consists of fields (columns), and rows. A database is an organized collection of data.
Explain the use of a primary key within a table.
It is a unique identifier used to identify a record of data within a table.
Draw an entity relationship diagram for the following situation:
- A receipt has one customer
- A customer can have many receipts
- A receipt has one product
- A product can be part of many receipts
- A receipt was created by one staff member
- A staff member can create many receipts
Explain the difference between 1st normal form and 2nd normal form.
1NF - Atomic Data Test
If a table has a primary key it is said to be in First Normal form if the table does not have repeating groups of attributes. All attributes within the table need to be dependent only on the primary key.
2NF - Partial Dependence Test
For a table to be in Second Normal form it must first be in First Normal (1NF) Form and then contain no data that is dependent on only part of the Primary Key
What are the requirements for data to be in the 3rd normal form?
A table is in third normal form when the following conditions are met:
- It is in second normal form.
- All nonprimary fields are dependent on the primary key.
List 2 advantages of having data in the 3rd normal form.
- The database does not have redundant data, it is smaller in size, therefore storage requirements are reduced.
- There is less data to search through, therefor queries will run faster.
- There is no data duplication, allowing for better data integrity and less risk of mistakes.
- Because there is no data duplication there is less chance of storing two or more different copies of the data
What does DDL stand for, and when is it used?
DDL = Data definition language.
It is used to interact with tables and relational databases. It is posible to create, edit, and delete tables and databases via DDL commands.
What are two security considerations when planning a project involving SQL database(s)?
- SQL Injection
- Access to SQL Server
- Password hashing (+ salting)
*
What are some counter measures which can be put in place to help safeguard against SQL attacks?
- Limiting permissions on accounts able to execute queries to databases. (Pricipal of least privilege.)
- Filtering SQL commands for potientially malicious symbols (eg: ‘, “)
- Limiting the amount of queries which can be executed by an account per minute to a realistic ammount for your application so that if control is lost the attackers are still limited.
Explain what is meant by adding a salt to a password.
The process of salting involves adding a string of random characters to the front of a field before the field is hashed, this is to ensure that ranbow attacks can’t happen.
What is the difference between a composite and foreign key and when are each used?
Composite Key - Where a collection of attributes uniquely identify a tuple rather than just one. Used when the collection of attributes would be unique, when combined they negate the need for a primary key.
Foreign Key - An attribute in one table that is a primary key in another table, often there to reference to said table later.
Write a DDL statement to create the following table:
Customer(CustomerID, CustomerName, Address, Postcode, Email)
CREATE TABLE Customer( CustomerID INTEGER PRIMARY KEY
NOT NULL AUTO_INCREMENT, CustomerName VARCHAR(20), Address VARCHAR(50), Postcode VARCHAR(10), Email VARCHAR(30) )
