Database

Question 1

What is a colum

Answer 1

A column is a field that has a single piece of data stored about an entity

Question 2

What is a row

Answer 2

A row is a record that is a complete set of data (fields) about an individual entity

Question 3

What is a table

Answer 3

Table or file is all of the known individual entities of the entity type

Question 4

What is a key field

Answer 4

It uniquely identifies a record / individual entity’s

Forms relationships

Question 5

What is a primary key

Answer 5

It’s a unique identifier for each individual record

Question 6

What is a foreign key

Answer 6

This is a primary key from another table in order to form relationships

Question 7

What is a flat file database

Answer 7

One single table where all the data is recorded for every transaction

Question 8

What are the problems with a Flat File Database

Answer 8

1: Unnecessary repetition of data
A) waste of resources e.g hardware space
B) take longer to complete: paying staff

2: Data inconsistency - The same piece of data has different values within the database
A) incorrect data
B) lose records/ error in processing

Question 9

What is a Relational Database

Answer 9

Each table stores data about one entity and are limited together through relationships which are formed by primary and foreign keys

Question 10

Advantages of a Relational Database

Answer 10

1: No data redundancy: unnecessary repetition of data
- each entities data is written once in the relevant table
- Use key field to refer to it

2: Improved data consistency: each piece of data is the same throughout entire database

Question 11

What is a composite key

Answer 11

Primary key that consists of more than 1 field. Some or all of those field may be primary keys in another table

Question 12

What is an attribute

Answer 12

It is a single piece of data for an entity e.g field name ‘member id’

Question 13

What is an entity?

Answer 13

It is an object which information is stored e.g student

Question 14

What is the Logical Data Model

Answer 14

It is in the design stage
It identifies the entities and the relationships between them using keys
It identifies attributes
It doesn’t describe the physical structure

Question 15

What is the Physical Data Model

Answer 15

It is described from the logical data model
Produced during implementation stage
It creates the table structures such as
-Column Names
-Data type of each field
- Validation rules
The physical implementation differs depending on the actual database management system used

Question 16

What is unnormalised data

Answer 16

It is a list of all the data fields much like a flat file database

Question 17

Explain what 1NF, 2NF and 3NF are

Answer 17

1NF - A table is in 1Nf if all attributes are atomic and there are no repeating fields

2NF - A table is in 2NF ( and only if) it is in 1NF and all non key attributes are depends if in whole primary key

3NF - A table is in 3NF (and only if)it is 2NF and there are no functional dependencies between non key fields

Question 18

How do you normalize data

Answer 18

1NF - Remove All repeating fields to new table with a copy of the private key

2NF - Fields that are partially dependent on primary key
Part of the primary key which they are dependent

3NF - Fields with a dependency on any non key field
The non key field on which they are dependent

Question 19

What are the advantages and disadvantages of normalization

Answer 19

Advantages

• More efficient due to less redundancy (less hard disk>more storage space)
• More efficient better data structure, faster processing quicker retrieval of information
• Less redundancy means less inconsistency in data because data will only be entered once
• Data integrity is improved

Disadvantage

• Complex process to create database structure
• Generates more tables than de-normalized tables can lower efficiency
• Complexity, hard to use the database

Question 20

What is referential integrity

Answer 20

Referential Integrity- Cannot enter a value for a foreign key field, if that value is not present in the referenced table (eg cannot add invoice in the invoice table linked to customer no 47 if there isn’t a number 47 in customer table)
A primary value cannot be changed or deleted if it is referred to I.e. the same value is listed on a foreign key in another table

Question 21

What is a Data Dictionary

Answer 21

It is a list of all of the fields in the database contains all of the ‘meta data’ about each field e.g
Field name
Validation rules
Data type

Question 22

What is the purpose of a Data dictionary

Answer 22

A data dictionary is useful when lots of people are working on the same system, we have consistent naming throughout the database
E.g. Foreign key in one table used the same name as the primary key in its linked field

Question 23

What is the SQL code for creating a table

Answer 23

CREATE TABLE table_name (col1, col2)

Question 24

What is the SQL code for retrieve (select)

Answer 24

SELECT Field
FROM table name
WHERE criteria (search criteria)
ORDER BY col1 ASC

Ascending*
SELECT author title 
FROM Books
WHERE category = “Science”
ORDER BY author DESC

Question 25

What is the SQL code of adding (inserting) data into a table

Answer 25

INSERT INTO table name

VALUES (data being inserted) (“Example”, “Test”)

Question 26

What is the SQL code for updating data

Answer 26

UPDATE table name
SET col1 = “work” what data is changing
WHERE col2 = “test”

Example
UPDATE tbl customer
SET status = “suspended”
WHERE balance <=0

Question 27

What is the SQL code for Deleting records

Answer 27

DELETE FROM table name

WHERE col1 = “test”

Question 28

What is the difference between SQL and QBE (advantages and disadvantages)

Answer 28

Structured Query Language (SQL)
Insert code in specified format
… using SQL commands to create/use database e.g. CREATE, INSERT INTO, UPDATE etc
Adv- Creates more complex and powerful queries
Disadvantages- Complex- only suitable for experts, big learning centre

Query by Example (QBE)
Use graphical interface 
...Filling in forms
... Inputting criteria 
...Selecting options 
Adv- Easy to use for non experts 
Quickly create queries 
Disadvantages- Limited to options provided by QBE interface (less powerful)

Question 29

What is a query

Answer 29

All instructions are queries
Create table
Forming relationships