U8 Databases

Question 1

what is a File-based system?

Answer 1

Data stored in discrete files, stored on computer, and can be accessed, altered or removed by user

Question 2

Disadvantages of file based system:

Answer 2

• No enforced control on structure of files
• Data repeated in different files
• Sorting must be done manually
• Data may be in different format
• Can’t be multi user - can become chaotic
• Security not sophisticated; user accesses everything

Question 3

What is a database?

Answer 3

Collection of non-redundant, inter-related data

Question 4

What is a DBMS?

Answer 4

Software programs that allow databases to be constructed, defined and manipulated

Question 5

Features of DBMS:

Answer 5

Data management, Data dictionary, Data modelling, Data integrity, Data security

Question 6

What is data management?

Answer 6

Data stored in relational databases - tables in secondary storage

Question 7

What does data dictionary do and have in it?

Answer 7

Stores metadata about the database. Has field names, table name, primary/secondary keys etc.

Question 8

What is data modelling?

Answer 8

Analysis of data objects used in database and identifying relationships between them

Question 9

What is a logical schema?

Answer 9

Overview of database structure, models the problem and independent of any particular DBMS

Question 10

What is data integrity in DBMS?

Answer 10

Entire block copied to user’s area when being changed, saved back when done

Question 11

What is data security in DBMS?

Answer 11

Handles password allocation and verification, backups database automatically. controls user’s view by access rights of individuals or groups of users.

Question 12

What are the tools in a DBMS?

Answer 12

Developer interface and Query processor

Question 13

What is the developer interface?

Answer 13

Allows creating and manipulating database in SQL, rather than graphically

Question 14

What is the query processor?

Answer 14

Handles high-level enquiries. Parses, validates, optimises, and compiles/interprets a query which results in a query plan

Question 15

What is an entity?

Answer 15

Object/event which can be distinctly identified

Question 16

What is a table?

Answer 16

Contains group of related entities in rows and columns called an entity set

Question 17

What is a tuple?

Answer 17

Row or record in a relational database

Question 18

What is an attribute?

Answer 18

Field or column in a relational database

Question 19

What is a primary key?

Answer 19

Attribute or combination of them that relates 2 different tables

Question 20

What is a candidate key?

Answer 20

Attribute that can potentially be a primary key

Question 21

What is a foreign key?

Answer 21

Attribute or combination of them that can relate 2 different tables

Question 22

What is referential integrity?

Answer 22

Prevents users or applications from entering inconsistent data

Question 23

What is the secondary key?

Answer 23

Candidate keys not chosen as a primary key

Question 24

What is indexing?

Answer 24

Creating secondary keys on an attribute to provide full access when searching on that attribute

Question 25

Creating a database

Answer 25

CREATE DATABASE <database-name></database-name>

Question 26

Creating a table

Answer 26

CREATE TABLE <table-name> (…)</table-name>

Question 27

Changing a table

Answer 27

ALTER TABLE <table-name></table-name>

Question 28

Adding a primary key

Answer 28

PRIMARY KEY (field)
ADD <field-name>:<data-type></data-type></field-name>

Question 29

Adding a foreign key

Answer 29

FOREIGN KEY (field) REFERENCES <table>(field)

Question 30

Adding data to a table

Answer 30

INSERT INTO <table-name>(field1, field2, field3)
VALUES (value1, value2, value3)</table-name>

Question 31

Deleting a record

Answer 31

DELETE FROM <table-name>
WHERE <condition></condition></table-name>

Question 32

Updating field in a record

Answer 32

UPDATE <table-name>
SET <field-name> = <value>
WHERE <condition></condition></value></field-name></table-name>