Databases

Question 1

What is a data base

Answer 1

collection of data items structured so that data can be searched and retrieved when necessary.

Question 2

What is a flat file structure

Answer 2

where the entire database is stored in a single file or table and there is one row for each record and one column for each field. This type of structure leads to data being repeated unnecessarily

Question 3

What is data duplication

Answer 3

Duplicate copies of data – or duplicate copies of non-key data.

Question 4

What is data inconsistency

Answer 4

The more often data must be keyed the more chance there is for errors in data entry

Question 5

What is poor data integrity

Answer 5

Data that is erroneous or inconsistent is not reliable.

Question 6

What is a relational database structure

Answer 6

allow data to be stored across a number of tables. The tables are linked or related together, using relationships, on common fields.

Question 7

Does a relational data base need multiple tables

Answer 7

Yes

Question 8

What is access rights

Answer 8

Most relational database systems provide access rights which can be assigned to different users depending on their role. Some of the operations that can be allowed or disallowed to a user are SELECT, INSERT, DELETE, ALTER, and CREATE.

Question 9

What is sql

Answer 9

Structured Query Language

When manipulating data in the database, SQL queries are formulated.

Question 10

What is a logical data model

Answer 10

The logical data model is produced during the design of the database, and is independent of the database software,

Question 11

What is a physical data model

Answer 11

A physical data model is produced during the implementation of the database and defines the physical structure of the database. It creates the table structures, along with the columns (fields) inside them, and the data type for each field. The primary key and any foreign or composite keys for each table are specified here as well as any appropriate validation rules.

Question 12

What is an attribute

Answer 12

Attribute
An individual data item within an entity. For example, Employee Name in an entity called EMPLOYEE. This is also known as a field.

Question 13

What is an entity

Answer 13

Identifies an object or subject about which data will be stored within the system. A person, place, thing, or event about which data is collected and held in a table. For example, an EMPLOYEE or a PROJECT. An entity is represented as a table.

Question 14

What is a primary key

Answer 14

A primary key is an attributes that uniquely identifies one record in a table. For example, the Employee No would uniquely identify each employee in a table.

Question 15

What is a composite key

Answer 15

Consists of two or more attributes that uniquely identifies one record in a table

Question 16

What is a relationship

Answer 16

A relationship is an association of entities. A relationship is established by a foreign key in one entity linking to the primary key in another.

Question 17

What different relationships can you have

Answer 17

One-to-One (1:1)
• One-to-Many (1:M)
• Many-to-Many (M:N)

Question 18

What is referential integrity

Answer 18

ensures that the data in one table does not contradict the data in another table. Specifically, every foreign key value which exists in a table must have a matching primary key value in a related table.

Question 19

What is an ER diagram

Answer 19

method to graphically model and design relational databases. ER diagrams will represent real world objects and the links, or relationships, between them.

Question 20

What is normalisation

Answer 20

Normalisation is a technique of organising the data in the database to eliminate problems such as data redundancy and data inconsistency and improving data integrity.

Question 21

What the rules of UNF

Answer 21

Select a name for the main entity – in this case PROJECT.
2. Each field contained within the entity is listed in brackets.
3. Primary keys are underlined.
4. Foreign keys are represented with *.
5. Repeating groups are contained within {}.
6. Derived attributes such as average Hourly Rate should not be included here but the developer may decide to store the value to improve performance. By derived, it means that the hourly rate is decided (or derived) based on the Pay Grade.

Question 22

What are the rules for 1 nf

Answer 22

all attributes must be atomic (Atomic means that the attribute cannot be further sub divided).
• entities must not have repeating groups.

Question 23

What are the rules of 2nf

Answer 23

• it fulfils the conditions for 1NF – all repeating groups are removed
• all non-key attributes (e.g. attributes which aren’t primary/foreign keys) in the relation are fully dependent on the primary key.

Question 24

What are the rules for 3 nf

Answer 24

it is in 2NF – there are no partial key dependencies
• there are no non-key dependencies.

Question 25

Advantage to normalised data

Answer 25

The resulting database will take up less storage space because the duplication of data is minimised. Data is only stored once. The advantages of this are
• No multiple record changes needed
• More efficient storage
• Simple to delete or modify details.
• All records in other tables having a link to that entry will show the change.
2. Information retrieval will be more efficient because data is structured effectively. Queries will be processed faster as complex queries can be carried out using SQL.
3. Less redundancy means less inconsistencies in data because data will only have to be entered once.
4. Data integrity will be increased.

Question 26

Disadvantages to normalised data

Answer 26

• Normalisation is a complex process required to create the database structure.
• Normalisation can generate more tables than an un-normalised database.
• More tables mean a more complex database and queries may be slow to run.
• It is necessary to assign more relationships to interact with larger numbers of tables.
• With more tables, setting up queries can become more complex.