11. databases and software development

Question 1

entity

Answer 1

is a category of object, person, event or thing of interest to an organisation about which data is to be recorded

Question 2

attributes

Answer 2

a characteristic specification that defines a property or feature about an entity stored in a database

Question 3

entity relationship modelling

Answer 3

a method of abstractly describing the data tables and the relationships between them visually. they can be used to reduce redundancy and construct a relational database.

Question 4

entity identifier

Answer 4

uniquely identifies the entity e.g. primary key

Question 5

primary key

Answer 5

a unique identifier that identifies each record in a table. the primary key is underlined.

Question 6

relational databases

Answer 6

a database where separate tables are made for each entity, and relationships between entities are represented by foreign keys

Question 7

foreign key

Answer 7

a linking attribute that joins two tables in a relational database by being a primary key in one and a foreign key in the other

Question 8

relationships between entities

Answer 8

• one-to-one
• one-to-many
• many-to-many

Question 9

relational database (TB).

Answer 9

is a collection of tables in which relationships are modelled by shared attributes

Question 10

normalisation

Answer 10

is a process used to come up with the best possible design for a relational database.

Question 11

tables should be organised in such a way that

Answer 11

• no data is unnecessarily duplicated
• data is consistent throughout the database
• the structure of each table is flexible enough to allow you to enter as many or as few items as required
• the structure should enable a user to make all kinds of complex queries relating data from different tables

Question 12

first normal form

Answer 12

contains no repeating attribute or groups pf attributes

Question 13

second normal form

Answer 13

if the table is in 1NF and contains no partial dependencies

Question 14

partial dependency

Answer 14

one or more of the attributes depends on only part of the primary key, which can only occur of the primary key is a composite key

Question 15

composite key

Answer 15

a primary key made up from two or more other keys

Question 16

third normal form

Answer 16

all attributes are dependent on the key, the whole key and nothing but the key

Question 17

the importance of normalisation

Answer 17

• easier to maintain and change a normalised database
• data integrity is maintained since there is no unnecessary duplication of data
• produce smaller tables with fewer fields, this results in faster searching and indexing operations as there is less data involved
• will not allow records in a table on the ‘one’ side of one-to-many relationship to be deleted accidently. this will prevent accidental deletion

Question 18

SQL

Answer 18

structured query language is a declarative language used for querying and updating tables in a relational database

Question 19

SELECT..FROM..WHERE

Answer 19

the SELECT statement is used to extract a collection of fields from a given table. the basic syntax of this statement is

SELECT list of fields to be displayed
FROM list the table or tables the data will come from
WHERE list of search criteria
ORDER BY list the fields that the results are to be stored on (default is ascending order)

Question 20

CREATE A NEW DATABASE TABLE

Answer 20

e.g.
CREATE TABLE employee
(
EmpID INTEGER NOT NULL PRIMARY KEY
EmpName VARCHAR (20) NOT FULL,
HireDate DATE,
Salary CURRENCY
)

Question 21

ALTERING A TABLE STRUCTURE

Answer 21

page 337-338 TB

Question 22

client-server database

Answer 22

a system that provides simultaneous access to the database for multiple clients

Question 23

advantages of client-server database

Answer 23

• the consistency is maintained because only one copy of the data is held (on the server) rather than a copy at each workstation
• an expensive resource (powerful computer and large database) can be made available to a large number of users
• access rights and security can be managed and controlled centrally
• backup and recovery can be managed centrally

Question 24

potential problems with client-server database

Answer 24

• allowing multiple users to simultaneously update table may cause one of the updates to be lost unless measures are taken to prevent this
• when an item is updated, the entire record (indeed the whole block in which the record is physically held) will be copied into the user’s own local memory area at the work station. when the record is saved, the block is written to the file server.

Question 25

record locks

Answer 25

is the technique of preventing simultaneous access to objects in a database in order to prevent updates being lost or inconsistencies in the data arising.

“record is locked whenever a user retrieves it for editing or updating. anyone else attempting to retrieve the same record is denied access until the transaction is completed or cancelled”

Question 26

problems with record locking

Answer 26

if two users are attempting to update two records, a situation can arise in which neither an proceed, known as deadlock

Question 27

sterilisation

Answer 27

this is a technique which ensures that transactions do not overlap in time and therefore cannot interfere with each other or lead to updates being lost. a transaction cannot start until the previous one has finished. it can be implemented using a timestamp ordering

Question 28

time stamp ordering

Answer 28

a technique to handle concurrent access by executing transactions from multiple users based on the value of their associated timestamp. each transaction receives a time stamp whenever it begins.
“whenever a transaction starts, it is given a timestamp, so that if two transactions affect the same object (for example record or table), the transaction with the earlier timestamp should be applied first”

Question 29

commitment ordering

Answer 29

this is another serialisation technique used to ensure that transactions are not lost when two or more users are simultaneously trying to access the same database object.
transactions are ordered in terms of their dependencies on each other as well as the time they were initiated. it can be used to prevent deadlock by blocking one request until another is completed.