Databases

Question 1

What is a Database?

Answer 1

A database is an organised, persistent collection of data.
Organised: Data organised into tables, records and fields
Persistent: Non-volatile using secondary storage
Data: Raw info with no context

Question 2

What does a database consist of?

Answer 2

Files, records and fields

Question 3

What operations can be performed on databases?

Answer 3

• Can be organised/sorted in numerous ways
• Displayed/Printed in a variety of styles/layouts
• Searched for info and updated
• Backed up easily and automatically
• Local or distributed
• Stored/Moved easily

Question 4

What is an attribute?

Answer 4

An individual fact, detail or characteristic of an entity (aka a field)

Question 5

What is an entity?

Answer 5

A thing, person, object or relationship about which data can be collected

Question 6

What is a flat file database?

Answer 6

A database containing a single table

Question 7

What is a primary key?

Answer 7

A unique piece of information within a table to identify a record

Question 8

What is a foreign key?

Answer 8

A field in the table which contains the value of a primary key in another table (links to one record)

Question 9

What is redundancy?

Answer 9

Duplication of data within a database

Question 10

What is a record?

Answer 10

A collection of fields/attributes about the same thing, person, object or relationship in a table

Question 11

What is a relational database?

Answer 11

A database with multiple linked tables

Question 12

What is a conceptual data model

Answer 12

a representation of the data requirements constructed in a way that is independent of any software used to construct the database

Question 13

Click to view a relationship example

Answer 13

e.g.
Entities:

Student, Teacher, Class, Subject, Classroom…

Attributes:

Student: first name, second name, DOB, student number, year group, form ▪ Teacher: first name, last name, Subject, Teacher ID, DOB
Subject: Subject ID, Requirements, Max Intake
Classroom: Room ID, capacity, room type, facilities
Class: Class ID, Classroom ID, Subject ID, Teacher ID, Period, Time

Relationships:

Teacher teaches Subject
Student attends Class

Entities in a system like this are always singular. The entity describes the characteristics of one instance of the entity, a so-called entity occurrence.

Question 14

What shape denotes an entity set?

Answer 14

A rectangle

Question 15

What shape denotes an attribute?

Answer 15

An oval

Question 16

What shape denotes a relationship?

Answer 16

A diamond

Question 17

Primary Key Attributes (PK)

Answer 17

uniquely identify each entity occurrence, it is a field that is unique for each record within a table

Question 18

Foreign Key Attributes (FK)

Answer 18

are found on the ‘many’ (or ‘one’ in a one-to-one) side of a relationship and always take the same values as the primary key attribute in a linked entity. They define the relationship.

Question 19

Non-Key Attributes

Answer 19

describe characteristics of the entity

Question 20

Problems with many to many relationships? how to solve them?

Answer 20

• Cause data redundancy (repeating data)
• Can cause error if implemented within a database software
  To solve this, a bridging table can be added in between the two tables, called an associative entity which holds details of a single occurrence of the relationship between the two other entities

Question 21

Put this list into database notation called Customer with CustomerId as the primary key;
-Customer ID
-Title
-Forename
-AddressLine1
-Town

Answer 21

tblCUSTOMER(CustomerId, Title, Forename, AddressLine1, Town)
—————–

(Underline CustomerId as it is the PK, overline any FK)

This is a simple flat data structure

Question 22

Problems with flat file data structures

Answer 22

• Inefficiency
• Data Redundancy
• Data Inconsistency

Question 23

Degree

Answer 23

Number of columns in a table

Question 24

Attribute/field

Answer 24

1 column in a table

Question 25

Domain

Answer 25

Allowed values

Question 26

Relation

Answer 26

Whole table

Question 27

Tuple

Answer 27

1 row

Question 28

Normalisation

Answer 28

Normalisation is the process of organising data in a database, through converting a single flat-file entity into multiple separate entities/relations/tables. Normalisation also includes the removal of redundant data

Question 29

1st Normal Form

Answer 29

Contains only atomic values (all or nothing) ; NOT [red, green] à [Red],[Green] I.e. each field (Colum) only has one value - There are no repeating groups [Author1], [Author2], [Author3]) - Each row (Tuple/Record) holds unique set of data After 1NF data has been flattened This may create issues because of the duplicated data: - Insertion Anomaly: Cannot insert due to requiring certain attributes for the PL - Update Anomaly: If the name of something is altered, many changes would be req. - Deletion Anomaly: Losing information not intended when certain attribute is deleted

Question 30

2nd Normal Form

Answer 30

(in 1NF) and no partial dependencies on anything but the primary key. Each column/field depends on the whole of the primary key

Question 31

3rd Normal Form

Answer 31

(in 2NF) and no transitive dependencies (Non primary attribute is dependent on another non primary attribute) All data items must depend on nothing but the key The primary key must fully define all non-key fields/column, and non-key fields/columns must NOT depend on any other Key but the primary key.

Question 32

What is a query language?

Answer 32

A query language is used when a user wants to search a database Extract selected data from a database by stating criteria E.g. SQL (Structured Query Language) SQL can also create tables

Question 33

Data Definition Language (DDL)

Answer 33

Provides commands that allow you to define and modify a database and its components (e.g. tables, relationships)

Question 34

Data Manipulation Language (DML)

Answer 34

Provides commands that allow you to insert, update and delete data from a database

Question 35

Data Query Language (DQL)

Answer 35

Provides commands that allow you to retrieve data from a database

Question 36

SQL Operators

Answer 36

= Equal > Greater than < Less than >= Greater than or equal <= Less than or equal <>, != not equal BETWEEN - Between a range LIKE - Search for a pattern IN - Specify multiple possible values ASC - Ascending order DESC - Descending order * - All

Question 37

SQL Commands

Answer 37

SELECT - Extracts data UPDATE - Updates data DELETE INSERT INTO - Inserts new data CREATE DATABASE ALTER DATABASE CREATE TABLE ALTER TABLE DROP TABLE CREATE INDEX DROP INDEX

Question 38

CHAR vs VARCHAR

Answer 38

CHAR; Stores values in fixed lengths Max 255 characters Used when sizes of column data entries are consistent VARCHAR; Values variable length long, 1 byte or 2 byte length prefix Max 65535 characters Used when sizes of data entries change considerably

Question 39

Create SQL table: Called Manager with fields; - ManagerId - Firstname - Surname - Date of Birth

Answer 39

ANSWER CREATE TABLE Manager { ManagerID CHAR(5) NOT NULL, Firstname CHAR(20) NOT NULL, Surname CHAR(20) NOT NULL, DOB DATE NOT NULL, PRIMARY KEY (Manager ID) }

Question 40

Referential Integrity

Answer 40

If a value appears in a foreign key table, it must also appear in the primary key in another table. If a primary key of a record in one table is changed then it must be updated in all tables where it is used as a foreign key. To maintain referential integrity you need associations to avoid orphaned records

Question 41

Client Server Database

Answer 41

Provides simultaneous access to the database for multiple clients through Database Management Systems (DBMS) with server w software running on network server and client software

Question 42

Concurrent access

Answer 42

can result in the problem of updates being lost if two clients edit a record at the same time. This can be managed using record locks, serialisation, timestamp ordering and commitment ordering

Question 43

Record Lock

Answer 43

A technique that prevents simultaneous access to objects in database in order to prevent update being lot or arising data inconsistencies

Question 44

Serialisation

Answer 44

Ensures that transactions do not overlap in time and therefore cannot interfere with each other or lead to updates being lost and implemented using the Timestamp ordering

Question 45

Timestamp ordering

Answer 45

A timestamp is given to each transaction so that if 2 transactions affect the same object (record) the transaction with the darker timestamp would be applied first. To ensure data are not lost every object in a database has a read and write timestamp which are updated whenever an object is read or written

Question 46

Commitment ordering

Answer 46

Serialisation technique ensures safe transaction without any loss of data when >2 users are simultaneously accessing the same object in a database and ordered in terms of their dependencies on each other and the time they were initiated