4.10 SQL

Question 1

What is Normalisation, and how many forms of it are there?

Answer 1

Normalization is the process that is used to come up with the best possible design for a relational database, there are three forms:
1) 1st Normal Form

2) 2nd Normal Form
3) 3rd Normal Form

Question 2

How should a Normalised Database look like?

Answer 2

1) No Duplicated Information
2) Consistent Data throughout the Data-Base
3) Flexibility so as many items can be added and removed as needed
4) Queries should be able to be processed and an answer returned

Question 3

How do you know if a table is in First Normal Form

Answer 3

It contains NO repeating attributes or group of attributes

Question 4

How do you know if a table is in Second Normal Form

Answer 4

1) It must be ALREADY in 1st Normal Form
2) Contain No Partial Dependencies (remove attributes that depend on only part of the primary key by creating additional tables)

Question 5

How do you know if a table is in Third Normal Form?

Answer 5

1) It must ALREADY be in 1st and 2nd Normal Form

2) All attributes must be dependent on the key the WHOLE KEY and nothing but the key

Question 6

What is a Foreign Key?

Answer 6

A Foreign Key is an attribute that creates a join between two tables

Question 7

What types of Relationships between Entities are there?

Answer 7

1) One-to-One
2) One-to-Many
3) Many-to-Many

Question 8

What are the benefits of having a Normalised database over a Non-Normalised one?

Answer 8

1) Easier to maintain and modify
2) Data Integrity is maintained as there is no duplication of data
3) Customers who aren’t in the database cannot input their data
4) Faster searching and Sorting as the tables are smaller

Question 9

What is a Primary Key?

Answer 9

An entity identifier, which uniquely identifies every entity in the table

Question 10

What is an Entity?

Answer 10

An Entity is a category, object, person, event or thing that can be recorded in a Database
E.g Employee, Firm, Actor

Question 11

What is an Attribute?

Answer 11

An Attribute is a specific detail about an Entity E.g. A Dentist is an Entity but FirstName Surname of the Dentist are Attributes

Question 12

What is a Relational Database?

Answer 12

A Relational Database is where a separate table is created for each entity identified in the system

Question 13

What is a Composite Key?

Answer 13

A Composite Key uses more than one Attribute combined together

Question 14

What is an Entity?

Answer 14

An Entity is information that is stored in Relational Data-Base

Question 15

What is a Client-Server Database?

Answer 15

A Client-Server Database provides simultaneous access to the database for multiple clients

Question 16

What is Concurrent Access?

Answer 16

Concurrent Access occurs if updates are lost when if two clients edit a record at the same time, this problem can be solved using

1) Record Locks
2) Serialisation
3) TimeStamp Ordering
4) Commitment Ordering

Question 17

What are Record Locks?

Answer 17

A record lock is a lock on the data where as soon as a user with right access takes an item of data a lock is put on that data item so no user can save to that location

Question 18

What is Serialisation?

Answer 18

The Process of only allowing transactions on a particular database to take place at one time this process is managed by the DBMS

Question 19

What is Timestamp ordering?

Answer 19

Every Transaction that takes place has a read and write timestamp that indicated the last time the record was written to or recorded from

Question 20

What is Commitment Ordering?

Answer 20

The system looks at each command that has been asked to take place and works out which one should take precedence over other commands