Databases

Question 1

Flat file DB

Answer 1

One single table contains all the information.

Question 2

Relational DB

Answer 2

Several linked tables, each representing an entity in the system. Each piece of data stored only once and then looked up via links from other tables. Tables and relationships between them are managed by a DBMS (DataBase Management System).

Question 3

Relational (v.s. Flat File)

Answer 3

Avoids data redundancy (storing same data item in more than one place) and inconsistency (two versions may be different).

Question 4

Advantages of ff

Answer 4

Simple to construct.

Question 5

Disadvantages of ff

Answer 5

Tedious duplicate entry of data, data redundancy, inconsistency, less integrity, hard sharing data, less control over user access

Question 6

Advantages of relational

Answer 6

Reduced data duplication and data redundancy. Improves data consistency, data integrity and security. Control access to data.

Question 7

Disadvantages of relational

Answer 7

More complex to set up than flat file. Need a good DBMS to ensure users only see the data relevant to them.

Question 8

Entity

Answer 8

A thing about which data is stored e.g. customer, employee, stock).

Question 9

Relationship

Answer 9

A link between two entities (1-1, 1-many…).

Question 10

Normalisation

Answer 10

Resolves many to many relationships and ensures data is stored in one place and in the best place.

Question 11

1NF

Answer 11

First Normal Form requires that there be no multivalued / repeating attributes.

Question 12

2NF

Answer 12

Second Normal Form requires that any non-key attribute be dependent on the entire key.

Question 13

3NF

Answer 13

The rule of Third Normal Form (3NF) states that no non-key attribute can be dependent on another non-key attribute.

Question 14

Showing table design

Answer 14

CAPITAL letters for the name of the table, Underline the key field, Overline the foreign keys.
e.g. PATIENTS (-PatientID-, Surname, Forename, Disease, WardID ).

Question 15

Primary key

Answer 15

A unique identifier in a table. Can consist of one or more fields. If more than one field it is known as a composite primary key.

Question 16

Composite key

Answer 16

2 or more attributes that uniquely identify an entity occurrence.

Question 17

Secondary key

Answer 17

An attribute which can be used to search for information or allows records to be sorted (accessed in a different order).
Secondary keys are indexed which allows for fast searching of the data in a database.

Question 18

Foreign key

Answer 18

Is a primary key from one table used as an attribute in another to link the tables. They represent many to one relationships.

Question 19

DBMS

Answer 19

SW handling the complexities of managing a database. May provide a user interface and use SQL to communicate with other programs. It provides different views of the data for different users.

Question 20

Tasks performed by DBMS

Answer 20

Finds data, adds new data, updates data, maintains indexes, enforces data integrity rule, manages access rights.

Question 21

DBMS Access rights

Answer 21

Controls what data each user is allowed to see. Also controls what they are allowed to do with the data (view, update etc).

Question 22

Database schema

Answer 22

Is the structure of the database that defines the objects in the database.

Question 23

Data dictionary

Answer 23

A file containing descriptions of data in a database, used by database managers when altering database structure, uses metadata to define the tables.

Question 24

Data dictionary - examples

Answer 24

Names of tables, characteristics (length, data type…), restrictions of values in columns, meaning of data columns, relationships between data, which programs can access the data. Identifies: primary and foreign keys, indexes and defines access rights.

Question 25

Data Description Language

Answer 25

DDL - is used to create the database, the tables, the users, their access rights, alter a table (e.g. define a foreign key). It records the attributes, data types, validation used and the relationship between entities, creates users grants access rights.

Question 26

DDL SQL Commands

Answer 26

CREATE DATABASE, CREATE TABLE, CREATE USER, GRANT, ALTER TABLE, ALTER TABLE_ ADD CONSTRAINT_…FOREIGN KEY, DROP.

Question 27

SQL DROP

Answer 27

Destroys an existing database, table, index, or view.

Question 28

Data Manipulation Lang.

Answer 28

DML - is used to query, sort, add data to the database and manipulate the data in it (update and delete).

Question 29

DML SQL Commands

Answer 29

SELECT, SELECT…FROM…ORDER BY, INSERT INTO, UPDATE, DELETE.

Question 30

SQL – CREATE

Answer 30

CREATE TABLE tablename (column_name data_type attributes…,column_name data_type attributes…,… )

Question 31

SQL – INSERT

Answer 31

INSERT INTO tablename (column_name,…) VALUES (value,…)

Question 32

SQL – SELECT

Answer 32

SELECT column,… FROM table,… WHERE condition GROUP BY group_by_expression HAVING condition ORDER BY order_exp

Question 33

SQL – DELETE

Answer 33

DELETE FROM table WHERE condition

Question 34

SQL – UPDATE

Answer 34

UPDATE table SET column=expression WHERE condition

Question 35

SQL – DROP

Answer 35

DROP TABLE tablename

Question 36

SQL – Views

Answer 36

You can use “CREATE VIEW” to create a virtual table from a SELECT statement. E.g. CREATE VIEW contactview AS (SELECT name,phone,zip FROM people,phonenumbers,address WHERE people.id=phonenumbers.id AND people.addressid=address.addressid)

Question 37

Reports

Answer 37

Are presentation of selected data usually in the form of a table. It may be defined in advance so the user does not need to set it up. Features of a report definition include: a query and a display order.